oracle数据库sql语句之创建和使用包的讲解

--创建包

create or replace package pkg_emp

as

name emp.ename%type;

procedure pro_emp(id emp.deptno%type, num out number);

function fun_deptno(p_empno emp.empno%type, p_sal out number) return emp.ename%type;

end;

/

--创建包主体

create or replace package body pkg_emp

as

procedure pro_emp(id emp.deptno%type, num out number)

as

begin

select count(*) into num from emp where deptno=id;

end;

function fun_deptno(p_empno emp.empno%type, p_sal out number) return emp.ename%type

as

begin

select ename,sal into name,p_sal from emp where empno=p_empno;

return name;

end;

end;

/

--调用包中成员

declare

num number;

sal number;

eName emp.ename%type;

begin

pkg_emp.pro_emp(10, num);

eName:=pkg_emp.fun_deptno(7902, sal);

end;

/