Oracle数据库实用教程 教学课件 作者 吴思远 第10章 PLSQL.pptVIP

* * * * * * * * create or replace package body my_pkg is pragma serially_reusable; --变量的声明 v_flag number; --游标的声明 cursor mycursor is select deptno, dname from dept; --函数声明和定义 function check_dept(v_deptno number) return number is begin select count(*) into v_flag from dept where deptno = v_deptno; if v_flag 0 then v_flag :=1 ; end if; return v_flag; end check_dept; -- 公有函数定义add_dept function add_dept(v_deptno number,v_deptname varchar2,v_deptloc varchar2) return number is begin if check_dept(v_deptno) = 0 then insert into dept values(v_deptno,v_deptname,v_deptloc); return 1; else return 0; end if; exception when others then v_sqlcode := sqlcode; v_sqlerrm :=sqlerrm; return -1; end add_dept; -- 公有函数定义remove_dept function remove_dept(v_deptno number) return number is begin if check_dept(v_deptno) = 1 then delete from dept where deptno=v_deptno; return 1; else return 0; end if; exception when others then v_sqlcode := sqlcode; v_sqlerrm :=sqlerrm; return -1; end remove_dept; -- 公有过程定义query_dept procedure query_dept(v_deptno number) is begin if check_dept(v_deptno) =1 then select * into v_deptrec from dept where deptno =v_deptno; end if; exception when others then v_sqlcode :=sqlcode; v_sqlerrm := sqlerrm; end query_dept; -- 公有过程定义 read_dept procedure read_dept is v_deptno number; v_dname varchar2(14); begin for c_mycursor in mycursor loop v_deptno := c_mycursor.deptno; v_dname := c_mycursor.dname; dbms_output.put_line(v_deptno|| ||v_dname); end loop; end read_dept; begin --包体初始化部分，对公有变量进行初始化 v_sqlcode :=null; v_sqlerrm :=初始化消息文本; end my_pkg; * * * * * * * * * * * * *