PL/SQL程序包的创建和调用

代码实现

--程序包：包规范、包主体
create or replace package pk_test
is
 deptrec dept%rowtype;
 --增加部门信息的函数
 function add_dept(
   dept_no number,
   dept_name varchar2,
   dept_loc varchar2)
   return number;
 --根据部门编号删除部门信息的函数
 function delete_dept(dept_no number)
   return number;
 --根据部门编号查询部门信息的过程
 procedure query_dept(dept_no number);
end pk_test;
--创建包体
create or replace package body pk_test
is
 function add_dept(
   dept_no number,
   dept_name varchar2,
   dept_loc varchar2)
   return number
   is
   begin
     insert into dept values(dept_no,dept_name,dept_loc);
     if sql%found then
       return 1;
     end if;
   end add_dept;
  function delete_dept(dept_no number)
    return number
    is
    begin
      delete dept where deptno=dept_no;
      if sql%found then
        return 1;
      else
        return 0;
      end if;
   end delete_dept;
  procedure query_dept(dept_no number)
    is
    begin
      select * into deptrec from dept where deptno=dept_no;
    exception
      when no_data_found then
        dbms_output.put_line('没有该部门');
    end query_dept;
  end pk_test;
--调用
select * from dept;
delete dept where deptno in(60,50,90);
declare
  v_row number;
  begin
    v_row := pk_test.add_dept(50,'TEST','CHANGSHA');
    if v_row = 1 then
      dbms_output.put_line('增加成功');
    end if;
  end;