IT IS A PROGRAMMING LANGUAGE WHICH IS USED TO DEFINE OUR OWN LOGICS
IT IS USED TO EXECUTE BLOCK OF STATEMENTS AT A TIME AND INCREASE THE PERFORMANCE
IT SUPPORTS VARIABLES, CONDITIONAL STATEMENTS, LOOPS, OBJECT ORIENTED
PROGRAMMING & COMPOSITE DATA TYPES
IT SUPPORTS HANDLE THE ERROR HANDILING MECHANISM
BLOCK:
IT IS ONE OF THE AREA WHICH IS USED TO WRITE A PROGRAMMING LOGIC. THIS BLOCK IS HAVE 3
SECTIONS
1. DECLARATION SECTION (USED TO DECLARE THE VARIABLES, CURSORS & EXCEPTIONS ETC.
OPTIONAL)
2. EXECUTABLE SECTION (USED TO WRITE A PROGRAM CODING. MANDATORY)
3. EXCEPTION SECTION (USED TO HANDLE THE ERRORS AT RUN TIME. OPTIONAL)
THERE ARE 2 TYPES OF BLOCKS SUPPORTED BY PLSQL
1. ANONYMOUS BLOCK (THESE BLOCKS DOES NOT HAVE A NAME AND DOES NOT STORED IN DB.
SYN:
DECLARE
------------
BEGIN
-------------
-------------
END;
EX:
BEGIN
DOPL (‘RASATIMSETTI VENKATESH’)
END;
2. NAMED BLOCK (THESE BLOCKS HAVE A NAME AND STORED IN DB)
EX: PROCEDURES, FUNCTIONS, PACKAGES, TRIGGERS ETC
DBMS_OUTPUT.PUT_LINE IS A PREDEFINED PKG PROVIDED BY ORACLE
SET SERVEROUTPUT ON. DISPLAYS THE RESULT IN SAME PROMPT.
VARIABLE: IT IS ONE OF THE MEMORY LOCATION WHICH IS USED TO STORE THE DATA.
GENERALLY, WE DECLARE THE VARIABLES IN DECLARATION SECTION. THESE ARE SUPPORTED
WITH DEFAULT & NOT NULL.
SYNTAX: VARIABLE_NAME DATATYPE (SIZE);
CREATE IS THERE… THEN THAT IS CALLED NAMED BLOCK.
ASSIGNMENT OPERATOR [:=]. WE STORING VALUE IN TO A VARIABLE.
DISPLAY MESSAGE OR VARIABLE VALUE [WE HAVE ONE PREDEFINED PKG WHICH IS USED TO
DISPLAY THE MESSAGE OR VALUE IN A PROGRAM. SYN: DOPL
(‘MESSAGE’)/DOPL(VARIABLE_NAME)]
SELECT INTO CLAUSE (RETRIVE THE DATA FROM TABLE & STORING INTO PLSQL VARIABLES)
SYN: SELECT COL1, COL2 INTO VAR1, VAR2
DATA TYPES:
1. PREDEFINED DATA TYPES:
A. %TYPE
[USED TO ASSIGN THE COLUMN DATA TYPE TO A VARIABLE. USED TO STORE ONE VALUE AT A
TIME. NOT POSSIBLE TO HOLD MORE THAN ONE COLUMN VALUES OR ROW VALUES.
SYN: VARIABLE_NAME TABLE_NAME.COLUMN_NAME%TYPE]
EX:
DECLARE
VNAME [Link]%TYPE;
BEGIN
SELECT ENAME INTO VNAME FROM EMP WHERE EMPNO=&NO;
DOPL (VNAME);
END;
/
B. %ROWTYPE
[USED TO ASSIGN ALL THE COLUMN DATA TYPES OF TABLE TO A VARIABLE. IT HOLDS ENTIRE
RECORD OF THE SAME TABLE. EACH OF THE TIME, IT OVERRIDE ONLY ONE RECORD. NOT
POSSIBLE TO CAPTURE THE MORE THAN ONE TABLE DATA.
SYN: VARIABLE_NAME TABLE_NAME%ROWTYPE]
EX:
DECLARE
VROW EMP%ROWTYPE;
BEGIN
SELECT * INTO VROW FROM EMP WHERE EMPNO=&NO;
DOPL ([Link]||’,’|| [Link]||’,’|| [Link]||’,’|| [Link]);
END;
/
2. USER DEFINED TEMPORARY DATA TYPES:
A. RECORD TYPE OR PLSQL RECORD
[USED TO STORE MORE THAN ONE TABLE DATA OR TO ASSIGN MORE THAN ONE COLUMN
DATATYPES. MUST CONTAIN ATLEAST ONE ELEMENT. PINPOINT OF DATA IS NOT POSSIBLE.
SYN: TYPE TYPE_NAME IS RECORD (VALUE1 DATATYPE, VALUE2 DATATYPE…)
VARIABLE_NAME TYPE_NAME]
EX:
DECLARE
TYPE REC IS RECORD (VNO [Link]%TYPE, VNAME [Link]%TYPE, VDNAME
[Link]%TYPE, VDLOC [Link]%TYPE)
VROW REC;
BEGIN
SELECT [Link], [Link], [Link], [Link] INTO VROW FROM EMP, DEPT
WHERE [Link]=[Link] AND [Link]=&NO;
DOPL ([Link]||’,’|| [Link]||’,’|| [Link]||’,’|| [Link]);
END;
/
CONDITIONAL STATEMENTS:
1. IF
2. IF ELSE
3. ELSIF
4. CASE
EX1:
DECLARE
AGE NUMBER(2) :=&N;
STATUS VARCHAR2(20);
BEGIN
IF AGE>=18 THEN STATUS:=’VALID AGE’;
END IF;
DOPL(STATUS);
END;
EX2:
DECLARE
AGE NUMBER(2) :=&N;
STATUS VARCHAR2(20);
BEGIN
IF AGE>=18 THEN STATUS:=’VALID AGE’;
ELSE STATUS:=’INVALID AGE’;
END IF;
DOPL(STATUS);
END;
EX3:
DECLARE
AGE NUMBER(2) :=&AGE;
STATUS VARCHAR2(20);
BEGIN
IF AGE>1 AND AGE<=15 THEN STATUS:=’VALID AGE’;
ELSIF AGE>15 AND AGE<=40 THEN STATUS:=’YOUNG AGE’;
IF AGE>40 AND AGE<=99 THEN STATUS:=’OLD AGE’;
ELSE STATUS:=’INVALID’;
END IF;
DOPL(STATUS);
END;
EX3:
DECLARE
VNO [Link]%TYPE;
VSAL [Link]%TYPE;
STATUS VARCHAR2(20);
BEGIN
SELECT EMPNO, SAL INTO VNO, VSAL FROM EMP WHERE EMPNO=&EMPNO;
IF VSAL>1 AND VSAL<=1000 THEN STATUS:=’MIN SAL’;
ELSIF VSAL>1000 AND VSAL<=3000 THEN STATUS:=’AVG SAL’;
ELSIF VSAL>3000 AND VSAL<=5000 THEN STATUS:=’MAX SAL’;
ELSE STATUS:=’INVALID SAL’;
END IF;
DOPL(‘EMPLOYEE SAL STATUS IS:’ ||STATUS||’,’||VSAL);
END;
EX4:
DECLARE
VNO [Link]%TYPE;
VSAL [Link]%TYPE;
STATUS VARCHAR2(20);
BEGIN
SELECT EMPNO, SAL INTO VNO, VSAL FROM EMP WHERE EMPNO=&EMPNO;
CASE WHEN VSAL>1 AND VSAL<=1000 THEN STATUS:=’MIN SAL’;
WHEN VSAL>1000 AND VSAL<=3000 THEN STATUS:=’AVG SAL’;
WHEN VSAL>3000 AND VSAL<=5000 THEN STATUS:=’MAX SAL’;
ELSE STATUS:=’INVALID SAL’;
END CASE;
DOPL(‘EMPLOYEE SAL STATUS IS:’ ||STATUS||’,’||VSAL);
END;
EX4:
DECLARE
VNO [Link]%TYPE;
VSAL [Link]%TYPE;
VJOB [Link]%TYPE;
STATUS VARCHAR2(50);
BEGIN
SELECT EMPNO, SAL,JOB INTO VNO, VSAL,VJOB FROM EMP WHERE EMPNO=&EMPNO;
CASE WHEN VSAL>1 AND VSAL<=1000 AND VJOB=’CLERCK’ THEN STATUS:=’MIN SAL’;
WHEN VSAL>1000 AND VSAL<=3000 THEN STATUS:=’AVG SAL’;
WHEN VSAL>3000 AND VSAL<=5000 THEN STATUS:=’MAX SAL’;
ELSE STATUS:=’INVALID SAL’;
END CASE;
DOPL(‘EMPLOYEE SAL STATUS IS:’ ||STATUS||’,’||VSAL||’,’||VJOB);
END;
LOOPS:
1. SIMPLE LOOP
2. WHILE LOOP
3. FOR LOOP
INSTEAD OF TRIGGERS:
1. WE WRITE TRIGGERS ONLY ON VIEWS & THIS TRIGGERS ARE ALWAYS LOW LEVEL TRIGGERS.
2. BY USING SQL IT IS NOT POSSIBLE TO PERFORM DML OPERATIONS ON COMPLEX VIEWS BUT
USING INSTEAD OF TRIGGERS IT IS POSSIBLE
BULK COLLECT & BULK BIND:
IT IS A MECHANISM OR A PROCESS WHICH IS USED TO INCREASE THE PERFORMANCE. BY
DECREASING THE INTERACTIONS BETWEEN SQL & PLSQL ENGINES.
DYNAMIC SQL/PLSQL AND EXECUTE:
1. AT RUN TIME, WHEN WE FRAME A STATEMENT, THOSE STATEMENT WILL CALL IT AS
DYNAMIC SQL STATEMENT.
2. TO EXECUTE DYNAMIC SQL STATEMENTS, WE USE A PLSQL STMT CALLED AS EXECUTE
IMMEDIATE.
3. BLOCKS DON’T ALLOW US TO EXECUTE DDL STMT’S BUT USING EXECUTE IMMEDIATE IT IS
POSSIBLE.
EX: CREATE OR REPLACE PROCEDURE P (X IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE’||X;
END;
EXEC P(‘XTAB’);
ACCESSIBLE BY (12C):
CREATE OR REPLACE FUNCTION F RETURN NUMBER
ACCESSIBLE BY (P1) IS
BEGIN
RETURN 1000;
END;
CREATE OR REPLACE PROCEDURE P1 IS
X NUMBER;
BEGIN
X:=F;
END;
RESULT CACHE:
IT IS A MEMORY AREA WHERE WE CAN STORE FUNCTION RESULT ALONG WITH PARAMETERS.
NEXT TIME WHEN WE CALL THE FUNCTION WITH SAME PARAMETERS, ENGINE SKIPS THE
FUNCTION EXECUTION AND BRING THE DATA FROM RESULT CACHE. BY DOING SO WE CAN
ENHANCE THE PERFORMANCE. IT IS USED IN PERFORMANCE TUNING.
CREATE OR REPLACE PACKAGE PACK IS
V NUMBER:=0;
END;
CREATE OR REPLACE FUNCTION F (X IN NUMBER) RETURN VARCHAR2 RESULT_CACHE IS
VNAME [Link]%TYPE;
BEGIN
PACK.V=PACK.V+1;
SELECT ENAME INTO VNAME FROM EMP WHERE EMPNO=X;
RETURN VNAME;
END;