PL/SQL PROGRAMMS
WRITE A program to display the multiplication tables.
1) declare
a number:=&a;
b number:=&b;
c number;
begin
loop
c:= a*b;
dbms_output.put_line('print '||a||'*'||b||'='|| c );
b:=b+1;
exit when b>10;
end loop;
end;
/
2) WRITE programme to %type within the variables
DECLARE
E_NO EMP.EMPNO%TYPE;
D_NO E_NO%TYPE;
BEGIN
SELECT EMPNO,DEPTNO INTO E_NO,D_NO FROM EMP where
empno=7369;
DBMS_OUTPUT.PUT_LINE(E_NO||' AND HIS NAME ' ||D_NO);
END;
/
3)Write a programm %ROWTYPE USING PL/SQL RECORDS
DECLARE
TYPE E_REC_T IS RECORD
(EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE,
JOB EMP.JOB%TYPE,
SAL EMP.SAL%TYPE);
EMP_REC E_REC_T;
BEGIN
SELECT EMPNO,ENAME,JOB,SAL INTO EMP_REC FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE( EMP_REC.EMPNO ||' ' ||
EMP_REC.ENAME ||' ' ||EMP_REC.JOB ||' ' ||
EMP_REC.SAL );
END;
/
1)A FUNCTIONALLY SIMILAR BLOCK, ADDING A DECLARATION SECTION
DECLARE
V_DATE VARCHAR2(10);
BEGIN
V_DATE := SYSDATE;
DBMS_OUTPUT.PUT_LINE(V_DATE);
END;
/
2) DIFFERENT WAYS OF INITIALIZING THE VARIABLES IN THE DECLARATION
SECTION
DECLARE
V_NO NUMBER(2) :=10;
V_NAME VARCHAR2(10) DEFAULT 'SAIBABA';
V_SAL NUMBER(5) NOT NULL := 5000;
V_JOB CONSTANT VARCHAR2(100) := 'MANAGING DIRECTOR OF ORACLE';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_NO);
DBMS_OUTPUT.PUT_LINE(V_NAME);
DBMS_OUTPUT.PUT_LINE(V_SAL);
DBMS_OUTPUT.PUT_LINE(V_JOB);
END;
3) WAP TO PRINT THE FOLLOWING
WELCOME TO PL/SQL PROGRAMMING
DECLARE
V_MESSAGE VARCHAR2(100) := 'WELCOME TO PL/SQL PROGRAMMING';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_MESSAGE);
V_MESSAGE := 'WILSHIRE SOFTWARE TECHNOLOGIES';
DBMS_OUTPUT.PUT_LINE(V_MESSAGE);
DBMS_OUTPUT.PUT_LINE('HAI ');
END;
/
4) WAP TO ACCEPT THE TEMP IN CENTIGRADE AND CONVERT IT INTO FARENHEIT
F=C*1.8+32
DECLARE
C NUMBER := &C;
F NUMBER;
BEGIN
F := C*1.8+32;
DBMS_OUTPUT.PUT_LINE('FARENHEIT=' || F);
END;
/
5) WAP TO ACCEPT A SENTANCE AND COUNT THE NO. OF CHARACTERS IN THAT
SENTANCE
DECLARE
STR VARCHAR2(100) := '&STR';
I NUMBER;
BEGIN
I := INSTR(STR,'.');
I := LENGTH(STR);
DBMS_OUTPUT.PUT_LINE('NO OF CHARACTERS=' || I);
DBMS_OUTPUT.PUT_LINE('LENGTH = ' || I);
END;
6)WAP TO ACCEPT THE TWO STRINGS AND CONCAT THE TWO STRINGS
DECLARE
STR VARCHAR2(20) := '&STR';
STR1 VARCHAR2(20) := '&STR1';
V VARCHAR2(40);
BEGIN
V := STR || ' , ' || STR1;
DBMS_OUTPUT.PUT_LINE(V);
END;
7) WAP TO ACCEPT THE MGR AND FIND HOW MANY EMPS ARE WORKING UNDER THAT
MGR
DECLARE
V_MGR NUMBER(4) := &V_MGR;
N NUMBER :=0;
BEGIN
SELECT COUNT(*)
INTO N
FROM EMP
WHERE MGR=V_MGR;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES = ' || N);
END;
8) WAP TO ACCEPT 2 NO.S FIND THE REMAINDER WHEN THE FIRST NUMBER IS
DIVIDED BY THE SECOND NUMBER
DON'T USE THE MOD FUNCTION
DECLARE
A NUMBER := &A;
B NUMBER := &B;
C NUMBER ;
M NUMBER ;
BEGIN
C := TRUNC (A/B);
M := A-C*B;
DBMS_OUTPUT.PUT_LINE('REMAINDER IS = ' || M);
END;
9) USING THE MOD FUNCTION
DECLARE
A NUMBER := &A;
B NUMBER := &B;
C NUMBER;
BEGIN
C := MOD(A,B);
DBMS_OUTPUT.PUT_LINE('REMAINDER = ' || C);
END;
/
10) WAP TO ACCEPT THE TIME IN HH,MIN FORMAT AND FIND THE TOTAL SECONDS
DECLARE
H NUMBER := &HOUR;
M NUMBER := &MINUTE;
S NUMBER(10);
BEGIN
S := (H*60*60)+(M*60);
DBMS_OUTPUT.PUT_LINE('TOTAL SECONDS = ' || S);
END;
11) GO TO STATEMENT
BEGIN
GOTO SECOND_OUTPUT;
DBMS_OUTPUT.PUT_LINE(' THIS LINE WILL NEVER EXECUTE');
<<SECOND_OUTPUT>>
DBMS_OUTPUT.PUT_LINE('WE ARE HERE!');
END;
12) GO TO STATEMENT
BEGIN
GOTO SECOND_OUTPUT;
DBMS_OUTPUT.PUT_LINE(' THIS LINE WILL NEVER EXECUTE');
<<SECOND_OUTPUT>>
DBMS_OUTPUT.PUT_LINE('WE ARE HERE!');
END;
/
13) NULL STATMENT
BEGIN
NULL;
END;
14) NULL STATEMENT WITH IF ELSE END IF LOGIC
DECLARE
V_NAME VARCHAR2(25) := '&V_NAME';
BEGIN
IF V_NAME='BOND' THEN
DBMS_OUTPUT.PUT_LINE('007');
ELSE
NULL;
END IF;
END;
/
15) LOOPS
DECLARE
V_NAME VARCHAR2(10) := 'SAI';
V_C NUMBER(2) :=0;
BEGIN
LOOP
V_C := V_C+1;
--DBMS_OUTPUT.PUT_LINE(V_NAME);
EXIT WHEN V_C>10;
--DBMS_OUTPUT.PUT_LINE(V_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_NAME);
END;
16) WHILE LOOP
WAP TO PRINT THE NUMBERS FROM 1 TO 10
DECLARE
v_N NUMBER(3):= 1;
v_V VARCHAR2(100) ;
BEGIN
WHILE v_N <=10 LOOP
V_v :=v_V|| ' ' ||v_N;
v_N :=v_N+1;
DBMS_OUTPUT.PUT_LINE(v_V);
END LOOP;
END;
17) FOR LOOP EXAMPLE
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('HELLO');
END LOOP;
END;
/
18) FOR LOOP EXAMPLE
DECLARE
V_L NUMBER(2) :=1;
V_H NUMBER(2) :=10;
BEGIN
FOR I IN V_L..V_H LOOP
DBMS_OUTPUT.PUT_LINE('HELLO');
END LOOP;
END;
/
19) FOR LOOP EXAMPLE WITH BOND VALUE CHANGING
DECLARE
V_L NUMBER(2) :=1;
V_H NUMBER(2) :=10;
BEGIN
FOR I IN V_L..V_H LOOP
DBMS_OUTPUT.PUT_LINE('HELLO');
V_L :=20;
V_H :=40;
END LOOP;
END;
20) FOR LOOP PRINTING THE VALUES OF THE COUNTER
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(I || 'HELLO');
END LOOP;
END;
21) %TYPE
SYNTAX:
IDENTIFIER TABLE.COLUMN_NAME%TYPE;
DECLARE
V_SAL EMP.SAL%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT SAL,ENAME
INTO V_SAL,V_ENAME
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_ENAME || ' AND HIS SALARY IS ' || V_SAL);
END;
/
22) WITH OUT USING %TYPE
DECLARE
V_SAL NUMBER(2);
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
/
23) WITH OUT USING %TYPE
DECLARE
V_SAL VARCHAR2(10);
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
24) %TYPE BASED ON THE PREVIOUSLY DEFINED VARIABLE
DECLARE
V_ENAME EMP.ENAME%TYPE;
V_JOB V_ENAME%TYPE;
BEGIN
SELECT ENAME,JOB
INTO V_ENAME,V_JOB
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;
25) SYNTAXT FOR %ROWTYPE
DECLARE
IDENTFIER REFERENCE%ROWTYPE;
WHERE
IDENTIFIER IS THE NAME CHOOSEN FOR A RECORD AS A WHOLE
REFERENCE IS THE NAME OF THE TABLE,VIEW, CURSOR OR CURSOR VARIABLE ON
WHICH
THE RECORD IS BASED.
TO REFERENCE AN INDIVIDUAL FIELD, WE USE DOT NOTATION
FOR EXAMPLE:
RECORD_NAME.FIELD_NAME;
FOR EXAMPLE, YOU REFERENCE THE ENAME FIELD IN THE EMP_RECORD RECORD AS
FOLLOWS
EMP_RECORD.ENAME;
YOU CAN THEN ASSIGN A VALUE TO THE RECORD FIELD AS FOLLOWS;
EMP_RECORD.ENAME:='BOND';
26) %ROWTYPE ATTRIBUTE
--DECLARE A VARIABLE TO STORE THE INFORMATION ABOUT A DEPARTMENT FROM
THE
--DEPT TABLE
DEPT_RECORD DEPT%ROWTYPE;
--DECLARE A VARIABLE TO STORE THE INFORMATION ABOUT AN EMPLOYEE FROM THE
--EMP TABLE
EMP_RECORD EMP%ROWTYPE;
27) ADVANTAGES OF USING %ROWTYPE
THE NUMBER AND DATA TYPES OF THE UNDERLYING COLUMNS NEED NOT BE KNOWN.
THE NUMBER AND DATA TYPES OF THE UNDERLYING DATABASE COLUMNS MAY CHANGE
AT RUNTIME.
THE ATTRIBUTE IS USEFULL WHEN RETRIEVING A ROW WITH THE
SELECT * STATMENT.
28) %ROWTYPE WITH DEPT TABLE
DECLARE
DEPT_RECORD DEPT%ROWTYPE;
BEGIN
SELECT * INTO DEPT_RECORD
FROM DEPT
WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(DEPT_RECORD.DNAME);
DBMS_OUTPUT.PUT_LINE(DEPT_RECORD.LOC);
DBMS_OUTPUT.PUT_LINE(DEPT_RECORD.DEPTNO);
END;
/
29) %ROWTYPE GETTING THE VALUES FROM OTHER TABLE CONCEPT
DECLARE
EMP_RECORD EMP%ROWTYPE;
BEGIN
SELECT *
INTO EMP_RECORD
FROM EMP1
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
END;
/
30) CREATING A PL/SQL RECORD
SYNTAX:
TYPE type_name IS RECORD
(field_declaration ...........);
identifier type_name;
WHERE field_declration is
field_name (field_type | variable%type | table.column%type | table
%rowtype )
where
TYPE_NAME IS THE NAME OF THE RECORD TYPE
FIELD_NAME IS THE NAME OF A FIELD WITHIN THE RECORD
FIELD_TYPE IS THE DATATYPE OF THE FIELD
NOTE:- HERE WE CAN'T USE REF CURSOR
31) VARIABLE%TYPE EXAMPLE
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
(ENAME VARCHAR2(10),
JOB ENAME%TYPE,
SAL NUMBER(7,2),
EMPNO SAL%TYPE);
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT ENAME,JOB,SAL,EMPNO
INTO EMP_RECORD
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO);
END;
32) PL/SQL RECORD WITH %TYPE
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
(EMPNO EMP.EMPNO%TYPE,
ENAME EMP.ENAME%TYPE);
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT EMPNO,ENAME
INTO EMP_RECORD
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(emp_record.ENAME);
END;
/
33) PL/SQL RECORD WITH %TYPE
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
( SAL EMP.SAL%TYPE,
EMPNO SAL%TYPE);
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT SAL,EMPNO
INTO EMP_RECORD
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.SAL);
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO);
END;
34) CREATING PL/SQL RECORD WITH %ROWTYPE
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
(E1 EMP%ROWTYPE);
EMP_RECORD EMP_RECORD_TYPE;
BEGIN
SELECT * INTO EMP_RECORD
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
END;
35) PL/SQL COLLECTIONS
INDEX BY TABLES (PL/SQL TABLES)
1) ARE COMPOSED OF TWO COMPONENTS
A) PRIMARY KEY OF DATA TYPE BINARY_INTEGER.
B) COLUMN OF SCALAR OR RECORD DATA TYPE
NOTE:-
1) CAN INCREASE IN SIZE DYNAMICALLY BECAUSE THEY ARE UNCONSTRAINED
2) OBJECTS OF THE TABLE TYPE ARE CALLED INDEX BY TABLES
3) THEY ARE MODELED AS (BUT NOT THE SAME AS) DATABASE TABLES
4) INDEX BY TABLES USE A PRIMARY KEY TO PROVIDE YOU WITH ARRAY-LIKE
ACCESS TO
ROWS
5) SIMPLY WE CAN SAY INDEX BY TABLE IS JUST LIKE A ARRAY
36) - SYNTAX:
TYPE TYPE_NAME IS TABLE OF
COLUMN_TYPE | VARIABLE%TYPE | TABLE.COLUMN%TYPE | TABLE%ROWTYPE
INDEX BY BINARY_INTEGER;
IDENTIFIER TYPE_NAME;
INDEX BY TABLE STRUCURE
UNIQUE IDENTIFIER COLUMN
1 JONES
2 SMITH
3 MADURO
BINARY_INTEGER SCALAR
NOTE:-
1) THE COLUMNS CANNOT BE NAMED
2) WE CANNOT INITIALIZE AN INDEX BY TABLE IN ITS DECLARATION.
3) AN INDEX BY TABLE IS NOT POPULATED AT THE TIME OF THE DECLARATION
4) IT CONTAINS NO KEYS OR NO VALUES
5) AN EXPLICIT EXECUTABLE STATMENT IS REQUIRED TO INITIALIZE(POPULATE)
THE
INDEX BY TABLE
37) --EXAMPLE OF INDEX BY TABLE OF RECORDS
DECLARE
TYPE EMP_TABLE_TYPE IS TABLE OF
EMPLOYEES%ROWTYPE
INDEX BY BINARY_INTEGER;
MY_EMP_TABLE EMP_TABLE_TYPE;
V_COUNT NUMBER(2) :=4;
BEGIN
FOR I IN 1..V_COUNT LOOP
SELECT * INTO
MY_EMP_TABLE(I)
FROM EMPLOYEES
WHERE EMPNO=I;
dbms_output.put_line(my_emp_table(i).ename);
END LOOP;
--FOR I IN MY_EMP_TABLE.FIRST..MY_EMP_TABLE.LAST LOOP
--DBMS_OUTPUT.PUT_LINE(MY_EMP_TABLE(I).ENAME);
--DBMS_OUTPUT.PUT_LINE(MY_EMP_TABLE.COUNT);
--DBMS_OUTPUT.PUT_LINE(MY_EMP_TABLE.NEXT(1);
--DBMS_OUTPUT.PUT_LINE(MY_EMP_TABLE.PRIOR(3);
--END LOOP;
IF MY_EMP_TABLE.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('FIRST RECORD IS EXISTING');
END IF;
END;
38) NOTE:- IN SCALAR IT'S NOT POSSIBLE TO DISPLAY THE VALUE
-- ONLY WE CAN MAKE A DECISION BY USING EXISTS METHOD
DECLARE
TYPE ENAME_TABLE_TYPE IS TABLE OF
EMPLOYEES.ENAME%TYPE
INDEX BY BINARY_INTEGER;
ENAME_TABLE ENAME_TABLE_TYPE;
BEGIN
FOR I IN 1..10 LOOP
SELECT ENAME INTO ENAME_TABLE(I) FROM EMPLOYEES
WHERE EMPNO=I;
--dbms_output.put_line(ename_table(i).ename);
END LOOP;
IF ENAME_TABLE.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('IT EXISTS');
END IF;
END;
39) NOTE:- IN SCALAR IT'S NOT POSSIBLE TO DISPLAY THE VALUE
-- ONLY WE CAN MAKE A DECISION BY USING EXISTS METHOD
DECLARE
TYPE ENAME_TABLE_TYPE IS TABLE OF
EMPLOYEES.ENAME%TYPE
INDEX BY BINARY_INTEGER;
ENAME_TABLE ENAME_TABLE_TYPE;
BEGIN
FOR I IN 1..10 LOOP
SELECT ENAME INTO ENAME_TABLE(I) FROM EMPLOYEES
WHERE EMPNO=I;
--dbms_output.put_line(ename_table(i).ename);
END LOOP;
IF ENAME_TABLE.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('IT EXISTS');
END IF;
END;
/
40) INDEX BY TABLE METHODS
THE FOLLOWING METHODS MAKE INDEX BY TABLES EASIER TO USE
EXISTS
COUNT
NEXT
TRIM
FIRST AND LAST
DELETE
PRIOR
A INDEX BY TABLE METHOD IS A BUILT-IN PROCEDURE OR FUNCTION THAT
OPERATES ON TABLES AND IS CALLED BY USING DOT NOTATION.
PROCEUDRE:- IS USED TO PERFORM AN ACTION
FUNCTION :- IS USED TO RETURN A VALUE
SYNTAX:-
TABLE_NAME.METHODS_NAME(PARAMETERS)
EXISTS(N) :- RETURNS TRUE IF THE nTH ELEMENT IN A PL/SQL TABLE EXISTS
COUNT:- RETURNS THE NUMBER OF ELEMENTS THAT A PL/SQL TABLE
CURRENTLY CONTAINS
FIRST LAST:- RETURNS THE FIRST AND LAST(SMALLEST AND LARGEST) INDEX
NUMBERS
IN A PL/SQL TABLE .
RETURNS NULL IF THE PL/SQL TABLE IS EMPTY
PRIOR(N):- RETURNS THE INDEX NUMBER THAT PRECEDES INDEX N IN A PL/SQL
TABLE
NEXT(N):- RETURNS THE INDEX NUMBER THAT SUCCEEDS INDEX N IN A PL/SQL
TABLE
TRIM:- TRIM REMOVES ONE ELEMENT FROM THE END OF A PL/SAL TABLE
TRIM(N) REMOVES N ELEMENTS FROM THE END OF A PL/SQL TABLE
DELETE:- DELETE REMOVES ALL ELEMENTS FROM A PL/SQL TABLE
DELETE(N) REMOVES THE NTH ELEMENT FROM A PL/SQL TABLE
DELETE(M,N) REMOVES ALL ELEMENTS IN THE RANGE M....N FROM A
PL/SQL
TABLE
41) CURSORS
EVERY SQL STATEMENT EXECUTED BY THE ORACLE SERVER HAS AN INDIVIDUAL
CURSOR ASSOCIATED WITH IT
CLASSIFICATION OF CURSORS
1) IMPLICIT CURSORS
2) EXPLICIT CURSORS
IMPLICIT CURSORS:- DECLARED FOR ALL DML, PL/SQL SELECT STATEMENTS
EXPLICIT CURSORS:- DECLARED AND NAMED BY THE PROGRAMMER
NOTE:- THE ORACLE SERVER USES WORK AREAS, CALLED PRIVATE SQL AREAS,
TO EXECUTE SQL STATEMENTS AND TO STORE PROCESSING INFORMATION
YOU CAN USE PL/SQL CURSORS TO NAME A PRIVATE SQL AREA AND
ACCESS ITS STORED INFORMATION
IMPLICIT CURSORS:- IMPLICIT CURSORS ARE DECLARED BY THE PL/SQL
IMPLICITYLY
FOR ALL DML AND PL/SQL SELECT STATEMENTS, INCLUDING
QUERIES
THAT RETURN ONLY ONE ROW.
EXPLICIT CURSORS:- FOR QUERIES THAT RETURN MORE THAN ONE ROW, EXPLICIT
CURSORS
ARE DECLARED AND NAMED BY THE PROGRAMMER AND MANIPULATED
THROUGH SPECIFIC STATEMENTS IN THE BLOCK'S EXECUTABLE
ACTIONS
CONTROLLING EXPLICIT CURSORS STEPS
1) OPEN THE CURSOR
2) FETCH A ROW
3) CLOSE THE CURSOR
EXPLICIT CURSOR ATTRIBUTES:-
ARE USED TO OBTAIN STATUS INFORMATION ABOUT A CURSOR
ATTRIBUTES:-
%ISOPEN BOOLEAN
%NOTFOUND BOOLEAN
%FOUND BOOLEAN
%ROWCOUNT NUMBER
%ISOPEN:- EVALUATES TO TRUE IF THE CURSOR IS OPEN
%NOTFOUND:- EVALUATES TO TRUE IF THE MOST RECENT FETCH DOES NOT RETURN A
ROW
%FOUND:- EVALUATES TO TRUE IF THE MOST RECENT FETCH RETURNS A ROW;
COMPLIMENT OF %NOTFOUND
%ROWCOUNT:- EVALUATES TO THE TOTAL NUMBER OF ROWS RETURNED SO FAR
NOTE:- WE CANNOT REFERENCE CURSOR ATTRIBUTES DIRECTLY IN A SQL STATEMENT
------------------------------------------------------------------------
----
DECLARING THE CURSOR
SYNTAX: CURSOR CURSOR_NAME IS SELECT STATEMENT;
CURSOR_NAME IS A PL/SQL IDENTIFIER
SELECT STATEMENT:- IT IS A STATEMENT WITHOUT AN INTO CLAUSE
NOTE:- IF PROCESSING ROWS IN A SPECIFIC SEQUENCE IS REQUIRED, USE THE
ORDER BY
CLAUSE IN THE QUERY
EXPLICIT CURSOR FUNCTIONS
1) USE EXPLICIT CURSORS TO INDIVIDUALLY PROCESS EACH ROW RETURNED BY A
MULTIPLE-ROW SELECT STATEMENT.
2) THE SET OF ROWS RETURNED BY A MULTIPLE-ROW QUERY IS KNOWN AS ACTIVE
SET.
3) ITS SIZE IS EQUAL TO THE NUMBER OF ROW THAT MEET YOUR CRITERIA.
CONTROLLING EXPLICIT CURSORS
1) OPEN THE CURSOR:-
THE OPEN STATEMENT EXECUTES THE QUERY ASSOCIATED WITH THE CURSOR,
IDENTIFIES
THE RESULT SET(ACTIVE SET) AND POSITIONS THE CURSOR BEFORE THE FIRST ROW
2) FETCH A ROW:-
THE FETCH STATEMENT RETRIEVES THE CURRENT ROW AND ADVANCES THE CURSOR TO
THE
NEXT ROW UNTILE EITHER THERE ARE NO MORE ROWS OR UNTILL THE SPECIFIED
CONDITION IS MET
3) CLOSE THE CURSOR:-
CLOSE THE CURSOR WHEN THE LAST ROWS HAS BEEN PROCESSED.
THE CLOSE STATEMENT DISABLES THE CURSOR.
NOTE:- IF THE QUERY RETURNS NO ROWS WHEN THE CURSOR IS OPENED, PL/SQL
DOES NOT
RAISE AN EXCEPTION(ERROR).
FETCHING DATE FROM THE CURSOR
SYNTAX:
LOOP
FETCH STATEMENT;
EXIT CONDITION;
END LOOP;
42) --EXAMPLES ON CURSOR
DECLARE
CURSOR EMP_CURSOR IS SELECT EMPNO,ENAME FROM EMP;
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO V_EMPNO,V_ENAME;
EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMPNO||' NAME IS ' || V_ENAME);
END LOOP;
CLOSE EMP_CURSOR;
END;
/
43) -EXAMPLE ON %ROWCOUNT ATTRIBUTE
DECLARE
CURSOR EMP_CURSOR IS SELECT EMPNO,ENAME FROM EMP;
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO V_EMPNO,V_ENAME;
--DBMS_OUTPUT.PUT_LINE(V_ENAME);
EXIT WHEN EMP_CURSOR%ROWCOUNT>5;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE EMP_CURSOR;
END;
44) -%FOUND EXAMPLE
DECLARE
CURSOR EMP_CURSOR IS SELECT ENAME FROM EMP;
V_ENAME EMP.ENAME%TYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO V_ENAME;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
EXIT WHEN EMP_CURSOR%FOUND;
END LOOP;
CLOSE EMP_CURSOR;
END;
/
45) %IS OPEN ATTRIBUTE
DECLARE
CURSOR EMP_CURSOR IS SELECT EMPNO FROM EMP;
V_EMPNO EMP.EMPNO%TYPE;
BEGIN
IF NOT EMP_CURSOR%ISOPEN THEN
OPEN EMP_CURSOR;
END IF;
LOOP
FETCH EMP_CURSOR INTO V_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_EMPNO);
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;
END;
/
46) CURSORS AND RECORDS
DECLARE
CURSOR EMP_CURSOR IS SELECT * FROM EMP;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;
CLOSE EMP_CURSOR;
END;
/
SYNTAX:-
FOR RECORD_NAME IN CURSOR_NAME LOOP
STATEMENT1;
STATEMENT2;
END LOOP;
NOTE:-
1) THE CURSOR FOR LOOP IS A SHORTCUT TO PROCESS EXPLICIT CURSORS
2) IMPLICIT OPEN,FETCH,EXIT AND CLOSE OCCUR.
3) THE RECORD IS IMPLICITLY DECLARED
A CURSOR FOR LOOP PROCESS ROWS IN AN EXPLICIT CURSOR.
IT IS A SHORTCUT BECAUSE THE CURSOR IS OPENED ,
ROWS ARE FETCHED ONCE FOR EACH ITERATION IN THE LOOP,
THE LOOP EXITS WHEN THE LAST ROWS IS PROCESSED,
AND THE CURSOR IS CLOSED AUTOMATICALLY.
THE LOOP IS TERMINATED AUTOMATICALLY AT THE END OF THE ITERATION
NOTE:- DO NOT USE THE CURSOR FOR LOOP
WHEN THE CURSOR OPERATION MUST BE HANDLED EXPLICITLY
48) CURSOR FOR LOOP EXAMPLE
DECLARE
CURSOR EMP_CURSOR IS SELECT ENAME,DEPTNO FROM EMP;
BEGIN
FOR EMP_RECORD IN EMP_CURSOR LOOP
IF EMP_RECORD.DEPTNO=10 THEN
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
END IF;
END LOOP;
END;
/
49) CURSOR FOR LOOPS USING SUBQUERIES
-- NOTE:- NO NEED TO DECLARE THE CURSOR
BEGIN
FOR EMP_RECORD IN (SELECT * FROM EMP) LOOP
IF EMP_RECORD.DEPTNO=10 THEN
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
END IF;
END LOOP;
END;
/
50) ADVANCED EXPLICIT CURSOR CONCEPTS
1) CURSORS WITH PARAMETERS
2) THE FOR UPDATE CLAUSE
3) THE WHERE CURRENT OF CLAUSE
A) CURSORS WITH PARAMETERS
SYNTAX:-
CURSOR CURSOR_NAME (PARAMETER_NAME DATATYPE, PARAMETER_NAME1
DATATYPE.....)
IS
SELECT STATEMENT;
PASS PARAMETER VALUES TO A CURSOR WHEN THE CURSOR IS OPENED AND THE
QUERY
IS EXECUTED
OPEN AN EXPLICIT CURSOR SEVERAL TIMES WITH A DIFFERENT ACTIVE SET EACH
TIME
EG:- OPEN CURSOR_NAME(PARAMETER_VALUE....);
NOTEPOINT:-
IT IS POSSIBLE TO TERMINATE THE PL/SQL BLOCK WITHOUT CLOSING THE
CURSORS,
BUT WE SHOULD MAKE IT A HABIT TO CLOSE ANY CURSOR THAT YOU DECLARE
EXPLICITYLY TO FREE UP RESOURCES
THERE IS A MAXIMUM LIMIT TO THE NUMBER OF OPEN CURSORS PER USER,
WHICH IS DETERMINED BY THE OPEN_CURSORS PARAMETER IN THE DATABASE
PARAMETER FILE.
FOR EG:- OPEN_CURSORS=50 BY DEFAULT
51) SIMPLE CURSOR EXAMPLE WITH OUT PASSING ANY PARAMETER
-- NOTE POINT:- ALWAYS USE CURSORNAME IN FETCH STATEMENT
--FETCH STATEMENT IS ALWAYS USED WITH CURSORS ONLY
DECLARE
CURSOR EMP_CURSOR IS
SELECT * FROM EMP
WHERE DEPTNO=10;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;
CLOSE EMP_CURSOR;
END;
/
52) CURSOR WITH PARAMETERS
-- FOR PARAMETER DATA TYPE WE SHOULD NOT SPECIFY THE SIZE
-- I.E. THE SIZE SHOULD NOT BE CONSTRAINED(NO RESTRICTION ON SIZE)
DECLARE
CURSOR EMP_CURSOR(P_DEPTNO NUMBER) IS
SELECT * FROM EMP
WHERE DEPTNO=P_DEPTNO;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
OPEN EMP_CURSOR(10);
LOOP
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE('THESE EMPLOYEES ARE WORKING IN DEPT10');
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
DBMS_OUTPUT.PUT_LINE('----------------------------');
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;
CLOSE EMP_CURSOR;
OPEN EMP_CURSOR(20);
LOOP
FETCH EMP_CURSOR INTO EMP_RECORD;
DBMS_OUTPUT.PUT_LINE('THESE EMPLOYEES ARE WORKING IN DEPT20');
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;
CLOSE EMP_CURSOR;
END;
/
53) THE FOR UPDATE CLAUSE
SYNTAX:-
SELECT ........
FROM
FOR UPDATE [OF COLUMN_REFERENCE] [NOWAIT];
1) USE EXPLICIT LOCKING TO DENY ACCESS FOR THE DURATION OF A
TRANSACTION.
2) LOCK THE ROWS BEFORE THE UPDATE OR DELETE.
COLUMN_REFERENCE:-
1) IS A COLUMN IN THE TABLE AGAINST THE QUERY IS PROCESSED
2) A LIST OF COLUMNS MAY ALSO BE USED
NOWAIT:- RETURNS AN ORACLE ERROR IF THE ROWS ARE LOCKED BY ANOTHER
SESSION
NOTE:- FOR UPDATE CLAUSE IS THE LAST CLAUSE IN A SELECT STATEMENT, EVEN
AFTER THE ORDER BY, IF ONE EXISTS.
FOR UPDATE OF COL_NAMES(S) LOCKS ROWS ONLY IN TABLES THAT CONTAIN THE
COL_NAMES(S)
NOWAIT:- IT TELLS ORACLE NOT TO WAIT IF REQUESTED ROWS HAVE BEEN LOCKED
BY
ANOTHER USER
CONTROL IS IMMEDIATELY RETURNED TO YOUR PROGRAMM SO THAT IT CAN DO OTHER
WORK
BEFORE TRYING AGAIN TO ACQUIRE THE LOCK.
IF YOU OMIT THE NOWAIT KEYWORD, ORACLE WAITS UNTIL THE ROWS ARE
AVAILABLE
NOTE:- IF THE ORACLE SERVER CANNOT ACQUIRE THE LOCKS ON THE ROWS IT
NEEDS IN A
SELECT FOR UPDATE, IT WAITS INDEFINITELY.
54) THE WHERE CURRENT OF CLAUSE
SYNTAX :-
WHERE CURRENT OF CURSOR;
USE CURSORS TO UPDATE OR DELETE THE CURRENT ROW.
INCLUDE THE FOR UPDATE CLAUSE IN THE CURSOR QUERY TO LOCK THE ROWS FIRST
USE THE WHERE CURRENT OF CLAUSE TO REFERENCE THE CURRENT ROW FROM AN
EXPLICIT CURSOR
CURSOR:- IS THE NAME OF A DECLARE CURSOR (THE CURSOR MUST HAVE BEEN
DECLARED
WITH THE FOR UPDATE CLAUSE)
NOTE:- THE WHERE CURRENT OF CLAUSE IS USED TO UPDATE OR DELETE THE
CURRENT ROW
BEING ADDRESSED WITHOUT THE NEED TO EXPLICITLY REFERENCE THE ROWID
55) EXAMPLE
DECLARE
CURSOR SAL_CURSOR IS
SELECT SAL FROM EMP
WHERE DEPTNO=10
FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR EMP_RECORD IN SAL_CURSOR LOOP
IF EMP_RECORD.SAL<50000 OR EMP_RECORD.SAL IS NULL THEN
UPDATE EMP
SET SAL=9999
WHERE CURRENT OF SAL_CURSOR;
--COMMIT;
END IF;
END LOOP;
END;
HANDLING EXCEPTIONS
AN EXCEPTION IS AN IDENTIFIER IN PL/SQL THAT IS RAISED DURING THE
EXECUTION
OF A BLOCK THAT TERMINATES ITS MAIN BODY OF ACTIONS
A BLOCK ALWAYS TERMINATES WHEN PL/SQL RAISES AN EXCEPTION , BUT YOU CAN
SPECIFY AN EXCEPTION HANDLER TO PERFORM FINAL ACTIONS
HOW IT IS RAISED?
AN ORACLE ERROR OCCURS
YOU RAISE IT EXPLICITLY
HOW DO YOU HANDLE IT?
TRAP IT WITH A HANDLER
PROPAGATE IT TO THE CALLING ENVIRONMENT
EXCEPTION TYPES
PREDEFINED ORACLE SERVER IMPLICITLY RAISED
NONPREDEFINED ORACLE SERVER IMPLICITLY RAISED
USER-DEFINED EXPLICITLY RAISED
TRAPPING EXCEPTIONS
SYNTAX:
EXCEPTION
WHEN EXCEPTION1 THEN
STATEMENT1;
WHEN EXCEPTION2 THEN
STATMENT1;
WHEN OTHERS THEN
STATEMENT1;
EXCEPTION:- IS THE STANDARD NAME OF A PREDEFINED EXCEPTION OR
THE NAME OF A USER DEFINED EXCEPTION DECLARED WITH IN THE
DECLARATIVE SECTION
STATEMENT:- IS ONE OR MORE PL/SQL OR SQL STATEMENTS
OTHERS :- IS AN OPTIONAL EXCEPTIONAL-HANDLING CLAUSE THAT TRAPS
UNSPECIFIED
EXCEPTIONS
NOTE:- THE OTHERS HANDLER TRAPS ALL EXCEPTIONS NOT ALREADY TRAPPED.
SOME ORACLE TOOLS HAVE THEIR OWN PREDEFINED EXCEPTIONS THAT YOU
CAN
RAISE TO CAUSE EVENTS IN THE APPLICATION.
THE OTHERS HANDLER CAN ALSO TRAP THESE EXCEPTION
TRAPPING EXCEPTIONS GUIDELINES
THE EXCEPTION KEYWORD STARTS EXCEPTION-HANDLING SECTION
SEVERAL EXCEPTION HANDLERS ARE ALLOWED
ONLY ONE HANDLER IS PROCESSED BEFORE LEAVING THE BLOCK
WHEN OTHERS IS THE LAST CLAUSE
YOU CAN HAVE ONLY ONE OTHERS CLAUSE
EXCEPTIONS CANNOT APPEAR IN ASSIGNMENT STATEMENTS OR SQL STATEMENTS
SAMPLE PREDEFINED EXCEPTIONS
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
NO_DATA_FOUND :- SINGLE ROW RETURNED NO DATA
TOO_MANY_ROWS :- SINGLE-ROW SELECT RETURNED MORE THAN ONE ROW
INVALID_CUROSR :- ILLEGAL CURSOR OPERATION OCCURED
ZERO_DIVIDE :- ATTEMPTED TO DIVIDE BY ZERO
DUP_VAL_ON_INDEX ATTEMPTED TO INSERT A DUPLICATE VALUE
56) NO_DATA_FOUND EXCEPTION
DECLARE
V_NAME VARCHAR2(25);
BEGIN
SELECT ENAME
INTO V_NAME
FROM EMP
WHERE EMPNO=2;
END;
/
57) NO_DATA_FOUND EXCEPTION HANDLING
DECLARE
V_NAME VARCHAR2(20);
V_NO NUMBER(10);
BEGIN
SELECT ENAME
INTO V_NAME
FROM EMP
WHERE EMPNO=2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--select sal into v_no
--from emp
--where empno=7369;
v_no := 10000;
DBMS_OUTPUT.PUT_LINE(V_NO);
END;
58) TOO_MANY_ROWS
DECLARE
V_ENAME VARCHAR2(10);
BEGIN
SELECT ENAME INTO V_ENAME
FROM EMP;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('A VARIABLE CAN HOLD ONLY SINGLE VALUE AT A TIME');
END;
/
59) ZERO_DIVIDE
DECLARE
V_NO NUMBER(5) := 10;
V_NO1 NUMBER(5) :=0;
V_RES NUMBER(5);
BEGIN
V_RES := (V_NO/V_NO1);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('UNDEFINED VALUE');
END;
60) INVALID CURSOR
DECLARE
CURSOR EMP_CURSOR IS SELECT * FROM EMP;
EMP_RECORD EMP_CURSOR%ROWTYPE;
BEGIN
--LOOP
--FETCH EMP_CURSOR INTO EMP_RECORD;
--EXIT WHEN EMP_CURSOR%NOTFOUND;
--END LOOP;
CLOSE EMP_CURSOR;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE('NOT OPENED CURSOR TILL YET');
END;
61) DUP_VAL_ON_INDEX EXCEPTION
BEGIN
INSERT INTO A
VALUES
(1);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('PRIMARY KEY VIOLATION');
END;
62) NON PREDEFINED ERROR
DECLARE
V_SAL NUMBER(2);
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
63) TRAP FOR ORACLE SERVER ERROR NUMBER -6502, AN VALUE TOO LARGE
DECLARE
V_SAL NUMBER(2);
E_HIGH EXCEPTION;
PRAGMA EXCEPTION_INIT(E_HIGH,-6502);
BEGIN
SELECT SAL
INTO V_SAL
FROM EMP
WHERE EMPNO=7369;
EXCEPTION
WHEN E_HIGH THEN
DBMS_OUTPUT.PUT_LINE('VALUE TOO HIGH');
END;
/
64) TRAP FOR ORACLE SERVER ERROR NUMBER -2292 , AN INTEGRITY CONSTRAINT
-- VIOLATION
FUNCTIONS FOR TRAPPING EXCEPTION
SQLCODE:- RETURNS THE NUMERIC VALUE FOR THE ERROR CODE
SQL CODE VALUE :-
0 NO EXCEPTION ENCOUNTERED
1 USER-DEFINED EXCEPTION
+100 NO_DATA_FOUND EXCEPTION
NEGATIVE NUMBER ANOTHER ORACLE SERVER ERROR NUMBER
SQLERRM :- RETURNS THE MESSAGE ASSOCIATED WITH THE ERROR NUMBER
65) FUNCTIONS FOR TRAPPING EXCEPTIONS
DECLARE
V_ERROR_NUMBER NUMBER;
V_ERROR_MESSAGE VARCHAR2(255);
V_SAL NUMBER(9);
v_high exception;
pragma exception_init(v_high,-6502);
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO=7369;
v_error_number := sqlcode;
v_error_message := sqlerrm;
dbms_output.put_line(v_error_number);
EXCEPTION
--WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.PUT_LINE('VALUE NOT EXISTING');
WHEN v_high THEN
dbms_output.put_line('value too large');
when others then
V_ERROR_NUMBER := SQLCODE;
V_ERROR_MESSAGE := SQLERRM;
DBMS_OUTPUT.PUT_LINE(V_ERROR_NUMBER);
DBMS_OUTPUT.PUT_LINE(V_ERROR_MESSAGE);
END;
