Oracle数据库子程序之存储过程和函数调用

程序员文章站 2024-03-17 19:59:58

...

Oracle数据库子程序之存储过程和函数调用

PL/SQL——无名块，不存储在数据库中，不能从其他的PL/SQL块中调用。而下面介绍的结构——函数、过程、包和触发器都是有名块，它们可以存储在数据库中，并且可以在需要的任何地方进行调用。

1、开发一个存储过程和函数的步骤：

(1).用系统编辑器或文字处理软件写一个含有CREATE PROCEDURE或CREATE FUNCTION语句的脚本文件；(2.) 在写CREATE PROCEDURE或CREATE FUNCTION语句时，应该充分考虑运行时出错的处理，即应该考虑例外处理EXCEPTION；(3). 在SQL*PLUS中运行脚本文件，将过程或函数的源代码编译成编译代码P_code，把两种代码存储到数据库中；(4). 调试编译错误；(5). 在Oracle环境下调用开发的函数或存储过程。

注意：

不论编译是否成功，创建过程/函数命令CREATE PROCEDURE或CREATE FUNCTION都将自动把其源代码存入数据库中，而编译代码只有在编译成功后才能存入数据库中。

只有编译代码被存入到数据库的存储过程和函数才能被调用。

2.1、创建过程：

创建过程语句的语法如下所示：

Oracle数据库子程序之存储过程和函数调用

IN、OUT、IN OUT三种模式的描述：

模式	描述
IN（默认模式）参数（输入参数）	用来从调用环境中向存储过程传递值，不能给IN参数赋值，给此参数传递的值可以是常量、有值的变量、表达式等。
OUT参数（输出参数）	用来从过程中返回值给调用者，不能将此参数的值赋给另一个变量，不能是常量或表达式。在过程体内，必须给OUT参数赋值。
IN OUT参数（输入输出参数）	既可以从调用者向过程中传递值，执行过程后还可返回可能改变了的值给调用者。

举例说明三种模式参数的区别以及在过程体内可以出现的位置：

Oracle数据库子程序之存储过程和函数调用

利用过程进行查询处理，获取一个雇员的信息。

Oracle数据库子程序之存储过程和函数调用

2.2、创建函数

函数类似于过程。两者都带有参数，而参数具有模式，两者都不同于带有声明、可执行以及异常处理部分的PL/SQL块。两者都可以存储在数据库中或在块中声明。两者不同的是，过程调用本身是一个PL/SQL语句，而函数调用是作为表达式的一部分执行的。

Oracle数据库子程序之存储过程和函数调用

参数列表是可选的。但是函数的返回类型是必需的。因为函数调用是表达式的一部分。RETURN语句用来控制通过一个数值返回给调用环境。

RETURN语句的语法格式： RETURN （表达式）其中：表达式是要返回的数值，数值类型与函数定义的RETURN子句中指定的类型相同。在执行RETURN 语句时，控制会立即返回到调用环境中。

在一个函数中，可以使用多个RETURN语句，但是只有一个RETURN语句被执行。

（1）不带参数的函数用法：

//不带参数的函数

create or replace function  <function_name>
         return <variable_type> 
  is
        <variable declarations>
     begin
        <execution_code>;
     end <function_name>;

（2）带参数的函数用法:

Oracle数据库子程序之存储过程和函数调用

举例：利用函数实现一个查询，获取某雇员的工资：

CREATE OR REPLACE  FUNCTION get_sal
       (p_emp_no IN emp.empno%type)
RETURN NUMBER
 IS
       v_emp_sal emp.sal%type:=0;
 BEGIN
    SELECT sal INTO v_emp_sal from emp WHERE     empno=p_emp_no;
     RETURN(v_emp_sal);
  EXCEPTION
    WHEN no_data_found or too_many_rows then
              dbms_output.put_line('发生系统错误');
    WHEN others then
             dbms_output.put_line(sqlerrm);
 END get_sal;

利用函数实现一个查询获取某雇佣的工资：

SQL> variable v_sal number
   SQL> declare 
         v_empno number:=7654;
         begin
             :v_sal:=get_sal(v_empno);
          end;
         /
SQL>Print v_sal;

函数可以使用在任何表达式中，可以用在以下场合：

（1）select语句（2）where子句（3）select语句的order by、group by子句（4）insert语句中的values （5）update的set子句

2.3、过程与函数的区别：

区别之一：

参数形式及返回值不同函数有零个或多个参数，并且只有一个返回值；过程有零个或多个参数，并且不返回值，其返回值是靠OUT参数带出来的；过程和函数都可以有IN参数，通过参数列表接受参数的输入；函数不能有OUT参数，函数值的返回是靠RETURN子句返回的。

区别之二：

调用形式不同。过程可以作为单独可执行语句被调用如：过程名（实际参数1，实际参数2，…），语句可以在PL/SQL块中单独出现。

3、如何在数据字典中查询过程和函数的信息？

存储的信息	描述	获得的途径
源代码	过程、函数的文本	查看USER_SOURCE数据字典或DESCRIBE命令
语法分析树	表达式的语法分析	无法获得
编译代码	编译代码（p_code）	无法获得
编译错误	PL/SQL块的语法错误	查看USER_ERRORS数据字典或用SHOW ERRORS命令
运行调试信息	用户对变量或表达式的调试信息	用DBMS_OUTPUT包中过程

3.1 查询过程、函数的源代码：

通过查询数据字典视图USER_SOURCE来获得。

select text  
       from USER_SOURCE 
          where name =‘QUERY_EMP' and      
                                          type='PROCEDURE';

3.2、查询过程、函数的编译出错信息：

过程、函数的编译出错信息通过USER_ERRORS获得。

SQL〉describe user_errors

SQL〉show error

Oracle数据库子程序之存储过程和函数调用

4、过程和函数的调用：

过程和函数一旦被存储到数据库中，就可从各种环境中调用。既可通过命令行的方式调用，也可从一个具体应用程序中调用。但不同的调用环境，其调用过程函数的语法有所区别。

过程与函数的调用方法的区别：

结构名	返回值	用法
过程	无	出现在一个完整的可执行语句中
函数	返回一个值	可以在返回语句中代替变量或表达式的位置

1. 在PL/SQL中调用存储过程：

Declear 
      v_empno number：=7564；
  Begin
      fire_emp(v_empno );
      dbms_output.put_line(‘已经开除7564员工’)；
  END；

参数v_empno为IN模式; 其他三个为OUT 模式：

Declare
 v_empno emp.empno%type：=7564；
 v_empname emp.empname%type;
 v_sal emp.sal%type;
 v_comm  emp.comm%type;
Begin
  query_emp(v_empno, v_empname,v_sal,v_comm );
  dbms_output.put_line(v_ename||’’||v_sal||’ ’||v_comm)；
END；

2. 在SQL* PLUS中调用过程：

调用只有IN模式参数的过程方法: SQL*PLUS中用EXECUTIVE命令调用过程。例如：从SQL*PLUS中调用fire_emp.过程fire_emp只有IN模式的参数，用具体值替代：

Oracle数据库子程序之存储过程和函数调用

在一个用户中调用另一个用户的过程

方法:在过程名之前加其所属的用户名来加以限制

SQL>EXEC scott.fire_emp(7654);

调用异地数据库的过程

方法:在过程名后面加链路名加以限制

例如：从SQL*PLUS调用一个数据库链路被命名为newsoft 的异地数据库的过程。

SQL>EXECUTIVE aaa@qq.com(7654);

调用带OUT参数的过程

从SQL*PLUS中调用过程QUERY_EMP,该过程有一个IN参数，3个OUT参数，必须在SQL*PLUS中用VARIABLE定义定义3个变量，执行过程时往这3个变量中赋值，可以用下列SQL*PLUS的脚本实现：

6、过程和函数的优点：

（1）提高数据的安全性和完整性利用安全性的权限来控制那些没有足够权限的用户对数据库的间接访问；通过把相关联的表的操作集中到一起，来保证针对这些相关表执行一致的操作或任何操作都不做。

（2）改善操作性能多个用户使用同一个SQL语句时，只做一次语法分析；只在编译时进行语法分析，运行时不再重做，直接调用编译编码。

（3）节省存储空间多个不同应用，用同一个存储代码维护性高

（4）模块化