xml地图|网站地图|网站标签 [设为首页] [加入收藏]
oracle中 procedure(存储过程)和function(函数)本质区别
分类:网络频道

Oracle function里面是可以允许有DML语句的,但是不能在查询的时候使用。

1、基础知识

PLSQL是一种类Pascal语言,每一段程序都是由Block(代码块)组成

declare
     变量定
begin
    sql语句
    pl语句
exception
    异常处理
end;

PLSQL块分为三种:

  • 匿名块 (Anonymous)
  • 存储过程 (Procedure)
  • 函数 (function)

上面举得例子就是匿名块,因为没有名字,所以叫匿名块。存储过程和函数都是有名字的,函数有返回值。

我们常用的function如:

2、PLSQL变量

PLSQL变量主要有以下四种:

  • 系统内置的常规简单变量类型
  • 用户自定义复杂类型变量
  • 引用类型变量(保存了一个指针值)
  • 大对象类型(LOB)

变量类型举例:

  • 布尔类型
  • 日期类型
  • BFILE的二进制文件类型
  • 日期类型
  • BLOB类型
  • long类型,长字符串
  • 字符串类型

变量声明举例:

DECLARE v_hiredateDATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
  • 变量声明后再没有赋值之前其值为NULL
  • 同一个块中,应该避免多个变量使用相同的名字

常规变量的申明:

DECLARE
    v_job VARCHAR2(9);
    v_count BINARY_INTEGER := 0;
    v_total_sal NUMBER(9,2) := 0;
    v_orderdate DATE := SYSDATE + 7;
    c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
    v_valid BOOLEAN NOT NULL := TRUE;
    ... 
-- 特殊申明变量方式:
    v_name employees.last_name%TYPE;
    v_min_balance v_balance%TYPE := 10;
begin
   null;
end;
select max(a) from table ; 

PLSQL中的游标

游标概论宝马娱乐在线网址,:

游标是一个私有的SQL工作区域,Oracle数据库中有两种游标,分别是隐式游标和显式游标,隐式游标不易被用户和程序员察觉和意识到,实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句; 而显式游标是程序员在程序中显式声明的;通常我们说的游标均指显式游标。

这种调用方式是不能执行带有DML的FUNCTION的。

显示游标

对于返回多行结果的SQL语句的返回结果,可使用显式游标独立的处理器中每一行的数据。

此处图片缺失

显式游标控制的一般过程:

DECLARE
    v_empno employees.employee_id%TYPE;
    v_ename employees.last_name%TYPE;
    CURSOR emp_cursor IS --1.创建游标
      SELECT employee_id, last_name FROM employees;
BEGIN
    OPEN emp_cursor; --2.打开游标
    LOOP
      FETCH emp_cursor
        INTO v_empno, v_ename; --3.提取变量  fetch cursor_name into value1,value2;

      EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; --emp_cursor%NOTFOUND 4.用来测试是否有数据
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno) || ' ' || v_ename);
    END LOOP;
    CLOSE emp_cursor; --5.关闭游标
END;

但是如果不用在SQL里面是可以有的

for循环控制游标

举例1:直接使用for+sql创建并适用游标

   --一般格式:
   --for record_name in cursor_name loop ... end loop;
   BEGIN
   FOR emp_record IN (SELECT last_name, department_id
   FROM employees) LOOP
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
   END;

举例2:先创建游标emp_cursor,再用for调用

--这种情况适用于多次调用游标的情况
 DECLARE
   CURSOR emp_cursor IS --先创建游标
   SELECT last_name, department_id
   FROM employees;
BEGIN
   FOR emp_record IN emp_cursor LOOP --再调用游标访问
   -- implicit open and implicit fetch occur
   IF emp_record.department_id = 80 THEN
   ...
   END LOOP; -- implicit close occurs
END;

比如 dbms_output.put_line(func(...));

游标带参数

直接返回和参数相关的查询结果

 DECLARE
    CURSOR emp_cursor
       (p_deptno NUMBER, p_job VARCHAR2) IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = p_deptno
        AND job_id = p_job;
BEGIN
    OPEN emp_cursor (80, 'SA_REP');
    . . .
    CLOSE emp_cursor;
    OPEN emp_cursor (60, 'IT_PROG');
    . . .
END;

如果函数里面采用自治事务,是可以有DML 的。

在游标中使用FOR UPDATE NOWAIT

有的时候我们打开一个游标是为了更新或者删除一些记录,这种情况下我们希望
在打开游标的时候即锁定相关记录,应该使用for update nowait语句,倘若锁定失败我们就停止不再继续,以免出现长时间等待资源的死锁情况。

举例:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, last_name, department_name
        FROM employees,departments
        WHERE employees.department_id =
              departments.department_id
        AND employees.department_id = 80
        FOR UPDATE OF salary NOWAIT; 

什么是“自治事务”:

PLSQL中的异常

PLSQL中一般有两种异常,一种是由Oracle内部错误抛出的异常,分为预定义和非预定义两种;另外一种是由程序员显式的抛出。

自治事务是可以在其他事务中调用的独立事务。

常见的异常:

异常代码 异常类型
NO_DATA_FOUND 没有发现数据
INVALID_CURSOR 游标在被打开以前,引用%NOTFOUND属性会产生此异常
TOO_MANY_ROWS 返回数据过多
ZERO_DIVIDE 除数为零,出现异常
DUP_VAL_ON_INDEX 唯一索引上有重复值

自治事务可以使事务离开调用事务的上下文执行SQL操作、提交或回滚其他操作并返回到调用事务的上下文然后继续调用事务。

对othres的处理

others表明我们未能预计的错误,所以全部归到othres中去,单发生这种情况时,我们希望了解错误号和相关信息,可以使用Oracle内置的函数SQLCODE和SQLERRM来返回错误号和错误描述,举例见下:

DECLARE
    v_error_code NUMBER;
    v_error_message VARCHAR2(255);
BEGIN
    ...
EXCEPTION
    ...
WHEN OTHERS THEN
    ROLLBACK;
    v_error_code := SQLCODE ;
    v_error_message := SQLERRM ;
    INSERT INTO errors
    VALUES(v_error_code, v_error_message);
END;

自治事务调用后,事务完全与调用它的主事务独立。

处理用户自定义异常

此处图片缺失

举例:

DECLARE
    e_invalid_department EXCEPTION;
BEGIN
    UPDATE departments
    SET department_name = &p_department_desc
    WHERE department_id = &p_department_number;
    IF SQL%NOTFOUND THEN
        RAISE e_invalid_department;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_invalid_department THEN
        DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

不会看到任何主事务尚未提交的改变、不会共享主事务的锁或资源。自治事务的改变在自治事务提交后可以被其他事务可见。

传递异常到外层代码块

DECLARE
    . . .
    e_no_rows exception;
    e_integrity exception;
    PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
    FOR c_record IN emp_cursor LOOP
      --内层代码块
      BEGIN
        SELECT ...
        UPDATE ...
        IF SQL%NOTFOUND THEN
            RAISE e_no_rows; --将异常扔到外层代码块中,使用关键字:RAISE
        END IF;
      END;

    END LOOP;
EXCEPTION
    WHEN e_integrity THEN ...
    WHEN e_no_rows THEN ...
END;

自治事务可以调用其他自治事务,嵌套的层数没有限制。

存储过程

本文由宝马娱乐在线发布于网络频道,转载请注明出处:oracle中 procedure(存储过程)和function(函数)本质区别

上一篇:子程序和程序包 下一篇:没有了
猜你喜欢
热门排行
精彩图文