PL/SQL Tutorial

What is PL/SQL?

Procedural Language extension to SQL

It integrates procedural constructs with SQL

SET SERVEROUTPUT ON

This command is used to send the output from the server to the screen

Lesson 1-2 Introduction & Declaring PL/SQL Identifiers

SQL> SET SERVEROUTPUT ON

Program1: Printing a String

BEGIN

dbms_output.put_line (‘Welcome to PL/SQL’);

END;

/

Output:

Welcome to PL/SQL

Program2:

Declaring the variable & Initializing the variable in the declare section

DECLARE

v_name VARCHAR2 (10) := ‘Star’;

BEGIN

dbms_output.put_line (v_name);

END;

/

Output:

Star

Program3:

Declaring the variable in declare section & initializing the variable in the executable section

DECLARE

v_name VARCHAR2 (10);

BEGIN

dbms_output.put_line (‘Name is ‘ || v_name);

v_name := ‘Star’;

dbms_output.put_line (‘Name is ‘ || v_name);

END;

/

Output:

Name is

Name is Star

Program4: Modifying the variable value in the Executable Section

DECLARE

v_name VARCHAR2 (10) := ‘Star’;

BEGIN

dbms_output.put_line (‘Previous Value ‘ || v_name);

v_name := ‘PLSQL Star’;

dbms_output.put_line (‘Modified to ‘|| v_name);

END;

/

Output:

Previous Value Star

Modified to PLSQL Star

Program5: Defining a Variable with Not Null

DECLARE

v_no NUMBER (4) NOT NULL :=10;

BEGIN

dbms_output.put_line (v_no);

END;

/

Output:

10

Program6: Defining a Variable with a Constant Value

DECLARE

v_pi CONSTANT NUMBER (5, 2) := 3.14;

BEGIN

dbms_output.put_line (v_pi);

END;

/

Output:

3.14

Program7: Defining a Variable with DEFAULT

DECLARE

v_no NUMBER (5) default 10;

BEGIN

dbms_output.put_line (v_no);

END;

/

Output:

10

Program8: Writing a PL/SQL Statement (INTO Clause)

DECLARE

v_sal NUMBER (5);

BEGIN

SELECT salary

INTO v_sal

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_sal);

END;

/

Output:

17000

Program9: Importance of %type with Scalar Variable (Variable Size is less)

DECLARE

v_sal NUMBER (2);

BEGIN

SELECT salary

INTO v_sal

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_sal);

END;

/

Output:

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06512: at line 4

Program10: Usage of %type with Scalar Variable

DECLARE

v_sal employees.salary%type;

BEGIN

SELECT salary INTO v_sal

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_sal);

END;

/

Output:

17000

Program11: Assigning a Previously defined variable data type to a new variable by using %TYPE

DECLARE

v_name VARCHAR2 (10) := ‘star’;

v_job v_name%type := ‘clerk’;

BEGIN

dbms_output.put_line (v_name);

dbms_output.put_line (v_job);

END;

/

Output:

star

clerk

Program12: Bind Variable

VARIABLE g_sal NUMBER

BEGIN

SELECT salary

INTO :g_sal

FROM employees

WHERE employee_id=101;

END;

/

PRINT g_sal

Output:

G_SAL

———-

17000

SET AUTOPRINT ON

BEGIN

SELECT salary

INTO :g_sal

FROM employees

WHERE employee_id=101;

END;

/

Output:

G_SAL

———-

17000

Program13: Usage of Substitution Variable (&) with Scalar Variable

DECLARE

v_sal employees.salary%type;

BEGIN

SELECT salary

INTO v_sal

FROM employees

WHERE employee_id=&NO;

dbms_output.put_line (v_sal);

END;

/

Output:

Enter value for no: 100

old   7: WHERE employee_id=&NO;

new   7: WHERE employee_id=100;

24000

Program14: Usage of Substitution Variable (&&) with Scalar Variable

DECLARE

v_sal employees.salary%type;

BEGIN

SELECT salary

INTO v_sal

FROM employees

WHERE employee_id=&&NO2;

dbms_output.put_line (v_sal);

END;

/

Output:

Enter value for no2: 102

old   7: WHERE employee_id=&&NO2;

new   7: WHERE employee_id=102;

17000

Program15: DEFINE Variable

DEFINE no3=110

DECLARE

v_sal employees.salary%type;

BEGIN

SELECT salary INTO v_sal

FROM employees

WHERE employee_id=&no3;

dbms_output.put_line (v_sal);

END;

/

Output:

old   6: WHERE employee_id=&no3;

new   6: WHERE employee_id=110;

8200

Lesson 3-4: Writing Executable Statements & Interacting with the Oracle Server

Program1: Usage of a Single Row Function (LOWER) with a Scalar Variable

DECLARE

v_in_name VARCHAR2 (20):= ‘STAR’;

v_out_name VARCHAR2 (20);

BEGIN

v_out_name:= LOWER (v_in_name);

dbms_output.put_line (v_out_name);

END;

/

Output:

star

Program2: Usage of a Single Row Function (LENGTH) with a Scalar Variable

DECLARE

v_in_name VARCHAR2 (20) := ‘STAR’;

BEGIN

v_in_name := LENGTH(v_in_name);

dbms_output.put_line(v_in_name);

END;

/

Output:

4

Program3: Usage of a Single Row Function (TO_CHAR) with a Scalar Variable

DECLARE

v_date DATE := sysdate;

v_out VARCHAR2 (50);

BEGIN

v_out := TO_CHAR(sysdate,’dd-mon-year’);

dbms_output.put_line (v_out);

END;

/

Output:

27-jan-twenty eleven

Program4: NESTED BLOCK

DECLARE

outer_block VARCHAR2 (30) := ‘Global_Variable’;

BEGIN

DECLARE

inner_block VARCHAR2 (30) := ‘Inner_Variable’;

BEGIN

dbms_output.put_line (outer_block);

dbms_output.put_line (inner_block);

END;

dbms_output.put_line (outer_block);

END;

/

Output:

Global_Variable

Inner_Variable

Global_Variable

Program5: NESTED BLOCK with Label

<<OUTER>>

DECLARE

outer_block VARCHAR2 (30) := ‘Global_Variable’;

BEGIN

DECLARE

inner_block VARCHAR2 (30) := ‘Inner_Variable’;

outer_block VARCHAR2 (30) := ‘Inner_Variable without label’;

BEGIN

dbms_output.put_line (outer_block);

dbms_output.put_line (inner_block);

dbms_output.put_line (OUTER.outer_block);

END;

dbms_output.put_line (outer_block);

END;

/

Output:

Inner_Variable without label

Inner_Variable

Global_Variable

Global_Variable

Program6: Comments (Single Line Comment)

DECLARE

— Single Line Comment

v_no NUMBER (4);

BEGIN

v_no:= 5*6;

dbms_output.put_line (v_no);

END;

/

Output:

30

Program7: Multiple Line Comment

DECLARE

v_no NUMBER(4);

BEGIN

/* Multiple Line Commenting here we will multiply

And place the result in the v_no */

v_no:= 5*6;

dbms_output.put_line (v_no);

END;

/

Output:

30

Program23: Using the Group Function in PL/SQL Statement

DECLARE

v_sal employees.salary%type;

BEGIN

SELECT SUM (salary)

INTO v_sal

FROM employees

WHERE department_id=60;

dbms_output.put_line(v_sal);

END;

/

Output:

28800

Program24: Usage of %ROWCOUNT Cursor Attribute

DROP TABLE emp PURGE;

CREATE TABLE emp AS

SELECT * FROM EMPLOYEES;

SELECT COUNT (*) FROM emp;

Output:

COUNT (*)

———-

107

DECLARE

v_del_rows NUMBER (4);

BEGIN

DELETE emp;

v_del_rows:= SQL%rowcount;

dbms_output.put_line (v_del_rows);

END;

/

Output:

107

Lesson 5-6 Writing Control Structures & Working with Composite Data types

Program1: Usage of IF statement

DECLARE

v_myage NUMBER := 31;

BEGIN

IF v_myage <11 THEN

dbms_output.put_line (‘ I am a child ‘);

END IF;

END;

/

Output:

Program2: Usage of IF THEN ELSE statement

DECLARE

v_myage NUMBER := 31;

BEGIN

IF v_myage < 11 THEN

dbms_output.put_line (‘ I am a child ‘);

ELSE

dbms_output.put_line (‘ I am not a child’);

END IF;

END;

/

Output:

I am not a child

Program3: Usage of IF ELSIF ELSE Clause

DECLARE

v_myage NUMBER := 31;

BEGIN

IF v_myage < 11 THEN

dbms_output.put_line (‘ I am a child ‘);

ELSIF v_myage < 20 THEN

dbms_output.put_line (‘ I am young’);

ELSIF v_myage < 30 THEN

dbms_output.put_line (‘ I am in twenties’);

ELSIF v_myage < 40 THEN

dbms_output.put_line (‘ I am in fourties’);

ELSE

dbms_output.put_line (‘ I am always young’);

END IF;

END;

/

Output:

I am in four ties

Program4: CASE statement

DECLARE

v_grade CHAR (1) := UPPER (‘&grade’);

v_appraisal VARCHAR2 (20);

BEGIN

v_appraisal :=

CASE v_grade

WHEN ‘A’ then ‘Excellent’

WHEN ‘B’ then ‘Very Good’

WHEN ‘C’ then ‘Good’

ELSE  ‘no such grade’

END;

dbms_output.put_line (v_appraisal);

END;

/

Output:

Enter value for grade: A

old   2: v_grade CHAR (1) := UPPER (‘&grade’);

new   2: v_grade CHAR (1) := UPPER (‘A’);

Excellent

Program5: SEARCHED CASE

DECLARE

v_grade CHAR (1) := UPPER (‘&grade’);

v_appraisal VARCHAR2 (20);

BEGIN

v_appraisal :=

CASE

WHEN v_grade=’A’ then ‘Excellent’

WHEN v_grade=’B’ then ‘Very Good’

WHEN v_grade IN (‘C’,’D’) then ‘Good’

ELSE ‘no such grade’

END;

dbms_output.put_line (v_appraisal);

END;

/

Output:

Enter value for grade: A

old   2: v_grade CHAR (1) := UPPER (‘&grade’);

new   2: v_grade CHAR (1) := UPPER (‘A’);

Excellent

Program6: Handing NULL Value

DECLARE

v_name VARCHAR2 (10);

BEGIN

IF NOT (v_name) = ‘star’ then

dbms_output.put_line (‘Welcome’);

else

Dbms_ output.put_line (‘working’);

END IF;

END;

/

Output:

working

Program7: Usage of Simple LOOP

DECLARE

v_count NUMBER (2):= 1;

BEGIN

LOOP

dbms_output.put_line (v_count);

v_count := v_count+1;

EXIT WHEN v_count>5;

END LOOP;

END;

/

Output:

1

2

3

4

5

Program8: Usage of Simple LOOP

DECLARE

v_count NUMBER (2):= 10;

BEGIN

LOOP

dbms_output.put_line (v_count);

v_count := v_count+1;

exit when v_count>5;

END LOOP;

END;

/

Output:

10

Program9: Usage of a WHILE Loop

DECLARE

v_count NUMBER (2) :=1;

BEGIN

WHILE v_count < 3 loop

dbms_output.put_line (v_count);

v_count := v_count+1;

END LOOP;

END;

/

Output:

1

2

Program10: Usage of a FOR Loop in Ascending Order

BEGIN

FOR I in 1..5 LOOP

dbms_output.put_line (I);

END LOOP;

END;

/

Output:

1

2

3

4

5

Program11: Usage of a FOR Loop in Descending Order

BEGIN

FOR I in REVERSE 1..5 LOOP

dbms_output.put_line (I);

END LOOP;

END;

/

Output:

5

4

3

2

1

Program12: Usage of a PL/SQL Record

DECLARE

TYPE emp_rec IS RECORD

(

v_name VARCHAR2 (10),

v_date DATE

);

v_rec emp_rec;

BEGIN

SELECT last_name, hire_date

INTO v_rec

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_rec.v_name);

dbms_output.put_line (v_rec.v_date);

END;

/

Output:

Kochhar

21-SEP-89

Program13: Usage of a %ROWTYPE

DECLARE

emp_rec employees%ROWTYPE;

BEGIN

SELECT *

INTO emp_rec

FROM employees

WHERE employee_id=101;

dbms_output.put_line (emp_rec.last_name);

dbms_output.put_line (emp_rec.salary);

END;

/

Output:

17000

Program14:  Usage of a DEFINE command

DEFINE countryid = CA

DECLARE

country_record countries%ROWTYPE;

BEGIN

SELECT *

INTO country_record

FROM countries

WHERE country_id=UPPER(‘&countryid’);

DBMS_OUTPUT.PUT_LINE(‘Country Id: ‘ || country_record.country_id || ‘Country Name: ‘ || country_record.country_name || ‘ Region: ‘ || country_record.region_id);

END;

/

Output:

old   7: WHERE country_id=UPPER(‘&countryid’);

new   7: WHERE country_id=UPPER(‘CA’);

Country Id: CACountry Name: Canada Region: 2

Program15: Usage of INDEX BY TABLE with %TYPE

DECLARE

TYPE emp_tab IS TABLE OF

employees.last_name%type

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT last_name INTO v_emp (1)

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_emp (1));

END;

/

Output:  Kochhar

Program16: Usage of INDEX BY TABLE with %TYPE

DECLARE

TYPE dept_table_type

IS TABLE OF

departments.department_name%TYPE

INDEX BY PLS_INTEGER;

my_dept_table   dept_table_type;

loop_count NUMBER(2) :=10;

deptno NUMBER(4) := 0;

BEGIN

FOR i IN 1..loop_count LOOP

deptno:=deptno+10;

SELECT department_name

INTO my_dept_table(i)

FROM departments

WHERE department_id = deptno;

dbms_output.put_line(my_dept_table(i));

END LOOP;

END;

/

Output:

Administration

Marketing

Purchasing

Human Resources

Shipping

IT

Public Relations

Sales

Executive

Finance

Program17: Usage of INDEX BY TABLE with %TYPE

DECLARE

TYPE dept_table_type

IS TABLE OF

departments.department_name%TYPE

INDEX BY PLS_INTEGER;

my_dept_table   dept_table_type;

loop_count NUMBER(2) :=10;

deptno NUMBER(4) := 0;

BEGIN

FOR i IN 1..loop_count LOOP

deptno:=deptno+10;

SELECT department_name

INTO my_dept_table(i)

FROM departments

WHERE department_id = deptno;

END LOOP;

FOR i IN 1..loop_count

LOOP

dbms_output.put_line(my_dept_table(i));

END LOOP;

END;

/

Output:

Administration

Marketing

Purchasing

Human Resources

Shipping

IT

Public Relations

Sales

Executive

Finance

Program18: Usage of INDEX BY TABLE with %ROWTYPE

DECLARE

TYPE dept_table_type

IS TABLE OF

departments%ROWTYPE

INDEX BY PLS_INTEGER;

my_dept_table   dept_table_type;

loop_count NUMBER(2) :=10;

deptno NUMBER(4) := 0;

BEGIN

FOR i IN 1..loop_count LOOP

deptno:=deptno+10;

SELECT *

INTO my_dept_table(i)

FROM departments

WHERE department_id = deptno;

dbms_output.put_line(my_dept_table(i).department_name);

END LOOP;

END;

/

Output:

Administration

Marketing

Purchasing

Human Resources

Shipping

IT

Public Relations

Sales

Executive

Finance

Program19: Usage of INDEX BY TABLE with %ROWTYPE

DECLARE

TYPE dept_table_type

IS TABLE OF

departments%ROWTYPE

INDEX BY PLS_INTEGER;

my_dept_table   dept_table_type;

loop_count NUMBER(2) :=10;

deptno NUMBER(4) := 0;

BEGIN

FOR i IN 1..loop_count LOOP

deptno:=deptno+10;

SELECT *

INTO my_dept_table(i)

FROM departments

WHERE department_id = deptno;

END LOOP;

FOR i IN 1..loop_count

LOOP

dbms_output.put_line(‘Department Number: ‘ || my_dept_table(i).department_id || ‘ Department Name: ‘ || my_dept_table(i).manager_id || ‘ Location Id: ‘ || my_dept_table(i).location_id);

END LOOP;

END;

/

Output:

Department Number: 10 Department Name: 200 Location Id: 1700

Department Number: 20 Department Name: 201 Location Id: 1800

Department Number: 30 Department Name: 114 Location Id: 1700

Department Number: 40 Department Name: 203 Location Id: 2400

Department Number: 50 Department Name: 121 Location Id: 1500

Department Number: 60 Department Name: 103 Location Id: 1400

Department Number: 70 Department Name: 204 Location Id: 2700

Department Number: 80 Department Name: 145 Location Id: 2500

Department Number: 90 Department Name: 100 Location Id: 1700

Department Number: 100 Department Name: 108 Location Id: 1700

Program20: Usage of INDEX BY TABLE with %TYPE

DECLARE

TYPE emp_tab IS TABLE OF

employees.last_name%type

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT last_name INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT last_name INTO v_emp (2)

FROM employees

WHERE employee_id=102;

dbms_output.put_line (v_emp (1));

dbms_output.put_line (v_emp (2));

END;

/

Output:

Kochhar

De Haan

Program21: Usage of INDEX BY TABLE with %ROWTYPE

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT * INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT * INTO v_emp (2)

FROM employees

WHERE employee_id=102;

dbms_output.put_line (v_emp (1).last_name || ‘ job ‘ || v_emp (1).job_id);

dbms_output.put_line (v_emp (2).last_name || ‘ job ‘ || v_emp (2).job_id);

END;

/

Output:

Kochhar job AD_VP

De Haan job AD_VP

Program22: Usage of INDEX BY TABLE with %ROWTYPE with EXISTS Method

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT * INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT * INTO v_emp (2)

FROM employees

WHERE employee_id=102;

IF v_emp.EXISTS (1) THEN

dbms_output.put_line (v_emp (1).last_name || ‘ job ‘ || v_emp (1).job_id);

END IF;

dbms_output.put_line (v_emp (2).last_name || ‘ job ‘ || v_emp (2).job_id);

END;

/

Output:

Kochhar job AD_VP

De Haan job AD_VP

Program23: Usage of INDEX BY TABLE with %ROWTYPE with COUNT Method

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT * INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT * INTO v_emp (2)

FROM employees

WHERE employee_id=102;

dbms_output.put_line (‘ counting ‘ || v_emp.count);

dbms_output.put_line (v_emp (1).last_name || ‘ job ‘ || v_emp (1).job_id);

dbms_output.put_line (v_emp (2).last_name || ‘ job ‘ || v_emp (2).job_id);

END;

/

Output:

Counting 2

Kochhar job AD_VP

De Haan job AD_VP

Program24: Usage of INDEX BY TABLE with %ROWTYPE with PRIOR Method

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT * INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT * INTO v_emp (2)

FROM employees

WHERE employee_id=102;

dbms_output.put_line (‘ prior ‘ || v_emp.prior (2));

dbms_output.put_line (v_emp (1).last_name || ‘ job ‘ || v_emp (1).job_id);

dbms_output.put_line (v_emp (2).last_name || ‘ job ‘ || v_emp (2).job_id);

END;

/

Output:

Prior 1

Kochhar job AD_VP

De Haan job AD_VP

Program25: Usage of INDEX BY TABLE with %ROWTYPE with NEXT Method

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

SELECT * INTO v_emp (1)

FROM employees

WHERE employee_id=101;

SELECT * INTO v_emp (2)

FROM employees

WHERE employee_id=102;

dbms_output.put_line (‘ Next ‘ || v_emp.next(1));

dbms_output.put_line (v_emp (1).last_name || ‘ job ‘ || v_emp (1).job_id);

dbms_output.put_line (v_emp (2).last_name || ‘ job ‘ || v_emp (2).job_id);

END;

/

Output:

Next 2

Kochhar job AD_VP

De Haan job AD_VP

Program26: Usage of INDEX BY TABLE with %ROWTYPE with FIRST..LAST Method

DECLARE

TYPE emp_tab IS TABLE OF

employees%rowtype

INDEX BY PLS_INTEGER;

v_emp emp_tab;

BEGIN

FOR I in 100..104 LOOP

SELECT * INTO v_emp (I) FROM employees WHERE employee_id=I;

END LOOP;

FOR I IN v_emp.FIRST..v_emp.LAST LOOP

dbms_output.put_line (v_emp (i).first_name || ‘ last name is ‘ || v_emp (i).last_name);

END LOOP;

END;

/

Output:

Steven last name is King

Neena last name is Kochhar

Lex last name is De Haan

Alexander last name is Hunold

Bruce last name is Ernst

Lesson 7-8 (Cursors & Exceptions)

Program1: Usage of %NOTFOUND Cursor Attribute

DECLARE

v_name VARCHAR2 (20);

CURSOR v_cur IS

SELECT first_name

FROM employees;

BEGIN

OPEN v_cur;

LOOP

FETCH v_cur INTO v_name;

dbms_output.put_line (v_name);

EXIT WHEN v_cur%NOTFOUND;

END LOOP;

CLOSE v_cur;

END;

/

Output:

Alana

Matthew

Jennifer

Eleni

Eleni

Program2: Usage of %FOUND Cursor Attribute

DECLARE

v_name VARCHAR2 (20);

CURSOR v_cur IS

SELECT first_name

FROM employees;

BEGIN

OPEN v_cur;

LOOP

FETCH v_cur INTO v_name;

dbms_output.put_line (v_name);

EXIT WHEN v_cur%FOUND;

END LOOP;

CLOSE v_cur;

END;

/

Output:          Ellen

Program3: Usage of %ROWCOUNT Cursor Attribute

DECLARE

v_name VARCHAR2 (20);

CURSOR v_cur IS

SELECT first_name

FROM employees;

BEGIN

OPEN v_cur;

LOOP

FETCH v_cur INTO v_name;

dbms_output.put_line (v_name);

EXIT WHEN v_cur%ROWCOUNT>5;

END LOOP;

CLOSE v_cur;

END;

/

Output:

Ellen

Sundar

Mozhe

David

Hermann

Shelli

Program4: Usage of RECORD with Cursor

DECLARE

CURSOR v_cur IS

SELECT * FROM employees;

v_emp v_cur%ROWTYPE;

BEGIN

OPEN v_cur;

LOOP

FETCH v_cur INTO v_emp;

dbms_output.put_line (v_emp.first_name);

EXIT WHEN v_cur%ROWCOUNT>5;

END LOOP;

CLOSE v_cur;

END;

/

Output:

Donald

Douglas

Jennifer

Michael

Pat

Susan

Program5: Usage of CURSOR FOR LOOP

DECLARE

CURSOR v_cur IS SELECT * FROM employees;

BEGIN

FOR v_emp IN v_cur LOOP

dbms_output.put_line (v_emp.first_name);

END LOOP;

END;

/

Output:

Samuel

Vance

Alana

Kevin

Program6: Usage of CURSOR FOR LOOP WITH sub query

BEGIN

FOR v_emp IN (SELECT * FROM EMPLOYEES) LOOP

dbms_output.put_line (v_emp.first_name);

END LOOP;

END;

/

Output:

Samuel

Vance

Alana

Kevin

Program7: Usage of Cursor with Parameters

DECLARE

CURSOR c1 (p_deptno IN NUMBER, p_job IN VARCHAR2) IS

SELECT employee_id, last_name

FROM employees

WHERE department_id=p_deptno AND job_id=p_job;

v1 c1%rowtype;

BEGIN

OPEN c1 (10,’AD_ASST’);

LOOP

FETCH C1 INTO v1;

EXIT WHEN c1%notfound;

dbms_output.put_line (‘dept10 details ‘ || v1.last_name);

END LOOP;

CLOSE C1;

OPEN c1 (20,’MK_MAN’);

LOOP

FETCH C1 INTO v1;

EXIT WHEN c1%notfound;

dbms_output.put_line (‘dept20 details ‘ || v1.last_name);

END LOOP;

CLOSE C1;

END;

/

Output:

dept10 details Whalen

dept20 details Hartstein

Program8: Usage of Cursor with FOR UPDATE OF Clause

SQL> select salary from employees where department_id=60;

SALARY

———

9000

6000

4800

4800

4200

SQL> UPDATE employees

SET salary=4000

WHERE department_id=60;

Output:

5 rows updated.

DECLARE

CURSOR c1 IS

SELECT employee_id, salary

FROM employees

WHERE department_id=60

FOR UPDATE OF SALARY NOWAIT;

BEGIN

FOR emp_rec IN c1 LOOP

IF emp_rec.salary<5000 then

UPDATE employees

SET salary=3000;

END IF;

END LOOP;

END;

/

SQL> select salary from employees where department_id=60;

SALARY

—————

3000

3000

3000

3000

3000

Note :         Cursor can’t move backward

SQL> ROLLBACK;

Rollback complete.

SQL> select salary from employees where department_id=60;

SALARY

—————–

9000

6000

4800

4800

4200

Program9: Usage of Cursor with FOR UPDATE OF, WHERE CURRENT OF Clause

DECLARE

CURSOR c1 IS

SELECT employee_id, salary

FROM employees

WHERE department_id=60

FOR UPDATE OF SALARY NOWAIT;

BEGIN

FOR emp_rec IN c1 LOOP

IF emp_rec.salary<5000 then

UPDATE employees

SET salary=salary+999

WHERE CURRENT OF c1;

END IF;

END LOOP;

END;

/

SQL> select salary from employees where department_id=60;

SALARY

—————–

9000

6000

5799

5799

5199

SQL> rollback;

Rollback complete.

Program10: Printing the ROWID value

DECLARE

v_row rowid;

v_empno employees.employee_id%type;

v_sal employees.salary%type;

CURSOR c1 IS

SELECT ROWID,employee_id, salary

FROM employees

WHERE department_id=60;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_row,v_empno,v_sal;

exit when c1%NOTFOUND;

dbms_output.put_line(v_row ||’-‘||’-‘||v_empno||’-‘||v_sal);

END LOOP;

CLOSE c1;

END;

/

Output:

AAAMg3AAFAAAABYAAD–103-9000

AAAMg3AAFAAAABYAAE–104-6000

AAAMg3AAFAAAABYAAF–105-4800

AAAMg3AAFAAAABYAAG–106-4800

AAAMg3AAFAAAABYAAH–107-4200

SQL> rollback;

Rollback complete.

Program11: Implementing with ROWID (9i)

DECLARE

v_row rowid;

v_empno employees.employee_id%type;

v_sal employees.salary%type;

CURSOR c1 IS

SELECT ROWID,employee_id, salary

FROM employees

WHERE department_id=60;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_row,v_empno,v_sal;

dbms_output.put_line(v_row ||’-‘||’-‘||v_empno||’-‘||v_sal);

IF V_SAL < 5000 THEN

update employees

set salary=salary+999

where rowid=v_row;

END IF;

exit when c1%NOTFOUND;

END LOOP;

CLOSE c1;

END;

/

Output:

AAAMg3AAFAAAABYAAD–103-9000

AAAMg3AAFAAAABYAAE–104-6000

AAAMg3AAFAAAABYAAF–105-4800

AAAMg3AAFAAAABYAAG–106-4800

AAAMg3AAFAAAABYAAH–107-4200

AAAMg3AAFAAAABYAAH–107-4200

Program12: Raising the Implicit Exception

SELECT first_name

FROM employees

WHERE first_name=’John’;

FIRST_NAME

——————-

John

John

John

DECLARE

v_name VARCHAR2 (10);

BEGIN

SELECT first_name INTO v_name

FROM employees

WHERE first_name=’John’;

dbms_output.put_line (v_name);

END;

/

Output:

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 4

Program13: Usage of TOO_MANY_ROWS Exception

DECLARE

v_name VARCHAR2 (10);

BEGIN

SELECT first_name INTO v_name

FROM employees

WHERE first_name=’John’;

dbms_output.put_line (v_name);

EXCEPTION

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line (‘Returning more than one row’);

END;

/

Output:   Returning more than one row

Program14: Usage of VALUE_ERROR Exception

DECLARE

v_name VARCHAR2(3);

BEGIN

SELECT last_name INTO v_name

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_name);

EXCEPTION

WHEN VALUE_ERROR THEN

dbms_output.put_line (‘Data type size is small’);

END;

/

Output:  Data type size is small

Program15: Usage of ZERO_DIVIDE Exception

DECLARE

v_sal NUMBER;

BEGIN

SELECT salary/0 INTO v_sal

FROM employees

WHERE employee_id=101;

dbms_output.put_line (v_sal);

EXCEPTION

WHEN ZERO_DIVIDE THEN

dbms_output.put_line (‘We cant divide by zero’);

END;

/

Output:           We cant divide by zero

Program16: Usage of Non Predefined Exception

DECLARE

v_excep EXCEPTION;

PRAGMA EXCEPTION_INIT (v_excep,-6502);

v_name VARCHAR2 (2);

BEGIN

SELECT last_name INTO v_name

FROM employees

WHERE employee_id=101;

EXCEPTION

WHEN v_excep THEN

dbms_output.put_line (‘Check the Variable Size’);

END;

/

Output:               Check the Variable Size

Program17: Usage of Tracking the Error Number, Error Message

DECLARE

v_name VARCHAR2 (2);

v_err_num NUMBER;

v_err_mess VARCHAR2 (250);

BEGIN

SELECT last_name INTO v_name

FROM employees WHERE employee_id=101;

EXCEPTION

WHEN OTHERS THEN

v_err_num := SQLCODE;

v_err_mess := SQLERRM;

dbms_output.put_line (v_err_num);

dbms_output.put_line (v_err_mess);

END;

/

Output:

-6502

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Program18: Usage of User Defined Exception

DECLARE

v_excep EXCEPTION;

BEGIN

UPDATE employees

SET salary=8000

WHERE employee_id=1;

IF SQL%NOTFOUND THEN

RAISE v_excep;

END IF;

EXCEPTION

WHEN v_excep THEN

dbms_output.put_line (‘Explicitly Raised Exception’);

END;

/

Output:

Explicitly Raised Exception

Program19: Usage of RAISE_APPLICATION_ERROR

BEGIN

UPDATE employees

SET salary=8000

WHERE employee_id=1;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR (-20000,’Raising Error’);

END IF;

END;

/

Output:

BEGIN

*

ERROR at line 1:

ORA-20000: Raising Error

ORA-06512: at line 6

Program20: Handling Multiple Exceptions in Exception Section

DROP TABLE messages PURGE;

SQL>CREATE TABLE MESSAGES

(

MESSAGE VARCHAR2 (250)

);

Table created.

SQL> DELETE FROM MESSAGES;

0 rows deleted.

SQL> DEFINE sal = 6000

DECLARE

ename employees.last_name%TYPE;

emp_sal employees.salary%TYPE := &sal;

BEGIN

SELECT last_name

INTO ename

FROM employees

WHERE salary = emp_sal;

INSERT INTO messages VALUES (ename || ‘ – ‘ || emp_sal);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO messages VALUES

(‘No employee with a salary of ‘|| TO_CHAR (emp_sal));

WHEN too_many_rows THEN

INSERT INTO messages VALUES

(‘More than one employee with a salary of ‘||TO_CHAR (emp_sal));

WHEN others THEN

INSERT INTO messages VALUES

(‘Some other error occurred.’);

END;

/

Output:

old   3: emp_sal employees.salary%TYPE := &sal;

new   3: emp_sal employees.salary%TYPE := 6000;

SQL> SELECT * FROM messages;

MESSAGE

——————————————————–

More than one employee with a salary of 6000

Program21: Child Record found exception

DECLARE

childrecord_exists EXCEPTION;

PRAGMA EXCEPTION_INIT (childrecord_exists, -02292);

BEGIN

DBMS_OUTPUT.PUT_LINE (‘ Deleting department 40……..’);

DELETE FROM departments

WHERE department_id=40;

EXCEPTION

WHEN childrecord_exists THEN

DBMS_OUTPUT.PUT_LINE (‘ Cannot delete this department. There are employees in this department

(child records exist.) ‘);

END;

/

Output:

Deleting department 40……..

Cannot delete this department. There are employees in this department

(child records exist.)

Lesson 9-10 Procedures & Functions

Program1: Simple Procedure to display a String

CREATE OR REPLACE PROCEDURE p1

IS

BEGIN

dbms_output.put_line (‘welcome to 1st Procedure’);

END p1;

/

Output:

Procedure created.

Executing a procedure

SQL> exec p1;

welcome to 1st Procedure

Program2: Simple Procedure to display a String

CREATE PROCEDURE hello_again IS

BEGIN

DBMS_OUTPUT.PUT_LINE (‘Hello World again’);

END;

/

Output:

CREATE PROCEDURE hello_again IS

BEGIN

DBMS_OUTPUT.PUT_LINE (‘Hello World again’);

END;

/

Executing a procedure in a PL/SQL block

SQL> BEGIN

hello_again;

END;

Output:

Hello World again

Program66: Creating a Procedure with IN parameters (INSERT)

CREATE OR REPLACE PROCEDURE add_job

(

jobid jobs.job_id%TYPE,

jobtitle jobs.job_title%TYPE

)

IS

BEGIN

INSERT INTO jobs (job_id, job_title) VALUES (jobid, jobtitle);

COMMIT;

END add_job;

/

Executing a Procedure

SQL> EXECUTE add_job (‘IT_DBA’, ‘Database Administrator’);

PL/SQL procedure successfully completed.

SQL> SELECT *

FROM jobs

WHERE job_id = ‘IT_DBA’;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

———- ———————————– ———- ———-

IT_DBA     Database Administrator

Program67: Creating a Procedure with IN parameters (UPDATE) with Exception Handling

CREATE OR REPLACE PROCEDURE upd_job

(

jobid IN jobs.job_id%TYPE,

jobtitle IN jobs.job_title%TYPE

)

IS

BEGIN

UPDATE jobs

SET job_title = jobtitle

WHERE job_id = jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR (-20202, ‘No job updated.’);

END IF;

END upd_job;

/

Output:

Procedure created.

Executing a Procedure:

SQL> EXECUTE upd_job (‘IT_DBA’, ‘Data Administrator’);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM jobs WHERE job_id = ‘IT_DBA’;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY

———- ———————————– ———- ———-

IT_DBA     Data Administrator

SQL> EXEC upd_job (‘IT_WEB’, ‘Web Master’);

SQL> EXEC upd_job (‘IT_WEB’, ‘Web Master’);

BEGIN upd_job (‘IT_WEB’, ‘Web Master’); END;

*

ERROR at line 1:

ORA-20202: No job updated.

ORA-06512: at “HR.UPD_JOB”, line 12

ORA-06512: at line 1

Program: 68

Creating a Procedure with IN parameters (DELETE) with Exception Handling

CREATE OR REPLACE PROCEDURE del_job

(

jobid jobs.job_id%TYPE

)

IS

BEGIN

DELETE FROM jobs

WHERE job_id = jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR (-20203, ‘No jobs deleted.’);

END IF;

END DEL_JOB;

EXECUTE del_job (‘IT_DBA’)

SELECT * FROM jobs WHERE job_id = ‘IT_DBA’;

SQL> EXECUTE del_job (‘IT_WEB’);

BEGIN del_job (‘IT_WEB’); END;

*

ERROR at line 1:

ORA-20203: No jobs deleted.

ORA-06512: at “HR.DEL_JOB”, line 11

ORA-06512: at line 1

Program69: Creating a Procedure with IN parameter along with local variables

CREATE OR REPLACE PROCEDURE P1

(

P_NO IN NUMBER

)

IS

v_name VARCHAR2 (10);

BEGIN

SELECT first_name

INTO v_name

FROM employees

WHERE employee_id=P_NO;

dbms_output.put_line (v_name);

END;

/

Output:

Executing a procedure:

EXEC P1 (100);

Program70: Procedure with Cursors

CREATE OR REPLACE PROCEDURE P1

IS

CURSOR emp_cursor IS SELECT * FROM employees;

BEGIN

FOR emp_rec IN emp_cursor LOOP

dbms_output.put_line (emp_rec.employee_id);

END LOOP;

END;

EXEC p1;

Program: 71

Procedure with OUT Parameter (Use Bind variable for OUT Parameter)

CREATE OR REPLACE PROCEDURE p1

(P_NO IN NUMBER, P_JOB OUT VARCHAR2, P_SAL OUT NUMBER)

IS

BEGIN

SELECT job_id, salary

INTO P_JOB, P_SAL

FROM employees

W HERE employee_id=P_NO;

END p1;

Procedure Created

SQL> variable g_job VARCHAR2 (20)

SQL> variable g_sal varchar2 (20)

SQL> exec p1 (100, :g_job, :g_sal);

SQL> print g_job g_sal

Program72: Procedure with OUT Parameter (Use Bind variable for OUT Parameter) With Exception Handling

CREATE OR REPLACE PROCEDURE p1

(

P_NO IN NUMBER,

P_JOB OUT VARCHAR2,

P_SAL OUT NUMBER

)

IS

BEGIN

SELECT job_id, salary

INTO P_JOB, P_SAL

FROM employees

WHERE employee_id=P_NO;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line (‘Check the Parameter Value’);

END p1;

/

Variable g_job VARCHAR2(20)

Variable g_sal VARCHAR2(20)

EXEC p1 (10, :g_job, :g_sal);

Note:

A procedure can become invalid if the table it is based on is deleted or changed

We can recompile an invalid procedure using this command:

ALTER PROCEDURE procedure_name COMPILE;

Example1:

CREATE OR REPLACE FUNCTION f1

(

p_empno employee.employee_id%TYPE

)

RETURN varchar2

AS

v_name varchar2(50);

BEGIN

SELECT first_name

INTO v_name

FROM employees

WHERE employee_id=p_empno;

RETURN v_name;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN(‘The employee_id is not in the database’);

WHEN OTHERS THEN

RETURN(‘Error in running function’);

END;

/

EXEC f1;

Example:2

CREATE OR REPLACE FUNCTION id_is_good

(

p_empno IN NUMBER

)

RETURN BOOLEAN

AS

v_id_cnt NUMBER;

BEGIN

SELECT COUNT(*)

INTO v_id_cnt

FROM employees

WHERE employee_id = p_empno;

RETURN 1 = v_id_cnt;

EXCEPTION

WHEN OTHERS THEN

RETURN FALSE;

END id_is_good;

/

DECLARE

v_id number;

BEGIN

v_id := &id;

IF id_is_good(v_id) THEN

DBMS_OUTPUT.PUT_LINE (‘Student ID: ‘||v_id||’ is a valid.’);

ELSE

DBMS_OUTPUT.PUT_LINE (‘Student ID: ‘||v_id||’ is not valid.’);

END IF;

END;

/

Advertisements

FUNCTION & PROCEDURE Differences

                     FUNCTION          PROCEDURE
functions can be used in select or update or delete statement while

A Function can be used in the SQL Queries

The functions are used to do the calculations instead of doing in query

Function should return value

Function can be called from select/where/having clause

The function cannot run independently, It has to be the part of the SQL statement

Function cannot call the stored procedures.

The temporary table cannot be created in the function.

But the function can do only select operation.

Function cannot have the transaction statements.

But the function can create, update and delete the table variable. It can return table variable.

procedure can’t

procedure cannot be used in SQL queries 

The stored procedure normally used to perform a speck task.

Stored procedures may or may not return values

Stored procedure cannot be used in the select/where/having clause

Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

Stored procedure can call the user defined functions

The stored procedure allows us to create the temp tables in the stored procedure.

Stored procedures can have select and all DML operations.

Stored procedure can use transaction statements.

Stored procedures can create table variable and cannot return the table variable.