PLSQL

INDEX

Features of PL/SQL

PL/SQL Block

Variable Declaration:
Executable Statements:
Anonymous Blocks:
Named Blocks
Attribute Declaration:

Conditional Constructs

IF
Simple IF
Complex IF
CASE

Iteration Control Statements

Simple LOOP
WHILE LOOP
FOR LOOP

Cursors

Implicit Cursor
Explicit Cursor
Cursor FOR Loop
Cursors with Parameters

Exceptions

Pre Defined Exceptions
USER DEFINED EXCEPTIONS
Non Pre Defined Exceptions

PRAGMA
Autonomous Transaction
Sub Programs

Procedure
Function
Packages

Composite Data Types
Collections
Dynamic SQL
BULK COLLECT
Ref Cursor
File Input and Output

UTL_FILE Operations
Modes

Triggers

NEW and OLD
RAISE_APPLICATION_ERROR
Instead of Triggers

********************************************************************

PL/SQL: Programming Language of SQL

Features of PL/SQL:

Supports to execute a block of statements as a unit.
Supports Variables and Constants.
Supports conditional constructs (IF).
Supports iteration control statements (FOR and WHILE Loops).
Supports error handling.
Supports to define user defined data types
Supports Event driven programming thru triggers.
Supports to share code with other users thru sub programs.

PL/SQL Block:

It is an collection of SQL and Programming Language Statements
We have two types of PL/SQL Blocks

Variable Declaration:

Supports all SQL data types and Boolean data types.
Data types are

Scalar Data types:
1. NUMBER
2. CHAR
3. VARCHAR2
4. DATE
5. CONSTANT
6. BOOLEAN etc
Composite Data types:

1. RECORDS

2. TABLES

3. VARRAYS

LOB Datatypes:
1. BLOB

2. BFILE

3. CLOB

Reference Data types

1. REF CURSORS

DECLARE

v_empno number (10);

v_ename varchar2 (15);

c_pin constant number(6):=600101;

v_doj date default sysdate;

v_flag boolean:= true;

Executable Statements:

DML, TCL are allowed
DDL, DCL are allowed through Dynamic SQL
SELECT <column list> INTO <variables> from <variable list> WHERE <condition> (It is used to retrieve the columns values into variables)
:= <==> Assignment Operator
= <==>Comparison Operator
-- <==> Comment
/* */ <==> Multiple Line Comment
DBMS_OUTPUT.PUT_LINE('Message Text'|| variables); (It is used to print messages and variable contents on the screen)

Anonymous Blocks: Blocks without any standard name

DECLARE

<Variable Declaration> ;----Optional

BEGIN

<Executable Statements>;

EXCEPTION

<Executable Statements> ;----Optional

END;

Named Blocks: Blocks with a permanent name

EX:Sub Programs and Triggers

CREATE or REPLACE PROCEDURE proc as

<Variable Declaration>;----Optional

BEGIN

<Executable Statements>;

EXCEPTION

<Executable Statements>;----Optional

END proc;

This PL/SQL Block retrieves the employee details and calculates net salary and prints:

DECLARE

v_empno number(10):=7900;

v_ename varcaher2 (15);

v_sal number(12, 2);

v_comm number(10, 2);

v_net number(12, 2);

BEGIN

--Retrieving employee details

SELECT ename, sal, comm INTO v_ename, v_sal, v_comm FROM emp

WHERE empno = v_empno;

--Calculating net salary

v_net := v_sal + nvl (v_comm, 0);

DBMS_OUTPUT.PUT_LINE ('Employee Details are :');

DBMS_OUTPUT.PUT_LINE (v_empno||','||v_ename||','||v_net);

END;

Attribute Declaration:

Supports to define the variables dynamically according to table structure

%Type: Used to define the variable according to column structure
Variable <table>.<column>%type;
Example: v_empno emp.empno%type;
%Rowtype: Used to define the variable representing complete table structure
variable <table name>%rowtype;
Example: l_emp emp%rowtype;

Conditional Constructs:

IF CASE

Simple IF:

IF <condition> THEN

<Executable Statements>;

END IF;

Complex IF:

IF <Condition1> THEN

<Executable Statements> ;

ELSIF <Condition2> THEN

<Executable Statements> ;

ELSE

<Executable Statements> ;

END IF;

This PL/SQL Block checks for existing commission and provides new commission:

DECLARE

v_eno emp.empno%type:=&empno;

v_ename emp.ename%type;

v_comm emp.comm%type;

BEGIN

SELECT ename, comm INTO v_ename, v_comm

FROM emp

WHERE empno=v_eno;

IF v_comm is NULL THEN

v_comm := 500;

ELSIF v_comm <500 THEN

v_comm := v_comm+200;

ELSE

v_comm := v_comm + v_comm * .25;

END IF;

UPDATE emp SET comm = v_comm WHERE empno = v_eno;

DBMS_OUTPUT.PUT_LINE (v_eno||','||v_ename||','||v_comm);

END;

CASE:

CASE <variable>

WHEN <value1> THEN

<Executable Statements>;

WHEN <value2> THEN

<Executable Statements>;

OTHERS

<Executable Statements>;

END CASE;

This PL/SQL Block checks for Grade

DECLARE

grade char (1) := '&grade';

BEGIN

CASE grade

WHEN 'A' THEN

DBMS_OUTPUT.PUT_LINE ('Grade in A');

WHEN 'B' THEN

DBMS_OUTPUT.PUT_LINE ('Grade in B');

WHEN 'C' THEN

DBMS_OUTPUT.PUT_LINE ('Grade in C');

ELSE

DBMS_OUTPUT.PUT_LINE ('Grade in D');

END CASE;

END;

Iteration Control Statements:

Supports to execute the block of statements repeatedly
Four types of Iterations are there

1. Simple Loop

2. WHILE Loop

3. Numeric FOR Loop

4. Cursor FOR Loop

Simple LOOP:

LOOP

< Executable statements>;

EXIT WHEN <condition>;
END LOOP;

This PL/SQL Block prints the first 10 Numbers:

DECLARE

N Number (2):=1;

BEGIN

DBMS_OUTPUT.PUT_LINE ('The Numbers are :');

LOOP

DBMS_OUTPUT.PUT_LINE (N);

N: = N+1;

EXIT WHEN (N > 10);

END LOOP;

END;
WHILE LOOP: It is pre tested Loop

WHILE <condition> LOOP

< Executable statements>;

END LOOP;

This PL/SQL Block prints the first 10 Numbers:

DECLARE

N Number: =1;

BEGIN

DBMS_OUTPUT.PUT_LINE ('The Numbers are :');

WHILE (N <= 10) LOOP

DBMS_OUTPUT.PUT_LINE (N);

N: = N+1;

END LOOP;

END;

FOR LOOP:

No need to DECLARE the variables
No need to assign the values
No need to Increment the value

FOR <variable> IN [REVERSE] <lower bound>.. <upper bound>

LOOP

< Executable statements>;

END LOOP;

This PL/SQL Block prints the first 10 Numbers:

BEGIN

DBMS_OUTPUT.PUT_LINE ('The Numbers are:');

FOR N IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE (N);

END LOOP;

END;

Cursors:

It is a temporary memory used to hold the transactional data
It is valid in PL/SQL block only
It will be created in logical memory of server
They are not stored in data base and they are not reusable
Two types of cursors are there

1. Implicit Cursor

2.Explicit Cursor

Implicit Cursor:

Automatically created by Oracle when ever DML statements are performed.
It has Standard name 'SQL'
Cursor Attribute are

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

It will give the status of DML operation
Valid in PL/SQL block only

This PL/SQL Block gives the no of employees are updated:

DECLARE

v_deptno emp.deptno%type: =&deptno;

BEGIN

UPDATE emp SET sal := sal + 300 WHERE deptno = v_deptno;

IF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE (' No Employees in Department:'||v_deptno);

ELSE

DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT||'Employees are Updated');

COMMIT;

END IF;

END;

Explicit Cursor:

Created by user in PL/SQL block
Declaration of Cursor

Cursor <cursor name> is <select statement>;

Cursor Operations are

1. OPEN <Cursor name> :
Open the Cursor (Memory will be allocated to Cursor after opening it only)

2. FETCH <Cursor name> INTO <variable list>:
Used to retrieve the rows from Cursor to Variables at a time. It Returns only one row.
Generally it will be provided in LOOP

3. CLOSE <Cursor name> <==> Close the Cursor (Memory allocated will be Deallocated)

Cursor Attributes are

1. <Curcor name>%ISOPEN <==> Return true if Cursor is opened Successfully

2. <Cursor name>%FOUND <==> Return true if fetch statement is Successful

3. <Cursor name>%NOTFOUND <==> Return true if fetch statement is not successful

4. <Cursor name>%ROWCOUNT <==> Gives the number of rows fetched from Cursor

This PL/SQL Block checks for existing commission and provides new commission:

DECLARE

v_eno emp.empno%type;

v_ename emp.ename%type;

v_comm emp.comm%type;

CURSOR emp_cursor is SELECT empno, ename, comm FROM emp;

BEGIN

--Opening Cursor

OPEN emp_cursor;

LOOP

--Fetching Rows

FETCH emp_cursor INTO v_eno, v_ename, v_comm;

EXIT WHEN emp_cursor%NOTFOUND;

IF v_coom is NULL THEN

v_comm := 500;

ELSIF v_comm = 0 THEN

v_comm:= 200;

ELSE

v_comm:= v_comm + v_comm * .25;

END IF;

UPDATE emp SET comm = v_comm WHERE empno = v_eno;

DBMS_OUTPUT.PUT_LINE (v_eno||','||v_ename||','||v_comm);

END LOOP;

DBMS_OUTPUT.PUT_LINE ('No of rows manipulated with new comm:'||

emp_cursor%ROWCOUNT);

--Closing Cursor

CLOSE emp_cursor;

COMMIT;

END;

Cursor FOR Loop:

No need to OPEN, FETCH and CLOSE the Cursor
No need to declare variables
No need to Check for end
Syntax of Cursor FOR Loop is

FOR <variable> IN <Cursor name> LOOP
< Executable statements>;
END LOOP;

This PL/SQL Block gives Employee Details

DECLARE

CURSOR emp_cursor is SELECT empno, ename, comm FROM emp;

BEGIN

FOR C IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE (C.empno||','||C.ename||','||C.comm);

END LOOP;

END;

Cursors with Parameters:

Supports to define Cursors with arguments
Cursor accept values dynamically while opening them
Maximum 32 parameters can be passed to cursor

Example for Cursors with Parameters:

DECLARE

CURSOR emp_cursor (dno number) IS
SELECT *
FROM emp
WHERE deptno = dno;

i emp_cursor%ROWTYPE;

incr number(10);

exp number(10);

BEGIN

OPEN emp_cursor(&dno);

LOOP

FETCH emp_cursor INTO i;

EXIT WHEN emp_cursor%NOTFOUND;

exp := ROUND (MONTHS_BETWWEN(SYSDATE, i.hiredate)/12;

IF exp <= 2 THEN

incr := i.sal + 300;

ELSIF exp > 2 AND exp <= 4 THEN

incr := i.sal + 500;

ELSE

incr := i.sal + 700;

END IF;

UPDATE emp SET sal := sal+ incr WHERE empno = i.empno;

DBMS_OUTPUT.PUT_LINE (i.empno||','||i.ename||','||i.sal||','||exp||','||incr);

END LOOP;

CLOSE emp_cursor;

COMMIT;

END;

Pradipta's Oracle Application

Saturday, 12 March 2016

Oracle PLSQL

Variable Declaration:

Attribute Declaration: