Oracle Applications

 Sub Programs: ( Named Pl/sql Blocks )
 A set of pl/sql statements stored permenantly in
 database and used to perform a task.
 They can accept input from user as arguments      dynamically.
 They are stored in "user_source" system table.
 They are re-usable components.
 They can be shared with other users.
 They can be used in other application s/w tools.
 They are faster in execution - stored in compiled     format.
 They support modularity.
 Modularity - A huge task divided into N no.of sub     tasks.   Easy to manage the Logic.
            Easy to debug errors.
 2 Types .  i> Procedures    ii> Functions

 Procedure: A sub program type performs a task     and will not return the values.
    --  Generally procedures are used to perform            DML operations on database.
   --  They cannot be called in SELECT stmt.

 Function: A sub program type performs a task and   will return the value. ( Returns only one value )
     -- Generally functions are used for Reporting                purpose or for calculating results.
            --  They can be called in SELECT stmt.
            -- DML operations are not allowed

 Syntax : Procedure
 Create or replace procedure <procedure name>
 [ (arguments  MODE  <data type>) ]  is
 [ <variable declaration>; ]
 begin
 <exec stmts>;
 [ exception
 <exec stmts>; ]
 end;
 Procedure can be used in SQL , Other S/W tools, * PL/SQL, Triggers, Procedures, Functions,             Packages,Developer 6i.

 * Syntax: ( Using Procedure )
  begin
  Procedure_name(arguments);
  end;

 Syntax : Function
 Create or replace Function <Function name>
 [ (arguments  MODE  <data type>) ] 
 Return <data type> is
 [ <variable declaration> ];
 begin
 <exec stmts>;
 Return( variable );
 [ exception
 <exec stmts>; ]
 end;

 Function can be used in SQL , Select ,Other Appln    S/W  tools
 * PL/SQL, Triggers, Procedures, Functions,            Packages, Developer 6i

 * Syntax: ( Using Function )
  begin
  variable := Function_name(arguments);
  end;
---------------------------------------------------------------
 

 * Procedure calculates Intrest: create or replace procedure cal_intr
 ( p number, n number, r number )
 is
 si number(12);
 ci number(14);
 begin
 si := ( p * n * r )/100;   --   pnr/100
 ci := P * power((1 + r/100),n);  -- p*(1+r/100)^n
 dbms_output.put_line(' Simple Intrest is : '||si);
 dbms_output.put_line(' Compound Intrest is :'||ci);
 end;

 Using Procedure: At SQL:
 > desc cal_intr
 > exec cal_intr(12000,12,8.5);   *exec/execute
 > exec cal_intr(100000,25,11.5);

 In PL/SQL: >begin
  cal_intr(120000,12,8.5);
  end;
--------------------------------------------------------------- 

* Function calculates Intrest: create or replace Function intr_cal
 ( p number, n number, r number )
 return number is
 ci number(14);
 begin
  ci := P * power((1 + r/100),n);
 return(round(ci));
 end;

 Using Function: At SQL:
 > desc intr_cal
 i)> select intr_cal(120000,12,8.5),                intr_cal(100000,25,11.5)  from dual;
 ii)> variable result number  -- declaring variable at                                                            sql
    > exec :result := intr_cal(75000,10,9.5);
    > print result                      * variable/var

 In PL/SQL: declare
 result number(14);
 begin
 result := intr_cal(120000,12,8.5);
 dbms_output.put_line(' Compound Intrest is :'     ||result);
 end;

 >create table loan_master
 (cust_id number(4), cname varchar2(20), amount    number(12), duration number(3),
 irate number(5,2));
 >insert into loan_master values   (&1,'&2',&3,&4,&5);

 >select cust_id, upper(cname),    intr_cal(amount,duration,irate)  as "Intrest"  from    loan_master;
---------------------------------------------------------------
 

 * Function checks for Leap year   create or replace function chk_year( y number )
   return varchar2 is
   begin
   if mod(y,4) = 0 then
   return(' Leap Year ');
   else
   return(' Not Leap Year ');
   end if;
   end;
         
 Using Function:   At SQL: > desc chk_year
 > select chk_year(2008), chk_year(2010) from         dual;
 > var result varchar2(30)
 > exec :result := chk_year(2012);
 > print result
 > select ename, hiredate,
   chk_year(to_char(hiredate,'yyyy'))  "year" from       emp;

 set autoprint on --- Automatically prints the      variables . No need to use print stmt.
---------------------------------------------------------------
 * Procedure calculates the new commision for all        employees of a particular department . create or replace procedure cal_comm
 (vdept  number) is
  cursor c1 is select empno,ename,comm from emp
  where deptno = vdept;
  i c1%rowtype;
 begin
 dbms_output.put_line
 (' Employ commision Report ');
 for i in c1 loop
 if i.comm is null then
 i.comm := 3000;
 elsif i.comm = 0 then
 i.comm := 2500;
 else
 i.comm := i.comm + i.comm * .25;
 end if;
 update emp set comm = i.comm where empno =    i.empno;
 dbms_output.put_line(i.empno||'  '||i.ename
 ||'    '||i.comm);
 end loop;
 commit;
 end;

 Using Procedure at SQL:  >desc cal_comm
 >exec cal_comm(30);
--------------------------------------------------------------- 

* Checking the Existing Subprograms >desc user_source
 >select name from user_source;
 >select text from user_source where name =          'CHK_YEAR';
 * User_source -- System Table holds source code      of Triggers, Procedures, Functions and Packages

 To Remove Sub Programs: drop procedure <proc name>;
 drop function <func name>;
 >drop procedure cal_intr;
 >drop function chk_year;

 Sharing Sub programs:
 scott :
 >Grant execute on chk_year to user1;
 user1:
 > desc scott.chk_year
 > select scott.chk_year(2020),             scott.chk_year(2015),
    scott.chk_year(2016) from dual;
 >Revoke execute on chk_year from user1;
---------------------------------------------------------------
 

 * Procedure Prints the Students Fee Due Report :  >create table stu_info(roll number(3), sname           varchar2(20), course varchar2(20),
  fee_paid number(5));
  insert into stu_info values(&1,'&2','&&3',&4);

 create or replace procedure
 stu_rep(vcourse varchar2,vfee number) is
 cursor c1 is select roll,sname,fee_paid from    stu_info
 where course = vcourse and fee_paid < vfee;
 due number(5); 
 k c1%rowtype;
 totdue number(10) := 0;
 begin
 dbms_output.put_line(' Student fee due Report ');
 for k in c1 loop
 due := vfee - k.fee_paid;
 dbms_output.put_line(k.roll||'  '||k.sname
 ||'   '||k.fee_paid||'  '||due);
  totdue := totdue + due;
 end loop;
 dbms_output.put_line( ' Total Due Amount is : '     ||totdue);
 end;

 At SQL:
 >desc stu_rep
 >exec stu_rep('ORACLE',2500);
 >Exec stu_rep('JAVA',3000);
 >Exec stu_rep('Dev6i',2000);
 >Exec stu_rep('UNIX',500);
---------------------------------------------------------------
 Ex : Create sequence s1 increment by 1
        start with 11;

 * Procedure adds a department into DEPT table
 >create or replace procedure add_dept
 (vname varchar2 default 'unknown',
 vloc varchar2 default 'Hyderabad') is
 begin
 insert into dept values(s1.nextval,vname,vloc);
 commit;
  end;

 Using at SQL:
 >desc add_dept
 >exec add_dept;
 >exec add_dept('SALES','MUMBAI');   
 >exec add_dept('EXPORT');
 >exec add_dept(vloc => 'CHENNAI');
 >exec add_dept(vloc => 'PUNE', vname =>              'SOFTWARE');

 Note: Passing arguments to sub programs is supported in  2  methods.
 i. Positional Notation: (default notation)
   Arguments are passed based on their position.
 ii. Named Notation:( => )
    Arguments are passed based on their name.

 Sharing Sub programs: >Grant execute on add_dept to user1;
 User1:
 >desc scott.add_dept
 >exec scott.add_dept;
 >exec scott.add_dept('Testing');
 >insert into scott.dept values(22,'HR','Secbad'); --                                                                      error
 * User can manipulate the Table only thru               Procedure but not directly thru insert stmt.  
 * Whenever subprograms are shared with other         users ,automatically all the components used in        subprogram are shared with other users.
---------------------------------------------------------------
 Parameter Modes in Sub Programs: ------------------------------------------
 "Mode" indicates the behaviour of argument in
  sub program
 3 Modes
 1. IN - default  mode   IN parameter is used to carry the input into sub     program. It cannot be assigned with a value inside   sub program.   

 2. OUT    OUT parameter is used to return the results from    sub program. It can be assigned with a value         inside the sub program.

 3. IN OUT    IN OUT parameter is used to carry the input and    return the results from sub program . It can be     assigned with a value inside sub program .

 Ex:
 Create or replace procedure p1
 ( a IN number, b OUT number ) is
 begin
 b := a * a;
 end;

 Using Procedure:  At SQL > desc p1
 > var x number        *variable/var
 > exec p1(5,:x);
 > print  x

 Create or replace procedure p2
 ( a number, b out number, c out number ) is
 begin
 b := a * a;
 c := a * a * a;
 end;
          
  Using Procedure:  At SQL > desc p2
 > var p number
 > var q number
 > exec p2(5,:p,:q);
 > print  p  q

 Create or replace procedure p3
 ( a in out nocopy  number)  is
 begin
 a := a * a * a;
 end;

 * Nocopy : Applied with arguments in subprograms
 It assigns the value to argument but it will not     copy into it permenantly. It will save resources.
 It will be specified while transfering Huge loads of
 data into parameters.

 Using at SQL: > desc p3
 > var n number
 > exec :n  := 5;  -- initializing variable at SQL
 > exec p3(:n);
 > print  n

 * Procedure Returns Total Salary, No.of Employees    in a department.
 create or replace procedure get_dept_info
 (dno number,dtot out number, dcnt out number) is
 begin
 select sum(sal),count(*) into dtot, dcnt from emp
 where deptno = dno;
 end;

 At Sql:
 var a number
 var b number
 exec Get_dept_info(30,:a,:b);
 print a b
---------------------------------------------------------------
 

* Write a procedure to get name,course,fee_paid        using rollno thru OUT parameters.---------------------------------------------------------------
* Function calculates Factorial of given number and
  checks for odd/even numbers:
 create or replace function cal_fact( n in number,
 f out  number) return boolean is
  fac number(12) := 1;
 begin
 -- calculating factorial
 -- 5! = 5 * 4 * 3 * 2 * 1 = 120
 for i in 1 .. n loop
 fac := fac * i;
 end loop;
 -- Storing result in OUT Parameter
 f := fac;
 -- check for odd/even
 if mod(n,2) = 0 then
 return(true);
 else
 return(false);
 end if;
 end;
 /

 Using Function:
 declare
    a number(3) := &num;
   fact number(12);          -- x boolean;
 begin
 -- calling function           --  x := cal_fact(a,fact);
 if cal_fact(a,fact) then      --  if x = true then
 dbms_output.put_line(' Given number is Even ');
 else
 dbms_output.put_line(' Given number is Odd ');
 end if;
 dbms_output.put_line(' Factorial of given number    is  : '||fact);
 end;

 Note:
 Function returning "Boolean" value or with OUT      parameter cannot be used in SQL or Select stmt.
 * DML are Not valid in Functions becoz they can be  called in " SELECT " statement.
------------------------------------------------------------  

Trigger Vs Sub Programs:
 Triggers are automatically activated by " DML "      statements.
 Procedures & Functions has to be Explicitly   invoked by user.
------------------------------------------------------------   Advantage of Sub programs:
  - Code Reusabilty
  - Faster Execution of code
  - Easy to manage code
  - Executed in the Oracle Server memory even if it       is activated from other Application s/w tools.
 - These r called as Stored Procedures & Stored         Functions ---------------------------------------------------------------
 Database Objects in ORACLE :
 Tables , Views , Synonyms , Sequences , Indexes ,  Clusters ,Roles , Triggers , Procedures , Functions ,  Packages , Objects
---------------------------------------------------------------