Oracle 入门教程--PL/SQL存储过程、存储函数详解

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程语法：

    create [or replace] PROCEDURE 过程名 [(参数名 in/out 数据类型)]
    AS 
    begin
    	PLSQL子程序体;
    end;

或者

    create [or replace] PROCEDURE 过程名 [(in/out 数据类型)]
    is 
    begin
    	PLSQL子程序体：
    end 过程名;

示例：打印hello world

    create or replace procedure sayhelloworld
    as
    begin
      dbms_output.put_line('hello world');
    end;

调用：

    方式一：直接调用
    SQL> exec sayhelloworld;

    方式二：在另一个PL/SQL中调用
    begin
    	syahelloworld();
    end;

带参数的存储过程

示例：给指定的员工涨100工资，并打印出涨前和涨后的工资

    create or replace procedure raiseSalary(eno in number)
    is
           --定义变量保存涨前的薪水
           psal emp.sal%type;
    begin
           --得到涨前的薪水
           select sal into psal from emp where empno=eno;
           
           --涨100
           update emp set sal=sal+100 where empno=eno;
           
           --要不要commit？
           
           dbms_output.put_line('涨前:'||psal||'   涨后：'||(psal+100));
    end raiseSalary;

调用：

    begin
      raiseSalary(eno => 7902);
      commit;
    end;

存储函数

函数（Function）为一命名的存储程序，可以带参数，并返回一个计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

建立存储函数的语法：

    CREATE [OR REPLACE] FUNCTION 函数名（参数列表）
    RETURN 函数值的类型
    AS
    	PLSQL子程序体；
    END[函数名];

存储过程和存储函数的区别：
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以理用out参数，在过程和函数中实现返回多个值。

示例：查询某个员工的年薪

    create or replace function queryEmpIncome(eno in number) 
    return number
    is
           --定义变量保存月薪和奖金
           psal emp.sal%type;
           pcomm emp.comm%type;
    begin
           --得到月薪和奖金
           select sal,comm into psal,pcomm from emp where empno=eno; 
           
           --返回年收入
           return psal*12+nvl(pcomm,0);
    
    end queryEmpIncome;

调用：

    begin
      :result := queryempincome(7839);
    end;

使用存储过程替换上面的示例：

    create or replace procedure empincomep(eno in emp.empno%type, income out number)
    is
      psal emp.sal%type;
      pcomm emp.comm%type;
    begin
      select t.sal, t.comm into psal, pcomm from emp t 
      where t.empno = eno;
        income := psal*12+nvl(pcomm,0);
    end empincomep;

调用：

    declare
      income number;
    begin
      empincomep(7369, income);
      dbms_output.put_line(income);
    end;

阅读全文