pl/sql——package

2019独角兽企业重金招聘Python工程师标准>>>

包中只有一个过程

create or replace package sp_package3 is
procedure mytest_insert(name varchar2,password varchar2);
end;

create or replace package body sp_package3 is
procedure mytest_insert(name varchar2,password varchar2) is
  begin
    insert into mytest values (name,password);
  end;
 end;

包中只有一个函数

create or replace package sp_package4 is
       function annal_income(name varchar2) return number;
end;

     create or replace package body sp_package4 is
       function annal_income(name varchar2) return number is salNum number(7,2);
         begin
           select sal*13+nvl(comm,0)*13 into salNum from emp where ename = name;
           return salNum;
         end;
      end;
      
      var a number;
      call sp_package4.annal_income('SCOTT') into:a;

创建一个过程和一个方法的包

create or replace package sp_package5 is
procedure mytest_insert(name varchar2,password varchar2);
function annal_income(name varchar2) return number;
end;

create or replace package body sp_package5 is
procedure mytest_insert(name varchar2,password varchar2) is
  begin
     insert into mytest values (name,password);
  end;
  function annal_income(name varchar2) return number is SalNum number(7,2);
  begin
     select sal*13+nvl(comm,0)*13 into salNum from emp where ename = name;
     return SalNum;
  end;
 end;

创建两个过程一个方法的包

create or replace package sp_package6 is
procedure mytest_insert(name varchar2,password varchar2);
procedure mytest_update(name varchar2,password varchar2);
function annal_income(name varchar2) return number;
end;

create or replace package body sp_package6 is
procedure mytest_insert(name varchar2,password varchar2) is
  begin
     insert into mytest values (name,password);
  end;
procedure mytest_update(name varchar2,password varchar2) is
  begin
     update mytest set name = name,password = password where name = name;
  end;  
  function annal_income(name varchar2) return number is SalNum number(7,2);
  begin
     select sal*13+nvl(comm,0)*13 into salNum from emp where ename = name;
     return SalNum;
  end;
 end;

创建两个方法两个过程的包

create or replace package sp_package7 is
procedure mytest_insert(name varchar2,password varchar2);
procedure mytest_update(name varchar2,password varchar2);
function annal_income(name varchar2) return number;
function income(name varchar2) return number;
end;

create or replace package body sp_package7 is
procedure mytest_insert(name varchar2,password varchar2) is
  begin
     insert into mytest values (name,password);
  end;
procedure mytest_update(name varchar2,password varchar2) is
  begin
     update mytest set name = name,password = password where name = name;
  end;  
  function annal_income(name varchar2) return number is SalNum number(7,2);
  begin
     select sal*13+nvl(comm,0)*13 into salNum from emp where ename = name;
     return SalNum;
  end;
  function income(name varchar2) return number is SalNum number(7,2);
  begin
     select sal*13+nvl(comm,0)*13 into salNum from emp where ename = name;
     return SalNum;
  end;
 end;

1、ORACLE中的function 、package、package bodies、procedure的区别和相同：
 function有返回值，有参数；procedure无返回值，有参数；package、package body是同时存在的，就像.h和.cpp文件，
 如果要外部调用的，就在package里声明一下，包内调用的，只要在body里写就行了。package可包括function,procedure
2、我在存储过程就是procedures中写了个存储过程，再写个包名包含进去吗？还是可以直接在包中写包名：再把想写的存储过程直接写进包中就行啦呢？
不用  
注意：
1.每一包，必须创建对应的包体，已经创建过过程和方法都不能代替，否则报错如下：
ORA-04067: 未执行, package body "SCOTT.SP_PACKAGE2" 不存在
ORA-06508: PL/SQL: 无法找到正在调用 : "SCOTT.SP_PACKAGE2" 的程序单元
ORA-06512: 在 line 1

转载于:https://my.oschina.net/869088067/blog/2244572