ORACLE数据库 子程序和程序包 (一)

子程序：包括过程和函数。
程序包：是存储过程和函数的集合

以下代码是在PLSQL Developer 6.0环境中运行的。为了方便，我们使用 scott/tiget登陆PL/SQL,然后拷贝好样例表：
       生成emp_copy表： create table emp_copy as select * from emp;
       生成dept_copy表：  create table dept_copy as select * from dept;

下面我们可以来进行学习了：

1.    子程序
        a. 创建过程:

/**//*建立一个存储过程find_emp，or replace表示可以覆盖重新建立这个存储过程,(emp_no)是该存储过程的参数*/
create or replace procedure  find_emp(emp_no number) /**//*默认的in类型参数，表示是传入的参数*/
as
       empname varchar(20);/**//*声明一个变量用来存储员工姓名*/
begin
     select ename into empname from emp_copy where empno=emp_no; /**//*将传入的参数值作为查询条件*/
     dbms_output.put_line('雇员姓名是 ' || empname);
exception
         when no_data_found then
              dbms_output.put_line('雇员编号没有找到');
end find_emp;  /**//*find_emp过程结束*/
/

执行方式：
SQL> set serveroutput on;
SQL> execute find_emp(7900);
雇员姓名是 JAMES

PL/SQL procedure successfully completed

SQL>

      b.  创建带IN和OUT参数的过程

create or replace procedure itemdesc(item_code IN number,t_sal out varchar2) /**//*带一个传入参数，一个输出参数*/
as
  sals number;
begin
     select sal into sals from emp_copy where empno=item_code; /**//*条件是输入参数*/
     if sals>2000 then
        t_sal := '工资普通';
     elsif sals>4000 then
        t_sal := '工资偏高';
     else
        t_sal :='工资偏低';
     end if;
end;
/

输出结果：
SQL> set serveroutput on;
SQL> declare tmp varchar2(50);
  2  begin
  3  itemdesc(7900,tmp);
  4  dbms_output.put_line('该员工工资 ' || tmp);
  5  end;
  6  /
该员工工资 工资偏低

PL/SQL procedure successfully completed

c. 函数:与过程类似，区别是函数必须有返回值,过程不一定有返回值。而函数主要是为了实现某一些功能，而过程主要用来操作数据的。

/**//*创建一个有业务功能的函数*/
create or replace function emp_sals(sals number) /**//*参数是待检查的员工工资*/
return varchar2  /**//*返回的值是varchar2类型*/
as
  min_sal number; /**//*申明两个变量*/
  max_sal number;
begin
     select max(sal),min(sal) into max_sal,min_sal from emp_copy; /**//*给两个变量赋值*/
     if sals>=min_sal and sals<max_sal then
        return '输入的工资数在员工合理范围内';
     else
        return '输入的工资不合理';
     end if;
end;
/

 调用函数：
SQL> set serveroutput on;
SQL> select emp_sals(4000) from dual;

EMP_SALS(4000)
--------------------------------------------------------------------------------
输入的工资数在员工合理范围内

SQL>

2    程序包:包含两部分：程序包规范和程序主体.
            程序包规范其实就是类似于C++中函数的声明
           程序主体：就是类似于c++函数的实现。

         a. 简单示例：

/**//*程序包规范代码   其实就是程序包声明,先声明程序包内包括了一些什么子程序*/
set serveroutput on;
create or replace package pack_test /**//*创建pack_test程序包*/
is
    procedure prod_emp(eno number); /**//*包括一个过程*/
    function  func_emp(sals number) return varchar2; /**//*包括一个函数*/
end pack_test;
/

/**//*程序包主体*/
set serveroutput on;
create or replace package body pack_test
as

/**//*过程的主体*/
  procedure prod_emp(eno number)
  is
    names varchar2(50);
  begin
    select ename into names from emp_copy where empno=eno;
    dbms_output.put_line('员工姓名：' || names);
  end prod_emp;
  
/**//*函数的主体*/
  function func_emp(sals number) return varchar2
  is
     min_sal number; /**//*申明两个变量*/
     max_sal number;
  begin
     select max(sal),min(sal) into max_sal,min_sal from emp_copy; /**//*给两个变量赋值*/
     if sals>=min_sal and sals<max_sal then
        return '输入的工资数在员工合理范围内';
     else
        return '输入的工资不合理';
     end if;
  end func_emp;
    
end pack_test;
/

程序包创建完毕，使用：
SQL> execute pack_test.prod_emp(7900);    /*使用程序包中的过程*/
员工姓名：JAMES

PL/SQL procedure successfully completed

SQL> select pack_test.func_emp(5000) from dual;   /*调用程序包中的函数*/

PACK_TEST.FUNC_EMP(5000)
--------------------------------------------------------------------------------
输入的工资数在员工合理范围内

SQL>