add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, May 17

Pl/sql Basics



A sample program can be divided into ‘n’ number of blocks. It is a block structured language it contains the sections they are

1) Declare (optional): we can declare variables, cursors and exceptions

2) Begin (mandatory): we can write the code to perform an action

3) Exception (optional): we can write the code to perform an action when error occurs

4) End (mandatory): it is an end of the block

Ex:
Declare
v1 number: =2;
v2 number;
v3 number;
Begin
v2:=3;
v3:=v1+v2;
dbms_output.put_line ('sum of v1 & v2 is:'||v3);
end;

SET SERVEROUTPUT ON: It is sql*plus command which will take the result from the buffer and prints on the screen.

DBMS_OUTPUT.PUT_LINE: DBMS_OUTPUT is a package and PUT_LINE is a procedure take the result from the program and prints on the buffer.

Types of variables:
             Host variable: we can pass the values at run time it declares with ‘&’.
                                       Ex: declare
                                             Nam varchar2 (10);
                                             NUM NUMBER;
                                             Begin
                                             Select empno into num from EMP where ename='&nam'; --host variable
                                             DBMS_OUTPUT.PUT_LINE (' id is '||NUM);
                                             End;

               Bind variable: it is the variable which u can declare in calling environment declares with ‘:’
                                       Ex: declare
                                              nam varchar2 (10):='SMITH';
                                              begin
                                              Select JOB into: num1 from EMP where ename=NAM;
                                              dbms_output.put_line (:num1);
                                              End;
                                               /           
Types of conditions:
If then end if: Ex:  declare
                                v_sal number;
                                v_empno number:=&empno;
begin
select sal into v_sal from emp where empno=v_empno;
if v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status is low');
end if;
if v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high sal');
end if;
end;
/

If then elsif end if
Ex: declare
                                       v_sal number;
v_empno number:=&empno;
begin
select sal into v_sal from emp where empno=v_empno;
if v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status is low');
elsif v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high sal');
end if;
end;
/

If then else end if:  Ex: declare
v_sal number;
v_empno number:=&empno;
begin
select sal into v_sal from emp where empno=v_empno;
if v_sal between 0 and 1999 then
dbms_output.put_line(v_empno||'status is low');
elsif v_sal between 2000 and 4999 then
dbms_output.put_line(v_empno||'high sal');
else
dbms_output.put_line(v_empno||'great sal');
end if;
end;
/
        
Type of loops: it repeats the same statement n number of time
Basic:
While:
For:
Cursor for loop: cursor has been called in to the loop

CURSOR: it is nothing but oracle server opens the sql area in order to execute he sql statements. This sql area is called as cursor. The data in the cursor is called active set. Cursor will perform the following tasks
--> It opens the sql area in the memory
--> Sql area is populated with the data requested by the query
--> Data is processed as per our requirement
--> Close the cursor or sql area when processing is done.
Cursors are of two types
a.        implicit cursor: we cannot control the cursor
b.        explicit cursor: we can control the cursor if select statement returns more than one row then we use explicit cursor
    Cursor attributes are
i)                   % is open
ii)                 % found
iii)              % notfound
iv)               % rowcount


REFCURSOR: It is a dynamic cursor by using only one cursor to more than one select statement.
 
EXCEPTIONS (ERRORS): we get the errors at two times
i) Compilation time: we can’t handle these type of errors programmatically we need to 
                                 Correct the code.
                     ii) Runtime errors: any programming language must have ability to handle the errors and ecall from them. These are of 3 types
       i) Predefined oracle server errors: don’t declare and allow the oracle server to raise        Them implicitly.
      ii) Non predefined oracle server errors: declare and allow the oracle server to raise          Them implicitly.
    iii) user defined errors: user can declare the errors and has to raise them explicitly.

PRAGMA: It is the non predefined exception compiler directly serves the instructions to the compiler when ever compiler is compiling the code.

RAISE APPLICATION ERROR: It is the user exception looks like oracle predefined error and it terminate the program the error number should between -20,000 to -20,998 it prints the message and stop the program.

Types of subprograms:
i)Procedures: it is used to perform an action here we can also define the parameters and parameters are of 3 types
     
‘In’ (default): we can pass the values from calling environment to program it can accept parameters.

      ‘Out’: we can pass the values from program to calling environment it can accept parameters.

    In out’: we can pass the values from calling environment to program or program to calling Environment.

ii)Functions: it returns only one value it is used most probably for calculating purpose it can accept Parameters.

iii)Triggers: procedures and functions we need to call explicitly but triggers can be raised
                    Automatically when ever particular event occurs it won’t accept parameters.
                    Types of triggers
                    DML triggers: it can be raised on DML statements
                    Instead of triggers: used to modify the complex views.
                    System Triggers: it can be raised on two events DDL and Database events.
iv) Packages: we can group the subprograms it has two parts package body and package specification
                       Package specification: contains the procedures, functions and variables declarations
                       Package body:contains the code for the subprograms which are defined in the package Specifications.
With out package body there can be package specification but not vice versa

Difference between procedures and functions:
        
                        Procedure                                                       functions
     1) in order to perform an action                                      1) it is used to compute a value
     2) it may or may not return value                                2) it has to return value (only one value)

No comments: