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;
/
