ORACLE相关语法--子程序和程序包(package,function,procedure)(二)

CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
[Public type and item declarations]
[Subprogram bodies]
[BEGIN
Initialization_statements]
END [package_name];

create or replace package body pack_op is
procedure pro_print_ename(id number) is
name emp.ename%type;
begin
select ename into name from emp where empno=id;
dbms_output.put_line('职员姓名：'||name);
end pro_print_ename;
procedure pro_print_sal(id number) is
salary emp.sal%type;
begin
select sal into salary from emp where empno=id;
dbms_output.put_line('职员工资：'||salary);
end pro_print_sal;
function fun_re_date(id number) return date is
bedate emp.hiredate%type;
begin
select hiredate into bedate from emp where empno=id;
return bedate;
end fun_re_date;
end pack_op;

create or replace package pack_emp is
cursor cur_emp return emp%rowtype;
procedure pro_cur;
end pack_emp;

create or replace package body pack_emp is
cursor cur_emp return emp%rowtype is
select * from emp;
procedure pro_cur is
rec_emp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into rec_emp;
exit when cur_emp%notfound;
if rec_emp.sal<1000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');
elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');
else
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');
end if;
end loop;
end pro_cur;
end pack_emp;

CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt(mycs out mytype);
end;
CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt(mycs out mytype)
is
r_cur mytype;
begin
open r_cur for select * from emp;
mycs:=r_cur;
end p_wt;
end pk_wt;

column object_name format a18
select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');