PL/SQL
======
- PL/SQL STANDS FOR PROCEDURAL LANGUAGE WHICH IS AN EXTENSION
OF
SQL.PL/SQL WAS INTRODUCED IN ORACLE 6.0 VERSION.
- SQL IS A NON-PROCEDURAL LANGUAGE WHEREAS PL/SQL IS A
PROCEDURAL LANGUAGE.
- SQL SUPPORTS A SINGLE LINE STATEMENT (QUERY) EXECUTION
PROCESS WHEREAS PL/SQL SUPPORTS MULTI LINES STATEMENTS(PROGRAM)
EXECUTION PROCESS.
- IN SQL EVERY QUERY STATEMENT IS COMPILING AND EXECUTING
INDIVIDUALLY.SO THAT NO.OF COMPILATIONS ARE INCRESED AND REDUCE
PERFORMANCE OF DATABASE.
- IN PL/SQL ALL SQL QUERIES ARE GROUPED INTO A SINGLE BLOCK AND
WHICH WILL COMPILE AND EXECUTE ONLY ONE TIME.SO THAT IT WILL REDUCE
NO.OF COMPILATIONS AND IMPROVE PERFORMANCE OF DATABASE.
FEATURES OF PL/SQL:
-------------------------------------
1. TO IMPROVES PERFORMANCE.
2. SUPPORTING CONDITIONAL & LOOPING STATEMENTS.
3. SUPPORTING REUSABILITY.
4. PROVIDING SECURITY BECAUSE ALL PROGRAMS ARE SAVED
IN DATABASE AND AUTHORIZED USER CAN ONLY ACCESS THE
PROGRAMS.
5. SUPPORTING PORTABILITY i.e PL/SQL PROGRAMS CAN BE MOVED FROM ONE
PLATFORM TO ANOTHER PLATFORM WITHOUT ANY CHANGES.
6. SUPPORTING EXCEPTION HANDLING MECHANISM.
7. SUPPORTING MODULAR PROGRAMMING i.e IN A PL/SQL A BIG PROGRAM CAN BE
DIVIDED INTO SMALL MODULES WHICH ARE CALLED AS STORED PROCEDURE AND
STORED FUNCTIONS.
PL/SQL ARCHITECTURE:
----------------------------------------
> PL/SQL IS BLOCK STRUCTURE PROGRAMMING LANGUAGE.WHICH IS HAVING
THE FOLLOWING TWO ENGINES THOSE ARE
1. SQL ENGINE
2. PL/SQL ENGINE
> WHENEVER WE ARE SUBMITING A PL/SQL BLOCK INTO ORACLE SERVER THEN ALL
SQL STATEMENTS(QUERIES) ARE SEPERATED AND EXECUTING BY SQLQUERY
EXECUTOR WITH IN SQL ENGINE.WHERE AS ALL PL/SQL STATEMENTS(CODE) ARE
SEPERATED AND EXECUTING BY PL/SQL CODE EXECUTOR WITH IN PL/SQL ENGINE.
BLOCK:
======
> A BLOCK IS A SET OF STATEMENTS WHICH ARE COMPILE & EXECUTED BY
BY ORACLE AS A SINGLE UNIT. PL/SQL SUPPORTING THE FOLLOWING
TWO TYPES OF BLOCKS THOSE ARE,
1. ANONYMOUS BLOCK
2. SUB BLOCK
DIFF. B/W ANONYMOUS & SUB BLOCK :
----------------------------------------------------------------
ANONYMOUS BLOCK SUB BLOCK
--------------------------------- -------
-------------
1. UNNAMED BLOCK 1. NAMED BLOCK
2. THIS BLOCK CODE IS NOT 2. THIS BLOCK CODE IS
SAVED IN DB. SAVED IN DB.
3. IT CANNOT REUSABLE. 3. IT CAN BE REUSABLE.
4. EVERY TIME COMPILATION OF 4. PRE - COMPILED CODE
CODE. ( FIRST TIME
COMPILATION ONLY)
5. ARE USING IN "DB TESTING". 5. ARE USING IN APPLICATION
DEVELOPMENT LIKE "JAVA",
".NET" & "DB APPLICATIONS ".
ANONYMOUS BLOCKS:
--------------------------------------
> THESE ARE UNNAMED BLOCKS IN PL/SQL.WHICH CONTAINS THREE MORE
BLOCKS THOSE ARE,
i) DECLARATION BLOCK
ii) EXECUTION BLOCK
iii) EXCEPTION BLOCK
i) DECLARATION BLOCK:
=====================
> THIS BLOCK STARTS WITH " DECLARE " STATEMENT.
> DECLARING VARIABLES,CURSORS,USER DEFINE EXCEPTIONS.
> IT IS OPTIONAL BLOCK.
ii) EXECUTION BLOCK:
===================
> THIS BLOCK STARTS WITH " BEGIN " STATEMENT & ENDS WITH "END"
STATEMENT.
> IMPLEMENTING SQL STATEMENTS(SQL) & LOGICAL CODE OF A PROGRAM
(PL/SQL).
> IT IS MANDATORY BLOCK.
iii) EXCEPTION BLOCK:
===================
> THIS BLOCK STARTS WITH "EXCEPTION" STATEMENT.
> HANDLING EXCEPTIONS.
> IT IS A OPTIONAL BLOCK.
STRUCTURE:
===========
DECLARE
< VARIABLES,CURSOR,UD EXCEPTIONS>;
BEGIN
< WRITING SQL STATEMENTS>;
< PL/SQL LOGICAL CODE>;
EXCEPTION
< HANDLING EXCEPTIONS>;
END;
/
VARIABLES IN PL/SQL:
===================
STEP1: DECLARING VARIABLES:
===========================
SYNTAX:
---------------
DECLARE
<VARIABLE NAME> <DT>[SIZE];
EX:
DECLARE
A NUMBER(10) (OR) A INT;
B VARCHAR2(10);
STEP2: ASSIGNING / STORING A VALUE INTO VARIABLE:
===============================================
SYNTAX:
--------------
<VARIABLE NAME> := <VALUE>;
EX:
A := 1021;
B := 'SAI';
Here,
:= - ASSIGNMENT OPERATOR IN PL/SQL
= - COMPARISION OPERATOS IN PL/SQL
STEP3: PRINTING VARIABLES VALUES:
================================
SYNTAX:
--------------
DBMS_OUTPUT.PUT_LINE(<VARIABLE NAME > (OR) '<UD MESSAGE>');
EX:
DBMS_OUTPUT.PUT_LINE(A);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL');
EX1:
TO PRINT "WELCOME TO PL/SQL" STATEMENT.
SOL:
SQL> BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO PL/SQL');
END;
/
PL/SQL procedure successfully completed.
NOTE:
THE ABOVE PROGRAM WILL NOT DISPLAY THE OUTPUT OF A PL/SQL PROGRAM.IF
ORACLE SERVER WANT TO DISPLAY OUTPUT OF A PL/SQL PROGRAM THEN WE USE
THE FOLLOWING SYNTAX,
SYNTAX:
SET SERVEROUTPUT OFF / ON;
Here,
OFF : OUTPUT IS NOT DISPLAY
ON : OUTPUT IS DISPLAY
SQL> SET SERVEROUTPUT ON;
SQL> /
WELCOME TO PL/SQL
EX2:
TO PRINT VARIABLES VALUES ?
SOL:
SQL> DECLARE
2 X NUMBER(10);
3 Y NUMBER(10);
4 BEGIN
5 X:=100;
6 Y:=200;
7 DBMS_OUTPUT.PUT_LINE('VARIABLES VALUES ARE:'||X||','||Y);
8 END;
9 /
VARIABLES VALUES ARE:100,200
EX3:
TO PRINT SUM OF TWO NUMBERS AT RUNTIME ?
SOL:
DECLARE
X NUMBER(2);
Y NUMBER(2);
Z NUMBER(10);
BEGIN
X:=&X;
Y:=&Y;
Z:=X+Y;
DBMS_OUTPUT.PUT_LINE(Z);
END;
/
OUTPUT:
Enter value for x: 10
old 6: X:=&X;
new 6: X:=10;
Enter value for y: 20
old 7: Y:=&Y;
new 7: Y:=20;
30
VERIFY :
========
ON = DISPLAY OLD,NEW BIND VARIABELE STATEMENTS
OFF = DOESNOT DISPLAY OLD,NEW BIND VARIABLES STATEEMTNS
SYNTAX:
--------------
SET VERIFY ON / OFF
EX:
SQL> SET VERIFY OFF;
SQL> /
Enter value for x: 10
Enter value for y: 20
30
SELECT...... INTO STATEMENT:
=========================
> STORING COLUMNS VALUES INTO VARIABELS.
> RETURNS A SINGLE ROW (OR) A SINGLE VALUE.
> CAN USE IN EXECUTION BLOCK.
SYNTAX:
--------------
SELECT <COLUMN NAME1>,<COLUMN NAME2>,...........INTO <VARIABLE NAME1>,
<VARIABLE NAME2>,..... FROM <TN> [ WHERE <CONDITION>];
EX1:
WA PL/SQL PRG. TO DISPLAY ENAME,SALARY DETAILS FROM EMP TABLE AS PER
THE
GIVEN EMPNO BY USING SELECT ......INTO STATEMENT ?
SOL:
DECLARE
v_ENAME VARCHAR2(10);
v_SAL NUMBER(10);
BEGIN
SELECT ENAME,SAL INTO v_ENAME,v_SAL FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE(v_ENAME||','||v_SAL);
END;
/
OUTPUT:
Enter value for empno: 7788
SCOTT,3000
EX:
WA PL/SQL PRG. TO FETCH MAX.SALARY OF EMP TABLE BY USING
"SELECT INTO" STATEMENT?
SOL:
DECLARE
v_MAXSAL NUMBER(10);
BEGIN
SELECT MAX(SAL) INTO v_MAXSAL FROM EMP;
DBMS_OUTPUT.PUT_LINE(v_MAXSAL);
END;
/
OUTPUT:
5000
VARIABLES ATTRIBUTES (OR) ANCHOR NOTATIONS:
============================================
> VARIABLES ATTRIBUTES ARE USED IN PLACE OF DATATYPES AT VARIABLE
DECLARATION.
> WHENEVER WE ARE USING VARIABLES ATTRIBUTES INTERNALLY ORACLE SERVER
IS ALLOCATE SOME MEMORY FOR THESE VARIABLES ATTRIBUTES FOR STORING
THE CORRESPONDING VARIABLE COLUMN DATATYPE WHICH WAS ASSIGNED AT THE
TIME OF TABLE CREATION.
> VARIABLES ATTRIBUTES ARE ALSO CALLED AS "ANCHOR NOTATIONS".
> THE ADVANTAGE OF VARIABLES ATTRIBUTES ARE WHENEVER WE WANT TO CHANGE
A PARTICULAR COLUMN DATATYPE IN A TABLE THEN THE CORRESPONDING COLUMN
VARIABLE DATATYPE ALSO CHANGED IN VARIABLE ATTRIBUTE MEMORY
AUTOMATICALLY.
> PL/SQL SUPPORTS THE FOLLOWING TWO TYPE VARIABLES ATTRIBUTES ARE,
1. COLUMN LEVEL ATTRIBUTES
2. ROW LEVEL ATTRIBUTES
1. COLUMN LEVEL ATTRIBUTES:
===========================
> IN THIS LEVEL WE ARE DEFINING VARIABLES ATTRIBUTES FOR
INDIVIDUAL COLUMNS.IT IS REPRESENTING WITH "%TYPE" STATEMENT.
SYNTAX:
---------------
<VARIABLE NAME> <TN>.<COLUMN NAME>%TYPE;
EX:
v_ENAME EMP.ENAME%TYPE;
v_SAL EMP.SAL%TYPE;
PRG:
====
DECLARE
v_ENAME EMP.ENAME%TYPE;
v_SAL EMP.SAL%TYPE;
BEGIN
SELECT ENAME,SAL INTO v_ENAME,v_SAL FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE(v_ENAME||','||v_SAL);
END;
/
OUTPUT:
Enter value for empno: 7788
SCOTT,3000
2. ROW LEVEL ATTRIBUTES:
========================
> IN THIS LEVEL WE ARE DECLARING A SINGLE VARIABLE WILL REPRESENT ALL
DIFFERENT DATATYPES OF COLUMNS IN A TABLE.IT REPRESENT WITH
"%ROWTYPE ".
SYNTAX:
---------------
<VARIABLE NAME> <TABLE NAME>%ROWTYPE;
EX: i EMP%ROWTYPE;
PRG:
DECLARE
i EMP%ROWTYPE;
BEGIN
SELECT ENAME,SAL INTO i.ENAME,i.SAL FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE(i.ENAME||','||i.SAL);
END;
/
(OR)
DECLARE
i EMP%ROWTYPE;
BEGIN
SELECT * INTO i FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE(i.ENAME||','||i.SAL||','||i.DEPTNO);
END;
/
=======================================================================
==
