-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
cPackages are logically related sub programs. Package creating involves two steps. Step 1: Creating Package specification (PKS )
Package Specification: It contains declaration of sub programs Syntax: create or replace package <package_name> end;
Package Body: It contains definition of sub programs Syntax: create or replace package body <package_name>
Ex: Procedure add_num -- which takes two parameters and display its sum. Package Specification: procedure display_emp ( l_empno emp.empno%type); function cal_tax ( l_sal emp.sal%type) end test_pack; Package body: create or replace package body test_pack c := a+b; procedure display_emp (l_empno emp.empno%type) begin select sal into l_sal from emp dbms_output.put_line(l_ename||'.......'||l_sal); function cal_tax ( l_sal emp.sal%type) l_tax := l_sal *10/100; end; end test_pack; To invoke sub programs inside the package: SQL> EXEC test_pack.display_emp (7900) SQL> select empno, ename, sal, test_pack.cal_tax (sal) from emp; Procedure overloading using packages: We can achieve procedure overloading using Packages. ex: Create or replace package test_pack2 procedure p1 ( a number); end test_pack2; create or replace package body test_pack2
procedure p1 ( a number) dbms_output.put_line('The square of the number is '||a*a); end test_pack2; In the above package there are two procedures with the same name. Appropriate procedure is invoked basing on the no of parameters which are passed Ex: SQL> exec test_pack2(10, 20); The sum is 30 SQL> exec test_pack2(10); The square of the number is 100
To drop the package: We need to drop package bodu first and then the package specification. Drop package body <package_name>; Drop package <package_name>; Ex: Drop package body test_pack2; Drop package test_pack2; Guidelines of the packages:
|