oracle 存储过程与触发器

流云薇眠

不留痕迹

存储过程是一组为了完成特定功能的sql语句，存储在数据库中，数据库开发人员经常会用到存储过程，
存储过程能够反复使用，减少开发人员工作量。

为什么要写存储过程

1.效率高
2.降低网络流量
3.复用性高
4.可维护性高
5.安全性高

存储过程示例：

CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
-------------------
CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行');

存储过程语法

1:运算符：
算术运算符 + - * /
关系运算符  < >  = 
比较运算符 LIKE  BETWEEN in  is null 
逻辑运算符 and or not 
2:SELECT INTO STATEMENT语句 :
--给多个变量赋值
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
3:选择语句 a.IF..END IF
IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF
------
IF..ELSE..END IF
4:循环语句
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;
------
while 循环
WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;
for 循环
FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

触发器

触发器（trigger）是数据库提供给程序员和数据分析员来保证数据完整性的一种方法，
它是与表事件相关的特殊的存储过程。由事件触发。
ORACLE触发器有三种类型，分别是：DML触发器、替代触发器和系统触发器。
创建触发器：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

示例：建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 
CREATE OR REPLACE TRIGGER tr_del_emp 
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp 
   FOR EACH ROW   --说明创建的是行级触发器 
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal,
 :old.comm, :old.hiredate );
DELETE emp WHERE empno=7788;

限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE 
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI')
 NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');
 END IF;
END;

存储过程：示例

CREATE OR REPLACE PROCEDURE P_T_AS_D_ITEM_DATA_TB_D_TEST1 AS
  V_ITEM1         VARCHAR2(50); ---时间当前
  V_ITEM2         VARCHAR2(50);
  V_TIME_ID       VARCHAR2(30); ---当前时间（换算过后的标准时间）
  V_TIME_ID_MONTH VARCHAR2(30); ---当前月对应的第一天
  V_TIME_ID_YEAR  VARCHAR2(30);
  V_LOB_UNIT      CLOB;
  V_TIME_ID1      VARCHAR2(30);
  REPORT_TYPE     VARCHAR(50);
  REPORT_NAME     VARCHAR(50);
  V_ID            VARCHAR(50);
  V_DATE          VARCHAR2(30);
  CONT1           NUMBER;
  CONT2           NUMBER;
  CONT3           NUMBER;
BEGIN
  V_DATE := TO_CHAR(SYSDATE, 'yyyymmdd');
  V_ITEM1 := V_DATE;
  V_ITEM2 := '6' || V_ITEM1;
  SELECT TO_CHAR(SYSTIMESTAMP, 'yyyymmddhh24missffyymmddhh24miss')
    INTO V_ID
    FROM DUAL;
  ------当前日期
  SELECT SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2)
    INTO V_TIME_ID
    FROM D_PUBLICINFO_TIME S
   WHERE SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2) = V_ITEM1;
  DBMS_OUTPUT.PUT_LINE(V_TIME_ID);
  ------当前日期(长时间)
  SELECT S.TIMEID
    INTO V_TIME_ID1
    FROM D_PUBLICINFO_TIME S
   WHERE SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2) = V_ITEM1;
  ------月的第一天
  SELECT SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2)
    INTO V_TIME_ID_MONTH
    FROM D_PUBLICINFO_TIME S
   WHERE SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2) = SUBSTR(V_ITEM1, 1, 6) || '01';
  DBMS_OUTPUT.PUT_LINE(V_TIME_ID_MONTH);
  ------当前年1月1号
  SELECT SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2)
    INTO V_TIME_ID_YEAR
    FROM D_PUBLICINFO_TIME S
   WHERE SUBSTR(S.TIMEID, 1, 4) || SUBSTR(S.TIMEID, 6, 2) ||
         SUBSTR(S.TIMEID, 10, 2) = SUBSTR(V_ITEM1, 1, 4) || '01' || '01';
  SELECT COUNT(*)
    INTO CONT1
    FROM T_DATA_INDEX@Lygs T
   WHERE T.TIMEID = V_TIME_ID1;
  SELECT COUNT(*)
    INTO CONT2
    FROM T_DATA_INDEX_LYDL T
   WHERE T.TIMEID = V_TIME_ID1;
  SELECT COUNT(*)
    INTO CONT3
    FROM T_AS_D_ITEM_DATA_TB@DBLINK_10_82_1_13_GDMQ T
   WHERE T.ISSUE = '6' || V_DATE;
  SELECT VALUE_TYPE, REPORT_SHORT_NAME
    INTO REPORT_TYPE, REPORT_NAME
    FROM T_ITEM_REPORT_CODE
   WHERE REPORT_CODE = 'S_D_YX_0_SCYXQK_1';
  DBMS_OUTPUT.PUT_LINE(CONT1);
  DBMS_OUTPUT.PUT_LINE(CONT2);
  IF CONT3 >= 15555 THEN
    INSERT INTO CS_1
      (SELECT '已完成', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') FROM DUAL);
    COMMIT;
    IF CONT1 = CONT2 AND CONT1 = 0 THEN
      INSERT INTO CS_1
        (SELECT '未上报数据', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS')
           FROM DUAL);
      COMMIT;
      IF CONT1 != CONT2 THEN
        INSERT INTO CS_1
          (SELECT '数据不全', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS')
             FROM DUAL);
        COMMIT;
        IF CONT2 < 14943 THEN
          INSERT INTO CS_1
            (SELECT '数据不全', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS')
               FROM DUAL);
          COMMIT;
          IF CONT1 = CONT2 THEN
            INSERT INTO CS_1
            VALUES
              ('推送数据', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'));
            COMMIT;
            /* SELECT '0' INTO conte1 FROM dual;
            --sys.dbms_lock.sleep(600);
            WHILE conte1 < '99999999' LOOP
              --sys.dbms_lock.sleep(60);
              SELECT conte1+1 INTO conte1 FROM dual;
              \*INSERT INTO CS_1 VALUES('中间库条数:'||conte1,TO_CHAR(SYSDATE,
'YYYYMMDD HH24:MI:SS'));
               COMMIT;*\
              END LOOP;*/
            --sys.dbms_lock.sleep(600);
             SELECT COUNT (*) INTO cont1 FROM t_data_index t
              WHERE t.timeid = V_TIME_ID1 ;
             SELECT COUNT(*) INTO cont2 FROM t_data_index@lygs t
              WHERE t.timeid = V_TIME_ID1;
            WHILE cont1 != cont2 LOOP
               SELECT COUNT (*) INTO cont1 FROM t_data_index t
                WHERE t.timeid = V_TIME_ID1 ;
                INSERT INTO CS_1 VALUES('中间库条数:'||cont1,TO_CHAR(SYSDATE,
'YYYYMMDD HH24:MI:SS'));
                  COMMIT;
              END LOOP;*/
            ------插入前删除当前数据
            DELETE FROM T_AS_D_ITEM_DATA_TB@DBLINK_10_82_1_13_GDMQ T
             WHERE T.ISSUE = '6' || V_ITEM1;
            COMMIT;
         V_LOB_UNIT := '
          INSERT INTO T_AS_D_ITEM_DATA_TB@DBLINK_10_82_1_13_GDMQ
          SELECT distinct
                 ' || V_ITEM2 ||
                                  ' AS ISSUE,
                 ''AS''|| d.indexid AS DEPT_ID,
                 D.jj,
                 T.ITEMCODE,
                 CASE WHEN T.thisvalue<0 THEN 0 ELSE t.thisvalue END AS ZBZ,
                   '''',
            FROM T_DATA_INDEX_LYDL T,
                 d_baseinfo_resource_xj D
           WHERE SUBSTR(T.TIMEID, 1, 4) || SUBSTR(T.TIMEID, 6, 2) || 
SUBSTR(T.TIMEID, 10, 2)= ' ||
                                  V_TIME_ID || '
                 AND T.TIMETYPEID = ''day''
                 AND T.CALIBERCODE = ''AS01''
                 AND T.RESOURCECODE = D.RESOURCECODE';
            --- DBMS_OUTPUT.PUT_LINE(V_LOB_UNIT);
            EXECUTE IMMEDIATE V_LOB_UNIT;
            COMMIT;
            /*   INSERT INTO t_generalinfo_reportlog_LY@DBLINK_10_82_1_13_GDMQ 
              VALUES
            (V_ID,
            :NEW.TIMEID,
            :NEW.TIMETYPEID,
            :NEW.RESOURCECODE,
            :NEW.REPORTCODE,
             REPORT_TYPE,
             REPORT_NAME,   
            :NEW.REPORTTIME
            COMMIT;*/
            INSERT INTO T_GENERALINFO_REPORTLOG_LY@DBLINK_10_82_1_13_GDMQ T
              (SELECT V_ID,
                      G.TIMEID,
                      G.TIMETYPEID,
                      G.RESOURCECODE,
                      G.REPORTCODE,
                      REPORT_TYPE,
                      REPORT_NAME,
                      G.REPORTTIME
                 FROM T_GENERALINFO_REPORTLOG G
                WHERE G.TIMEID = V_TIME_ID1);
            COMMIT;
            INSERT INTO CS_1
            VALUES
              ('当日数据推送完成', TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'));
            COMMIT;
            NULL;