oracle存储过程和触发器

re2212

通过系统视图DBA_SOURCE获取存储过程信息
SYS AS SYSDBA@ORCL>SELECT DISTINCT NAME FROM DBA_SOURCE WHERE OWNER='SYS' AND TYPE='PROCEDURE';

NAME
--------------------
VALIDATE_ORDIM


使用CREATE PROCEDURE语句创建过程
CREATE [OR REPLACE] PROCEDURE <过程名>
[<参数列表>] IS | AS
[<局部变量声明>]
BEGIN
    <过程体>
END[<过程名>]

参数声明：
<参数名>[IN|OUT|IN OUT]<参数类型>[:=<初始值>]

SYS AS SYSDBA@ORCL>CREATE OR REPLACE PROCEDURE HR.SETDEPNAME
  2  (V_DEPARTMENT_ID IN NUMBER,V_DEPARTMENT_NAME IN VARCHAR2)
  3  AS
  4  BEGIN
  5  UPDATE HR.DEPARTMENTS SET DEPARTMENT_NAME=V_DEPARTMENT_NAME WHERE DEPARTMENT_ID=V_DEPARTMENT_ID;
  6  END;
  7  /

Procedure created.

初始值       40 Human Resources                203        2400

SYS AS SYSDBA@ORCL>CREATE OR REPLACE PROCEDURE HR.GETDEPAVGWAGE
  2  (V_DEPID IN NUMBER,V_AVGWAGE OUT NUMBER)
  3  AS
  4  BEGIN
  5  SELECT AVG(SALARY) INTO V_AVGWAGE
  6  FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=V_DEPID;
  7  END;
  8  /
Procedure created.


执行过程：
EXEC[UTE]<过程名> [参数列表]

SYS AS SYSDBA@ORCL>EXEC HR.SETDEPNAME(40,'HR');

SYS AS SYSDBA@ORCL>SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID=40;
DEPARTMENT_ID DEPARTMENT_NAME             MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
       40 HR                    203        2400


删除存储过程：
DROP PROCEDURE [<方案名>.]<过程名>
    DROP PROCEDURE HR.SETDEPNAME;


使用CREATE FUNCTION 语句创建函数
CREATE [OR REPLACE] FUNCTION <函数名>
[<参数列表>]
[RETURN <函数数据类型>] IS|AS
[<局部变量声明>]
BEGIN
    <过程体>
    RETURN<函数值>
END [<过程名>]

SYS AS SYSDBA@ORCL>CREATE FUNCTION HR.GETSALARY
  2  (ID IN HR.EMPLOYEES.EMPLOYEE_ID%TYPE)
  3  RETURN HR.EMPLOYEES.SALARY%TYPE
  4  AS
  5  OUTSALARY HR.EMPLOYEES.SALARY%TYPE;
  6  BEGIN
  7  SELECT SALARY INTO OUTSALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=ID;
  8  RETURN OUTSALARY;
  9  END;
10  /

调用函数:
SYS AS SYSDBA@ORCL>SET SERVEROUTPUT ON;
SYS AS SYSDBA@ORCL>DECLARE
  2  VARSALARY HR.EMPLOYEES.SALARY%TYPE;
  3  BEGIN
  4  VARSALARY:=HR.GETSALARY(100);
  5  DBMS_OUTPUT.PUT_LINE(VARSALARY);
  6  END;
/



删除函数
DROP FUNCTION [<方案名>.]<函数名>

SYS AS SYSDBA@ORCL>drop FUNCTION HR.GETSALARY;

Function dropped.



使用CREATE PACKAGE 语句创建程序包
CREATE [OR REPLACE] PACKAGE <程序包>
IS|AS
[<声明部分>]
END [<程序包名>];

CREATE OR REPLACE PACKAGE HR.MYPACK
IS
PROCEDURE SETDEPNAME
(V_DEPID IN NUMBER,V_DEPNAME IN VARCHAR2);
FUNCTION GETWAGE
(ID IN HR.EMPLOYEES.EMP_ID%TYPE)
RETURN HR.EMPLOYEES.WAGE%TYPE;
END MYPACK;
/


删除程序包
DROP PACKAGE [<方案名>.]<程序包名>
DROP PACKAGE HR.MYPACK;



使用CREATE PACKAGE BODY 语句创建程序包体
CREATE [OR REPLACE] PACKAGE BODY <程序包名>
IS|AS
[<声明部分>]
[<过程体>]
[<函数体>]
[<初始化部分>]
END [<程序包名>]

SYS AS SYSDBA@ORCL>CREATE PACKAGE BODY HR.MYPACK
  2  IS
  3  PROCEDURE SETDEPNAME
  4  (V_DEPARTMENT_ID IN NUMBER,V_DEPARTMENT_NAME IN VARCHAR2)
  5  AS
  6  BEGIN
  7  UPDATE HR.DEPARTMENTS SET DEPARTMENT_NAME=V_DEPARTMENT_NAME WHERE DEPARTMENT_ID=V_DEPARTMENT_ID;
  8  END SETDEPNAME;
  9  FUNCTION GETSALARY
10  (ID IN HR.EMPLOYEES.EMPLOYEE_ID%TYPE)
11  RETURN HR.EMPLOYEES.SALARY%TYPE
12  AS
13  OUTSALARY HR.EMPLOYEES.SALARY%TYPE;
14  BEGIN
15  SELECT SALARY INTO OUTSALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=ID;
16  RETURN OUTSALARY;
17  END GETSALARY;
18  END MYPACK;
19  /

删除程序包体：
DROP PACKAGE BODY [<方案名>.]<程序包体名>

DROP PACKAGE BODY HR.MYPACK;

调用程序包中的过程和函数
<方案名>.<程序包名>.<过程名>

<方案名>.<程序包名>.<函数名>

SET SERVEROUTPUT ON;
DECLARE
VARWAGE HR.EMPLOYEES.SALARY%TYPE;
BEGIN
VARWAGE:=HR.MYPACK.GETSALARY(1);
DBMS_OUTPUT.PUT_LINE(VARWAGE);
END;
/


创建语句触发器
CREATE [OR REPLACE] TRIGGER <触发器名>
[BEFORE|AFTER]<触发事件>ON<表名>
<PL/SQL程序体>

SYS AS SYSDBA@ORCL>CREATE OR REPLACE TRIGGER HR.LOGUPDATETRIGGER
  2  AFTER INSERT OR UPDATE OR DELETE
  3  ON HR.TEST
  4  DECLARE LOG_ACTION VARCHAR2(50);
  5  BEGIN
  6  IF INSERTING THEN LOG_ACTION:='INSERT';
  7  ELSIF UPDATING THEN LOG_ACTION:='UPDATE';
  8  ELSIF DELETING THEN LOG_ACTION:='DELETE';
  9  ELSE DBMS_OUTPUT.PUT_LINE('...');
10  END IF;
11  INSERT INTO HR.LOGTABLE(LOG_DATE,ACTION) VALUES(SYSDATE,LOG_ACTION);
12  END;
13  /

SYS AS SYSDBA@ORCL>SELECT * FROM HR.LOGTABLE;

LOG_DATE  ACTION
--------- --------------------------------------------------
23-DEC-14 INSERT
23-DEC-14 UPDATE
23-DEC-14 INSERT
23-DEC-14 DELETE



创建行触发器(在受影响的每一行上执行) 两个表的关连列的列名要一样
CREATE [OR REPLACE] TRIGGER <触发器名>
[BEFORE|AFTER]<触发事件>ON<表名>
FOR EACH ROW    //表示当前触发器为行触发器
<PL/SQL程序体>

SYS AS SYSDBA@ORCL>CREATE OR REPLACE TRIGGER HR.MYTRIGGER
  2  AFTER UPDATE ON HR.DEPARTMENTS
  3  FOR EACH ROW
  4  BEGIN
  5  UPDATE HR.EMPLOYEES SET DEPARTMENT_ID=:NEW.DEPARTMENT_ID WHERE DEPARTMENT_ID=:OLD.DEPARTMENT_ID;
  6  END;
  7  /

UPDATE HR.DEPARTMENTS SET DEPARTMENT_ID=11 WHERE DEPARTMENT_ID=10;


创建INSTEAD OF 触发器（可以把对视图的修改应用到视图的基表上）
CREATE [OR REPLACE ] TRIGGER <触发器名>
INSTEAD OF <触发事件> ON <表名>
<PL/SQL程序体>
   
CREATE VIEW HR.TEST_EMPDEP
SELECT E.EMPLOYEE_ID,E.FIRST_NAME,E.SALARY,D.DEPARTMENT_NAME,D.DEPARTMENT_ID FROM HR.TEST E,HR.TEST1 D WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

SYS AS SYSDBA@ORCL>CREATE OR REPLACE TRIGGER HR.MYINSTEADOFTRIGGER
  2  INSTEAD OF UPDATE ON HR.TEST_EMPDEP
  3  BEGIN
  4  UPDATE HR.TEST SET FIRST_NAME=:NEW.FIRST_NAME,SALARY=:NEW.SALARY WHERE EMPLOYEE_ID=:OLD.EMPLOYEE_ID;
  5  UPDATE HR.TEST1 SET DEPARTMENT_NAME=:NEW.DEPARTMENT_NAME WHERE DEPARTMENT_ID=:OLD.DEPARTMENT_ID;
  6  END;
  7  /



创建LOGON和LOGOFF触发器
LOGON触发器在用户登录数据库时被触发，LOGOFF触发器则在用户注销时被触发,可以用来记录用户登录数据库的情况记录在一个日志文件中

SYS AS SYSDBA@ORCL>DESC HR.TEST_USERS_LOG;
Name                                   Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
USERNAME                                    VARCHAR2(50)
ACTIVITY                                    VARCHAR2(50)
EVENTDATE                                    DATE


SYS AS SYSDBA@ORCL>CREATE OR REPLACE TRIGGER HR.MYLOGONTRIGGER
  2  AFTER LOGON
  3  ON HR.SCHEMA
  4  BEGIN
  5  INSERT INTO HR.TEST_USERS_LOG VALUES(USER,'LOGON',SYSDATE);
  6  END;
  7  /


SYS AS SYSDBA@ORCL>CREATE OR REPLACE TRIGGER HR.MYLOGOFFTRIGGER
  2  BEFORE LOGOFF
  3  ON HR.SCHEMA
  4  BEGIN
  5  INSERT INTO HR.TEST_USERS_LOG VALUES(USER,'LOGOFF',SYSDATE);
  6  END;
  7  /

Trigger created.


SYS AS SYSDBA@ORCL>SELECT * FROM HR.TEST_USERS_LOG;

USERNAME                       ACTIVITY                          EVENTDATE
-------------------------------------------------- -------------------------------------------------- ---------
HR                           LOGON                          23-DEC-14
HR                           LOGOFF                          23-DEC-14

2 rows selected.


启用和禁用触发器
ALTER TRIGGER <方案名.><触发器名> DISABLE;

ALTER TRIGGER HR.MYLOGOFFTRIGGER DISABLE;

ALTER TRIGGER <方案名.><触发器名> ENABLE;

ALTER TRIGGER HR.MYLOGOFFTRIGGER ENABLE;

禁用开启表上面的所有的触发器：
ALTER TABLE  <方案名.><表名> DISABLE|ENABLE ALL TRIGGERS;
ALTER TABLE HR.TEST DISABLE ALL TRIGGERS;


查看编译触发器的错误信息：
SELECT * FROM DBA_ERRORS WHERE TYPE='TRIGGER'

重新编译触发器：
ALTER TRIGGER HR.MYTRIGGER COMPILE

删除触发器
DROP TRIGGER <方案名.><触发器名>