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.