Oracle 存储函数和存储过程(PL/SQL子程序)

文章来源：http://www.itnose.net/detail/6033960.html
更多文章：http://www.itnose.net/type/97.html

PL/SQL子程序 包括函数和过程。这里的函数指的是用户自己定义的函数，和系统函数是不同的。子程序一般是完成特定功能的PL/SQL程序块，并且具有一定的通用性，可以被不同的应用程序多次调用。Oracle提供可以把PL/SQL程序存储在数据库中，并可以再任何地方来运行它。这样就叫做存储过程或者是函数。过程和函数的唯一区别就是函数总是向调用者返回数据，而过程则不返回数据。

函数

如果用户要经常执行某些操作，并且需要返回特定的数据，那么就可以将这些操作构造成一个函数。

可以使用SQL语句定义函数。

基本语法：

create or replace function fun_name(argument [in | out | in out ]datatype  ...)
return datatype 
is | as
       -- 需要定义的变量 ，记录类型 ， 游标
begin
       --函数的执行体
exception
       --处理出现的异常
end ;

其中，return datatype ， 是要返回数据的类型。IN参数标记表示传递给函数的值在函数执行中不改变；OUT标记表示一个值在函数进行计算并通过该参数传递给调用语句；IN OUT 标记表示传递给函数的值可以变化并传递给调用语句。

例：定义一个返回hello 的函数

create or replace function hello
return varchar2 
is
begin
       return 'Hello World';
end ;

执行 ，Function created ，函数创建成功。

函数的调用，与Oracle内置函数的调用相同，可以使用select hello from dual ;进行调用，也可以使用PL/SQL语句进行调用 ：

begin 

  dbms_output.put_line(hello);

end ;

例:创建带参数的helloworld函数

create or replace function helloworld(str varchar2)
return varchar2
is
begin
       return 'Hello' || '_' ||str ;
end ;

函数的调用要在函数名称后面加上参数,即：select helloworld('World') from dual ;使用Pl/sql的调用除了加上参数外与上面相同，不在赘述。

例：求一个部门中，员工的工资总数的函数

create or replace function get_sal(dept number)
return number 
is
       v_sum number(10) := 0 ;
       cursor sal_cursor is select sal from emp where deptno = dept ;
begin 
       for c in sal_cursor loop
           v_sum := v_sum + c.sal ;
       end loop ;
       return v_sum ;
end ;

存储过程

存储过程，可以被多个应用程序调用，也可以向存储过程传递参数，向存储过程传回参数。

基本语法

create or replace procedure pro_name(argument [in | out | in out ]datatype  ...) 
is | as
       -- 需要定义的变量 ，记录类型 ， 游标
begin
       --函数的执行体
exception
       --处理出现的异常
end ;

例：使用存储过程，求部门的工资总和

create or replace procedure get_sal1(dept number ,sumsal out  number)
is
       cursor sal_cursor is select sal from emp where deptno = dept ;
begin 
       sumsal := 0 ;
       for c in sal_cursor loop
           sumsal := sumsal + c.sal ;
       end loop ;
       dbms_output.put_line(sumsal);
end ;

存储过程的调用：

declare 
  v_sum number(10) := 0 ;
begin 
  get_sal1(30 , v_sum);
end ;

调用格式：

 

CALL | EXCEUTE procedure_name(arg_list) ;

可以使用show error 命令来提示源码的错误位置。使用user_error 数据字典来查看各存储过程的错误位置。

 

删除过程和函数

删除过程

语法如下：

DROP PROCEDURE[USER.]procedure_name ;

删除函数

语法如下：

DROP FUNCTION [USER.]function_name ;