plsql programing

Published on August 20 2015 by LakshmiSaahul,Dhana Royal

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;

Advertising