PL/SQL CONCEPTS

Oracle added a procedural programming language known, as PL/SQL (Procedural Language/SQL). PL/SQL is a third generation language and contains the standard programming constructs. Anonymous Block: An Unnamed block which will not store any where in the database is know as Anonymous block. Block Structure: PL/SQL programs are divided up into structures known as blocks, with each block containing the PL/SQL and SQL statements. The syntax for the structure is given below. Syntax: [DECLARE Variable declaration Begin Executable_statements [Exception Exception_handling_statements] End; Ex:  SQL> SET SERVEROUT ON; SQL> Declare v_1 number; v_2 number; v_3 number; Begin v_1:=&v_1; v_2:=&v_2; v_3:=&v_3; if v_1 < v_2 and v_1 < v_3 then dbms_output.put_line('1st is small'); elsif v_2 < v_3 then dbms_output.put_line('2nd is small'); else dbms_output.put_line('3rd is small'); end if; Exception When zero_divide then DBMS_OUTPUT.PUT_LINE(‘Division by ZERO’); End; O/p: It will ask for the three input values and will display the smallest number in that. Difference between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values so we pass into variables. Here in SQL there is buffer to hold the data temporarily. Ex: SQL> Select ename from emp where is empno = 7788; SQL> Declare V_name varchar2(10); Begin Select ename into v_name from emp where empno = 7788; End; Cursors: Cursor is a private SQL area provided by the oracle engine. It is mainly used to retrieve the data from more than one column. There are two types of cursors they are given below.
          Implicit Cursors
          Explicit Cursors Implicit Cursors: Implicit cursor raises implicitly when we use INSERT, UPDATE, DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed by the PL/SQL engine. Ex: SQL> declare v_edata emp%ROWTYPE; begin select * into v_edata from emp where empno=7788; if sql%notfound then  dbms_output.put_line('Record Not Found'); else dbms_output.put_line('Record Found'); end if; end; Processing Explicit Cursors: The four PL/SQL steps necessary for explicit cursor processing are as follows.
       Declare the cursor.
        Open the cursor for a Query.
        Fetch the results into PL/SQL variables.
        Close the Cursor. Declaration of Cursor: The declaration of the cursor is associated with the select statement. The syntax for the cursor is given below. Syntax: CURSOR cursor_name IS select_statement; Ex: -- To print the Cumulative SAL of the Employee table order by empno SQL>declare v_sal number; v_dno number; l_count number; cursor test_cursor is select * from scott.emp order by empno; begin v_sal:=0; v_dno:=0; for i in test_cursor loop if(i.deptno!=v_dno) then v_sal:= 0; end if; v_sal:=v_sal+i.sal; dbms_output.put_line('Ename: ' || i.ename||' Sal: '|| i.sal || '_ Cum_sal: '|| v_sal || 'Deptno: ' || i.deptno); l_count:=test_cursor%rowcount; end loop; dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor'); end test_cursor; Cursor Attributes: There are four cursors available in PL/SQL that can be applied to cursors. Those are given below.
        %FOUND
        %NOTFOUND
        %ISOPEN
         %ROWCOUNT %FOUND:  %FOUND is a Boolean attribute. It returns TRUE if the previous FETCH returned a row and FALSE if it didn’t. %NOTFOUND:  %NOTFOUND is also a Boolean attribute. It returns FALSE if the previous FETCH returned a row and TRUE if it didn’t. It behaves opposite to the %FOUND. %ISOPEN:  %ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is open other wise it will return FALSE. %ROWCOUNT: %ROWCOUNT is a Numeric attribute. It returns number of rows returned by the cursor so far. Declaring, Opening Fetching and Closing the Cursor: Ex: SQL> declare v_eno number; v_ename varchar2(20); l_count number; cursor razia_cursor is select empno,ename from scott.emp; begin open razia_cursor; loop fetch razia_cursor into v_eno, v_ename; exit when razia_cursor%notfound; l_count:=razia_cursor%rowcount; dbms_output.put_line('Ename: ' || v_ename||' ENUM: '|| v_eno); end loop; close razia_cursor; dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor'); end; Ex: SQL> -- Example to print the odd rows in the Table EMP declare n number; l_count number; cursor razia_cursor is select empno,ename,rownum from scott.emp; begin for i in razia_cursor loop n:=mod(i.rownum,2); if (n>0) then dbms_output.put_line('Empno: ' || i.empno||' NAME: '|| i.ename||' ROWNUM: '|| i.Rownum); end if; l_count:=razia_cursor%rowcount; end loop; dbms_output.put_line(l_count||' Number of Row(s) Fetched by the Cursor'); end razia_cursor; Exceptions: An Exception is a error handling statement. It is used when ever our program terminates abnormally by using the exception we can execute the program from abnormal termination to normal termination. There are mainly two types of exceptions they are given below.
                  Predefined Exceptions.
                  User defined Exceptions. Predefined Exceptions: Oracle has predefined several exceptions that correspond to the most common oracle errors. Some predefined exceptions are given below. LOGIN_DENIED NO_DATA_FOUND ZERO_DIVIDE TOO_MANY_ROWS STORAGE_ERROR ROWTYPE_MISMATCH INVALID_CURSOR CURSOR_ALREADY_OPEN Ex: SQL> declare cursor test_cursor is select e.ename, d.deptno, d. dname from scott.emp e, scott.dept d where e.deptno=20 and                                            d.dname='RESEARCH'; begin for i in test_cursor loop dbms_output.put_line(i.ename || ' ' || i.deptno ||' '|| i.dname); end loop; exception when no_data_found then dbms_output.put_line('NO DATA FOUND EXCEPTION RAISED'); when others then dbms_output.put_line(' An Error Raised ' || sqlerrm); end test_cursor; User Defined Exceptions: A user defined exception is an error defined by the programmer. User defined exceptions are declared in the declarative section of the PL/SQL block just like variables. Syntax: DECLARE E_myexception EXCEPTION; Ex: SQL> create or replace procedure razia_proc(v_sal in number) as v_sl number; razia_excep exception; begin if(v_sal >= 5000) then raise razia_excep; else select sal into v_sl from emp order by deptno; end if; exception when razia_excep then dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION'); end razia_proc; Procedures: Procedures are also known as subprograms. Given below is the syntax for the creation of the procedure. Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name [(argument [{IN | OUT | IN OUT}] type, … [(argument [{IN | OUT | IN OUT}] type) ] {IS | AS} BEGIN procedure_body END procedure_name; Ex: SQL> create or replace procedure razia_proc as v_sal number; cursor razia_cursor is select * from emp order by deptno; begin v_sal := &v_s; for i in razia_cursor loop if(i.sal > v_sal) then dbms_output.put_line( 'Employee Name: ' || i.ename); end if; end loop; exception when others then dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION'); dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW'); end razia_proc; Execution: exec razia_proc; Passing IN Parameter to the Procedures: Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as v_name varchar2(30); begin select ename into v_name from emp where empno=p_inpar; dbms_output.put_line( 'Employee Name: ' || v_name); exception when others then dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION'); end kanthi_proc;  Using IN and OUT parameters to Procedures: Ex: SQL> create or replace procedure shabbir_proc(p_outpar out varchar2,p_inpar in number) as begin select ename into p_outpar from emp where empno=p_inpar; end; --To Catch the Output variable out side the procedure in the SQL declare v_name varchar2(20); v_num number; begin mypro(v_name,&v_num); dbms_output.put_line( 'Employee Name: ' || v_name); end shabbir_proc; Functions: Function is similar to a procedure except that a function must return a value to the statement from which it is called. The syntax for creating a function is given below. Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name [(parameter_name[IN | OUT | IN OUT] type [,….])] RETURN type; {IS | AS} BEGIN Function_body END function_name; Ex: create or replace function haritha_func(p_empno number) return varchar2 is v_ename varchar2(20); v_sal number; begin select ename,sal into v_ename, v_sal from emp where empno=p_empno; if v_sal>=6000 then return 'TRUE'; else return 'FALSE'; end if; end haritha_func; Execution: SQL> select haritha_func(7788) from dual; Ex: SQL> create or replace function haritha_fun(p_radius number) return number as v_pi number := 3.141; v_area number; begin v_area := v_pi * POWER(p_radius,2); return v_area; end haritha_fun; Execution: SQL> select haritha_fun(7) from dual; Dropping Function and Procedure: When we want to drop the function or procedure then we use this DROP statement the syntax for the DROP is given below. Syntax for Dropping Function: DROP FUNCTION function_name; Syntax for Dropping Procedure: DROP PROCEDURE procedure_name; Packages: Package is declarative part of the functions and procedures which are stored in that package. There are two blocks in defining a package.
            Package Specification
            Package body Package Specification: The package specification contains information about the contents of the package. It does not contain code for any subprograms. Syntax: CREATE [OR REPLACE] PACKAGE package_name {IS | AS} Type_definition | Procedure_specification | Function_specification | Variable_declaration | Exception_declaration | Cursor_declaration | Pragma_declaration END [package_name]; Package Body: The package body is separate data dictionary object from the package header. It cannot be successfully compiled unless the package header is already been successfully compiled. Syntax: CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} …. BEGIN Initialization_code; END package_name; Ex: SQL> create or replace package vamsi_pack as procedure razia_proc(mynum in number); end vamsi_pack; -- Declaration of Package Body and passing value to the procedure create or replace package body vamsi_pack as procedure razia_proc(mynum in number) as cursor mycursor is select ename,empno,sal from emp where empno=mynum; begin dbms_output.put_line(' NAME ' || ' NUMBER ' || ' SALARY '); for i in mycursor loop dbms_output.put_line(' '||i.ename||' '|| i.empno ||' '|| i.sal); end loop; exception when others then dbms_output.put_line('YOU HAVE DONE AN ILLEGAL OPERATION '); end myproc; end vamsi_pack; Execution: SQL> exec vamsi_pack.razia_proc(7788); Triggers: A Trigger is a procedure that will fire automatically by the database. When a specified DML statement is run against the specified table. Triggers are useful for doing things like advanced auditing of changes made to a column values in a table. The syntax for creating the trigger is given below. Syntax: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} trigger_event ON table_name [FOR EACH ROW [WHEN trigger_condition]] BEGIN Trigger_body; END; Ex: This example is when we insert into a table t1 then the same data has to be inserted into the table t2. For that we require two tables of one same column. SQL> CREATE TABLE t1( Eno number); SQL> CREATE TABLE t2(Eno number,Ins_date date); SQL> CREATE OR REPLACE TRIGGER razia_trigger BEFORE INSERT ON TABLE t1 FOR EACH ROW BEGIN INSERT INTO TABLE t2 VALUES(:new.eno,sysdate); END; Note: For every insert operation on t1 it will insert the eno col values to t2 along with the current sysdate in the other column. Disabling and Enabling Trigger: We can stop the trigger from firing by disabling the trigger by using the ALTER TRIGGER statement. The syntax for enabling the trigger and disabling the trigger is given below. Syntax: ALTER TRIGGER trigger_name DISABLE; ALTER TRIGGER trigger_name ENABLE; Dropping a Trigger: If we want to drop the trigger from the database then we have to use DROP TRIGGER statement. The syntax for dropping the trigger is given below. Syntax: DROP TRIGGER trigger_name; Check out our Popular Trainings  Structure of OAF Advantages and Dis-Advantages of OAF SQL Server DBA Training in Hyderabad Creating First OAF Page in Project