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
Previous Post
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: