Functions,procedures,packages

Procedures or functions:


It is a collection of sql and plsql blocks of codes used to prevent the representation of coding by making it occur only one time.

Procedures or functions are stored n the database.

When a call to procedure or functions is made in the database server it loads the current procedures or functions in to the memory which respond to many requests of the same requirements.

SYNTAX: CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(PARAMETERS)
                IS
               --DECLARE ANY VARIBES,CURSORS ETC….
             BEGIN
                     --ANY ACTION TO BE DONE IN EXECUTABLE SECTION.
             EXCEPTION SECTION
            END PROCEDURE NAME;


SYNTAX: CREATE OR REPLACE FUNCTION FUNCTION_NAME(PARAMETERS)
                   RETURN DATATYPE
                 IS
                                 --DECLARE ANY VARIBES,CUSRSORS ETC….
             BEGIN
                     --ANY ACTION TO BE DONE IN EXECUTABLE SECTION.
             RETURN VALUE;
             EXCEPTION SECTION
            END FUCNTION_NAME;

Ex: write a function to accept dept no as parameter and return the department name….
   Create or replace function xx_dept_1(p_deptno number)
       Return varchar2
   Is
 --declaration variables
ln_dname dept.dname%type;
    Begin
         Select dname
          Into  ln_dname
          From dept
           Where deptno = p_deptno;
 Return ln_dname;
 Exception
  When no_data_found then
   Return null;
   Dbms_output.put_line(‘No data found’);
   When others then
 Return null;
  Dbms_output.put_line(‘Error in the xx_dept function’||sqlerrm);
 End xx_dept_1;

Execution of a functions:

Method1:  select xx_dept_1(10) from dual;

Method2: declare
                   Ln_val varchar2(100);
                    Begin
                 Ln_val := xx_dept_1(10);
                  Dbms_output.put_line(ln_val);
                End;
                   

1)      Write a function to print grade of the employee by passing empno.
2)      Write a function to print the ename of the employee by passing empno.
3)      Write a function to print the student grade by passing std no.
4)      Write a function to print the sum of salaries for the passed deptno parameter.
5)      Write as function to print the salary by deducting 7.65% from that salary by passing emp no.
6)      Write a function to print the 3 highest paid employee name no parameters to be passed.
7)      Write a function to print the total deposited amount from the trx_table by passing accno.

Packages:
They are nothing but clubbing of functions, procedures as one unit.
It consists of 2 parts:
1) Package Specification:
    In this section we declare the functions, procedures etc….
2) Package Body:
  We write the procedure, functions body which is declared in the package specification.

Note: when the procedure or functions are declared in the package specification, their body must be written in the package body.


Syntax:
    Create or replace package Package_name
     Is
     Begin
      Procedure p1(parameters);
      Function f1(parameters) return number;
      Procedure p2(parameters);
  End;







Create or replace package body package_name
  Is
  Procedure p1(parameters)
    Is
 Begin
  -----action;
End p1;
---
Procedure p2(parameters)
    Is
 Begin
  -----action;
End p2;
fucntion f1(parameters)
    Is
 Begin
  -----action;
End f1;
End package_name;





























 ----------------------------------------------------
EX1:

CREATE OR REPLACE PACKAGE XX_EMP_PKG
 IS
  PROCEDURE  P_EMP_DTLS(P_DEPTNO NUMBER);
  FUNCTION      F_SALGRADE(P_EMPNO NUMBER) RETURN VARCHAR2;
  PROCEDURE  P_OUTPUT(P_STRING VARCHAR2);
END XX_EMP_PKG;

CREATE OR REPLACE PACKAGE BODY XX_EMP_PKG
IS
  FUNCTION  F_SALGRADE(P_EMPNO NUMBER)
     RETURN VARCHAR2
is
v_grade salgrade_ank.grade%type;
begin

    select grade
    into  v_grade
            from salgrade_ank sg, emp_ank e
            where e.empno = p_empno
            and e.sal between sg.lowsal and sg.highsal;
    return v_grade;

    exception
    when no_data_found then
     return null;
     P_OUTPUT ('No data found');
    when others then
     return null;
     P_OUTPUT ('Error in the F_SALGRADE function'||sqlerrm);
end F_SALGRADE;
--
PROCEDURE P_EMP_DTLS(P_DEPTNO NUMBER)
IS
cursor c_dept is select * from dept_ank where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)   /*Parameterised Cursor*/
is select * from emp_ank where deptno = v_deptno;
 
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
 
/*Execution Section*/
begin
 
P_OUTPUT ('----------------------Start of the report-------------------------');
P_OUTPUT ('----------------------Start of the report-------------------------');
 
for r_dept in c_dept
 loop
  v_flag:=1;
  dept_tot_sal:=0;
  emp_cnt:=0;
  P_OUTPUT ('Department No is: '||r_dept.deptno);
  P_OUTPUT ('Department Name is: '||r_dept.dname);
  P_OUTPUT ('Department Location is: '||r_dept.loc);
  P_OUTPUT ('------------------------------------');
 
 for r_emp in c_emp(r_dept.deptno)
  loop
    P_OUTPUT ('Employee Number: '||r_emp.empno);
    P_OUTPUT ('Employee Name: '||r_emp.ename);
    P_OUTPUT ('Salary: '||r_emp.sal);
    P_OUTPUT ('------');
    dept_tot_sal:=dept_tot_sal+r_emp.sal;
    emp_cnt:=emp_cnt+1;
  end loop;
  if emp_cnt=0 then
  P_OUTPUT ('No employee working for department no' ||r_dept.deptno);
  else
  P_OUTPUT ('The total no of employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt);
  end if;  
  P_OUTPUT ('The total salary for deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
  P_OUTPUT ('*****************************************************');
 
tot_sal:=tot_sal+dept_tot_sal;
 
end loop;
 
if v_flag=0 then
P_OUTPUT ('Invalid department number. Please enter the valid department number.');
end if;
 
if v_flag=1 and p_deptno is null then
P_OUTPUT ('The total salary of all the departments is ' ||tot_sal);
end if;
 
P_OUTPUT('--------------------------End of the Report------------------------');
 
 
exception
 when others then
 P_OUTPUT('Error in the P_EMP_DTLS procedure'||SQLERRM);
END P_EMP_DTLS;
--
PROCEDURE P_OUTPUT(P_STRING VARCHAR2)
  IS
 BEGIN
  DBMS_OUTPUT.PUT_LINE(P_STRING);
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(‘ERROR IN THE P_OUTPUT PROCEDURE’||SQLERRM);
END P_OUTPUT;
 ---

END XX_EMP_PKG;


----------------------------------------------------
EX2: MAKING P_OUTPUT PROCEDURE AS PRVATE PROCEDURE…..

CREATE OR REPLACE PACKAGE XX_EMP_PKG
 IS
  PROCEDURE  P_EMP_DTLS(P_DEPTNO NUMBER);
  FUNCTION      F_SALGRADE(P_EMPNO NUMBER) RETURN VARCHAR2;
END XX_EMP_PKG;

CREATE OR REPLACE PACKAGE BODY XX_EMP_PKG
IS
--
PROCEDURE P_OUTPUT(P_STRING VARCHAR2)
  IS
 BEGIN
  DBMS_OUTPUT.PUT_LINE(P_STRING);
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(‘ERROR IN THE P_OUTPUT PROCEDURE’||SQLERRM);
END P_OUTPUT;
 ---

  FUNCTION  F_SALGRADE(P_EMPNO NUMBER)
     RETURN VARCHAR2
is
v_grade salgrade_ank.grade%type;
begin

    select grade
    into  v_grade
            from salgrade_ank sg, emp_ank e
            where e.empno = p_empno
            and e.sal between sg.lowsal and sg.highsal;
     return v_grade;

    exception
    when no_data_found then
     return null;
     P_OUTPUT ('No data found');
    when others then
     return null;
     P_OUTPUT ('Error in the F_SALGRADE function'||sqlerrm);
end F_SALGRADE;
--
PROCEDURE P_EMP_DTLS(P_DEPTNO NUMBER)
IS
cursor c_dept is select * from dept_ank where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)   /*Parameterised Cursor*/
is select * from emp_ank where deptno = v_deptno;
 
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
 
/*Execution Section*/
begin
 
P_OUTPUT ('----------------------Start of the report-------------------------');
P_OUTPUT ('----------------------Start of the report-------------------------');
 
for r_dept in c_dept
 loop
  v_flag:=1;
  dept_tot_sal:=0;
  emp_cnt:=0;
  P_OUTPUT ('Department No is: '||r_dept.deptno);
  P_OUTPUT ('Department Name is: '||r_dept.dname);
  P_OUTPUT ('Department Location is: '||r_dept.loc);
  P_OUTPUT ('------------------------------------');
 
 for r_emp in c_emp(r_dept.deptno)
  loop
    P_OUTPUT ('Employee Number: '||r_emp.empno);
    P_OUTPUT ('Employee Name: '||r_emp.ename);
    P_OUTPUT ('Salary: '||r_emp.sal);
    P_OUTPUT ('------');
    dept_tot_sal:=dept_tot_sal+r_emp.sal;
    emp_cnt:=emp_cnt+1;
  end loop;
  if emp_cnt=0 then
  P_OUTPUT ('No employee working for department no' ||r_dept.deptno);
  else
  P_OUTPUT ('The total no of employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt);
  end if;  
  P_OUTPUT ('The total salary for deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
  P_OUTPUT ('*****************************************************');
 
tot_sal:=tot_sal+dept_tot_sal;
 
end loop;
 
if v_flag=0 then
P_OUTPUT ('Invalid department number. Please enter the valid department number.');
end if;
 
if v_flag=1 and p_deptno is null then
P_OUTPUT ('The total salary of all the departments is ' ||tot_sal);
end if;
 
P_OUTPUT('--------------------------End of the Report------------------------');
 
 
exception
 when others then
 P_OUTPUT('Error in the P_EMP_DTLS procedure'||SQLERRM);
END P_EMP_DTLS;
--

END XX_EMP_PKG;

EX2: USING FUNCTION IN A PROCEDURE

CREATE OR REPLACE PACKAGE XX_EMP_PKG
 IS
  PROCEDURE  P_EMP_DTLS(P_DEPTNO NUMBER);
END XX_EMP_PKG;

CREATE OR REPLACE PACKAGE BODY XX_EMP_PKG
IS
--
PROCEDURE P_OUTPUT(P_STRING VARCHAR2)
  IS
 BEGIN
  DBMS_OUTPUT.PUT_LINE(P_STRING);
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(‘ERROR IN THE P_OUTPUT PROCEDURE’||SQLERRM);
END P_OUTPUT;
 ---
procedure p_emp_out(p_empno number
                                                                                                              ,p_ename out varchar2
                                                                                                              ,p_sal   out number)
is
BEGIN
 BEGIN
  select ename,sal
  into   p_ename,p_sal
  from   emp
  where  empno = p_empno;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
   p_ename := null;
   p_sal := null;
  WHEN OTHERS THEN
P_OUTPUT('Error in the select statement'||SQLERRM);
 END;

EXCEPTION
  WHEN OTHERS THEN
   P_OUTPUT('Error in the main proceudre'||SQLERRM);
END p_emp_out;

---
  FUNCTION  F_SALGRADE(P_EMPNO NUMBER)
     RETURN VARCHAR2
is
v_grade salgrade_ank.grade%type;
begin

    select grade
    into  v_grade
            from salgrade_ank sg, emp_ank e
            where e.empno = p_empno
            and e.sal between sg.lowsal and sg.highsal;
    return v_grade;

    exception
    when no_data_found then
     return null;
     P_OUTPUT ('No data found');
    when others then
     return null;
     P_OUTPUT ('Error in the F_SALGRADE function'||sqlerrm);
end F_SALGRADE;
--
PROCEDURE P_EMP_DTLS(P_DEPTNO NUMBER)
IS
cursor c_dept is select * from dept_ank where deptno = nvl(p_deptno,deptno);
cursor c_emp(v_deptno number)   /*Parameterised Cursor*/
is select * from emp_ank where deptno = v_deptno;
 
/*Variables*/
dept_tot_sal number:=0;
emp_cnt number:=0;
tot_sal number:=0;
v_flag number:=0;
LN_GRADE VARCHAR2(1);
LN_ENAME EMP.ENAME%TYPE;
LN_SAL   EMP.SAL%TYPE;
 
/*Execution Section*/
begin
 
P_OUTPUT ('----------------------Start of the report-------------------------');
P_OUTPUT ('----------------------Start of the report-------------------------');
 
for r_dept in c_dept
 loop
  v_flag:=1;
  dept_tot_sal:=0;
  emp_cnt:=0;
  P_OUTPUT ('Department No is: '||r_dept.deptno);
  P_OUTPUT ('Department Name is: '||r_dept.dname);
  P_OUTPUT ('Department Location is: '||r_dept.loc);
  P_OUTPUT ('------------------------------------');
 
 for r_emp in c_emp(r_dept.deptno)
  loop
      --CALLING THE FUNCTION INORDER TO FETCH THE SALGRADE OF A EMPLOYEE…
    LN_GRADE := F_SALGRADE(r_emp.empno);
    --CALLING THE PROCEDURE TO FETCH DETIALS….
     p_emp_out(r_emp.empno,LN_ENAME,LN_SAL);
    P_OUTPUT ('TEST PROVATE PROC Employee NAME: '|| LN_ENAME);
    P_OUTPUT ('TEST PROVATE PROC SAL: '|| LN_SAL);
    P_OUTPUT ('Employee Number: '||r_emp.empno);
    P_OUTPUT ('Employee Name: '||r_emp.ename);
    P_OUTPUT ('Salary: '||r_emp.sal);
    P_OUTPUT (‘SALGRADE IS : '|| LN_GRADE);
    P_OUTPUT ('------');
    dept_tot_sal:=dept_tot_sal+r_emp.sal;
    emp_cnt:=emp_cnt+1;
  LN_GRADE := NULL;
  end loop;
  if emp_cnt=0 then
  P_OUTPUT ('No employee working for department no' ||r_dept.deptno);
  else
  P_OUTPUT ('The total no of employees working in deptno'||r_dept.deptno|| ' is ' ||emp_cnt);
  end if;  
  P_OUTPUT ('The total salary for deptno'||r_dept.deptno|| ' is ' ||dept_tot_sal);
  P_OUTPUT ('*****************************************************');
 
tot_sal:=tot_sal+dept_tot_sal;
 
end loop;
 
if v_flag=0 then
P_OUTPUT ('Invalid department number. Please enter the valid department number.');
end if;
 
if v_flag=1 and p_deptno is null then
P_OUTPUT ('The total salary of all the departments is ' ||tot_sal);
end if;
 
P_OUTPUT('--------------------------End of the Report------------------------');
 
 
exception
 when others then
 P_OUTPUT('Error in the P_EMP_DTLS procedure'||SQLERRM);
END P_EMP_DTLS;
--

END XX_EMP_PKG;

1)Difference between function,procedure and package.
2) what are collections.what are collection attributes.
3) what is difference between record type and table type.
4) what is bulk binding?
5) Difference between bulk collect and forall
5) What is ref cursor?
6) What is execute immediate?




A Package is a container that may have many functions and procedures within it. It provides useful capabilities such as scoping, interface definition and modular development.
It's generally advisable to put your procedures and functions into packages with well designed interfaces
Functions can be used in sql query but procedure we can't
used in sql query

For eg:
tax is a function we can use like

select ename,tax(sal) from emp where ename='saki';


In this we can use.....

Is This Answer Correct ?   
51 Yes
5 No
0
Esakkiraja
[HP]




  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
3
If no write (i.e. insert,delete,update,merge) operation is
made in a function then that function can be used sql
query. if only read operation (i.e. Select) is used in a
function, that can be called from sql query.

Is This Answer Correct ?   
18 Yes
2 No
4
Subhasish Dutta 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
4
The Function will return a value where procedure wont.

Is This Answer Correct ?   
11 Yes
25 No
0
Kalaiselvan.j 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
5
Procedure and functions contains scope with in package
only..but coming to it contains scope out side of the
package also.i.e we  can access package elements from out
side of the package also.

Is This Answer Correct ?   
11 Yes
3 No
0
Karunakar Remala 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
6
procedure call is pl/sql statement by itself.

function call is part of an expression.

Is This Answer Correct ?   
14 Yes
2 No
1
Rashmipriya 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
7
Function must return one value
Procedure may or may not return one or more values.
Can call functions in Sql statements
we can't call a procedure in sql statements
Functions can not return images
procedure returns images

Is This Answer Correct ?   
22 Yes
1 No
0
Suresh 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
8
Note:- procedure may or may not return value.function must
return value
Procedure return one or more value & function return only
single value.
Pakages are groups of functions,procedures,sql blocks
goruped together in a single unit.
Package & packagebody are two different thngs. Variable
defined in packages are global & they can use in anywhere
in packagebody. Variable defined in packagebody have
limited scope. & they can used in defined limit only.

Is This Answer Correct ?   
13 Yes
1 No
0
Deepak 

  Re: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer
9
A Procedure that performs an action.
A Function that computes a value.

We can call a Function in SELECT Statement.
We can't call a Procedurein SELECT Statement.


Post a Comment