PLSQL
Features of PL/SQL
PL/SQL Block
- Variable Declaration:
- Executable Statements:
- Anonymous Blocks:
- Named Blocks
- Attribute Declaration:
- IF
- Simple IF
- Complex IF
- CASE
- Simple LOOP
- WHILE LOOP
- FOR LOOP
- Implicit Cursor
- Explicit Cursor
- Cursor FOR Loop
- Cursors with Parameters
- Pre Defined Exceptions
- USER DEFINED EXCEPTIONS
- Non Pre Defined Exceptions
Autonomous Transaction
Sub Programs
- Procedure
- Function
- Packages
Collections
Dynamic SQL
BULK COLLECT
Ref Cursor
File Input and Output
- UTL_FILE Operations
- Modes
- NEW and OLD
- RAISE_APPLICATION_ERROR
- Instead of Triggers
********************************************************************
PL/SQL: Programming Language of 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.
It is an collection of SQL and Programming Language Statements
We have two types of PL/SQL Blocks
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. 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
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;
- 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;
%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:
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
< Executable statements>;
EXIT WHEN <condition>;
END LOOP;
END LOOP;
This
PL/SQL Block prints the first 10 Numbers:
DECLARE
N Number
(2):=1;
BEGIN
DBMS_OUTPUT.PUT_LINE ('The Numbers are :');
DBMS_OUTPUT.PUT_LINE (N);
N: = N+1;
EXIT WHEN (N > 10);
END LOOP ;
END;
WHILELOOP :
It is pre tested Loop
WHILE
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>
< 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
- Automatically created by Oracle when ever DML statements are performed.
- It has Standard name 'SQL'
- Cursor Attribute are
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
- It will give the status of DML operation
- Valid in PL/SQL block only
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)
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
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;
--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
< 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
DECLARE
CURSOR emp_cursor (dno number) IS
SELECT *
FROM emp
WHERE deptno = dno;
SELECT *
FROM emp
WHERE deptno = dno;
i emp_cursor%ROWTYPE;
incr
number(10);
exp number(10);
BEGIN
OPEN emp_cursor(&dno);
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;