通过系统视图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 <方案名.><触发器名>
原文：http://rscpass.blog.51cto.com/771159/1612751