Oracle学习之常用对象（二）—触发器&游标&函数

最新推荐文章于 2021-12-02 16:47:21 发布

u011744784

最新推荐文章于 2021-12-02 16:47:21 发布

阅读量930

点赞数 1

分类专栏： Oracle 文章标签： oracle 数据触发器游标函数

本文链接：https://blog.csdn.net/u011744784/article/details/50634553

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

除了表、视图、序列还有触发器、游标等等，我们常用的对象。接下来我们谈谈触发器、游标和函数。

一、触发器

触发器一旦创建就会立刻生效，有时可能需要临时禁用触发器，最常见的原因就是涉及数据加载。

ALTER TRIGGER trigger_name [ENABLE | DISABLE];

查看触发器名称：

SELECT T.OBJECT_NAME

FROM USER_OBJECTS T

WHERE T.OBJECT_TYPE = UPPER('trigger');

查看触发器内容：

SELECT T.* FROM USER_SOURCE T WHERE T.NAME = TRIGGER_NAME;

[注：行级触发器是插入一行数据触发一次，而语句级触发器是插入一次数据[插入一次也可能是一条也可能是多条]触发一次。]

[这里也不一定是插入操作其他的操作同样适用]

1、行级触发器

CREATE TRIGGER TEST_A_TGR

BEFORE INSERT OR UPDATE OR DELETE ON TEST_A

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'INSERT');

ELSIF UPDATING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'UPDATE');

ELSIF DELETING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'DELETE');

END IF;

END;

2、语句级触发器

CREATE TRIGGER TEST_A_TGR

BEFORE INSERT OR UPDATE OR DELETE ON TEST_A

BEGIN

IF INSERTING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'INSERT');

ELSIF UPDATING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'UPDATE');

ELSIF DELETING THEN

INSERT INTO LOG_TAB VALUES (SYSDATE, 'TEST_A', 'DELETE');

END IF;

END;

3、DDL类型触发器

SCHEMA级别是在该用户下有效，database级别是在整个数据库有效。

CREATE TRIGGER DDL_TGR

AFTER CREATE OR ALTER OR DROP OR RENAME OR GRANT OR REVOKE OR TRUNCATE ON SCHEMA

BEGIN

IF SYSEVENT = 'CREATE' THEN

INSERT INTO LOG_TAB (SYSDATE, ORA_DICT_OBJ_NAME, 'CREATE');

ELSIF SYSEVENT = 'DROP' THEN

INSERT INTO LOG_TAB (SYSDATE, ORA_DICT_OBJ_NAME, 'DROP');

ELSIF SYSEVENT = 'ALTER' THEN

INSERT INTO LOG_TAB (SYSDATE, ORA_DICT_OBJ_NAME, 'ALTER');

END IF;

END;

4、用户和系统事件触发器

可以创建在数据库事件上的触发器，包括启动、关闭、服务器错误、登录和注销等。

[注：LOGON触发器在用户登录数据库的时候被触发，LOGOFF触发器在用户注销时被触发]

CREATE TRIGGER LOGON_TGR

AFTER LOGON[OR LOGOFF] ON SCHEMA

BEGIN

INSERT INTO LOG_TAB VALUES (SYSDATE, ORA_LOGIN_USER, 'LOGON');

END;
5、DDL触发器事件以及属性函数

函数名返回值

ORA_CLIENT_IP_ADDRESS 客户端IP地址

ORA_DATABASE_NAME 数据库名称

ORA_DES_ENCRYPTED_PASSWORD 当前用户的DES算法加密后的密码

ORA_DICT_OBJ_NAME 触发DDL的数据库对象名称

ORA_DICT_OBJ_NAME_LIST 受影响的对象数量和名称列表

ORA_DICT_OBJ_OWNER 触发DDL的数据库对象属主

ORA_DICT_OBJ_OWNER_LIST 受影响的对象数量和名称列表

ORA_DICT_OBJ_TYPE 触发DDL的数据库对象类型

ORA_GRANTEE 被授权人数量

ORA_INSTANCE_NUM 数据库实例数量

ORA_IS_ALTER_COLUMN 如果操作的参数column_name指定的列，返回true,否则false

ORA_IS_CREATING_NESTED_TABLE 如果正在创建一个嵌套表则返回true,否则false

ORA_IS_DROP_COLUMN 如果删除的参数column_name指定的列，返回true,否则false

ORA_LOGIN_USER 触发器所在的用户名

ORA_PARTITION_POS SQL命令中可以正确添加分区子句位置

ORA_PRIVILEGE_LIST 授予或者回收的权限的数量。

ORA_REVOKEE 被回收者的数量

ORA_SQL_TXT 触发了触发器的SQL语句的行数。

ORA_SYSEVENT 导致DDL触发器被触发的时间

ORA_WITH_GRANT_OPTION 如果授权带有grant选项，返回true。否则false

二、游标
游标是从表中检索出的结果集中每次指向一条记录进行交互的机制。游标指定结果集中特定行的位置，可以在结果集的当前位置修改行中的数据。不过，
游标效率比较差，尽量避免使用。根据参考书或者别人的经验还有自己工作中的实践，一般我用到的都是静态游标。
静态游标是结果集已经确实（静态定义）的游标。分为隐式和显示游标。隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。
显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
1、隐式游标：
游标的状态通过属性来表示。[注：在存储过程中有时也会用到这些属性还判断。]
%FOUND：Fetch语句（获取记录）执行情况 True or False。
%NOTFOUND: 最后一条记录是否提取出 True or False。
%ISOPEN：游标是否打开 True or False。
%ROWCOUNT：游标当前提取的行数。
我们通过使用属性的例子来解释一下：
例子：如果更新语句成功，定位到这个更新语句的隐式游标属性 SQL%FOUND 就返回真。否则，返回假。
BEGIN
    UPDATE EMP SET SAL = SAL + 0.1 WHERE JOB = 'CLERK';
    IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('已经更新！');
    ELSE
      DBMS_OUTPUT.PUT_LINE('更新失败！');
    END IF;
END;
2、显式游标：
1>
声明游标：
DECLARE
CURSOR TEST_A_CUR IS SELECT * FROM TEST_A;
MYCUR_TEST_A TEST_A%ROWTYPE;--定义变量
--或者
DECLARE
CURSOR TEST_A_CUR IS SELECT B,C FROM TEST_A;
MYCUR_TEST_A_B VARCHAR2(20);
MYCUR_TEST_A_C VARCHAR2(20);--定义两个变量用来接收游标指定位置的两个字段值
打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。
    OPEN TEST_A_CUR;
获取记录：移动游标取一条记录
    FETCH TEST_A_CUR INTO MYCUR_TEST_A;
    或者
    FETCH TEST_A_CUR INTO MYCUR_TEST_A_B,MYCUR_TEST_A_C;
关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。
    CLOSE TEST_A_CUR;
遍历循环游标:
⑴FOR 循环游标
   循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
   FOR V_TEST_A IN TEST_A_CUR
   LOOP
    UPDATE TEST_A SET B=B||C WHERE CURRENT OF TEST_A_CUR;
   END LOOP;
⑵Loop循环游标
    LOOP
     FETCH TEST_A_CUR INTO MYCUR_TEST_A;
     EXIT WHEN TEST_A_CUR%NOTFOUND;
    END LOOP;
我们通过一个例子来看一下：
DECLARE
    CURSOR TEST_A_CUR IS
      SELECT B, C FROM TEST_A;
    MYCUR_TEST_A_B VARCHAR2(20);
    MYCUR_TEST_A_C VARCHAR2(20);
BEGIN
    IF TEST_A_CUR%ISOPEN = FALSE THEN
      OPEN TEST_A_CUR;
    END IF;
    LOOP
      FETCH TEST_A_CUR
        INTO MYCUR_TEST_A_B, MYCUR_TEST_A_C;
      EXIT WHEN TEST_A_CUR%NOTFOUND;
      UPDATE TEST_A SET B = B || C WHERE B = MYCUR_TEST_A_B;
    END LOOP;
    IF TEST_A_CUR%ISOPEN THEN
      CLOSE TEST_A_CUR;
    END IF;
END;

2>
更新和删除显示游标中的记录：
UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。
要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，
所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，
不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作。
在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。
如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。
使用更新或删除：
⑴声明更新或删除显示游标：
   CURSOR TEST_A_CUR IS SELECT B,C FROM TEST_A FOR UPDATE;
   CURSOR TEST_A_CUR IS SELECT B,C FROM TEST_A FOR DELETE;
⑵使用显示游标当前记录来更新或删除：
   UPDATE TEST_A SET B=B|C WHERE CURRENT OF TEST_A_CUR;
   DELETE FROM TEST_A WHERE CURRENT OF TEST_A_CUR;
我们通过例子来看一下如何更新显示游标记录：
DECLARE
    CURSOR TEST_A_CUR IS
      SELECT B, C FROM TEST_A FOR UPDATE;
    V_B TEST_A.B%TYPE;
    V_C TEST_A.C%TYPE;
BEGIN
    OPEN TEST_A_CUR;
    LOOP
      FETCH TEST_A_CUR
        INTO V_B;
      EXIT WHEN TEST_A_CUR%NOTFOUND;
      CASE (V_B)
        WHEN 'A' THEN
          V_C := 'A1';
        WHEN 'B' THEN
          V_C := 'B1';
        WHEN 'C' THEN
          V_C := 'C1';
        ELSE
          V_C := 'T';
      END CASE;
      UPDATE TEST_A SET C = C + V_C WHERE CURRENT OF TEST_A_CUR;
    END LOOP;
END;

3>
带参数的显示游标：
①与过程和函数相似，可以将参数传递给游标并在查询中使用。
参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。
与过程不同的是，游标只能接受传递的值，而不能返回值。
　可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。
游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。
②使用带参数的显示游标
声明带参数的显示游标：
CURSOR TEST_A_CUR [(参数形式)] IS SELECT 字段 FROM TEST_A;
参数形式：1 参数名数据类型
            2 参数名数据类型 DEFAULT 默认值
我们列出一个带参数的例子：
DECLARE
    CURSOR TEST_A_CUR(V_B VARCHAR2(20) DEFAULT 'A') IS
      SELECT B FROM TEST_A;
    V TEST_A_CUR%ROWTYPE;
BEGIN
    LOOP
      FETCH TEST_A_CUR
        INTO V;
      EXIT WHEN TEST_A_CUR%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(TEST_A_CUR%ROWCOUNT || ' ' || V.B);
    END LOOP;
END;

三、函数
    函数的主要特性是它必须返回一个值。创建函数时通过 RETURN 子句指定函数返回值的数据类型。
创建函数：
CREATE OR REPLACE FUNCTION TEST_A_FUNC(A NUMBER) RETURN VARCHAR2 AS
    V1 NUMBER;
    V2 NUMBER;
BEGIN
    SELECT MAX(D), MIN(D) INTO V1, V2 FROM TEST_A;
    IF A >= V2 AND A <= V1 THEN
      RETURN 'IT IS OK!';
    ELSE
      RETURN 'IT IS NOT OK!';
    END IF;
END;
调用函数：
SELECT B, C, D, TEST_A_FUNC(T.D) FROM TEST_A T;
删除函数:
DROP FUNCTION TEST_A_FUNC;