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

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

认识存储过程和函数
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,不允许从一个变化的表中读取数据.

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