6NHdrDkx

11
2qwertyuiopasdfghjklzxcvbnmqwertyui
3opasdfghjklzxcvbnmqwertyuiopasdfgh
4jklzxcvbnmqwertyuiopasdfghjklzxcvb
5nmqwertyuiopasdfghjklzxcvbnmqwer
6tyuiopasdfghjklzxcvbnmqwertyuiopas
7dfghjklzxcvbnmqwertyuiopasdfghjklzx
8cvbnmqwertyuiopasdfghjklzxcvbnmq
9wertyuiopasdfghjklzxcvbnmqwertyuio
10pasdfghjklzxcvbnmqwertyuiopasdfghjk
11lzxcvbnmqwertyuiopasdfghjklzxcvbnm
12qwertyuiopasdfghjklzxcvbnmqwertyuio
13pasdfghjklzxcvbnmqwertyuiopasdfghj
14klzxcvbnmqwertyuiopasdfghjklzxcvbn
15mqwertyuiopasdfghjklzxcvbnmrtyuio
16pasdfghjklzxcvbnmqwertyuiopasdfghj
17klzxcvbnmqwertyuiopasdfghjklzxcvbn
18mqwertyuiopasdfghjklzxcvbnmqwerty
19Reference Manual
20for
21Database Systems Lab
22School of Computing Science and Engineering
23Vellore-632014,Tamil Nadu
24Database Systems Lab Manual
25CONTENTS
26SNo
27Topics
28Page Number
291.
30BASIC SQL COMMANDS
311
322.
33CONSTRAINTS
3413
353.
36OPERATORS IN SQL*PLUS
3718
384.
39FUNCTIONS:SINGLE ROW AND GROUP FUNCTIONS
4023
415.
42JOINS
4334
446.
45VIEWS
4645
477.
48PL/SQL
4950
508.
51PL/SQL BLOCK SYNTAX AND GUIDELINES
5258
539
54CONTROL STRUCTURES IN PL/SQL
5567
5610
57CURSORS
5873
5911
60EXCEPTIONS
6180
6212
63PL/SQL BLOCK TYPES:Anonymous blocks and Subprograms
6485
6513
66PACKAGES
6793
6814
69TRIGGERS
7098
71Database Systems Lab Manual
72SQL STATEMENTS
73SQL statements are classified as follows:
74Data Retrieval Statement:
75SELECT is the data extracting statement which retrieves the data from the database.
76Data Manipulation Language (DML):
77This language constitutes the statements that are used to manipulate with the data. It has three commands, which are INSERT, UPDATE and DELETE.
78Data Definition Language (DDL):
79This is the language used to define the structure of the tables. It sets up, changes, and removes data structures from the tables. It uses 5 commands, which are CREATE, ALTER, DROP, RENAME and TRUNCATE.
80Data Transaction Language (DTL):
81This is the language used to do undo and redo the transaction performed in the database. The commands are Commit, Rollback, and Save Point
82Data Control Language:
83This language is used to sanction the rights to the users to use the other user’s database objects. The commands are Grant and Revoke
84Consider the following schema based on which the example queries are discussed in this manual.
85BASE SCHEMA
86EMPLOYEE
87Name Type
88-------------------------- ----------------------
89EMPLOYEE_ID NUMBER(3)
90FIRST_NAME VARCHAR2(10)
91LAST_NAME VARCHAR2(10)
92MGR NUMBER(4)
93HIRE_DATE DATE
94JOB_ID VARCHAR2(10)
95SALARY NUMBER(10)
96Database Systems Lab Manual
97COMMISION NUMBER(8)
98DEPTNO NUMBER(2)
99DEPARTMENT
100Name Type
101--------------- -----------------
102DEPTNO NUMBER(2)
103DNAME VARCHAR2(14)
104LOC VARCHAR2(13)
105BONUS
106Name Type
107---------------- -------------------
108ENAME VARCHAR2(10)
109JOB VARCHAR2(9)
110SAL NUMBER(10,2)
111COMM NUMBER(10)
112JOBGRADE
113Name Type
114----------------- ---------------------
115JOB_ID VARCHAR2(10)
116GRADE NUMBER
117LOSAL NUMBER
118HISAL NUMBER
119DATA TYPES IN ORACLE:
120Data Type
121Description
122VARCHAR2(size)
123Variable-length character data
124CHAR(size)
125Fixed-length character data
126NUMBER(p,s)
127Variable-length numeric data
128DATE
129Date and time values
130LONG
131Variable-length character data up to 2 gigabytes
132CLOB
133Character data up to 4 gigabytes
134RAW and LONG RAW
135Raw binary data
136Database Systems Lab Manual
137BLOB
138Binary data up to 4 gigabytes
139BFILE
140Binary data stored in an external file; up to 4 gigabytes
141ROWID
142A 64 base number system representing the unique address of a row in its table
143ORACLE 9I TABLE STRUCTURES
144 Table can be created at any time
145 No need to specify the size of table, the size is ultimately defined by the amount of space allocated to the database as a whole.
146 Tables can have up to 1000 columns
147NAMING RULES
148Table names and Column names
149ï‚· Must begin with a letter
150ï‚· Must be 1-30 characters long
151ï‚· Must contain only A-Z,a-z,0-9,_,$,#
152ï‚· Must not duplicate the name of another object owned by the same user
153ï‚· Must not be a reserved word
154Data Definition Language (DDL)
155The following are the DDL Commands:
1561. Create 2. Alter 3. Drop 4. Truncate 5. Rename
1571. a. Creating a table
158Syntax:
159Create table <Table Name>
160( <Field1> <Data Type> <(width) <constraints> ,
161<Field2> <Data Type> <(width)> <constraints>,
162..................................);
163Database Systems Lab Manual
164Example:
165SQL> create table employee
166( employee_id number(3),
167first_name varchar2(10),
168last_name varchar2(10),
169mgr number(4),
170hire_date date,
171job_id varchar2(10),
172salary number(10),
173commision number(8),
174deptno number(2));
175Output:
176Table created.
177Example:
178SQL> create table department
179(deptno number(2),
180dname varchar(14),
181loc varchar(13));
182Output:
183Table created.
184Note:
185Other tables can be created in the similar way.
186b. To view the Structure of the table, desc command is used
187Database Systems Lab Manual
188SQL> desc employee;
189Name Null? Type
190-------------------------------------- -------- ---------------
191EMPLOYEE_ID NUMBER(3)
192FIRST_NAME VARCHAR2(10)
193LAST_NAME VARCHAR2(10)
194MGR NUMBER(4)
195HIRE_DATE DATE
196JOB_ID VARCHAR2(10)
197SALARY NUMBER(10)
198COMMISION NUMBER(8)
199DEPTNO NUMBER(2)
2002. Alter Table Statement:
201Alter command is used to perform the following action on the table:
202a. Adding column in the existing table
203b. Increasing and decreasing the column size and changing data types
204c. Dropping column
205d. Renaming the column
206e. Adding and dropping constraints to the table( discussed in constraints topics)
207f. Enabling & disabling constraints in the table( discussed in constraints topics)
208a. To Add a column to the table (structure)
209Add option is used to add a new column
210Syntax:
211Alter Table <Table-Name> Add <Field Name> <Type> (width);
212Example:
213SQL> alter table employee add address varchar2 (20);
214Output:
215Table altered.
216b. To Modify a field of the table
217 Increase the width or precision of numeric column
218Database Systems Lab Manual
219 Increase the width of numeric or character columns
220 Decrease the width of the column only if the column contains only null values or if the table has no rows
221 Change the data type only if the column contains null values
222Syntax:
223Alter Table <tablename> MODIFY ( <column name > < newdatatype>);
224Example:
225SQL> alter table employee modify address varchar2 (10);
226Output:
227Table altered.
228c. To Drop a field of the table
229Drop option is used to delete a column or remove a constraint
230Syntax:
231Alter Table <tablename> DROP COLUMN < column name>;
232Example:
233SQL> alter table employee drop column address;
234Output:
235Table altered.
236d.To rename a column
237Syntax:
238ALTER TABLE <tablename> RENAME COLUMN <oldcolumnname> TO
239<newcolumn name>
240Example:
241SQL> alter table employee rename column mgr to manager;
242Output:
243Table altered.
244ï‚· To Drop a table - Deletes a Table along with all contents
245Syntax:
246Drop Table <Table-Name>;
247Database Systems Lab Manual
248Example:
249Drop Table Student_table;
250Output:
251Table Dropped
252ï‚· To Truncate a table - Deletes all rows from a table ,retaining its structure
253Syntax: Truncate Table <tablename>
254Example:
255SQL> truncate table employee;
256Output:
257Table truncated.
258g. To rename a table- Renames a table with new name
259Syntax:
260Rename <oldtablename> To <newtablename>
261Example:
262SQL> rename employee to emp;
263Output:
264Table renamed
265Data manipulation Language (DML)
266The following are the DML Commands: 1. Insert 2. Delete 3. Update 4. Select
267Insert command is used to load data into the table.
268a. Inserting values from user
269Syntax:
270Insert into <tablename> values ( val1,val2 …);
271Example:
272SQL> insert into department values(10,'accounts','chennai');
273Output:
2741 row created.
275Database Systems Lab Manual
276b. Inserting values for the specific columns in the table
277Syntax:
278Insert Into <Table-Name> (Fieldname1, Fieldname2, Fieldname3,..) Values (value1, value2, value3,..);
279Example:
280SQL> insert into department (deptno,dname)values(20,'finance');
281Output:
2821 row created.
283c. Inserting interactively(Inserting ,ultiple rows by using single insert command)
284Syntax:
285Insert Into <tablename> Values( &<column name1> , &<column name2> …);
286Example:
287SQL> insert into employee values(&empid,'&fn','&ln',&mgr,'&hdate','&job',&sal,
288&comm,&dept);
289Enter value for empid: 111
290Enter value for fn: Smith
291Enter value for ln: Ford
292Enter value for mgr: 222
293Enter value for hdate: 21-jul-2010
294Enter value for job: J1
295Enter value for sal: 30000
296Enter value for comm: 0.1
297Enter value for dept: 10
298old 2: &comm,&dept)
299new 2: 0.1,10)
300Output:
3011 row created.
302Note: Column names of character and date type should be included with in single quotation.
303ï‚· Inserting null values
304Database Systems Lab Manual
305Syntax:
306Insert Into <tablename> Values ( val1,’ ‘,’ ‘,val4);
307Example:
308insert into department values( ‘101’,’’,chennai);
309Output:
3101 row created.
3112. To Delete rows from a table
312Syntax:
313Delete from <table name> [where <condition>];
314Example:
315a) to delete all rows:
316SQL> delete from department;
317Output:
31889 rows deleted.
319b) conditional deletion:
320SQL> delete from department where loc='chennai';
321Output:
3221 row deleted.
3233. Modifying (Updating) Records:
324a. Updating single column
325Syntax:
326UPDATE <table name> Set <Field Name> = <Value> Where <Condition>;
327Example:
328SQL> update department set loc='Hyderabad' where deptno=20;
329Output:
3301 row updated.
331Note: Without where clause all the rows will get updated.
332b. Updating multiple column [while updating more than one column, the column must be separated by comma operator]
333Database Systems Lab Manual
334Example: SQL> update department set loc='Hyderabad', dname= ‘cse’ where deptno=20;
335Output:
3361 row updated.
3374. Selection of Records [Retrieving (Displaying) Data:]
338Syntax:
339Select <field1, field2 …fieldn> from <table name> where <condition>;
340Example:
341a) SQL> select * from department;
342Output:
343DEPTNO DNAME LOC
344---------- -------------- -------------
34510 accounts chennai
34620 finance Hyderabad
34730 IT Bangalore
34840 marketing chennai
349Example:
350b) SQL> select dname, loc from department;
351Output:
352DNAME LOC
353-------------- -------------
354accounts chennai
355finance Hyderabad
356IT Bangalore
357marketing Chennai
358ï‚· Using Alias name for a field
359Syntax:
360Select <col1> <alias name 1> , <col2> < alias name 2> from < tab1>;
361Example:
362SQL> select dname, loc as location from department;
363Output:
364DNAME LOCATION
365-------------- -------------
366accounts chennai
367Database Systems Lab Manual
368finance Hyderabad
369IT Bangalore
370marketing Chennai
371ï‚· With distinct clause [Used to retrieve unique value from the column]
372Syntax:
373Select distinct <col2> from < tab1>;
374Example:
375SQL> select distinct loc from department;
376Output:
377LOC
378-------------
379chennai
380Bangalore
381Hyderabad
382ï‚· Creating Table using subquery
383Syntax:
384Create table <new _table_name> as Select <column names> from <old_table_name>;
385Example:
386SQL> create table copyOfEmp as select * from employee;
387Output:
388Table created.
389ï‚· To view the contents of new Table
390SQL> select * from copyofemp;
391Output:
392EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER HIRE_DATE JOB_ID SALARY COMMISION DEPTNO
393111 Smith Ford 222 21-JUL-10 J1 30000 0.1 1 0
394ï‚· To create a table with same structure as an existing table
395Syntax:
396Database Systems Lab Manual
397Create table <new _table_name> as Select <column names> from<old_table_name>
398where 1=2;
399Example:
400create table copyOfEmp2 as select * from employee where 1=2;
401Output: Table created.
402SQL> select * from copyofemp2;
403Output:
404no rows selected
405SQL> desc copyofemp2;
406Output:
407Name Null? Type
408----------------------------------------- -------- -----------------
409EMPLOYEE_ID NUMBER(3)
410FIRST_NAME VARCHAR2(10)
411LAST_NAME VARCHAR2(10)
412MANAGER NUMBER(4)
413HIRE_DATE DATE
414JOB_ID VARCHAR2(10)
415SALARY NUMBER(10)
416COMMISION NUMBER(8)
417DEPTNO NUMBER(2)
418Note: Only structure of table alone is copied and not the contents.
419ï‚· Inserting into table using a subquery
420Syntax :
421Insert into <new_table_name> (Select <columnnames> from <old_table_name>);
422Example:
423SQL> insert into copyofemp2 (select * from employee where employee_id > 100);
424Output:
42550 rows created.
426Database Systems Lab Manual
427Constraints
428ï‚· Constraints enforce rules on the table whenever rows are inserted, updated and deleted from the table.
429ï‚· Prevents the deletion of a table if there are dependencies from other tables.
430ï‚· Name a constraints or the oracle server generate name by using SYS_cn format.
431ï‚· Define the constraints at column or table level. constraints can be applied while creation of table or after the table creation by using alter command.
432ï‚· View the created constraints from User_Constraints data dictionary.
433Constraints Types
434CONSTRAINT
435DESCRIPTION
436NOT NULL
437Specifies that a column must have some value.
438UNIQUE
439Specifies that columns must have unique values.
440PRIMARY KEY
441Specifies a column or a set of columns that uniquely identifies as row. It does not allow null values.
442FOREIGN KEY
443Foreign key is a column(s) that references a column(s) of a table.
444CHECK
445Specifies a condition that must be satisfied by all the rows in a table.
446Database Systems Lab Manual
4471. Creating Constraints without constraint name
448Syntax:
449CREATE TABLE < tablename> (
450<column name 1> < datatype>,
451<column name 2> < datatype> UNIQUE ,
452<column name 3> < datatype> ,
453PRIMARY KEY ( <column name2>)
454);
455Example:
456CREATE TABLE emp_demo2
457( employee_id NUMBER(6) PRIMARY KEY,
458first_name VARCHAR2(20) NOT NULL,
459last_name VARCHAR2(25) NOT NULL,
460email VARCHAR2(25) UNIQUE,
461phone_number VARCHAR2(20) UNIQUE,
462job_id VARCHAR2(10),
463salary NUMBER(8,2) CHECK(SALARY>0),
464deptid NUMBER(4)
465) ;
4662. Creating constraints with constraint name
467Syntax:
468CREATE TABLE < tablename1> (
469<column name 1> < datatype> CONSTRAINT <constraint name1> UNIQUE,
470<column name 2> < datatype> CONSTRAINT <constraint name2> NOT NULL,
471constraint < constraint name3 > PRIMARY KEY ( <column name1>),
472Database Systems Lab Manual
473constraint <constraint name4> FOREIGN KEY (<column name2>) REFERENCES <tablename2> (<column name1>)
474);
475Example:
476CREATE TABLE emp_demo3
477( employee_id NUMBER(6) CONSTRAINT emp_eid PRIMARY KEY,
478first_name VARCHAR2(20),
479last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL,
480email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL,
481phone_number VARCHAR2(20),
482job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,
483salary NUMBER(8,2) CONSTRAINT emp_salary_nn NOT NULL,
484deptid NUMBER(4), CONSTRAINT emp_dept FOREIGN KEY(deptid)
485REFERENCES department(deptid) ,
486CONSTRAINT emp_salary_min CHECK (salary > 0) ,
487CONSTRAINT emp_email_uk UNIQUE (email)
488) ;
4893. With check constraint
490Syntax:
491CREATE TABLE < tablename> (
492<column name1 > < datatype> ,
493<column name 2> < datatype>,
494CHECK ( < column name 1 > in ( values) )
495CHECK ( < column name 2 > between <val1> and <val2> ) );
496Example:
497CREATE TABLE emp_demo4
498Database Systems Lab Manual
499( emp_id NUMBER(6),
500emp_name VARCHAR2(15),
501salary NUMBER(10) CHECK (salary between 1000 and 10000)
502);
503Adding Constriants
504Constraints can be added after the table creation by using alter command
505Syntax: Add constraints
506ALTER TABLE <tablename> ADD CONSTRAINT <constraint_name> constriant_type (<column name>);
507Examples:
508ALTER TABLE emp_demo4 ADD CONSTRAINT con_pk1 PRIMARY KEY(emp_id);
509ALTER TABLE emp_demo4 ADD CONSTRAINT con_emp_uk UNIQUE(phoneno);
510ALTER TABLE emp_demo4 ADD CONSTRAINT con_empfk FOREIGN KEY(DNO) REFERENCES department(dno);
511ALTER TABLE emp_demo4 ADD CONSTRAINT con_emp_ck CHECK ( salary >0 );
512ALTER TABLE emp_demo4 MODIFY (<Column name> <datatype> CONSTRAINT constraint_name NOT NULL);
513Drop Constraints
514Syntax
515ALTER TABLE <tablename> DROP CONSTRAINT < constraint name >;
516Drop the unique key on the email column of the employees table:
517e.g ALTER TABLE employees DROP UNIQUE (email);
518CASCADE Constraints
519The CASCADE Constraints clause is used along with the Drop Column Clause.
520Database Systems Lab Manual
521• A foreign key with a cascade delete means that if a record in the parent table is deleted,
522then the corresponding records in the child table will automatically be deleted. This is
523called a cascade delete.
524• A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
525Syntax:
526CREATE TABLE table_name
527(column1 datatype null/not null,
528column2 datatype null/not null,
529...
530CONSTRAINT fk_column
531FOREIGN KEY (column1, column2, ... column_n)
532REFERENCES parent_table (column1, column2, ... column_n)
533ON DELETE CASCADE
534);
535Example:
536CREATE TABLE supplier
537(supplier_id number(10)not null,
538supplier_namevarchar2(50)not null,
539contact_namevarchar2(50),
540CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));
541CREATE TABLE products
542(product_id number(10)not null,
543suppl_id number(10) not null,
544CONSTRAINT fk_supplier FOREIGN KEY (suppl_id) REFERENCES
545supplier(supplier_id) ON DELETE CASCADE);
546Database Systems Lab Manual
547Because of the cascade delete, when a record with a particular supplier_ id is deleted from supplier table , then all the records of the same supplier_id will be deleted from products table also.
548Operators in SQL*PLUS
549Type
550Symbol / Keyword
551Where to use
552Arithmetic
553+ , - , * , /
554To manipulate numerical column values, WHERE clause
555Comparison
556=, !=, <, <=, >, >=, between, not between, in, not in, like, not like
557WHERE clause
558Logical
559and, or, not
560WHERE clause, Combining two queries
561ï‚· Between..And..
562Example:
563SQL> select first_name, deptno from employee where salary between 20000 and 35000;
564Output:
565FIRST_NAME DEPTNO
566---------- ----------
567Smith 10
568ï‚· IN
569Example:
570SQL> select first_name, deptno from employee where job_id in ('J1','J2');
571Output:
572FIRST_NAME DEPTNO
573---------- ----------
574Smith 10
575Database Systems Lab Manual
576Arun 30
577Nithya 10
578ï‚· NOT IN
579Example:
580SQL> select dname,loc from department where loc not in ('chennai','Bangalore');
581Output:
582DNAME LOC
583-------------- -------------
584finance Hyderabad
585ï‚· Like
586Use the LIKE condition to perform wild card searches of valid search string values.
587Search conditions can contain either characters or numbers
588% - denotes zero or many characters.
589_ - denotes one character.
590Example:
591SQL> select dname,loc from department where loc like 'c%';
592Output:
593DNAME LOC
594-------------- -------------
595accounts chennai
596marketing Chennai
597Example:
598SQL> select dname,loc from department where loc like 'chen_ _ _';
599Output:
600DNAME LOC
601-------------- -------------
602accounts chennai
603marketing Chennai
604Example:
605SQL> select dname,loc from department where loc not like 'c%';
606Database Systems Lab Manual
607Output:
608DNAME LOC
609-------------- -------------
610finance Hyderabad
611IT Bangalore
612ï‚· Between..and..
613Example:
614SQL> select first_name, deptno, salary from employee where salary not between 20000 and 35000;
615Output:
616FIRST_NAME DEPTNO SALARY
617---------- ---------- ----------
618Arun 30 40000
619Nithya 10 45000
620Note: Inserting null value into location column of department table
621Example:
622SQL> insert into department(deptno,dname) values(40,'Sales');
623Output:
6241 row created.
625ï‚· is Null
626Example:
627SQL> select * from department where loc is null;
628Output:
629DEPTNO DNAME LOC
630---------- -------------- -------------
63140 Sales
632Example:
633SQL> select * from department where loc is not null;
634Output:
635DEPTNO DNAME LOC
636Database Systems Lab Manual
637---------- -------------- -------------
63810 accounts chennai
63920 finance Hyderabad
64030 IT Bangalore
64140 marketing chennai
642LOGICAL OPERATORS: Used to combine the results of two or more conditions to produce a single result. The logical operators are: OR, AND, NOT.
643Operator Precedence
644ï‚· Arithmetic operators-Highest precedence
645ï‚· Comparison operators
646ï‚· NOT operator
647ï‚· AND operator
648ï‚· OR operator----Lowest precedence
649The order of precedence can be altered using parenthesis.
650Example:
651SQL> select first_name, deptno, salary from employee where salary > 20000 ;
652Output:
653FIRST_NAME DEPTNO SALARY
654---------- ---------- ----------
655Smith 10 30000
656Arun 30 40000
657Nithya 10 45000
658Example:
659SQL> select first_name, deptno, salary from employee
660where salary > 20000 and salary < 35000;
661Output:
662Database Systems Lab Manual
663FIRST_NAME DEPTNO SALARY
664---------- ---------- ----------
665Smith 10 30000
666Example:
667SQL> select first_name, deptno, salary+100 from employee where salary > 35000;
668Output:
669FIRST_NAME DEPTNO SALARY+100
670---------- ---------- ----------
671Arun 30 40100
672Example:
673SQL> update employee set salary = salary+salary*0.1 where employee_id = 111;
674Output:
6751 row updated.
676Example:
677SQL> select * from department where loc = 'chennai' or dname='IT';
678Output:
679DEPTNO DNAME LOC
680---------- -------------- -------------
68110 accounts chennai
68230 IT Bangalore
68340 marketing chennai
684FUNCTIONS
685ï‚· Single Row Functions
686ï‚· Group functions
687Single Row Functions
688Returns only one value for every row can be used in SELECT command and included in WHERE clause
689Types
690ï‚· Character functions
691Database Systems Lab Manual
692ï‚· Numeric functions
693ï‚· Date functions
694CHARACTER FUNCTIONS:
695Character functions accept a character input and return either character or number values. Some of them supported by Oracle are listed below
696Syntax
697Description
698initcap (char)
699Changes first letter to capital
700lower (char)
701Changes to lower case
702upper (char)
703Changes to upper case
704ltrim ( char, set)
705Removes the set from left of char
706rtrim (char, set)
707Removes the set from right of char
708translate(char, from, to)
709Translate ‘from’ anywhere in char to ‘to’
710replace(char, search string, replace string)
711Replaces the search string to new
712substr(char, m , n)
713Returns chars from m to n length
714lpad(char, length, special char)
715Pads special char to left of char to Max of length
716rpad(char, length, special char)
717Pads special char to right of char to Max of length
718chr(number)
719Returns char equivalent
720length(char)
721Length of string
722Examples:
723Function
724Input
725Output
726Initcap(char)
727SQL>select initcap(‘hello’) from dual;
728Hello
729Lower(char)
730SQL>select lower(‘FUN’) from dual;
731fun
732Database Systems Lab Manual
733Upper(char)
734SQL>select upper(‘sun’) from dual;
735SUN
736Ltrim(char, set)
737SQL>select ltrim(‘xyzhello’,’xyz’) from dual;
738hello
739Rtrim(char, set)
740SQL>select rtrim(‘xyzhello’,’llo’) from dual;
741xyzhe
742translate(char,from,to)
743SQL>select translate(‘jack’,’j’,’b’) from dual;
744back
745Replace(char,from,to)
746SQL>select replace(‘jack and jue’,’ j’, ’bl’) from dual;
747black and blue
748Example:
749SQL> select initcap(dname) from department;
750Output:
751INITCAP(DNAME)
752--------------
753Accounts
754Finance
755It
756Marketing
757Sales
758Lpad is a function that takes three arguments. The first argument is the character string which has to be displayed with the left padding. The second is the number which indicates the total length of the return value, the third is the string with which the left padding has to be done when required.
759Example:
760SQL> select lpad(dname,15,'*') lpd from department;
761Output:
762LPD
763---------------
764*******accounts
765********finance
766*************IT
767******marketing
768**********Sales
769Example:
770SQL> select rpad(dname,15,'*') rpd from department;
771Output:
772Database Systems Lab Manual
773RPD
774---------------
775accounts*******
776finance********
777IT*************
778marketing******
779Sales**********
780Length: returns the length of a string
781Example:
782SQL> select dname, length(dname) from department;
783Output:
784DNAME LENGTH(DNAME)
785-------------- -------------
786accounts 8
787finance 7
788IT 2
789marketing 9
790Sales 5
791Concatenation operator ||: is used to merge or more strings.
792Example:
793SQL> select dname || ' is located in ' || loc from department;
794Output:
795DNAME||'ISLOCATEDIN'||LOC
796------------------------------------------
797accounts is located in chennai
798finance is located in Hyderabad
799IT is located in Bangalore
800marketing is located in chennai
801Sales is located in
802NUMERIC FUNCTIONS:
803Numeric functions accept numeric input and returns numeric values as output.
804Database Systems Lab Manual
805Syntax
806Description
807abs ( )
808Returns the absolute value
809ceil ( )
810Rounds the argument
811cos ( )
812Cosine value of argument
813exp ( )
814Exponent value
815floor( )
816Truncated value
817power (m,n)
818N raised to m
819mod (m,n)
820Remainder of m / n
821round (m,n)
822Rounds m’s decimal places to n
823trunc (m,n)
824Truncates m’s decimal places to n
825sqrt (m)
826Square root value
827Examples:
828Function
829Input
830Output
831Abs( n)
832SQL>select abs(-15) from dual
83315
834Ceil(n)
835SQL>select ceil(48.778) from dual;
83649
837Cos(n)
838SQL>select cos(180) from dual;
839-0.59884601
840Cosh(n):
841SQL>select cosh(0) from dual;
8421
843Exp(n)
844SQL>select exp(4) from dual;
84554.59815
846Floor(n)
847SQL>select floor(4.678) from dual;
8484
849Power(m ,n)
850SQL>select power(5,2) from dual;
85125
852Mod(m ,n)
853SQL>select mod(11,2) from dual;
8541
855Round(m ,n)
856SQL>select round(112.257,2) from dual;
857112.26
858Example:
859SQL> select ln (2) from dual; (returns natural logarithm value of 2)
860Database Systems Lab Manual
861SQL>select sign (-35) from dual; (output is -1)
862CONVERSION FUNCTIONS: Convert a value from one data type to another.
863ï‚· To_char ( )
864To_ char (d [,fmt]) where d is the date fmt is the format model which specifies the format of the date. This function converts date to a value of varchar2datatype in a form specified by date format fmt.if fmt is neglected then it converts date to varchar2 in the default date format.
865Example:
866SQL> select to_char (hire_date, 'ddth "of" fmmonth yyyy') from employee;
867Output:
868TO_CHAR(HIRE_DATE,'DDT
869----------------------
87021st of july 2010
87105th of june 2008
87212th of february 1999
873ï‚· To_ date ( )
874The format is to_date (char [, fmt]). This converts char or varchar data type to date data type. Format model, fmt specifies the form of character.
875Example:
876SQL>select to_date (‘December 18 2007’,’month-dd-yyyy’) from dual;
877Output:
87818-DEC-07 is the output.
879Example:
880SQL> select round(hire_date,'year') from employee;
881Output:
882ROUND(HIR
883---------
88401-JAN-11
88501-JAN-08
88601-JAN-99
887ï‚· To_ Number( )
888Database Systems Lab Manual
889Allows the conversion of string containing numbers into the number data type on which arithmetic operations can be performed.
890Example: SQL> select to_number (‘100’) from dual;
891DATE FUNCTIONS
892Example:
893SQL> select sysdate from dual;
894Output:
895SYSDATE
896---------
89722-JUL-10
898Example:
899SQL> select hire_date from employee;
900Output:HIRE_DATE
901---------
90221-JUL-10
90305-JUN-08
904Function Name
905Return Value
906ADD_MONTHS (date, n)
907Returns a date value after adding ’n’months to the date ’x’.
908MONTHS_BETWEEN (x1, x2)
909Returns the number of months between dates x1 and x2.
910ROUND (x, date_format)
911Returns the date ‘x’rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
912TRUNC (x, date_format)
913Returns the date ‘x’ lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.
914NEXT_DAY (x, week_day)
915Returns the next date of the ‘week_day’on or after the date ‘x’ occurs.
916LAST_DAY (x)
917It is used to determine the number of days remaining in a month from the date 'x'specified.
918SYSDATE
919Returns the systems current date and time.
920Database Systems Lab Manual
92112-FEB-99
922Example:
923SQL> select add_months(hire_date,3) from employee;
924Output:
925ADD_MONTH
926---------
92721-OCT-10
92805-SEP-08
92912-MAY-99
930Example:
931SQL> select months_between(sysdate,hire_date) from employee;
932Output:
933MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
934---------------------------------
935.047992085
93625.5641211
937137.338315
938Example:
939SQL> select next_day(hire_date,'wednesday') from employee;
940Output:
941NEXT_DAY(
942---------
94328-JUL-10
94411-JUN-08
94517-FEB-99
946Example:
947SQL> select last_day(hire_date) from employee;
948Output:
949LAST_DAY(
950---------
95131-JUL-10
95230-JUN-08
95328-FEB-99
954Database Systems Lab Manual
955Group Functions: - Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT
956ï‚· Group functions operate on sets of rows to give one result per group of Employees
957Dept_id
958Salary
95990
9605000
96190
96210000
96390
96410000
96560
9665000
96760
9685000
969Types of Group Functions
970Syntax
971Description
972count (*),
973count (column name),
974count (distinct column name)
975Returns number of rows
976min (column name)
977Min value in the column
978max (column name)
979Max value in the column
980avg (column name)
981Avg value in the column
982sum (column name)
983Sum of column values
984The maximum salary in the employees table
985Max (salary) 10000
986Database Systems Lab Manual
987Group Functions Syntax:
988Select [column,] group_function(column),..
989From table
990[where condition]
991[GROUP BY column];
992Example:
993Q.Display the average,highest, lowest and sum of salaries for all the sales representatives.
994A. Select avg(salary), max(salary), min(salary), sum(salary) From employees where job_id like ‘%rep%’;
995Groups of Data : Divide rows in a table in to smaller groups by using the group by clause
996Employee Table
997Dept_id
998Salary
99910
10004000
100110
10025000
100310
10046000
100550
10065000
100750
10083000
1009SET OPERATORS: UNION,UNION ALL,DIFFERENCE,MINUS
1010Example:
1011sql> select first_name from employees union select name from sample ;
1012Output:
1013FIRST_NAME
1014----------
1015DHANA
1016GUNA
1017D_id
1018Avg(Salary)
101910
10205000
102150
10224000
1023The average salary in employees table for each department
1024Database Systems Lab Manual
1025JAI
1026JAISANKAR
1027KUMAR
1028RAJA
1029VENKAT
1030Example:
1031sql> select first_name from employees union all select name from sample ;
1032Output:
1033FIRST_NAME
1034----------
1035VENKAT
1036JAI
1037DHANA
1038GUNA
1039JAISANKAR
1040VENKAT
1041RAJA
1042KUMAR
1043Example:
1044sql> select first_name from employees intersect select name from sample ;
1045Output:
1046FIRST_NAME
1047----------
1048VENKAT
1049Example:
1050sql> select first_name from employees minus select name from sample ;
1051Output:
1052FIRST_NAME
1053----------
1054DHANA
1055GUNA
1056JAI
1057Database Systems Lab Manual
1058ï‚· JOINS :A join is the SQL way of combining the data from many tables. It is performed by WHERE Clause which combines the specified rows of the tables.
1059Type
1060Sub type
1061Description
1062Simple join
1063Equi join ( = )
1064Non – equi join (<, <=, >, >=, !=, < > )
1065Joins rows using equal value of the column
1066Joins rows using other relational operators(except = )
1067Self join
1068-- ( any relational operators)
1069Joins rows of same table
1070Outer join
1071Left outer join ((+) appended to left operand in join condition)
1072Right outer join ((+) appended to right operand in join condition)
1073Rows common in both tables and uncommon rows have null value in left column
1074Vice versa
1075Simple Join:
1076a. EQUI JOIN OR INNER JOIN : A column (or multiple columns) in two or more tables match.
1077Syntax:
1078SELECT <column_name(s)> FROM <table_name1> INNER JOIN <table_name2> ON <table_name1.column_name>=<table_name2.column_name>;
1079Example 1 :
1080SELECT employee.first_name, department.dname FROM employee INNER JOIN department
1081Database Systems Lab Manual
1082ON employee.deptno = department.deptno; Output:1
1083DEPTNO FIRST_NAME
1084---------- ----------
108510 Smith
108630 Arun
108710 Nithya
1088Oracle automatically defaults the JOIN to INNER so that the INNER keyword is not required. They are the same query, though. It is preferred not to type the INNER keyword. Example 2 using where Condition:
1089SELECT employee.ename, department.dname FROM employee JOIN department ON employee.deptno = department.deptno WHERE department.dname = 'SALES'; Output 2:
1090DEPTNO FIRST_NAME
1091---------- ----------
109210 Smith
109330 Arun
109410 Nithya
1095b. SELF JOIN: Is a join where a table is joined to itself.
1096Syntax:
1097SELECT <column_name(s)> FROM <table_name1> JOIN <table_name2> ON <table_name1.column_name>=<table_name1.column_name>;
1098Example1:
1099SELECT e1.first_name, e2.first_name
1100Database Systems Lab Manual
1101FROM employee e1 join employee e2
1102on e1.mgr = e2.employee_id;
1103OR
1104SELECT e1.first_name, e2.first_name
1105FROM employee e1 join employee e2
1106where e1.mgr = e2.employee_id;
1107Output:
1108FIRST_NAME FIRST_NAME
1109---------------- --------------------
1110john john
1111An alias is just a way to refer to a column or table with a UNIQUE name. If we try to call both of the instances of the table EMP, Oracle wouldn't know which table instance we refer to. Using an alias clears this confusion
1112c. OUTER JOIN
1113An outer join tells Oracle to return the rows on the left or right (of the JOIN clause) even if there are no rows. The LEFT OUTER keyword to the JOIN clause says, return the rows to the left (in this case DEPARTMENT) even if there are no rows on the right (in this case employee). Syntax:
1114SELECT <column_name(s)> FROM <table_name1> LEFT OUTER JOIN <table_name2> ON <table_name1.column_name>=<table_name2.column_name>;
1115Example:
1116SELECT department.dname, employee.first_name
1117FROM department LEFT OUTER JOIN employee ON department.deptno = employee.deptno WHERE department.dname = 'marketing’;
1118Database Systems Lab Manual
1119Output:
1120DNAME FIRST_NAME
1121-------------- ----------
1122Marketing
1123The RIGHT OUTER keyword to the JOIN clause says ,return the rows to the right relation (in this case DEPARTMENT) even if there are no matching rows on the left relation (in this case employee). Syntax:
1124SELECT <column_name(s)> FROM <table_name1> RIGHT OUTER JOIN <table_name2> ON <table_name1.column_name>=<table_name2.column_name>;
1125Example:
1126SELECT employee.first_name, department.dname FROM employee RIGHT OUTER JOIN department ON employee.deptno = department.deptno WHERE department.dname = 'marketing';
1127Output:
1128FIRST_NAME DNAME
1129---------- --------------
1130marketing
1131d. FULL OUTER JOIN
1132Let's insert a new record into the employee table:
1133INSERT INTO EMPLOYEE (employee_id, first_name, last_name, mgr, hiredate, job-id,sal, comm, deptno) VALUES (9999, 'Joe ‘,’Blow', 7698, sysdate ,0008, 10500, 0, NULL ); Note:
1134We inserted an employee record that has no department. How can we get the records for
1135Database Systems Lab Manual
1136all employees AND all departments? We would use the FULL OUTER join syntax: Syntax:
1137SELECT <column_name(s)> FROM <table_name1> FULL OUTER JOIN <table_name2> ON <table_name1.column_name>=<table_name2.column_name>;
1138Example:
1139SELECT employee.first_name, department.dname FROM employee FULL OUTER JOIN department ON employee.deptno = department.deptno; Output:
1140FIRST_NAME DNAME
1141---------- --------------
1142Nithya accounts
1143Smith accounts
1144finance
1145john IT
1146Arun IT
1147marketing
1148john
1149e.Cross Join
1150Displays all the rows and all the colums of both the tables.
1151Synatx:
1152SELECT <column_name(s)> FROM <table_name1> CROSS JOIN<table_name2>;
1153Example:
1154select employee.deptno from employee cross join department;
1155Or
1156select employee.deptno from employee,department;
1157Output:
1158DEPTNO
1159----------
116010
1161Database Systems Lab Manual
116210
116310
116410
116530
116630
116730
116830
116910
117010
117110
1172DEPTNO
1173----------
117410
117530
117630
117730
117830
1179f. Natural Join
1180If two tables have same column name the values of that column will be displayed only once.
1181Syntax:
1182SELECT <column_name(s)> FROM <table_name1> Natural JOIN<table_name2>;
1183Example:
1184select deptno,first_name from employee natural join department;
1185Output:
1186DEPTNO FIRST_NAME
1187------ ----------
118810 Smith
118930 Arun
119010 Nithya
119130 john
1192SUB QUERIES
1193ï‚· Nesting of queries
1194ï‚· A query containing a query in itself A
1195ï‚· Inner most sub query will be executed first
1196ï‚· The result of the main query depends on the values return by sub query
1197Database Systems Lab Manual
1198ï‚· Sub query should be enclosed in parenthesis
11991. Sub query returning only one value
1200a. Relational operator before sub query.
1201Syntax:
1202SELECT <column_name(s)> FROM <table_name> WHERE < column name >
1203< relational op.> < sub query>;
1204Example:
1205SELECT employee_id ,first_name FROM employee
1206WHERE deptno =
1207(SELECT deptno FROM department
1208WHERE dname = ‘IT’)
1209Output:
1210EMPLOYEE_ID FIRST_NAME
1211----------- ----------
1212112 Arun
1213114 john
12142. Sub query returning more than one value
1215a. ANY
1216For the clause any, the condition evaluates to true if there exists at least one row selected by the sub query for which the comparison holds. If the sub query yields an empty result set, the condition is not satisfied.
1217Syntax:
1218SELECT <column_name(s)> FROM <table_name> WHERE < column name >
1219Database Systems Lab Manual
1220< relational op.> ANY (<sub query>);
1221Example:
1222SELECT employee_id ,first_name FROM employee
1223WHERE salary>= ANY
1224(SELECT salary FROM employee
1225WHERE deptno = 30)
1226AND deptno = 10;
1227Output:
1228EMPLOYEE_ID FIRST_NAME
1229----------- ----------
1230113 Nithya
1231112 Arun
1232111 Smith
1233114 john
1234114 john
1235b. ALL
1236For the clause all, in contrast, the condition evaluates to true if for all rows selected by the sub query the comparison holds. In this case the condition evaluates to true if the Sub query does not yield any row or value.
1237Syntax:
1238SELECT <column_name(s)> FROM <table_name> WHERE < column name > < relational op.> ALL (<sub query>);
1239Example:
1240SELECT employee_id ,first_name FROM employee
1241WHERE salary > ALL
1242(SELECT salary FROM employee
1243WHERE deptno = 30);
1244Output:
1245EMPLOYEE_ID FIRST_NAME
1246Database Systems Lab Manual
1247----------- ----------
1248113 Nithya
1249c. IN :Main query displays the values that match with any of the values returned by sub query.
1250Syntax:
1251SELECT <column_name(s)> FROM <table_name>
1252WHERE < column name > IN (<sub query>);
1253Example:
1254SELECT employee_id ,first_name FROM employee
1255WHERE deptno IN
1256(SELECT deptno FROM department
1257WHERE loc = ’Bangalore’);
1258Output:
1259EMPLOYEE_ID FIRST_NAME
1260----------- ----------
1261114 john
1262112 Arun
1263d. NOT IN
1264Main query displays the values that match with any of the values returned by sub query.
1265Syntax:
1266SELECT <column_name(s)> FROM <table_name> WHERE < column name > NOT IN (<sub query>);
1267Example:
1268SELECT employee_id ,first_name FROM employee
1269WHERE deptno NOT IN
1270(SELECT deptno FROM department
1271WHERE loc = ’Bangalore’);
1272Database Systems Lab Manual
1273Output:
1274EMPLOYEE_ID FIRST_NAME
1275----------- ----------
1276113 Nithya
1277111 Smith
1278e. EXISTS
1279Main query displays the values that match with any of the values returned by sub query.
1280Syntax:
1281SELECT <column_name(s)> FROM <table_name> WHERE EXISTS (<sub query>);
1282Example:
1283SELECT * FROM department
1284WHERE EXISTS
1285(SELECT * FROM employee
1286WHERE deptno = department.deptno);
1287Output:
1288DEPTNO DNAME LOC
1289---------- -------------- -------------
129010 accounts chennai
129130 IT Bangalore
1292f. NOT EXISTS
1293Main query displays the values that match with any of the values returned by sub query.
1294Syntax:
1295SELECT <column_name(s)> FROM <table_name> WHERE NOT EXISTS (<sub query>);
1296Example:
1297SELECT * FROM department
1298WHERE NOT EXISTS
1299Database Systems Lab Manual
1300(SELECT * FROM employee
1301WHERE deptno = department.deptno);
1302Output:
1303DEPTNO DNAME LOC
1304---------- -------------- -------------
130520 finance Hyderabad
130640 marketing chennai
1307g. GROUP BY CLAUSE
1308Often applications require grouping rows that have certain properties and then applying an aggregate function on one column for each group separately. For this, SQL provides the clause group by <group column(s)>. This clause appears after the where clause and must refer to columns of tables listed in the from clause.
1309Rule:
1310Select attributes and group by clause attributes should be same.
1311Syntax:
1312SELECT <column_name(s)> FROM <table_name> Where <conditions>
1313GROUP BY <column2>, <column1>;
1314Example:
1315SELECT deptno, min(salary), max(salary)
1316FROM employee
1317GROUP BY deptno;
1318Output:
1319DEPTNO MIN(SALARY) MAX(SALARY)
1320Database Systems Lab Manual
1321--------- ----------- -----------
132230 30000 40000
132330000 30000
132410 33000 45000
1325h. HAVING CLAUSE: used to apply a condition to group by clause
1326Syntax:
1327SELECT <column(s)>
1328FROM <table(s)>
1329WHERE <condition>
1330[GROUP BY <group column(s)>]
1331[HAVING <group condition(s)>];
1332Example:
1333SELECT deptno, min(salary), max(salary)
1334FROM employee
1335WHERE job_id = ’J2’
1336GROUP BY deptno
1337HAVING count(*) > 1;
1338Output:
1339DEPTNO MIN(SALARY) MAX(SALARY)
1340---------- ----------- -----------
134130 13000 40000
1342A query containing a group by clause is processed in the following way:
13431. Select all rows that satisfy the condition specified in the where clause.
13442. From these rows form groups according to the group by clause.
13453. Discard all groups that do not satisfy the condition in the having clause.
13464. Apply aggregate functions to each group.
13475. Retrieve values for the columns and aggregations listed in the select clause.
1348Database Systems Lab Manual
1349i. ORDER BY
1350Used along with where clause to display the specified column in ascending order or descending order . Default is ascending order
1351Syntax:
1352SELECT [distinct] <column(s)>
1353FROM <table>
1354[ WHERE <condition> ]
1355[ ORDER BY <column(s) [asc|desc]> ]
1356Example:
1357SELECT first_name, deptno, hire_date
1358FROM employee
1359ORDER BY deptno ASC, hire_date desc;
1360Output:
1361FIRST_NAME DEPTNO HIRE_DATE
1362---------- ---------- ---------
1363Smith 10 21-JUL-10
1364Nithya 10 12-FEB-99
1365john 30 20-JAN-10
1366Arun 30 05-JUN-08
1367john 20-JAN-10
1368VIEWS
1369Definition: A view is a named, derived, virtual table. A view takes the output of a query and treats it as a table; therefore a view can be thought of as a ‘stored query’ or a ‘virtual table’. We can use views in most places where tables can be used. To the user, accessing a view is like accessing a table. The RDBMS creates an illusion of a table, by assigning a name to the view and storing its definition in the database.
1370The tables upon which the views are based are called as ‘base tables’.
1371CREATION OF A VIEW:
1372Database Systems Lab Manual
1373The syntax for creating a view is given by:
1374create [or replace][[no][force]]view <view_name> [column alias name…]as <query>[with[check option]read only][constraint]];
1375Example:
1376SQL>create or replace view EMP_VIEW as select * from EMP;
1377This statement creates a view named EMP_VIEW .The data in this view comes from the base table EMP. Any changes made to the base table are instantly visible through the view EMP_VIEW.We can use select statement just like on a table.
1378SQL>select * from EMP_VIEW;
1379When create or replace is given, view is created if it is not available otherwise it is recreated.
1380HOW DOES RDBMS HANDLE THE VIEWS: When a reference is made by a user, the RDBMS finds the definition of the view stored in the database .It then translates the user’s request that referenced the view into an equivalent request against the source tables of the view. Thus RDBMS maintains the illusion of the view.
1381TYPES OF VIEWS: The different types of views are
1382• Column subset view
1383• Row subset view
1384• Row-Column subset view
1385• Grouped view
1386• Joined view
1387COLUMN SUBSET VIEW:
1388A column subset view is one where all the rows but only some of the columns of the base table form the view. The create view
1389Example:
1390SQL>create or replace view CSV as select empno, ename, sal from EMP;
1391This view includes only columns empno, ename, sal of EMP table. Since there is no where clause it includes all the rows.
1392ROW SUBSET VIEW:
1393Database Systems Lab Manual
1394A row subset view is one where all columns but some rows of the source table form the view. All the columns of the base table participate in the view but all rows do not.
1395Example:
1396SQL> create or replace view RSV as select * from EMP where deptno=10;
1397The where clause restricts the no. of rows to those of employees working in Department Number 10.
1398ROW-COLUMN SUBSET VIEW:
1399A row-column subset view is a view which includes only some rows and columns of the base table.
1400Example:
1401SQL>create or replace view RCS as select EMPNO, ENAME, SAL from EMP where deptno=10;
1402GROUPED VIEW:
1403The query specified in the view definition can include the GROUP BY clause. This type of view is called as Grouped View.
1404Example:
1405SQL>create or replace view GV (dno, avgsal) as select deptno, AVG (SAL) from emp group by deptno;
1406JOINED VIEWS:
1407A joined view is formed by specifying a two or more table query in the view definition. A joined view draws its data from two or more tables and presents the result as a single virtual table.
1408Example:
1409SQL>create or replace view JV(empno,ename,sal,dname,loc) as select empno,ename,sal,dname,loc from EMP,DEPT where EMP.deptno=DEPT.deptno;
1410CREATING A READ ONLY VIEW:
1411Use with read only clause to prevent the users from manipulating records via the view.
1412Example:
1413Database Systems Lab Manual
1414SQL>create or replace view WRO as select * from EMP with read only;
1415Note: A view can be created without a base table using FORCE option of create view command.
1416Example:
1417SQL>create or replace force view FVIEW as select * from MYDEPT;
1418In this query MYDEPT table does not exist, so view is created with compilation errors. When MYDEPT table is created and this query is executed, the view is automatically recompiled and become valid.
1419VIEW WITH CHECK OPTION:
1420This option specifies that inserts and updates performed through the view must result in rows that the view query can select. The CHECK OPTION can be used to maintain integrity on a view.
1421Example:
1422SQL>insert into RSV (empno, ename, sal, deptno) values (1000,’dinesh’, 5500, 20);
1423Though the view is created for deptno 10, we are able to insert records for other department numbers .This can be restricted using WITH CHECK OPTION clause while creating a view.
1424Example:
1425SQL>create view DEPTNO10_VIEW as select * from EMP where deptno=10 WITH CHECK OPTION CONSTRAINT CHK_DNO10;
1426The above statement creates a view DEPTNO10 with a check constraint. This will enforce the view to be inserted or updated only for the department number 10. No other departments can be inserted or updated.
1427DROPPING A VIEW:
1428A view can be dropped by using DROP VIEW command.A view becomes invalid if its associated base table is dropped.
1429Example:
1430SQL>drop view DEPTNO10;
1431This will not affect the base table EMP.
1432Database Systems Lab Manual
1433ADVANTAGES OF VIEWS:
1434ï‚· Valid Information: Views let different users see a table from different perspectives. Only the part that is relevant to the users is visible to them.
1435ï‚· Restricted Access: Views restrict access to the table. Different users are allowed to see only certain rows or certain columns of a table.
1436ï‚· Simplified Access: Views simplify database access. For example a view that is a join of three tables where a user does not require all the data in all three tables.
1437ï‚· Data Integrity: Data Integrity can be maintained by having WITH CHECK OPTION while creating a view.
1438RESTRICTIONS ON VIEWS:
1439 A view’s query cannot select the CURRVAL or NEXTVAL pseudo columns.
1440 If a view’s query selects the ROWID, ROWNUM or LEVEL pseudo columns, they must have aliases in the view’s query.
1441 A view can’t be created with an ORDER BY clause.
1442 A view can’t be updated, deleted and inserted if it is a grouped view.
1443 A view created from multiple tables can’t be updatable.
1444ï‚· If a view is based on a single underlying table then you can insert, update or delete rows in this view. This will actually insert, update or delete rows in the underlying table. There are restrictions again on doing this:
1445ï‚· You cannot insert if the underlying table has a NOT NULL column that does not appear in the view.
1446 You cannot insert or update if any of the view’s columns referenced in insert or update consist of functions or calculations.
1447ï‚· You cannot insert, update or delete if the view contains GROUP BY, DISTINCT or a reference to a pseudo column ROWNUM.
1448Database Systems Lab Manual
1449PL/SQL
1450Overview of PL/SQL
1451PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code.
1452Pl/SQL Environment
1453The PL/SQL engine in the oracle server process the pl/sql block and it separates SQL staments and sends them individually to the SQL statements executor
1454Benefits of PL/SQL
1455ï‚· Integration
1456ï‚· Improved performance
1457ï‚· Modularized program development
1458ï‚· Portability
1459ï‚· Identifiers
1460PL/SQL engine
1461P SQL
1462SQL
1463PL/SQL block
1464Procedural statement executor
1465PL/SQL block
1466Oracle Server
1467SQL statement executor
1468Database Systems Lab Manual
1469PL/SQL Block structure
1470DECLARE (optional)
1471Variables, cursors, user-defined exceptions
1472BEGIN (Mandatory)
1473-SQL statements
1474-PL/SQL statements
1475EXCEPTION (optional)
1476Action to perform when error occur
1477END;
1478The PL/SQL Block consists of three sections:
1479DECLARATIVE
1480It contains all variables, constants, cursors and user defined exceptions that are referenced in the executable and declarative sections.
1481EXECUTABLE
1482It contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block.
1483EXCEPTION HANDLING
1484It specifies the actions to perform when errors and abnormal conditions arise in the executable section.
1485PL/SQL Block Types
1486A PL/SQL program comprises one or more blocks.
1487It is classified into two blocks
1488ï‚· Anonymous Blocks
1489Database Systems Lab Manual
1490It is unnamed blocks. It is declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at run time.
1491ï‚· Subprograms
1492Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. It can be declared either as procedures or as functions.
1493Sample PL/SQL programs
1494To write PL/SQL programs, create a script file and run the script file or use editor.
1495Steps to create script file
1496Step1:
1497SQL> edit z:\oracle\sql\var1.sql
1498Step2:
1499Type the program in notepad
1500Step 3:
1501Save the program
1502Step4:
1503Run the program
1504SQL> @z:\oracle\sql\var1.sql
1505SQL> set serveroutput on;
1506This command is used to display the statement executed by dbms_output.put_line package.
1507Program 1: Write a program to print a variable value.
1508SQL> declare
15092 a number:=3;
15103 begin
1511Database Systems Lab Manual
15124 dbms_output.put_line(a);
15135 end;
15146 /
15153
1516Program 2: Write a program to print your name and regno.
15171 declare
15182 v_name varchar2(10);
15193 v_regno number;
15204 begin
15215 v_name:='venkat';
15226 v_regno:=39;
15237 dbms_output.put_line( 'the name is' || v_name);
15248 dbms_output.put_line('the no is' || v_regno);
15259 end;
1526SQL> /
1527the name is venkat
1528the no is 39
1529PL/SQL procedure successfully completed.
1530Database Systems Lab Manual
1531Program 3: Write a program to retrieve ssn number of employee whose name is x.
1532Assume the following table:
1533SSN NAME ESSN DEPTNO SALARY
1534---------- ---------- ---------- ---------- ----------
1535101 x 102 1
1536102 y 103 2
1537103 z 102 3
1538104 p 102 4
1539105 q
1540declare v_no number;
1541begin
1542select ssn into v_no from emp where name='x';
1543dbms_output.put_line(v_no);
1544end;
1545SQL>/
1546101
1547PL/SQL procedure successfully completed.
1548SCALAR VARIABLE
1549It holds a single value and has no internal components.
1550Examples : number, character, date, boolean
1551Example: using scalar variable
1552Database Systems Lab Manual
15531 declare
15542 v_name varchar2(10);
15553 V_count binary_integer:=10;
15564 V_totalsal number(9,2);
15575 v_orderdate date:=sysdate;
15586 c_tax constant number(3,2):=6.23;
15597 v_valid boolean not null:=true;
15608 v_regno number default 23;
15619 begin
156210 v_name:='venkat';
156311 v_totalsal:=10000.23;
156412 dbms_output.put_line(v_name);
156513 dbms_output.put_line(v_count);
156614 dbms_output.put_line(v_orderdate);
156715 dbms_output.put_line(c_tax);
156816 dbms_output.put_line(v_regno);
156917 end;
157018 /
1571venkat
157210
157319-AUG-05
15746.23
157523
1576Database Systems Lab Manual
1577DECLARING VARIABLE WITH THE %TYPE ATTRIBUTE
1578The % type attribute is used to declare a variable according to:
15791. A database column definition
15802. Another previously declared variable
1581Example: using % type attribute
15821 declare
15832 v_no emp.ssn%type;
15843 V_name varchar2(10):='venkat';
15854 name v_name%type;
15865 begin
15876 v_no:=10;
15887 name:='ven';
15898 dbms_output.put_line(v_no);
15909 dbms_output.put_line(name);
159110*end;
159211 /
159310
1594ven
1595PL/SQL procedure successfully completed.
1596Database Systems Lab Manual
1597BIND VARIABLES
1598A bind variable is a variable that is declared in a host environment. Bind variables can be used to pass run-time values, which can be either number or character, into or out of one or more PL/SQL programs.
1599Example:
1600SQL> variable a number;
1601SQL> ed
1602File:
16031 begin
16042 select ssn into:a from emp where name='x';
16053 dbms_output.put_line(:a);
16064 end;
1607SQL> /
1608101
1609PL/SQL procedure successfully completed.
1610SQL> print a;
1611A
1612----------
1613101
1614PL/SQL procedure successfully completed.
1615Database Systems Lab Manual
1616REFERENCING NON PL/SQL VARIABLES
1617To reference host variables, prefix the references with a colon (:) to distinguish them from declared PL/SQL variable.
1618SQL> variable gg number;
1619SQL> define aa=1000;
1620SQL> set verify off
1621SQL> declare
16222 v_sal number(9,2):=&aa;
16233 begin
16244 :gg:=v_sal/12;
16255 end;
16266 /
1627PL/SQL procedure successfully completed.
1628SQL> print gg;
1629GG
1630----------
163183.3333333
1632PL/SQL BLOCK SYNTAX AND GUIDELINES
1633A line of pl/sql text contains groups of characters known as lexical units.
1634Lexicals are classified as follows:
1635ï‚· Delimiters
1636ï‚· Identifiers, which include reserved words
1637ï‚· Literals
1638ï‚· Character literals
1639ï‚· Numeric literals
1640Database Systems Lab Manual
1641COMMENTS:
1642-- single line commenting
1643/* beginning */ ending
1644PL/SQL HAS ITS OWN ERROR HANDLING:
1645ï‚· SQLCODE
1646ï‚· SQL ERRM
1647DATA TYPE CONVERSION
1648PL/SQL performs implicit conversions. For E.g. numeric to char.
1649The following program highlights conversion involving DATE.
1650SQL> ed
16511 declare
16522 vdate date;
16533 begin
16544 vdate:=to_date('aug 19 ,2005','mon dd,yyyy');
16555 dbms_output.put_line(vdate);
16566 end;
1657SQL> /
165819-AUG-05
1659PL/SQL procedure successfully completed.
1660Database Systems Lab Manual
1661SQL FUNCTIONS IN PL/SQL:
1662All SQL functions are allowed except decode function and group functions.
1663NESTED BLOCKS AND VARIABLE SCOPE
1664SQL> declare
16652 v_a number:=3;
16663 begin
16674 declare
16685 v_b number:=4;
16696 begin
16707 dbms_output.put_line(v_b);
1671dbms_output.put_line(v_a);
16728 end;
16739 dbms_output.put_line(v_a);
167410 end;
167511 /
16764
16773
1678PL/SQL procedure successfully completed.
1679Database Systems Lab Manual
1680QUALIFYING AN IDENTIFIER An identifier is qualified by using the block label prefix. In the example the outer block is labeled as outer. In the inner block the variable is reference by label, when variable names are same.
1681SQL> ed
16821 <<outer>>
16832 declare
16843 v_a number:=3;
16854 begin
16865 declare
16876 v_a number:=4;
16887 begin
16898 dbms_output.put_line(v_a);
16909 dbms_output.put_line(outer.v_a);
169110 end;
169211 end;
169312 /
16944
16953
1696PL/SQL procedure successfully completed.
1697Database Systems Lab Manual
1698PROGRAMMING GUIDELINES:
1699Category
1700Case Conversion
1701Examples
1702SQL statements
1703uppercase
1704SELECT,INSERT
1705PL/SQL statements
1706uppercase
1707DECLARE,BEGIN,IF
1708Data types
1709uppercase
1710VARCHAR2,BOOLEAN
1711Identifiers
1712lowercase
1713v_sal
1714Database tables and column
1715lowercase
1716emp, dept
1717INTERACTING WITH ORACLE SERVER:
1718ï‚· Extracts row of data from the database using select
1719ï‚· Effects changes in the database by using DML commands
1720ï‚· Controls a transaction with commit, rollback and save point
1721NOTES:
1722ï‚· An end in pl/sql block is not the end of transaction.
1723ï‚· A block can span multiple transactions, a transaction can span multiple blocks.
1724ï‚· DDL commands (create,alter,drop) and DCL commands(grant,revoke) are not directly supported.
1725SQL> select * from emp;
1726SSN NAME ESSN DEPTNO SALARY
1727---------- ---------- ---------- ---------- ----------
1728101 x 102 1
1729Database Systems Lab Manual
1730102 y 103 2
1731103 z 102 3
1732104 p 102 4
1733105 q
1734SQL> ed
17351 declare
17362 v_ssn number;
17373 v_name varchar2(10);
17384 begin
17395 select ssn,name into v_ssn,v_name from emp where name='x';
17406 dbms_output.put_line(v_ssn);
17417 dbms_output.put_line(v_name);
17428 end;
1743SQL> /
1744101
1745x
1746PL/SQL procedure successfully completed.
1747RETRIEVING DATA IN PL/SQL:
1748SQL> select * from job_grade;
1749GRA LOWEST_SAL HIGHEST_SAL
1750---- ---------- -----------------------------------------
1751a 3000 4000
1752Database Systems Lab Manual
1753b 5000 6000
1754c 3000 6000
1755d 4000 10000
1756e 2000 6000
1757SQL> ed
17581 declare
17592 v_lsal job_grade.lowest_sal%type;
17603 v_hsal job_grade.highest_sal%type;
17614 begin
17625 select sum(lowest_sal),sum(highest_sal) into v_lsal,v_hsal from job_grade;
17636 dbms_output.put_line(v_lsal);
17647 dbms_output.put_line(v_hsal);
17658 end;
17669 /
176717000
176832000
1769PL/SQL procedure successfully completed.
1770NAMING CONVENTIONS
1771A local variable in pl/sql name must not be equal to column names present in database .
1772declare
1773Database Systems Lab Manual
1774lastname varchar2(10);
1775begin
1776delete from emp where lastname-lastname;
1777The above code will delete all employees because of the naming convention problem.
1778MANIPULATING DATA USING PL/SQL
1779SUBSTIUTION VARIABLE:
1780SQL> ed
1781Wrote file afiedt.buf
17821 declare
17832 v_sal number;
17843 begin
17854 v_sal:=&v_sal;
17865 dbms_output.put_line(v_sal);
17876 end;
17887 /
1789Enter value for v_sal: 2000
17902000
1791PL/SQL procedure successfully completed.
1792INSERTION
1793SQL> ed
1794Database Systems Lab Manual
1795Wrote file afiedt.buf
17961 begin
17972 insert into emp(ssn,name) values(123,'venkat');
17983 dbms_output.put_line('record inserted');
17994 end;
18005 /
1801record inserted
1802PL/SQL procedure successfully completed.
1803USAGE OF SUBSTITUTION VARIABLE:
1804SQL> ed
1805Wrote file afiedt.buf
18061 begin
18072 insert into emp(ssn,name) values(&ssn,'&name');
18083 dbms_output.put_line('record inserted');
18094 end;
1810SQL> /
1811Enter value for ssn: 124
1812Enter value for name: sampath
1813record inserted
1814PL/SQL procedure successfully completed.
1815Database Systems Lab Manual
1816UPDATE:
1817SQL> ed
1818Wrote file afiedt.buf
18191 declare
18202 v_sal number;
18213 begin
18224 v_sal:=&v_sal;
18235 update job_grade set lowest_sal=v_sal where gra='a';
18246 dbms_output.put_line('record updated');
18257 end;
1826SQL> /
1827Enter value for v_sal: 12000
1828record updated
1829PL/SQL procedure successfully completed.
1830SQL> select *from job_grade;
1831GRA LOWEST_SAL HIGHEST_SAL
1832---- ---------- -------------------------------------------
1833a 12000 4000
1834b 5000 6000
1835c 3000 6000
1836Database Systems Lab Manual
1837d 4000 10000
1838e 2000 6000
1839DELETE:
1840SQL> ed
1841Wrote file afiedt.buf
18421 declare
18432 v_sal number;
18443 begin
18454 v_sal:=&v_sal;
18465 delete from job_grade where lowest_sal=v_sal;
18476 dbms_output.put_line('record deleted');
18487 end;
18498 /
1850Enter value for v_sal: 5000
1851record deleted
1852PL/SQL procedure successfully completed.
1853SQL> select * from job_grade;
1854GRA LOWEST_SAL HIGHEST_SAL
1855---- ---------- -----------
1856a 12000 4000
1857c 3000 6000
1858d 4000 10000
1859Database Systems Lab Manual
1860e 2000 6000
1861CONTROL STRUCTURES
1862• IF statements
1863 If –then-end if
1864 If-then-else-end if
1865 If-then-elseif-end if
1866• Case expressions
1867• Loop statements
1868 Basic loops
1869 While loops
1870 For loops
1871Syntax of IF:
1872If condition then
1873Statements;
1874Else if condition then
1875Statements;
1876Else
1877Statements;
1878End if;
1879Examples:
1880Database Systems Lab Manual
18811) Find the greatest among two numbers
18821 declare
18832 a number;
18843 b number;
18854 begin
18865 a:=&a;
18876 b:=&b;
18887 if a>b then
18898 dbms_output.put_line('gratest number is'||a);
18909 else
189110 dbms_output.put_line('gratest number is'||b);
189211 end if;
189312 end;
1894SQL> /
1895Enter value for a: 12 old 5: a:=&a;
1896new 5: a:=12;
1897Enter value for b: 4 old 6: b:=&b;
1898new 6: b:=4;
1899greatest number is12
1900PL/SQL procedure successfully completed
1901Database Systems Lab Manual
19022) if else with database
1903DEPT_ID
1904DEPT_NAME
1905MANAGER_ID
1906LOCATION_ID
1907---------
1908----------
1909----------
1910-----------
191110
1912cse
1913200
19141700
191520
1916it
1917300
19181800
191930
1920mech
1921400
19221500
192340
1924ece
1925500
19261600
19271 declare
19282 v_id departments.dept_id%type;
19293 v_dname departments.dept_name%type;
19304 begin
19315 select dept_id,dept_name into v_id,V_dname from departments where manager_id=200;
19326 if v_id=11 then
19337 dbms_output.put_line(v_dname);
19348 elsif v_dname='cse' then
19359 dbms_output.put_line(v_id);
193610 else
193711dbms_output.put_line('recorde not match');
193812 end if;
193913 end;
1940SQL> / 10
1941Database Systems Lab Manual
1942PL/SQL procedure successfully completed.
19433) if/else if/ else
19441 declare
19452 a number;
19463 b number;
19474 c number;
19485 begin
19496 a:=&a;
19507 b:=&b;
19518 c:=&c;
19529 if (a>b) and (a>c) then
195310 dbms_output.put_line('gratest number is'||a);
195411 elsif(b>a) and (b>c) then
195512 dbms_output.put_line('greatest number is'||b);
195613 else
195714 dbms_output.put_line('greatest number is'||c);
195815 end if;
195916 end;
1960SQL>/
1961Enter values for a,b and c: 6 4 12
1962C is greater :12.
1963Database Systems Lab Manual
1964PL/SQL procedure successfully completed.
19654) Case Expressions
1966A case expression selects a result and returns it. To select the result, the case expression uses an expression whose value is used to select one of several alternatives.
1967Syntax :
1968CASE selector
1969WHEN
1970expression1
1971THEN result1 WHEN
1972expression2
1973THEN result2
1974---------
1975WHEN expression N THEN result N [ELSE resultN+1
1976END;
1977Example:
19781 declare
19792 va varchar2(10);
19803 v_result varchar2(10);
19814 begin
19825 va:=&va;
1983Database Systems Lab Manual
19846 v_result:=
19857 CASE va
19868 WHEN 'a' THEN 'excellent'
19879 WHEN 'b' THEN 'very good'
198810 WHEN 'c' THEN 'good'
198911 ELSE 'poor'
199012 end;
199113 dbms_output.put_line('grade is'||v_result);
199214 end;
199315 /
1994SQL> Enter value for va: 'a' old 5: va:=&va;
1995new 5: va:='a';
1996grade is excellent
1997PL/SQL procedure successfully completed.
1998SQL> /
1999Enter value for va: 'b' old 5: va:=&va;
2000new 5: va:='b';
2001grade is very good
2002PL/SQL procedure successfully completed.
2003SQL> /
2004Database Systems Lab Manual
2005Enter value for va: 'c' old 5: va:=&va;
2006new 5: va:='c';
2007grade is good
2008PL/SQL procedure successfully completed.
20095) For Structure
2010SQL> 1 begin
20112 for emp_record in (select * from wer1) loop
20123 insert into wer1(name,ssn) values(emp_record.name,emp_record.ssn);
20134 end loop;
20145 commit;
20156 end;
20167 /
2017PL/SQL procedure successfully completed
2018Database Systems Lab Manual
2019CURSORS
2020The oracle server uses work areas, called private SQL areas, to execute SQL statement and to store processing information. This area is called cursor.
2021Cursor types:
2022ï‚· Implicit: queries returns only one row
2023ï‚· Explicit : queries returns more than one row
2024Explicit cursor
2025Active set: set of rows returned by multiple rows
2026Controlling explicit cursor
2027Open the cursor and execute the query associated with the cursor which identifies the result set.
2028Fetch
2029Retrieves the current row an advance the current row
2030Close the cursor
2031Syntax:
2032Cursor declaration
2033cursor cuname is select;
2034Open the cursor
2035open cursor name;
2036Close the cursor
2037Database Systems Lab Manual
2038close cursor name;
2039Fetch
2040fetch cname into variable or record
2041Explicit Cursor Attributes: To determine the status of the cursor, the cursor’s attributes are checked.Cursors have the following four attributes that can be used in a PL/SQL program.
2042%isopen -To check if the cursor is opened or not
2043%found-To check if a record is found and can be fetched from the cursor
2044%rowcount-To check for the number of rows fetched from the cursor
2045%notfound-To check if no more records can be fetched from the cursor
2046%isopen, %found,%notfound are boolean attributes which are set to either TRUE or FALSE.
2047A Simple Example:
20481 declare
20492 v_name wer1.name%type;
20503 v_ssn wer1.ssn%type;
20514 cursor emp_c is select * from wer1;
20525 begin
20536 open emp_c;
20547 for i in 1..5 loop
20558 fetch emp_c into v_name,v_ssn;
20569 dbms_output.put_line(v_name);
2057Database Systems Lab Manual
205810 end loop;
205911 close emp_c;
206012 end;
2061PL/SQL procedure successfully completed.
2062SQL> set serveroutput on;
2063SQL> / x
2064x
2065x
2066y
2067y
20682) %row count
20691 declare
20702 v_name wer1.name%type;
20713 v_ssn wer1.ssn%type;
20724 cursor emp_c is select * from wer1;
20735 begin
20746 open emp_c;
20757 for i in 1..5 loop
20768 fetch emp_c into v_name,v_ssn;
20779 exit when emp_c%rowcount>4;
207810 dbms_output.put_line(v_name);
2079Database Systems Lab Manual
208011end loop;
208112 close emp_c;
208213 end;
2083SQL> / x
2084x
2085x
2086y
2087PL/SQL procedure successfully completed.
20883) Cursor with record
2089It processes the rows of the active set by fetching values into a PL/SQL record.
20901 declare
20912 cursor emp_c is select * from wer1;
20923 emp_record emp_c%rowtype;
20934 begin
20945 open emp_c;
20956 for i in 1..5 loop
20967 fetch emp_c into emp_record;
20978 exit when emp_c%notfound;
20989 insert into wer(name,ssn) values(emp_record.name,emp_re
209910 end loop;
210011 commit;
210112 close emp_c;
2102Database Systems Lab Manual
210313 end;
210414 /
2105PL/SQL procedure successfully completed.
2106SQL> select * from wer;
2107NAME
2108SSN
2109----------
2110----------
2111x
2112101
2113x
2114101
2115x
2116101
2117x
2118101
2119x
2120101
2121x
2122101
2123x
2124101
2125x
2126101
2127x
2128101
2129x
2130101
2131y
2132102
2133y
2134102
213512 rows selected.
21364) Cursor with parameters
2137Database Systems Lab Manual
2138It passes the parameter values to the cursor in a cursor FOR loop. This means that you can open and close an explicit cursor several times in a block, returning a different active set on each occasion.
2139Example:
21401 declare
21412 v_number number;
21423 v_name varchar2(10);
21434 cursor c1(eno number,ename varchar2) is
21445 select ssn,name from emp where ssn=eno and name=ename;
21456 begin
21467 open c1(101,'x');
21478 fetch c1 into v_number,v_name;
21489 dbms_output.put_line(v_number);
214910 close c1;
215011 open c1(102,'y');
215112 fetch c1 into v_number,v_name;
215213 dbms_output.put_line(v_number);
215314 close c1;
215415 end;
215516 /
2156101
2157102
2158PL/SQL procedure successfully completed.
2159Database Systems Lab Manual
21605) Update
2161The update clause in the cursor query locks the affected rows when the cursor is opened.
2162Example:
2163declare
2164v_number number;
2165v_name varchar2(10);
2166cursor c1(eno number,ename varchar2) is select ssn,name from emp where ssn=eno and
2167name=ename for update of name nowait;
2168begin
2169open c1(101,'x');
2170fetch c1 into v_number,v_name;
2171dbms_output.put_line(v_number);
2172close c1;
2173open c1(102,'y');
2174fetch c1 into v_number,v_name;
2175dbms_output.put_line(v_number);
2176close c1;
2177end;
2178EXCEPTIONS
2179Database Systems Lab Manual
2180Syntax
2181When exception1
2182then Statement1
2183Statement2
2184……..
2185When exception2
2186then Statement1
2187Statement2
2188……..
2189When others
2190then Statement1
2191Statement2 ……..
2192Sample predefined exceptions:
2193NO_DATA_FOUND
2194TOO_MANY_ROWS
2195INVALID_CURSOR
2196ZERO_DIVIDE
2197DUP_VAL_ON_INDEX
2198Example:
21991)
22001 declare
2201Database Systems Lab Manual
22022 a number;
22033 b number;
22044 c number;
22055 begin
22066 a:=5;
22077 b:=0;
22088 c:= a/b;
22099 exception
221010 when zero_divide then
221111 dbms_output.put_line('zero divide error');
221212 end;
2213SQL> /
2214zero divide error
2215PL/SQL procedure successfully completed.
22162) Non-predefined error
2217Trapping a non-predefined exception
22181. Declare the name for the exception within the declarative section
22192. Associate the declared exception with the standard oracle server error number using the PRAGMA EXCEPTION_INIT statement
2220Syntax : PRAGMA EXCEPTION_INIT(exception, error_number);
22213. Reference the declared exception within the corresponding exception –handling routine.
2222Database Systems Lab Manual
2223Example:
22241 declare
22252 emp_remain exception;
22263 pragma exception_init
22274 (emp_remain,-2292);
22285 begin
22296 delete from emp where deptno=&deptno;
22307 commit;
22318 exception
22329 when emp_remain then
223310 dbms_output.put_line('cannot remove dept'|| 'employee exist');
223411end;
2235SQL> /
2236Enter value for deptno: 2
2237old 6: delete from emp where deptno=&deptno;
2238new 6: delete from emp where deptno=2;
2239cannot remove deptemployee exist
2240PL/SQL procedure successfully completed
2241SQL> /
2242Enter value for deptno: 8
2243old 6: delete from emp where deptno=&deptno;
2244Database Systems Lab Manual
2245new 6: delete from emp where deptno=8;
2246PL/SQL procedure successfully completed
22473) Functions for trapping exceptions
2248When an exception occurs, you can identify the associated error code or error message by using two functions.
2249SQLCODE: It returns the numeric value for the error code
2250SQLERRM: It returns character data containing the message associated with the error number.
2251Syntax:
2252declare;
2253v_error_code number;
2254v_error_messgage varchar2(255);
2255begin
2256when others then rollback;
2257v_error_code:=sqlcode;
2258v_error_message:=sqlerrm;
2259dbms_output.put_line(v_error_code||v_error_message);
2260end;
2261User defined function:
2262User defined PL/SQL exception must be
2263Database Systems Lab Manual
2264• Declared in the declare section of a PL/SQL block
2265• Raised explicitly with RAISE statements
2266Example:
22671 declare
22682 invalid_dept exception;
22693 begin
22704 delete from emp where deptno=&deptno;
22715 if sql%notfound then
22726 raise invalid_dept;
22737 end if;
22748 exception
22759 when invalid_dept then
227610 dbms_output.put_line('the deptnumber is not valid');
227711 end;
2278Enter value for deptno: 10
2279old 4: delete from emp where deptno=&deptno;
2280new 4: delete from emp where deptno=10;
2281the deptnumber is not valid
2282PL/SQL procedure successfully completed.
2283PL/SQL Block Types
2284A PL/SQL program comprises one or more blocks.
2285Database Systems Lab Manual
2286It is classified into two blocks
2287ï‚· Anonymous Blocks
2288It is unnamed blocks. It is declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at run time.
2289ï‚· Subprograms
2290Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. It can be declared either as procedures or as functions.
2291Overview of subprograms
2292A subprogram is named PL/SQL block that ca accept parameters and be invoked from a calling environment.
2293Two types of subprograms
2294ï‚· A procedure that performs an action
2295ï‚· A function that computes a value
2296Benefits of subprograms
2297ï‚· Easy maintenance
2298ï‚· Improved data security and integrity
2299ï‚· Improved performance
2300ï‚· Improved code clarity
2301Procedure
2302A procedure is a type of subprogram that performs an action. A procedure can be stored in the database, as a schema object, for repeated execution.
2303Database Systems Lab Manual
2304Syntax for creating procedure:
2305Create [or replace] procedure <procedure_name>
2306[( parameter1 [mode1] datatype1,parameter2 [mode2] datatype2,..)]
2307Is| As
2308PL/SQL Block;
2309The replace option indicates that if the procedure exists, It will be dropped and replaced with the new version created by the statement. Parameter name of a PL/SQL variable whose value is passed to or populated by the calling environment.
2310Mode: type of argument
2311IN, OUT, IN OUT
2312IN : It is the default mode and value is passed into subprogram.
2313OUT : It must be specified and is returned to calling environment.
2314IN OUT: It is passed into subprogram and returned to calling environment.
2315IN parameter
2316IN parameters are passed as constants from the calling environment into the procedure.
2317Example:1
23181 create or replace procedure raise_salary
23192 (grade in job_grade.gra%type)
2320Database Systems Lab Manual
23213 is
23224 begin
23235 update job_grade set lowest_sal=lowest_sal*1.10 where gra= grade;
23246* end raise_salary;
23257 /
2326Procedure created.
2327SQL> execute raise_salary('a'); // executing procedure
2328PL/SQL procedure successfully completed.
2329SQL> select * from job_grade;
2330GRA LOWEST_SAL HIGHEST_SAL
2331---- ---------- -----------------------------------------
2332a 13200 4000
2333c 3000 6000
2334d 4000 10000
2335e 2000 6000
2336IN, OUT parameter
2337Example:1
2338Database Systems Lab Manual
23391 create or replace procedure info
23402 (g in job_grade.gra%type,
23413 l_sal out job_grade.lowest_sal%type,
23424 h_sal out job_grade.highest_sal%type)
23435 is
23446 begin
23457 select lowest_sal,highest_sal into l_sal,h_sal from job_grade where gra=g;
23468* end info;
23479 /
2348SQL> edit g:\oracle\sql\info1.sql
2349SQL> @g:\oracle\sql\info1
2350Procedure created.
2351How to view the value of OUT parameters with sql *plus
23521.Run the sql script file to generate and compile the source code.
23532.Create host variables in sql*plus, using the variable command
23543.Invoke the procedure, supplying these host variables as the OUT parameters.: reference the host variables in the execute command.
23554.To view the values passed from the procedure to the calling environment ,use the print command.
2356SQL> variable g_sal number;
2357SQL> variable g1_sal number;
2358Database Systems Lab Manual
2359SQL> execute info('a',:g_sal,:g1_sal)
2360PL/SQL procedure successfully completed.
2361SQL> print g_sal;
2362G_SAL
2363----------
236413200
2365SQL> print g1_sal;
2366G1_SAL
2367----------
23684000
2369IN OUT parameter
2370Example:1
23711 create or replace procedure info
23722 (g in out number)
23733 is
23744 begin
23755 select lowest_sal into g from job_grade where highest_sal=g;
23766* end info;
23777 /
2378Procedure created.
2379SQL> variable g_sal number;
23801 begin
23812 :g_sal:=4000;
2382Database Systems Lab Manual
23833* end;
2384PL/SQL procedure successfully completed.
2385SQL> print g_sal;
2386G_SAL
2387----------
23884000
2389SQL> execute info (:g_sal)
2390PL/SQL procedure successfully completed.
2391SQL> print g_sal;
2392G_SAL
2393----------
239413200
2395Methods for passing parameters
2396Positional : List actual parameters in the same order as formal parameters
2397Named : List actual parameters in library order by associating each with its
2398corresponding formal parameter
2399Combination : List some of the actual parameters as positional and some as named.
2400Removing procedures
2401Drop a procedure stored in the database
2402Database Systems Lab Manual
2403Syntax:
2404Drop procedure procedure_name
2405Example:
2406Drop procedure raise_salary;
2407Functions
2408A function is a named PL/SQL block that returns a value. A function can be stored in the database as a schema object for repeated execution. A function is called as part of an expression.
2409Syntax:
2410Create [or replace] function function_name
2411[(parameter1 [mode1] datatype1,
2412Parameter2 [mode2] datatype2,
2413….)]
2414Return datatype
2415Is/as
2416PL/SQL block ;
2417Example:
2418declare
2419summation number;
2420Database Systems Lab Manual
2421average number;
2422function summa(m4 number,m5 number) return number is
2423begin
2424return(m4+m5);
2425end;
2426function aver( summ1 number) return number is
2427begin
2428return(summ1/2);
2429end;
2430begin
2431summation:=summa(&m1,&m2);
2432average:=aver(summation);
2433dbms_output.put_line('summation is:'||summation);
2434dbms_output.put_line('average is:'||average);
2435end;
2436Removing functions
2437Drop function function_name
2438Example:
2439Drop function summa;
2440Packages
2441Database Systems Lab Manual
2442Packages bundle are related PL/SQL types, items, and subprograms into one container.
2443A package usually has a specification and a body, stored separately in the database.
2444Package specification
2445It is the interface to the application. It declares the types, variables, constants, exceptions, cursors and subprograms.
2446A package specification can exist without a package body, but a package body cannot exist without a package specification.
2447Syntax
2448Create [or replace] package package_name
2449is| as
2450Public type and item declarations
2451Subprograms specifications
2452End package_name;
2453Example:
24541 create or replace package commp is
24552 g_comm number:=0.10;
24563 procedure reset_comm
24574 (p_comm in number);
24585 end commp;
2459Package created.
2460Database Systems Lab Manual
2461Package body
2462Syntax
2463Create [or replace] package body package_name
2464Is| as
2465Private type and item declarations
2466Subprogram bodies
2467End package_name;
2468Example
24691 create or replace package body commp
24702 is
24713 function validate_comm(p_comm in number)
24724 return boolean
24735 is
24746 v_max_comm number;
24757 begin
24768 select max(lowest_sal) into v_max_comm from job_grade;
24779 if p_comm>v_max_comm then return(false);
247810 else return(true);
247911 end if;
248012 end validate_comm;
2481Database Systems Lab Manual
248213 procedure reset_comm(p_comm in number)
248314 is
248415 begin
248516 if validate_comm(p_comm)
248617 then g_comm:=p_comm;
248718 else
248819 raise_application_error(-20210,'invalid commision');
248920 end if;
249021 end reset_comm;
249122 end commp;
249223 /
2493Package body created.
2494Invoking package constructs:
2495SQL> execute commp.reset_comm(0.15);
2496PL/SQL procedure successfully completed.
2497SQL> create or replace package global_con is
24982 a constant number:=2;
24993 b constant number :=3;
25004 end global_con;
25015 /
2502Package created.
2503SQL> execute dbms_output.put_line('20 miles='||20*global_con.a||'km');
2504Database Systems Lab Manual
250520 miles=40km
2506PL/SQL procedure successfully completed.
2507Referencing a public variable from a stand alone procedure:
2508SQL> ed
2509Wrote file afiedt.buf
25101 create or replace procedure me( x in number, y out number)
25112 is
25123 begin
25134 y :=x *global_con.a;
25145 end me;
2515SQL> /
2516Procedure created.
2517SQL> variable ya number;
2518SQL> execute me(3,:ya);
2519PL/SQL procedure successfully completed.
2520SQL> print ya;
2521YA
2522----------
25236
2524Removing packages:
2525Database Systems Lab Manual
2526drop package package name;
2527drop package body package_name;
2528Overloading
2529It is the use of same name for different subprograms inside a PL/SQL block, a subprogram, or a package.
2530Example:
25311 create or replace package over
25322 is
25333 procedure add_dept(p_n in emp.ssn%type,p_na in emp.name%type);
25344 procedure add_dept(p_n in emp.ssn%type,p_na in emp.name%type,p_dept in emp.deptno%type);
25355 end over;
25366 /
2537Package created
25381 create package body overp is
25392 procedure add_dept(p_n emp.ssn%type,p_na emp.name%type)
25403 is
25414 begin
25425 insert into emp (ssn,name) values(p_n,p_na);
25436 end add_dept;
25447 procedure add_dept(p_n emp.ssn%type,p_na emp.name%type,p_dn emp.deptno%type)
25458 is
25469 begin
254710 insert into emp (ssn,name,deptno) values(p_n,p_na,p_dn);
2548Database Systems Lab Manual
254911 end add_dept;
255012 end overp;
2551Trigger
2552A trigger is aPL/SQL block or a PL/SQL procedure associated with a table ,view, schema, or the database. It executes implicitly whenever a particular event takes place.
2553It can be:
2554Application trigger: fires whenever an event occurs with a particular application
2555Database trigger: fires whenever a data event or system event occurs on a schema or database.
2556A triggering statement contains:
2557ï‚· Triggering timing
2558o For table: BEFORE, AFTER
2559o For view: INSTEAD OF
2560ï‚· Triggering event: INSERT, UPDATE, or DELETE
2561ï‚· Table name: on table, view
2562ï‚· Trigger type: row or statement
2563ï‚· When clause: restricting condition
2564ï‚· Trigger body: PL/SQL block
2565Trigger type
2566Statement trigger:The trigger body executes once for the triggering event. this is default. A statement trigger fires once, even if no rows are affected at all.
2567Row trigger:The trigger body executes once for each row affected by the triggering event. A
2568Database Systems Lab Manual
2569row trigger is not executed if the triggering event affects no rows.
2570Syntax:
2571CREATE [OR REPLACE] TRIGGER trigger_name
2572Timing
2573Event1 [OR event2 OR event3]
2574ON table_name
2575Trigger _body
2576Example:
2577create trigger ab
2578before insert or delete or update on a
2579for each row
2580begin
2581raise_application_error(-20000,'not accessible')
2582end
2583This program raises an error during insertion and deletion and update operation in a row.
2584_____________________________________________________________________
2585Database Systems Lab Manual