Oracle过程，函数，包和触发器

ozqmteyesc

第十章 过程,函数,包和触发器

认识存储过程和函数
CREATE OR REPLACE PROCEDURE TEST
  (EMP_NO EMP.EMPNO%TYPE,
   SALARY EMP.SAL%TYPE)
IS(/AS)
  EMP_NAME EMP.ENAME%TYPE;
BEGIN
  UPDATE EMP SET SAL = SALARY WHERE EMPNO = EMP_NO;
  COMMIT;
  SELECT ENAME INT EMP_NAME FROM EMP WHERE EMPNO = EMP_NO;
  DBMS_OUTPUT.PUT_LINE(EMP_NAME);
END TEST;

查看过程:
DESC USER_OBJECTS;
SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME='TEST';
SELECT TEXT FROM USER_SOURCE WHERE NAME='TEST' ORDER BY LINE;
SPOOL C:/TEST.SQL;
SPOOL OFF;

执行方法:
1, EXECUTE TEST(7788,3300);
2,
BEGIN
  TEST(7788, 3500);
END;

查看错误:
SELECT * FROM USER_ERRORS;

删除过程:
DROP PROCEDURE XXX

修改过程,重新编译:
ALTER PROCEDURE XX COMPILE;

形式参数三种模式:IN,OUT,IN OUT.
OUT只可能出现在赋值语句的左边.
也可出现在INTO后.还可输出.

当有OUT参数时的调用方式(只能通过块来调用):
DECLARE
EN EMP.ENAME%TYPE;
BEGIN
EN := 'SCOTT';
TEST1(7788,3500, EN);
DBMS_OUTPUT.PUT_LINE(EN);
END;
-----------------------------
函数:
CREATE OR REPLACE FUNCTION FIND_NUM
  RETURN NUBMER
IS
  NUM NUMBER(5);
BEGIN
  SELECT COUNT(*) INTO NUM FROM EMP;
  RETURN NUM;
END FIND_NUM;

函数可以赋给一个变量,也可直接输出.

DESC XX;
可以直接查看过程与函数的参数列表.

过程与函数异常处理与Java类似.

过程与函数的安全性:
可以对权限进行灵活控制.
GRANT EXECUTE ON XXX TO SCOTT;

SELECT * FROM USER_DEPENDENCES;

===============================
包(PACKAGE)
包说明是操作接口.
CREATE OR REPLACE PACKAGE P1
AS
PROCUDEURE UPDATE_EMP(
EMP_NO EMP.EMPNO%TYPE,);
FUNCTION ....
TYPE CUR IS REF CURSOR;
PROCEDURE FIND_ALL(EMPLIST OUT CUR);
END P1;

使用系统包来操纵文件:
GRANT CREATE ANY DIRECTORY TO SCOTT;
CREATE DIRECTORY ABC AS 'C:/TMPFILE';
DESC DBA_DIRECTORIES;
SELECT * FROM DBA_DIRECTORIES;

CREATE OR REPLACE PROCEDURE GENERATE
  (
  DIRNAME VARCHAR2,
  FILENAME VARCHAR2,
  OBJECTNAME VARCHAR2
  )
AS
  CURSOR FILECURSOR IS SELECT TEXT FROM USER_SOURCE WHERE NAME=OBJECTNAME ORDER BY LINE;
  FILEID UTL_FILE.FILE_TYPE;
BEGIN
  FILEID = UTL_FILE.FOPEN(DIRNAME, FILENAME,'W');
  FOR LINES IN FILECURSOR LOOP
    UTL_FILE.PUT(FILEID,LINES.TEXT);
  END LOOP;
  UTL_FILE.FCLOSE(FILEID);
END;

=================================
触发器
语句级触发器
CREATE OR REPLACE TRIGGER TR1
  BEFORE INSERT OR UPDATE ON EMP
  FOR EACH ROW
DECLARE
  EMPNAME EMP.ENAME%TYPE;
BEGIN
  IF INSERTING THEN
    DBMS_OUTPUT.PUT_LINE(:NEW.ENAME);
    INSERT INTO EMP_LOG VALUES(:NEW.EMPNO,'INSERT'SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO EMP_LOG VALUES(:OLD.EMPNO,'UPDATE'SYSDATE);
    DBMS_OUTPUT.PUT_LINE(:NEW.ENAME);
  END IF;
END;

限制一周内插入数据的时间
CREATE OR REPLACE TRIGGER SECURE_EMP
BEFORE INSERT ON EMP
DECLARE
  V_DUMMY VARCHAR2(1);
BEGIN
  IF(TO_CHAR(SYSDATE,'DY')
......

行级触发器
CREATE OR REPLACE TRIGGER TR3
  BEFORE INSERT ON EMP
  FOR EACH ROW
DECLARE
  EX EXCEPTION;
BEGIN
  IF :NEW.SAL > 10000 THEN
    RAISE EX;
  END IF;
  EXCEPTION
    WHEN EX THEN
      DBMS_OUTPUT.PUT_LINE('TOO MUCH MONEY');
END;

触发器的触是按照创建的先后顺序触发的.
在数据上的操作,遇到系统异常会全部回滚.

INSTEAD OF触发器
用于操作视图的插入,修改,删除
create or replace trigger xxx
instead of insert on employee_names
begin
....
end;

触发器的管理
alter trigger ..
alter trigger ..
enable,disable

* 触发器每一次触发时都会重新编译.

user_triggers 数据字典查看系统有哪些触发器.

在触发器中进行的数据操作:
1,修改没有关联的表的数据.
2,修改被触发表的不同列数.
3,禁止修改表中主键值.
7,不允许从一个变化的表中读取数据.

触发器常见应用:
安全性
审计