P5w1jnAs

1BASIC SQL COMMANDS
2SQL STATEMENTS
3SQL statements are classified as follows:
4Data Retrieval Statement:
5SELECT is the data extracting statement which retrieves the data from the database.
6Data Manipulation Language (DML):
7This language constitutes the statements that are used to manipulate with the data. It has
8three commands, which are INSERT, UPDATE and DELETE.
9Data Definition Language (DDL):
10This is the language used to define the structure of the tables. It sets up, changes, and
11removes data structures from the tables. It uses 5 commands, which are CREATE, ALTER,
12DROP, RENAME and TRUNCATE.
13Data Transaction Language (DTL):
14This is the language used to do undo and redo the transaction performed in the database.
15The commands are Commit, Rollback, and Save Point
16Data Control Language:
17This language is used to sanction the rights to the users to use the other user’s database
18objects. The commands are Grant, Revoke
19BASE SCHEMA
20EMPLOYEE
21 Name Type
22-------------------------- ----------------------
23 EMPLOYEE_ID NUMBER(3)
24 FIRST_NAME VARCHAR2(10)
25 LAST_NAME VARCHAR2(10)
26 MGR NUMBER(4)
27 HIRE_DATE DATE
28 JOB_ID VARCHAR2(10)
29 SALARY NUMBER(10)
30 COMMISION NUMBER(8)
31 DEPTNO NUMBER(2)
32DEPARTMENT
33Name Type
34--------------- -----------------
35DEPTNO NUMBER(2)
36DNAME VARCHAR2(14)
37LOC VARCHAR2(13)
38BONUS
39Name Type
40---------------- -------------------
41ENAME VARCHAR2(10)
42JOB VARCHAR2(9)
43SAL NUMBER(10,2)
44COMM NUMBER(10)
45JOBGRADE
46Name Type
47----------------- ---------------------
48JOB_ID VARCHAR2(10)
49GRADE NUMBER
50LOSAL NUMBER
51HISAL NUMBER
52DATA TYPES IN ORACLE:
53Data Type Description
54VARCHAR2(size) Variable-length character data
55CHAR(size) Fixed-length character data
56NUMBER(p,s) Variable-length numeric data
57DATE Date and time values
58LONG Variable-length character data up to 2 gigabytes
59CLOB Character data up to 4 gigabytes
60RAW and LONG RAW Raw binary data
61BLOB Binary data up to 4 gigabytes
62BFILE Binary data stored in an external file; up to 4 gigabytes
63ROWID A 64 base number system representing the unique address of a row in
64its table
65ORACLE 9I TABLE STRUCTURES
66 Table can be created at any time
67 No need to specify the size of table, the size is ultimately defined by the amount of space
68allocated to the database as a whole.
69 Tables can have up to 1000 columns
70NAMING RULES
71Table names and Column names
72ï‚· Must begin with a letter
73ï‚· Must be 1-30 characters long
74ï‚· Must contain only A-Z,a-z,0-9,_,$,#
75ï‚· Must not duplicate the name of another object owned by the same user
76ï‚· Must not be a reserved word
77Data Definition Language (DDL)
781. Create 2. Alter 3. Drop 4. Truncate 5. Rename
791. a. Creating a table
80Syntax:
81Create table <Table Name>
82( <Field1> <Data Type> <(width) <constraints> ,
83 <Field2> <Data Type> <(width)> <constraints>,
84..................................);
85Example:
86SQL> create table employee
87( employee_id number(3),
88first_name varchar2(10),
89last_name varchar2(10),
90mgr number(4),
91hire_date date,
92job_id varchar2(10),
93salary number(10),
94commision number(8),
95deptno number(2));
96Output:
97Table created.
98Example:
99SQL> create table department
100(deptno number(2),
101 dname varchar(14),
102 loc varchar(13));
103Output:
104Table created.
105Note:
106Other tables can be created in the similar way.
107b. To view the Structure of the table, desc command is used
108SQL> desc employee;
109 Name Null? Type
110-------------------------------------- -------- ---------------
111EMPLOYEE_ID NUMBER(3)
112 FIRST_NAME VARCHAR2(10)
113 LAST_NAME VARCHAR2(10)
114 MGR NUMBER(4)
115 HIRE_DATE DATE
116 JOB_ID VARCHAR2(10)
117 SALARY NUMBER(10)
118 COMMISION NUMBER(8)
119 DEPTNO NUMBER(2)
1202. Alter Table Statement:
121 Alter command is used to perform the following action on the table:
122a. Adding column in the existing table
123b. Increasing and decreasing the column size and changing data types
124c. Dropping column
125d. Renaming the column
126e. Adding and dropping constraints to the table( discussed in constraints topics)
127f. Enabling & disabling constraints in the table( discussed in constraints topics)
128a. To Add a column to the table (structure)
129Add option is used to add a new column
130Syntax:
131Alter Table <Table-Name> Add <Field Name> <Type> (width);
132Example:
133SQL> alter table employee add address varchar2 (20);
134Output:
135Table altered.
136b. To Modify a field of the table
137 Increase the width or precision of numeric column
138 Increase the width of numeric or character columns
139 Decrease the width of the column only if the column contains only null values or if the table
140has no rows
141 Change the data type only if the column contains null values
142Syntax:
143Alter Table <tablename> MODIFY ( <column name > < newdatatype>);
144Example:
145SQL> alter table employee modify address varchar2 (10);
146Output:
147Table altered.
148c. To Drop a field of the table
149Drop option is used to delete a column or remove a constraint
150Syntax:
151Alter Table <tablename> DROP COLUMN < column name>;
152Example:
153SQL> alter table employee drop column address;
154Output:
155Table altered.
156d.To rename a column
157Syntax:
158ALTER TABLE <tablename> RENAME COLUMN <oldcolumnname> TO
159<newcolumn name>
160Example:
161SQL> alter table employee rename column mgr to manager;
162Output:
163Table altered.
164ï‚· To Drop a table - Deletes a Table along with all contents
165Syntax:
166Drop Table <Table-Name>;
167Example:
168 Drop Table Student_table;
169Output:
170Table Dropped
171ï‚· To Truncate a table - Deletes all rows from a table ,retaining its structure
172Syntax: Truncate Table <tablename>
173Example:
174SQL> truncate table employee;
175Output:
176Table truncated.
177g. To rename a table- Renames a table with new name
178Syntax:
179Rename <oldtablename> To <newtablename>
180Example:
181SQL> rename employee to emp;
182Output:
183Table renamed
184Data manipulation Language (DML)
1851. Insert 2. Delete 3. Update 4.Select
1861 Insert command is used to load data into the table.
187a. Inserting values from user
188Syntax:
189Insert into <tablename> values ( val1,val2 …);
190Example:
191SQL> insert into department values(10,'accounts','chennai');
192Output:
1931 row created.
194b. Inserting values for the specific columns in the table
195Syntax:
196Insert Into <Table-Name> (Fieldname1, Fieldname2, Fieldname3,..) Values (value1, value2,
197value3,..);
198Example:
199SQL> insert into department (deptno,dname)values(20,'finance');
200Output:
2011 row created.
202c. Inserting interactively(Inserting ,ultiple rows by using single insert command)
203Syntax:
204Insert Into <tablename> Values( &<column name1> , &<column name2> …);
205Example:
206SQL> insert into employee values(&empid,'&fn','&ln',&mgr,'&hdate','&job',&sal,
207 &comm,&dept);
208Enter value for empid: 111
209Enter value for fn: Smith
210Enter value for ln: Ford
211Enter value for mgr: 222
212Enter value for hdate: 21-jul-2010
213Enter value for job: J1
214Enter value for sal: 30000
215Enter value for comm: 0.1
216Enter value for dept: 10
217old 2: &comm,&dept)
218new 2: 0.1,10)
219Output:
2201 row created.
221Note: Column names of character and date type should be included with in single quotation.
222ï‚· Inserting null values
223Syntax:
224Insert Into <tablename> Values ( val1,’ ‘,’ ‘,val4);
225Example:
226insert into department values( ‘101’,’’,chennai);
227Output:
2281 row created.
2292. To Delete rows from a table
230Syntax:
231Delete from <table name> [where <condition>];
232Example:
233a) TO delete all rows:
234SQL> delete from department;
235Output:
23689 rows deleted.
237b) conditional deletion:
238SQL> delete from department where loc='chennai';
239Output:
2401 row deleted.
2413. Modifying (Updating) Records:
242a. Updating single column
243Syntax:
244UPDATE <table name> Set <Field Name> = <Value> Where <Condition>;
245Example:
246SQL> update department set loc='Hyderabad' where deptno=20;
247Output:
2481 row updated.
249Note: Without where clause all the rows will get updated.
250b. Updating multiple column [while updating more than one column, the column must be
251separated by comma operator]
252Example: SQL> update department set loc='Hyderabad', dname= ‘cse’ where deptno=20;
253Output:
2541 row updated.
2554. Selection of Records [Retrieving (Displaying) Data:]
256Syntax:
257Select <field1, field2 …fieldn> from <table name> where <condition>;
258Example:
259a) SQL> select * from department;
260Output:
261 DEPTNO DNAME LOC
262---------- -------------- -------------
263 10 accounts chennai
264 20 finance Hyderabad
265 30 IT Bangalore
266 40 marketing chennai
267Example:
268b) SQL> select dname, loc from department;
269Output:
270DNAME LOC
271-------------- -------------
272accounts chennai
273finance Hyderabad
274IT Bangalore
275marketing Chennai
276ï‚· Using Alias name for a field
277Syntax:
278Select <col1> <alias name 1> , <col2> < alias name 2> from < tab1>;
279Example:
280SQL> select dname, loc as location from department;
281Output:
282DNAME LOCATION
283-------------- -------------
284accounts chennai
285finance Hyderabad
286IT Bangalore
287marketing chennai
288ï‚· With distinct clause [Used to retrieve unique value from the column]
289Syntax:
290Select distinct <col2> from < tab1>;
291Example:
292SQL> select distinct loc from department;
293Output:
294LOC
295-------------
296chennai
297Bangalore
298Hyderabad
299ï‚· Creating Table using subquery
300Syntax:
301Create table <new _table_name> as Select <column names> from <old_table_name>;
302Example:
303SQL> create table copyOfEmp as select * from employee;
304Output:
305Table created.
306 To view the contents of new Table
307SQL> select * from copyofemp;
308Output:
309EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER HIRE_DATE JOB_ID
310SALARY COMMISION DEPTNO
311 111 Smith Ford 222 21-JUL-10 J1 30000
3120.1 1 0
313ï‚· To create a table with same structure as an existing table
314Syntax:
315Create table <new _table_name> as Select <column names> from<old_table_name>
316where 1=2;
317Example:
318create table copyOfEmp2 as select * from employee where 1=2;
319Output:
320Table created.
321SQL> select * from copyofemp2;
322Output:
323no rows selected
324SQL> desc copyofemp2;
325Output:
326 Name Null? Type
327----------------------------------------- -------- -----------------
328 EMPLOYEE_ID NUMBER(3)
329 FIRST_NAME VARCHAR2(10)
330 LAST_NAME VARCHAR2(10)
331 MANAGER NUMBER(4)
332 HIRE_DATE DATE
333 JOB_ID VARCHAR2(10)
334 SALARY NUMBER(10)
335 COMMISION NUMBER(8)
336 DEPTNO NUMBER(2)
337Note: Only structure of table alone is copied and not the contents.
338ï‚· Inserting into table using a subquery
339Syntax :
340Insert into <new_table_name> (Select <columnnames> from <old_table_name>);
341Example:
342SQL> insert into copyofemp2 (select * from employee where employee_id > 100);
343Output:
34450 rows created.
345Constraints
346ï‚· Constraints enforce rules on the table whenever rows is inserted, updated and deleted
347from the table.
348ï‚· Prevents the deletion of a table if there are dependencies from other tables.
349ï‚· Name a constraints or the oracle server generate name by using SYS_cn format.
350ï‚· Define the constraints at column or table level. constraints can be applied while creation
351of table or after the table creation by using alter command.
352ï‚· View the created constraints from User_Constraints data dictionary.
353
354Constraints Types
355CONSTRAINT DESCRIPTION
356NOT NULL Specifies that a column must have some value.
357UNIQUE Specifies that columns must have unique values.
358PRIMARY KEY Specifies a column or a set of columns that uniquely identifies as row.
359It does not allow null values.
360FOREIGN KEY Foreign key is a column(s) that references a column(s) of a table.
361CHECK Specifies a condition that must be satisfied by all the rows in a table.
3621. Creating Constraints without constraint name
363Syntax:
364CREATE TABLE < tablename> (
365<column name 1> < datatype>,
366<column name 2> < datatype> UNIQUE ,
367<column name 3> < datatype> ,
368 PRIMARY KEY ( <column name2>)
369);
370Example:
371 CREATE TABLE emp_demo2
372 ( employee_id NUMBER(6) PRIMARY KEY,
373 first_name VARCHAR2(20) NOT NULL,
374 last_name VARCHAR2(25) NOT NULL,
375 email VARCHAR2(25) UNIQUE,
376 phone_number VARCHAR2(20) UNIQUE,
377 job_id VARCHAR2(10),
378 salary NUMBER(8,2) CHECK(SALARY>0),
379 deptid NUMBER(4)
380 ) ;
3812. Creating constraints with constraint name
382Example:
383CREATE TABLE < tablename1> (
384 <column name 1> < datatype> CONSTRAINT <constraint name1> UNIQUE,
385<column name 2> < datatype> CONSTRAINT <constraint name2> NOT NULL,
386constraint < constraint name3 > PRIMARY KEY ( <column name1>),
387constraint <constraint name4> FOREIGN KEY (<column name2>)
388REFERENCES <tablename2> (<column name1>)
389);
390Example:
391 CREATE TABLE emp_demo3
392 ( employee_id NUMBER(6) CONSTRAINT emp_eid PRIMARY KEY,
393 first_name VARCHAR2(20),
394 last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL,
395 email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL,
396 phone_number VARCHAR2(20),
397 job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,
398 salary NUMBER(8,2) CONSTRAINT emp_salary_nn NOT NULL,
399 deptid NUMBER(4), CONSTRAINT emp_dept FOREIGN KEY(deptid)
400 REFERENCES department(deptid) ,
401 CONSTRAINT emp_salary_min CHECK (salary > 0) ,
402 CONSTRAINT emp_email_uk UNIQUE (email)
403 ) ;
4043. With check constraint
405Syntax:
406CREATE TABLE < tablename> (
407<column name1 > < datatype> ,
408 <column name 2> < datatype>,
409 CHECK ( < column name 1 > in ( values) )
410CHECK ( < column name 2 > between <val1> and <val2> ) );
411Example:
412CREATE TABLE emp_demo4
413 ( emp_id NUMBER(6),
414emp_name VARCHAR2(15),
415 salary NUMBER(10)CHECK (salary between 1000 and 10000)
416 );
417Adding Constriants
418Constraints can be added after the table creation by using alter command
419Syntax: Add constraints
420ALTER TABLE <tablename> ADD CONSTRAINT <constraint_name> constriant_type
421(<column name>);
422Examples:
423ALTER TABLE emp_demo4 ADD CONSTRAINT con_pk1 PRIMARY KEY(emp_id);
424ALTER TABLE emp_demo4 ADD CONSTRAINT con_emp_uk UNIQUE(phoneno);
425ALTER TABLE emp_demo4 ADD CONSTRAINT con_empfk FOREIGN KEY(DNO)
426REFERENCES department(dno);
427ALTER TABLE emp_demo4 ADD CONSTRAINT con_emp_ck CHECK ( salary >0 );
428ALTER TABLE emp_demo4 MODIFY (<Column name> <datatype> CONSTRAINT
429constraint_name NOT NULL);
430Drop Constraints
431Syntax
432ALTER TABLE <tablename> DROP CONSTRAINT < constraint name >;
433Drop the unique key on the email column of the employees table:
434 e.g ALTER TABLE employees DROP UNIQUE (email);
435CASCADE Constraints
436The CASCADE Constraints clause is used along with the Drop Column Clause.
437• A foreign key with a cascade delete means that if a record in the parent table is deleted,
438then the corresponding records in the child table will automatically be deleted. This is
439called a cascade delete.
440• A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or
441an ALTER TABLE statement.
442Syntax:
443CREATE TABLE table_name
444(column1 datatype null/not null,
445column2 datatype null/not null,
446...
447CONSTRAINT fk_column
448FOREIGN KEY (column1, column2, ... column_n)
449REFERENCES parent_table (column1, column2, ... column_n)
450ON DELETE CASCADE
451);
452Example:
453CREATE TABLE supplier
454(supplier_id number(10)not null,
455supplier_namevarchar2(50)not null,
456contact_namevarchar2(50),
457CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));
458CREATE TABLE products
459(product_id number(10)not null,
460suppl_id number(10) not null,
461CONSTRAINT fk_supplier FOREIGN KEY (suppl_id) REFERENCES
462supplier(supplier_id) ON DELETE CASCADE);
463Because of the cascade delete, when a record with a particular supplier_ id is deleted
464from supplier table ,then all the records of the same supplier_id will be deleted from products
465table also.
466Operators in SQL*PLUS
467Type Symbol / Keyword Where to use
468Arithmetic + , - , * , / To manipulate numerical column
469values, WHERE clause
470Comparison =, !=, <, <=, >, >=, between,
471not between, in, not in, like,
472not like
473WHERE clause
474Logical and, or, not WHERE clause,
475Combining two queries
476ï‚· Between
477Example:
478SQL> select first_name, deptno from employee where salary between 20000 and 35000;
479Output:
480FIRST_NAME DEPTNO
481---------- ----------
482Smith 10
483ï‚· IN
484Example:
485SQL> select first_name, deptno from employee where job_id in ('J1','J2');
486Output:
487FIRST_NAME DEPTNO
488---------- ----------
489Smith 10
490Arun 30
491Nithya 10
492ï‚· NOT IN
493Example:
494SQL> select dname,loc from department where loc not in ('chennai','Bangalore');
495Output:
496DNAME LOC
497-------------- -------------
498finance Hyderabad
499ï‚· Like
500Use the LIKE condition to perform wild card searches of valid search string values.
501Search conditions can contain either characters or numbers
502 % - denotes zero or many characters.
503 _ - denotes one character.
504Example:
505SQL> select dname,loc from department where loc like 'c%';
506Output:
507DNAME LOC
508-------------- -------------
509accounts chennai
510marketing Chennai
511Example:
512SQL> select dname,loc from department where loc like 'chen_ _ _';
513Output:
514DNAME LOC
515-------------- -------------
516accounts chennai
517marketing Chennai
518Example:
519SQL> select dname,loc from department where loc not like 'c%';
520Output:
521DNAME LOC
522-------------- -------------
523finance Hyderabad
524IT Bangalore
525ï‚· Between
526Example:
527SQL> select first_name, deptno, salary from employee where salary not between 20000 and
52835000;
529Output:
530FIRST_NAME DEPTNO SALARY
531---------- ---------- ----------
532Arun 30 40000
533Nithya 10 45000
534Note: Inserting null value into location column of department table
535Example:
536SQL> insert into department(deptno,dname) values(40,'Sales');
537Output:
5381 row created.
539ï‚· Is Null
540Example:
541SQL> select * from department where loc is null;
542Output:
543 DEPTNO DNAME LOC
544---------- -------------- -------------
545 40 Sales
546Example:
547SQL> select * from department where loc is not null;
548Output:
549 DEPTNO DNAME LOC
550---------- -------------- -------------
551 10 accounts chennai
552 20 finance Hyderabad
553 30 IT Bangalore
554 40 marketing chennai
555LOGICAL OPERATORS: Used to combine the results of two or more conditions to produce a
556single result. The logical operators are: OR, AND, NOT.
557Operator Precedence
558ï‚· Arithmetic operators-Highest precedence
559ï‚· Comparison operators
560ï‚· NOT operator
561ï‚· AND operator
562ï‚· OR operator----Lowest precedence
563The order of precedence can be altered using parenthesis.
564Example:
565SQL> select first_name, deptno, salary from employee where salary > 20000 ;
566Output:
567FIRST_NAME DEPTNO SALARY
568---------- ---------- ----------
569Smith 10 30000
570Arun 30 40000
571Nithya 10 45000
572Example:
573SQL> select first_name, deptno, salary from employee
574where salary > 20000 and salary < 35000;
575Output:
576FIRST_NAME DEPTNO SALARY
577---------- ---------- ----------
578Smith 10 30000
579Example:
580SQL> select first_name, deptno, salary+100 from employee where salary > 35000;
581Output:
582FIRST_NAME DEPTNO SALARY+100
583---------- ---------- ----------
584Arun 30 40100
585Example:
586SQL> update employee set salary = salary+salary*0.1 where employee_id = 111;
587Output:
5881 row updated.
589Example:
590SQL> select * from department where loc = 'chennai' or dname='IT';
591Output:
592 DEPTNO DNAME LOC
593---------- -------------- -------------
594 10 accounts chennai
595 30 IT Bangalore
596 40 marketing chennai
597FUNCTIONS
598ï‚· Single Row Functions
599ï‚· Group functions
600Single Row Functions
601Returns only one value for every row can be used in SELECT command and included in
602WHERE clause
603Types
604ï‚· Character functions
605ï‚· Numeric functions
606ï‚· Date functions
607CHARACTER FUNCTIONS:
608Character functions accept a character input and return either character or number values. Some
609of them supported by Oracle are listed below
610Syntax Description
611initcap (char) Changes first letter to capital
612lower (char) Changes to lower case
613upper (char) Changes to upper case
614ltrim ( char, set) Removes the set from left of char
615rtrim (char, set) Removes the set from right of char
616translate(char, from, to) Translate ‘from’ anywhere in char to ‘to’
617replace(char, search string, replace string) Replaces the search string to new
618substring(char, m , n) Returns chars from m to n length
619lpad(char, length, special char) Pads special char to left of char to Max of
620length
621rpad(char, length, special char) Pads special char to right of char to Max of
622length
623chr(number) Returns char equivalent
624length(char) Length of string
625Examples:
626Function Input Output
627Initcap(char) SQL>select initcap(‘hello’) from dual; Hello
628Lower(char) SQL>select lower(‘FUN’) from dual; fun
629Upper(char) SQL>select upper(‘sun’) from dual; SUN
630Ltrim(char, set) SQL>select ltrim(‘xyzhello’,’xyz’) from dual; hello
631Rtrim(char, set) SQL>select rtrim(‘xyzhello’,’llo’) from dual; xyzhe
632translate(char,from,to) SQL>select translate(‘jack’,’j’,’b’) from dual; back
633Replace(char,from,to) SQL>select replace(‘jack and jue’,’ j’, ’bl’) from
634dual;
635black and blue
636Example:
637SQL> select initcap(dname) from department;
638Output:
639INITCAP(DNAME)
640--------------
641Accounts
642Finance
643It
644Marketing
645Sales
646Lpad is a function that takes three arguments. The first argument is the character string which
647has to be displayed with the left padding. The second is the number which indicates the total
648length of the return value, the third is the string with which the left padding has to be done when
649required.
650Example:
651SQL> select lpad(dname,15,'*') lpd from department;
652Output:
653LPD
654---------------
655*******accounts
656********finance
657*************IT
658******marketing
659**********Sales
660Example:
661SQL> select rpad(dname,15,'*') rpd from department;
662Output:
663RPD
664---------------
665accounts*******
666finance********
667IT*************
668marketing******
669Sales**********
670Length: returns the length of a string
671Example:
672SQL> select dname, length(dname) from department;
673Output:
674DNAME LENGTH(DNAME)
675-------------- -------------
676accounts 8
677finance 7
678IT 2
679marketing 9
680Sales 5
681Concatenation || operator: is used to merge or more strings.
682Example:
683SQL> select dname || ' is located in ' || loc from department;
684Output:
685DNAME||'ISLOCATEDIN'||LOC
686------------------------------------------
687accounts is located in chennai
688finance is located in Hyderabad
689IT is located in Bangalore
690marketing is located in chennai
691Sales is located in
692NUMERIC FUNCTIONS:
693Numeric functions accept numeric input and returns numeric values as output.
694Syntax Description
695abs ( ) Returns the absolute value
696ceil ( ) Rounds the argument
697cos ( ) Cosine value of argument
698exp ( ) Exponent value
699floor( ) Truncated value
700power (m,n) N raised to m
701mod (m,n) Remainder of m / n
702round (m,n) Rounds m’s decimal places to n
703trunc (m,n) Truncates m’s decimal places to n
704sqrt (m) Square root value
705Function Input Output
706Abs( n) SQL>select abs(-15) from dual 15
707Ceil(n) SQL>select ceil(48.778) from dual; 49
708Cos(n) SQL>select cos(180) from dual; -0.59884601
709Cosh(n): SQL>select cosh(0) from dual; 1
710Exp(n) SQL>select exp(4) from dual; 54.59815
711Floor(n) SQL>select floor(4.678) from dual; 4
712Power(m ,n) SQL>select power(5,2) from dual; 25
713Mod(m ,n) SQL>select mod(11,2) from dual; 1
714Round(m ,n) SQL>select round(112.257,2) from dual; 112.26
715Example:
716SQL> select ln (2) from dual; (returns natural logarithm value of 2)
717SQL>select sign (-35) from dual; (output is -1)
718CONVERSION FUNCTIONS: Convert a value from one data type to another.
719ï‚· To__char ( )
720To_ char (d [,fmt]) where d is the date fmt is the format model which specifies the format of the
721date. This function converts date to a value of varchar2datatype in a form specified by date
722format fmt.if fmt is neglected then it converts date to varchar2 in the default date format.
723Example:
724SQL> select to_char (hire_date, 'ddth "of" fmmonth yyyy') from employee;
725Output:
726TO_CHAR(HIRE_DATE,'DDT
727----------------------
72821st of july 2010
72905th of june 2008
73012th of february 1999
731ï‚· To_ date ( )
732The format is to_date (char [, fmt]). This converts char or varchar data type to date data type.
733Format model, fmt specifies the form of character.
734Example:
735SQL>select to_date (‘December 18 2007’,’month-dd-yyyy’) from dual;
736Output:
73718-DEC-07 is the output.
738Example:
739SQL> select round(hire_date,'year') from employee;
740Output:
741ROUND(HIR
742---------
74301-JAN-11
74401-JAN-08
74501-JAN-99
746ï‚· To_ Number( )
747Allows the conversion of string containing numbers into the number data type on which
748arithmetic operations can be performed.
749Example:
750SQL> select to_number (‘100’) from dual;
751DATE FUNCTIONS
752Example:
753SQL> select sysdate from dual;
754Output:
755SYSDATE
756---------
75722-JUL-10
758Example:
759SQL> select hire_date from employee;
760Output:
761HIRE_DATE
762---------
76321-JUL-10
76405-JUN-08
76512-FEB-99
766Function Name Return Value
767ADD_MONTHS (date, n) Returns a date value after adding ’n’months to the date ’x’.
768MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2.
769ROUND (x, date_format)
770Returns the date ‘x’rounded off to the nearest century, year,
771month, date, hour, minute, or second as specified by the
772‘date_format’.
773TRUNC (x, date_format)
774Returns the date ‘x’ lesser than or equal to the nearest century,
775year, month, date, hour, minute, or second as specified by the
776‘date_format’.
777NEXT_DAY (x, week_day) Returns the next date of the ‘week_day’on or after the date ‘x’
778occurs.
779LAST_DAY (x) It is used to determine the number of days remaining in a
780month from the date 'x'specified.
781SYSDATE Returns the systems current date and time.
782Example:
783SQL> select add_months(hire_date,3) from employee;
784Output:
785ADD_MONTH
786---------
78721-OCT-10
78805-SEP-08
78912-MAY-99
790Example:
791SQL> select months_between(sysdate,hire_date) from employee;
792Output:
793MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
794---------------------------------
795 .047992085
796 25.5641211
797 137.338315
798Example:
799SQL> select next_day(hire_date,'wednesday') from employee;
800Output:
801NEXT_DAY(
802---------
80328-JUL-10
80411-JUN-08
80517-FEB-99
806Example:
807SQL> select last_day(hire_date) from employee;
808Output:
809LAST_DAY(
810---------
81131-JUL-10
81230-JUN-08
81328-FEB-99 
814ï‚· Group Functions: -Result based on group of rows.
815Group functions operate on sets of rows to give one result per group Employees
816Dept_id Salary
81790 5000
81890 10000
81990 10000
82060 5000
82160 5000
822Types of Group Functions
823Syntax Description
824count (*),
825count (column name),
826count (distinct column name)
827Returns number of rows
828min (column name) Min value in the column
829max (column name) Max value in the column
830avg (column name) Avg value in the column
831sum (column name) Sum of column values
832Group Functions Syntax:
833Select [column,] group_function(column),..
834From table
835[where condition]
836[GROUP BY column];
837
838The maximum
839salary in the
840employees
841table
842Max(salary)
84310000
844Example:
845Q.Display the average,highest, lowest and sum of salaries for all the sales representatives.
846A. Select avg(salary), max(salary), min(salary), sum(salary) From employees where
847 job_id like ‘%rep%’;
848Groups of Data :Divide rows in a table in to smaller groups by using the group by clause
849Employee Table
850Dept_id Salary
85110 4000
85210 5000
85310 6000
85450 5000
85550 3000
856
857SET OPERATORS: UNION,UNION ALL,DIFFERENCE,MINUS
858Example:
859sql> select first_name from employees union select name from sample ;
860Output:
861FIRST_NAME
862----------
863DHANA
864GUNA
865JAI
866JAISANKAR
867KUMAR
868RAJA
869VENKAT
870D_id Avg(Salary)
87110 5000
87250 4000
873The
874average
875salary in
876employees
877table for
878each
879department
880Example:
881sql> select first_name from employees union all select name from sample ;
882Output:
883FIRST_NAME
884----------
885VENKAT
886JAI
887DHANA
888GUNA
889JAISANKAR
890VENKAT
891RAJA
892KUMAR
893Example:
894sql> select first_name from employees intersect select name from sample ;
895Output:
896FIRST_NAME
897----------
898VENKAT
899Example:
900sql> select first_name from employees minus select name from sample ;
901Output:
902FIRST_NAME
903----------
904DHANA
905GUNA
906JAI
907ï‚· JOINS :A join is the SQL way of combining the data from many tables. It is performed by
908WHERE Clause which combines the specified rows of the tables.
909Type Sub type Description
910Simple join Equi join ( = )
911Non – equi join (<, <=, >, >=,
912!=, < > )
913Joins rows using equal value of
914the column
915Joins rows using other relational
916operators(except = )
917Self join -- ( any relational operators) Joins rows of same table
918Outer join Left outer join ((+) appended to
919left operand in join condition)
920Right outer join ((+) appended to
921right operand in join condition)
922Rows common in both tables and
923uncommon rows have null value
924in left column
925Vice versa
926Simple Join:
927a. EQUI JOIN OR INNER JOIN : A column (or multiple columns) in two or more tables
928match.
929Syntax:
930SELECT <column_name(s)>
931FROM <table_name1>
932INNER JOIN <table_name2>
933ON <table_name1.column_name>=<table_name2.column_name>;
934Example 1 :
935SELECT employee.first_name, department.dname
936FROM employee INNER JOIN department
937ON employee.deptno = department.deptno;
938Output:1
939 DEPTNO FIRST_NAME
940 ---------- ----------
941 10 Smith
942 30 Arun
943 10 Nithya
944Oracle automatically defaults the JOIN to INNER so that the INNER keyword is not required.
945They are the same query, though. It is preferred not to type the INNER keyword.
946Example 2 using where Condition:
947SELECT employee.ename, department.dname
948FROM employee JOIN department
949ON employee.deptno = department.deptno
950WHERE department.dname = 'SALES';
951 Output 2:
952 DEPTNO FIRST_NAME
953 ---------- ----------
954 10 Smith
955 30 Arun
956 10 Nithya
957b. SELF JOIN :Is a join where a table is joined to itself.
958Syntax:
959SELECT <column_name(s)>
960FROM <table_name1>
961JOIN <table_name2>
962ON <table_name1.column_name>=<table_name1.column_name>;
963Example1:
964SELECT e1.first_name, e2.first_name
965FROM employee e1 join employee e2
966on e1.mgr = e2.employee_id;
967OR
968SELECT e1.first_name, e2.first_name
969FROM employee e1 join employee e2
970where e1.mgr = e2.employee_id;
971Output:
972FIRST_NAME FIRST_NAME
973---------------- --------------------
974john john
975An alias is just a way to refer to a column or table with a UNIQUE name. If we try to call both of
976the instances of the table EMP, Oracle wouldn't know which table instance I meant. Using an
977alias clears that right up.
978c. OUTER JOIN
979An outer join tells Oracle to return the rows on the left or right (of the JOIN clause) even if
980there are no rows.
981The LEFT OUTER keyword to the JOIN clause says, return the rows to the left (in this case
982DEPARTMENT) even if there are no rows on the right (in this case employee).
983Syntax:
984SELECT <column_name(s)>
985FROM <table_name1>
986LEFT OUTER JOIN <table_name2>
987ON <table_name1.column_name>=<table_name2.column_name>;
988Example:
989SELECT department.dname, employee.first_name
990FROM department LEFT OUTER JOIN employee
991ON department.deptno = employee.deptno
992WHERE department.dname = 'marketing’;
993Output:
994DNAME FIRST_NAME
995-------------- ----------
996Marketing
997The RIGHT OUTER keyword to the JOIN clause says ,return the rows to the right (in this case
998DEPARTMENT) even if there are no rows on the left (in this case employee).
999Syntax:
1000SELECT <column_name(s)>
1001FROM <table_name1>
1002RIGHT OUTER JOIN <table_name2>
1003ON <table_name1.column_name>=<table_name2.column_name>;
1004Example:
1005SELECT employee.first_name, department.dname
1006FROM employee RIGHT OUTER JOIN department
1007ON employee.deptno = department.deptno
1008WHERE department.dname = 'marketing';
1009Output:
1010FIRST_NAME DNAME
1011---------- --------------
1012 marketing
1013d. FULL OUTER JOIN
1014Let's insert a new record into the employee table:
1015INSERT INTO EMPLOYEE (employee_id, first_name, last_name, mgr, hiredate, job-id,sal,
1016comm, deptno) VALUES (9999, 'Joe ‘,’Blow', 7698, sysdate ,0008, 10500, 0, NULL );
1017Note:
1018We inserted an employee record that has no department. How can we get the records for
1019all employees AND all departments? We would use the FULL OUTER join syntax:
1020Syntax:
1021SELECT <column_name(s)>
1022FROM <table_name1>
1023FULL OUTER JOIN <table_name2>
1024ON <table_name1.column_name>=<table_name2.column_name>;
1025Example:
1026SELECT employee.first_name, department.dname
1027FROM employee FULL OUTER JOIN department
1028ON employee.deptno = department.deptno;
1029Output:
1030FIRST_NAME DNAME
1031---------- --------------
1032Nithya accounts
1033Smith accounts
1034 finance
1035john IT
1036Arun IT
1037 marketing
1038john
1039e.Cross Join
1040Displays all the rows and all the colums of both the tables.
1041Synatx:
1042SELECT <column_name(s)> FROM <table_name1> CROSS JOIN<table_name2>;
1043Example:
1044select employee.deptno from employee cross join department;
1045Or
1046select employee.deptno from employee,department;
1047Output:
1048 DEPTNO
1049----------
1050 10
1051 10
1052 10
1053 10
1054 30
1055 30
1056 30
1057 30
1058 10
1059 10
1060 10
1061 DEPTNO
1062----------
1063 10
1064 30
1065 30
1066 30
1067 30
1068f. Natural Join
1069If two tables have same column name the values of that column will be displayed only once.
1070Syntax:
1071SELECT <column_name(s)> FROM <table_name1> Natural JOIN<table_name2>;
1072Example:
1073select deptno,first_name from employee natural join department;
1074Output:
1075DEPTNO FIRST_NAME
1076------ ----------
1077 10 Smith
1078 30 Arun
1079 10 Nithya
1080 30 john
1081SUB QUERIES
1082ï‚· Nesting of queries
1083ï‚· A query containing a query in itself
1084ï‚· Inner most sub query will be executed first
1085ï‚· The result of the main query depends on the values return by sub query
1086ï‚· Sub query should be enclosed in parenthesis
10871. Sub query returning only one value
1088 a. Relational operator before sub query.
1089Syntax:
1090SELECT <column_name(s)> FROM <table_name> WHERE < column name >
1091 < relational op.> < sub query>;
1092Example:
1093SELECT employee_id ,first_name FROM employee
1094WHERE deptno =
1095(SELECT deptno FROM department
1096WHERE dname = ‘IT’)
1097Output:
1098EMPLOYEE_ID FIRST_NAME
1099----------- ----------
1100 112 Arun
1101 114 john
11022. Sub query returning more than one value
1103 a. ANY
1104For the clause any, the condition evaluates to true if there exists at least on row selected by the
1105sub query for which the comparison holds. If the sub query yields an empty result set, the
1106condition is not satisfied.
1107Syntax:
1108SELECT <column_name(s)>
1109FROM <table_name>
1110 WHERE < column name >
1111 < relational op.> ANY (<sub query>);
1112Example:
1113SELECT employee_id ,first_name FROM employee
1114WHERE salary>= ANY
1115(SELECT salary FROM employee
1116WHERE deptno = 30)
1117AND deptno = 10;
1118Output:
1119EMPLOYEE_ID FIRST_NAME
1120----------- ----------
1121 113 Nithya
1122 112 Arun
1123 111 Smith
1124 114 john
1125 114 john
1126b. ALL
1127For the clause all, in contrast, the condition evaluates to true if for all rows selected by the sub
1128query the comparison holds. In this case the condition evaluates to true if the Sub query does not
1129yield any row or value.
1130Syntax:
1131SELECT <column_name(s)>
1132FROM <table_name>
1133WHERE < column name > < relational op.> ALL (<sub query>);
1134Example:
1135SELECT employee_id ,first_name FROM employee
1136WHERE salary > ALL
1137(SELECT salary FROM employee
1138WHERE deptno = 30);
1139Output:
1140EMPLOYEE_ID FIRST_NAME
1141----------- ----------
1142 113 Nithya
1143c. IN :Main query displays the values that match with any of the values returned by sub query.
1144Syntax:
1145SELECT <column_name(s)>
1146FROM <table_name>
1147WHERE < column name > IN (<sub query>);
1148Example:
1149SELECT employee_id ,first_name FROM employee
1150WHERE deptno IN
1151(SELECT deptno FROM department
1152WHERE loc = ’Bangalore’);
1153Output:
1154EMPLOYEE_ID FIRST_NAME
1155----------- ----------
1156 114 john
1157 112 Arun
1158d. NOT IN
1159Main query displays the values that match with any of the values returned by sub query.
1160Syntax:
1161SELECT <column_name(s)>
1162FROM <table_name>
1163 WHERE < column name > NOT IN (<sub query>);
1164Example:
1165SELECT employee_id ,first_name FROM employee
1166WHERE deptno NOT IN
1167(SELECT deptno FROM department
1168WHERE loc = ’Bangalore’);
1169Output:
1170EMPLOYEE_ID FIRST_NAME
1171----------- ----------
1172 113 Nithya
1173 111 Smith
1174e. EXISTS
1175Main query displays the values that match with any of the values returned by sub query.
1176Syntax:
1177SELECT <column_name(s)>
1178FROM <table_name>
1179WHERE EXISTS (<sub query>);
1180Example:
1181SELECT * FROM department
1182WHERE EXISTS
1183(SELECT * FROM employee
1184WHERE deptno = department.deptno);
1185Output:
1186 DEPTNO DNAME LOC
1187 ---------- -------------- -------------
1188 10 accounts chennai
1189 30 IT Bangalore
1190f. NOT EXISTS
1191Main query displays the values that match with any of the values returned by sub query.
1192Syntax:
1193SELECT <column_name(s)>
1194FROM <table_name>
1195WHERE NOT EXISTS (<sub query>);
1196Example:
1197SELECT * FROM department
1198WHERE NOT EXISTS
1199(SELECT * FROM employee
1200WHERE deptno = department.deptno);
1201Output:
1202 DEPTNO DNAME LOC
1203 ---------- -------------- -------------
1204 20 finance Hyderabad
1205 40 marketing chennai
1206g. GROUP BY CLAUSE
1207Often applications require grouping rows that have certain properties and then applying an
1208aggregate function on one column for each group separately. For this, SQL provides the clause
1209group by <group column(s)>. This clause appears after the where clause and must refer to
1210columns of tables listed in the from clause.
1211Rule:
1212Select attributes and group by clause attributes should be same.
1213Syntax:
1214SELECT <column_name(s)>
1215FROM <table_name>
1216Where <conditions>
1217GROUP BY <column2>, <column1>;
1218Example:
1219SELECT deptno, min(salary), max(salary)
1220FROM employee
1221GROUP BY deptno;
1222Output:
1223 DEPTNO MIN(SALARY) MAX(SALARY)
1224 --------- ----------- -----------
1225 30 30000 40000
1226 30000 30000
1227 10 33000 45000
1228h. HAVING CLAUSE: used to apply a condition to group by clause
1229Syntax:
1230SELECT <column(s)>
1231FROM <table(s)>
1232WHERE <condition>
1233[GROUP BY <group column(s)>]
1234[HAVING <group condition(s)>];
1235Example:
1236SELECT deptno, min(salary), max(salary)
1237FROM employee
1238WHERE job_id = ’J2’
1239GROUP BY deptno
1240HAVING count(*) > 1;
1241Output:
1242 DEPTNO MIN(SALARY) MAX(SALARY)
1243---------- ----------- -----------
1244 30 13000 40000
1245A query containing a group by clause is processed in the following way:
12461. Select all rows that satisfy the condition specified in the where clause.
12472. From these rows form groups according to the group by clause.
12483. Discard all groups that do not satisfy the condition in the having clause.
12494. Apply aggregate functions to each group.
12505. Retrieve values for the columns and aggregations listed in the select clause.
1251i. ORDER BY
1252Used along with where clause to display the specified column in ascending
1253order or descending order .Default is ascending order
1254Syntax:
1255SELECT [distinct] <column(s)>
1256FROM <table>
1257[ WHERE <condition> ]
1258[ ORDER BY <column(s) [asc|desc]> ]
1259Example:
1260SELECT first_name, deptno, hire_date
1261FROM employee
1262ORDER BY deptno ASC, hire_date desc;
1263Output:
1264FIRST_NAME DEPTNO HIRE_DATE
1265 ---------- ---------- ---------
1266 Smith 10 21-JUL-10
1267 Nithya 10 12-FEB-99
1268 john 30 20-JAN-10
1269 Arun 30 05-JUN-08
1270 john 20-JAN-10
1271
1272
1273
1274
1275
1276
1277
1278PLSQL
1279What is PL/SQL?
1280PL/SQL stands for Procedural Language extension of
1281SQL.
1282PL/SQL is a combination of SQL along with the
1283procedural features of programming languages. It was
1284developed by Oracle Corporation in the early 90’s to
1285enhance the capabilities of SQL.
1286The PL/SQL Engine:
1287Oracle uses a PL/SQL engine to processes the PL/SQL
1288statements. A PL/SQL code can be stored in the client
1289system (client-side) or in the database (server-side).
1290A Simple PL/SQL Block:
1291Each PL/SQL program consists of SQL and PL/SQL
1292statements which from a PL/SQL block.
1293A PL/SQL Block consists of three sections:
1294• The Declaration section (optional).
1295• The Execution section (mandatory).
1296• The Exception (or Error) Handling section (optional). 
1297DECLARE
1298 Variable declaration
1299BEGIN
1300 Program Execution
1301EXCEPTION
1302 Exception handling
1303END;
1304DATA TYPES:
1305Few of the data types used to define placeholders are as
1306given below.
1307Number (n,m) , Char (n) , Varchar2 (n) , Date , Long etc
1308To write PL/SQL programs, create a script file and
1309run the script file or use editor.
1310Steps to create script file
1311Step1:
1312SQL> edit z:\var1.sql
1313Step2:
1314Type the program in notepad 
1315Step 3:
1316Save the program
1317Step4:
1318Run the program
1319SQL> @ z:\var1.sql
1320VARIABLES
1321The General Syntax to declare a variable is:
1322variable_name datatype [NOT NULL := value ];
1323• variable_name is the name of the variable.
1324• datatype is a valid PL/SQL datatype.
1325• NOT NULL is an optional specification on the
1326variable.
1327• value or DEFAULT value is also an optional
1328specification, where you can initialize a variable.
1329• Each variable declaration is a separate statement and
1330must be terminated by a semicolon.
1331When it is specified as NOT NULL it should be
1332initialized. 
1333DECLARE
1334salary number(4);
1335dept varchar2(10) NOT NULL := “HR Dept”;
1336ASSIGNING values to variable from database column
1337using select
1338DECLARE
1339 var_salary number(6);
1340 var_emp_id number(6) = 1116;
1341BEGIN
1342 SELECT salary
1343 INTO var_salary
1344 FROM employee
1345 WHERE emp_id = var_emp_id;
1346 dbms_output.put_line(var_salary);
1347 dbms_output.put_line('The employee '
1348 || var_emp_id || ' has salary ' || var_salary);
1349END;
1350/-> Execute the program 
1351SCOPE OF THE VARIABLES
1352DECLARE
1353var_num1 number; // Global Variables
1354var_num2 number; // Global Variables
1355BEGIN
1356var_num1 := 100;
1357var_num2 := 200;
1358DECLARE
1359var_mult number; // Local Variable
1360BEGIN
1361var_mult := var_num1 * var_num2;
1362END;
1363END;
1364/
1365PL/SQL Constants
1366The General Syntax to declare a constant is:
1367constant_name CONSTANT datatype := VALUE;
1368• constant_name is the name of the constant i.e. similar
1369to a variable name.
1370• The word CONSTANT is a reserved word and ensures
1371that the value does not change.
1372• VALUE - It is a value which must be assigned to a
1373constant when it is declared. You cannot assign a
1374value later. 
1375DECLARE
1376salary_increase CONSTANT number (3) := 10;
1377Conditional Statements in PL/SQL
1378IF condition 1
1379THEN
1380 statement 1;
1381ELSIF condtion2 THEN
1382 statement 2;
1383ELSE
1384 statement 3;
1385END IF;
1386Iterative Statements in PL/SQL
1387There are three types of loops in PL/SQL:
1388• Simple Loop
1389• While Loop
1390• For Loop 
1391Simple Loop
1392A Simple Loop is used when a set of statements is to be
1393executed at least once before the loop terminates.
1394LOOP
1395 statements;
1396 EXIT;
1397 {or EXIT WHEN condition;}
1398END LOOP;
13992) While Loop
1400WHILE <condition>
1401 LOOP statements;
1402END LOOP;
14033) FOR Loop
1404FOR counter IN val1..val2
1405 LOOP statements;
1406END LOOP; 
1407Case Expressions
1408Syntax:
1409CASE selector
1410WHEN expression1 THEN result1
1411WHEN expression2 THEN result2
1412WHEN expression N THEN result N
1413[ELSE resultN+1]
1414END;
1415RECORDS
1416If a field is based on a column from database table, you
1417can define the field_type as follows:
1418col_name table_name.column_name%type;
1419DECLARE
1420TYPE employee_type IS RECORD
1421(employee_id number(5),
1422 employee_first_name varchar2(25),
1423 employee_last_name employee.last_name%type,
1424 employee_dept employee.dept%type);
1425 employee_salary employee.salary%type;
1426 employee_rec employee_type; 
1427If all the fields of a record are based on the columns of a
1428table, we can declare the record as follows:
1429record_name table_name%ROWTYPE;
1430DECLARE
1431 employee_rec employee%ROWTYPE;
1432Passing Values To and From a Record
1433SELECT col1, col2
1434INTO record_name.col_name1, record_name.col_name2
1435FROM table_name
1436[WHERE clause];
1437var_name := record_name.col_name;
1438Cursors
1439*A cursor is a temporary work area created in the system
1440memory when a SQL statement is executed.
1441*A cursor contains information on a select statement and
1442the rows of data accessed by it.
1443*This temporary work area is used to store the data
1444retrieved from the database, and manipulate this data.
1445*A cursor can hold more than one row, but can process
1446only one row at a time. 
1447Implicit cursors:
1448These are created by default when DML statements like,
1449INSERT, UPDATE, and DELETE statements are
1450executed. They are also created when a SELECT
1451statement that returns just one row is executed.
1452The cursor attributes available are %FOUND,
1453%NOTFOUND, %ROWCOUNT, and %ISOPEN.
1454DECLARE
1455 var_rows number(5);
1456BEGIN
1457 UPDATE employee SET salary = salary + 1000;
1458 IF SQL%NOTFOUND THEN
1459 dbms_output.put_line('None of the salaries where
1460updated');
1461 ELSIF SQL%FOUND THEN
1462 var_rows := SQL%ROWCOUNT;
1463 dbms_output.put_line('Salaries for ' || var_rows ||
1464'employees are updated');
1465 END IF;
1466END;
1467Explicit cursors:
1468They must be created when you are executing a SELECT
1469statement that returns more than one row. Even though
1470the cursor stores multiple records, only one record can be
1471processed at a time, which is called as current row. When 
1472you fetch a row the current row position moves to next
1473row.
1474Syntax: CURSOR cursor_name IS select_statement;
1475DECLARE
1476 variables;
1477 records;
1478 create a cursor;
1479 BEGIN
1480 OPEN cursor;
1481 FETCH cursor;
1482 process the records;
1483 CLOSE cursor;
1484 END;
1485DECLARE
1486emp_rec emp_tbl%rowtype;
1487CURSOR emp_cur IS
1488SELECT * FROM emp_tbl WHERE
1489salary > 10;
1490BEGIN
1491 OPEN emp_cur;
1492 FETCH emp_cur INTO emp_rec;
1493 dbms_output.put_line (emp_rec.first_name || ' ' ||
1494emp_rec.last_name);
1495 CLOSE emp_cur;
1496END; 
1497DECLARE
1498CURSOR emp_cur IS
1499SELECT first_name, last_name, salary FROM emp_tbl;
1500emp_rec emp_cur%rowtype;
1501BEGIN
1502IF NOT emp_cur%ISOPEN THEN
1503 OPEN emp_cur;
1504END IF;
1505FETCH emp_cur INTO emp_rec;
1506WHILE emp_cur%FOUND THEN
1507LOOP
1508dbms_output.put_line(emp_cur.first_name || ' '
1509||emp_cur.last_name
1510|| ' ' ||emp_cur.salary);
1511FETCH emp_cur INTO emp_rec;
1512END LOOP;
1513END;
1514/
1515FUNCTION
1516A function is a named PL/SQL Block which is similar to
1517a procedure. The major difference between a procedure
1518and a function is, a function must always return a value,
1519but a procedure may or may not return a value. 
15201) Return Type: The header section defines the return
1521type of the function. The return datatype can be any
1522of the oracle datatype like varchar, number etc.
15232) The execution and exception section both should
1524return a value which is of the datatype defined in the
1525header section.
1526For example, let’s create a function called
1527''employer_details_func' similar to the one created in
1528stored proc
1529CREATE OR REPLACE FUNCTION
1530employer_details_func
1531RETURN VARCHAR (20);
1532IS
1533emp_name VARCHAR(20);
1534BEGIN
1535SELECT first_name INTO emp_name
1536FROM emp_tbl WHERE empID = '100';
1537RETURN emp_name;
1538END;
1539/ 
1540declare
1541summation number;
1542average number;
1543function summa(m4 number,m5 number) return number
1544is
1545begin
1546return(m4+m5);
1547end;
1548function aver( summ1 number) return number is
1549begin
1550Return(summ1/2);
1551end;
1552begin
1553summation:=summa(&m1,&m2);
1554average:=aver(summation);
1555dbms_output.put_line('summation is:'||summation);
1556dbms_output.put_line('average is:'||average);
1557end; 
1558declare
1559 average number;
1560function summa(m4 number,m5 number) return number
1561is
1562beginreturn(m4+m5);
1563end;
1564function aver( summ1 number) return number is
1565begin
1566Return(summ1/2);
1567end;
1568 begin
1569 average:=aver(summa(&m1,&m2));
1570dbms_output.put_line('summation is:'||average);
1571end;
1572 /
1573Enter value for m1: 4
1574Enter value for m2: 5
1575old 11: average:=aver(summa(&m1,&m2));
1576new 11: average:=aver(summa
1577Removing functions
1578Drop function function_name
1579Example:
1580Drop function summa; 
1581PROCEDURE:
1582A stored procedure or in simple a proc is a named
1583PL/SQL block which performs one or more specific task.
1584CREATE OR REPLACE PROCEDURE
1585employer_details
1586IS
1587CURSOR emp_cur IS
1588SELECT first_name, last_name, salary FROM emp_tbl;
1589emp_rec emp_cur%rowtype;
1590BEGIN
1591FOR emp_rec in emp_cur
1592LOOP
1593dbms_output.put_line(emp_cur.first_name || ' '
1594||emp_cur.last_name || ' ' ||emp_cur.salary);
1595END LOOP;
1596END;
1597/
1598How to execute a Stored Procedure?
1599There are two ways to execute a procedure.
16001) From the SQL prompt.
1601 EXECUTE [or EXEC] procedure_name;
16022) Within another procedure – simply use the procedure
1603name. procedure_name; 
1604PARAMETERS
1605) IN type parameter: These types of parameters are used to send values to stored procedures.
16062) OUT type parameter: These types of parameters are used to get values from stored procedures. This
1607is similar to a return type in functions.
16083) IN OUT parameter: These types of parameters are used to send values and get values from stored
1609procedures.
1610Using IN and OUT parameter:
1611Let’s create a procedure which gets the name of the
1612employee when the employee id is passed.
1613CREATE OR REPLACE PROCEDURE emp_name (id
1614IN NUMBER, emp_name OUT NUMBER)
1615IS
1616BEGIN
1617SELECT first_name INTO emp_name
1618FROM emp_tbl WHERE empID = id;
1619END;
1620/
1621We can call the procedure ‘emp_name’ in this way from a
1622PL/SQL Block.
1623DECLARE
1624empName varchar(20);
1625CURSOR id_cur SELECT id FROM emp_ids;
1626BEGIN
1627FOR emp_rec in id_cur 
1628LOOP
1629emp_name(emp_rec.id, empName);
1630dbms_output.putline('The employee ' || empName || ' has
1631id ' || emp-rec.id);
1632END LOOP;
1633END;
1634/
1635TRIGGERS
1636A trigger is a pl/sql block structure which is fired
1637automatically when a DML statements like Insert, Delete,
1638Update is executed on a database table.
1639For Example: The price of a product changes constantly.
1640It is important to maintain the history of the prices of the
1641products.
1642We can create a trigger to update the
1643'product_price_history' table when the price of the product
1644is updated in the 'product' table.
16451) Create the 'product' table and 'product_price_history'
1646table
1647CREATE TABLE product_price_history
1648(product_id number(5),
1649product_name varchar2(32),
1650supplier_name varchar2(32), 
1651unit_price number(7,2) );
1652CREATE TABLE product
1653(product_id number(5),
1654product_name varchar2(32),
1655supplier_name varchar2(32),
1656unit_price number(7,2) );
1657CREATE or REPLACE TRIGGER price_history_trigger
1658BEFORE UPDATE OF unit_price
1659ON product
1660FOR EACH ROW
1661BEGIN
1662INSERT INTO product_price_history
1663VALUES
1664(:old.product_id,
1665 :old.product_name,
1666 :old.supplier_name,
1667 :old.unit_price);
1668END;
1669/
1670UPDATE PRODUCT SET unit_price = 800 WHERE
1671product_id = 100 
1672• CREATE [OR REPLACE ] TRIGGER trigger_name -
1673This clause creates a trigger with the given name or
1674overwrites an existing trigger with the same name.
1675• {BEFORE | AFTER | INSTEAD OF } - This clause
1676indicates at what time should the trigger get fired. i.e
1677for example: before or after updating a table.
1678INSTEAD OF is used to create a trigger on a view.
1679before and after cannot be used to create a trigger on
1680a view.
1681• {INSERT [OR] | UPDATE [OR] | DELETE} - This
1682clause determines the triggering event. More than one
1683triggering events can be used together separated by
1684OR keyword. The trigger gets fired at all the
1685specified triggering event.
1686• [OF col_name] - This clause is used with update
1687triggers. This clause is used when you want to trigger
1688an event only when a specific column is updated.
1689• [ON table_name] - This clause identifies the name
1690of the table or view to which the trigger is associated.
1691• [REFERENCING OLD AS o NEW AS n] - This
1692clause is used to reference the old and new values of
1693the data being changed. By default, you reference the
1694values as :old.column_name or :new.column_name.
1695The reference names can also be changed from old
1696(or new) to any other user-defined name. You cannot
1697reference old values when inserting a record, or new 
1698values when deleting a record, because they do not
1699exist.
1700• [FOR EACH ROW] - This clause is used to
1701determine whether a trigger must fire when each row
1702gets affected ( i.e. a Row Level Trigger) or just once
1703when the entire sql statement is
1704executed(i.e.statement level Trigger).
1705• WHEN (condition) - This clause is valid only for row
1706level triggers. The trigger is fired only for rows that
1707satisfy the condition specified.
1708Types of PL/SQL Triggers
1709There are two types of triggers based on the which level it
1710is triggered.
17111) Row level trigger - An event is triggered for each row
1712upated, inserted or deleted.
17132) Statement level trigger - An event is triggered for
1714each sql statement executed.
1715CREATE or REPLACE TRIGGER
1716Before_Update_Stat_product
1717BEFORE
1718UPDATE ON product
1719Begin
1720INSERT INTO product_check
1721Values('Before update, statement level',sysdate);
1722END; 
1723CREATE or REPLACE TRIGGER
1724Before_Upddate_Row_product
1725 BEFORE
1726 UPDATE ON product
1727 FOR EACH ROW
1728 BEGIN
1729 INSERT INTO product_check
1730 Values('Before update row level',sysdate);
1731 END;
1732DESC USER_TRIGGERS;
1733SELECT * FROM user_triggers WHERE trigger_name =
1734Before_Update_Stat_product';
1735CYCLIC CASCADING in a TRIGGER
1736This is an undesirable situation where more than one
1737trigger enter into an infinite loop. while creating a trigger
1738we should ensure the such a situtation does not exist.
1739The below example shows how Trigger's can enter into
1740cyclic cascading.
1741Let's consider we have two tables 'abc' and 'xyz'. Two
1742triggers are created. 
17431) The INSERT Trigger, triggerA on table 'abc' issues an
1744UPDATE on table 'xyz'.
17452) The UPDATE Trigger, triggerB on table 'xyz' issues an
1746INSERT on table 'abc'.
1747In such a situation, when there is a row inserted in table
1748'abc', triggerA fires and will update table 'xyz'.
1749When the table 'xyz' is updated, triggerB fires and will
1750insert a row in table 'abc'.
1751This cyclic situation continues and will enter into a
1752infinite loop, which will crash the database.
1753http://plsql-tutorial.com/plsql-exception-handling.htm