创建触发器,以下代码演示了插入或者修改 employees2 表中的first_name 如果等于 ‘chen’时触发器就会执行:
create or replace trigger tri_employees2
before insert or update of first_name
on employees2
referencing NEW as newdata OLD as olddata
for each row
when (newdata.first_name=''chen'')
begin
:newdata.salary :=20000;
dbms_output.put_line(''new.salary:'' || :newdata.salary);
dbms_output.put_line(''old.salary:'' || :olddata.salary);
end;
执行以上触发器:
insert into employees2 values(38,''SUP'',''WOR'',''chen'',''mp'',50000);
或者:
update employees2 set salary=90000,first_name=''chen'' where employee_id=38;
以下代码演示了行级触发器:
创建表:
drop table rowtable;
create table rowtable (id number(8) , name varchar2(100));
创建序列
create sequence rowtablesequence;
创建触发器:
create or replace trigger set_sequence
before insert on rowtable
for each row
declare
rsequence number(8);
begin
select rowtablesequence.nextval into rsequence from dual;
:NEW.id :=rsequence;
end;
/
执行SQL语句:
insert into rowtable values(232,''scott'');
语句级别触发器
创建表:
create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10));
创建触发
create or replace trigger tri_mylog
after insert or delete or update on employees2
begin
if inserting then
insert into mylog values(user,sysdate,''insert'');
elsif deleting then
insert into mylog values(user,sysdate,''delete'');
else
insert into mylog values(user,sysdate,''update'');
end if;
end;
/
INSTEAD OF 触发器
INSTEAD OF 触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器。
以下代码创建了视图:
create view employee_job as select e.job_id,e.employee_id,e.first_name,e.last_name,j.name from employees2 e,jobs j where e.job_id = j.job_id
以下代码创建 INSTEAD OF 触发器。
create or replace trigger tri_view
instead of insert on employee_job
for each row
begin
insert into jobs values(:new.job_id,:new.name);
insert into employees2(employee_id,first_name,last_name,job_id) values(:new.employee_id,:new.first_name,:new.last_name,:new.job_id);
end;
/
执行以下语句查看操作:
insert into employee_job values(''OTH'',43,''abc'',''dd'',''OTHER'');
模式触发器:可以在模式级的操作上建立触发器,如:create ,alter,drop,grant,revoke 和truncate 等 DDL语句:
以下示例对用户所删除的所有对象进行日志记录。
1. 创建数据库表:
drop table dropped_obj;
CREATE TABLE dropped_obj
(
obj_name VARCHAR2(30),
obj_type VARCHAR2(20),
drop_date DATE
);
2.创建触发器:
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO dropped_obj
VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/
3.创建和删除对象:
创建对象:CREATE TABLE for_drop ( x CHAR );
删除对象:DROP TABLE for_drop;
4.查看日志表中的信息:
SELECT * FROM dropped_obj;
数据库级别触发器:
创建数据库表:
Create table database_log (startdate date,description varchar2(20));
创建触发器:
create or replace trigger t_database
after startup on database
begin
insert into database_log values(sysdate, ''startup database'');
commit;
end;
起用和禁用触发器:
以下代码演示了禁用biu_emp_deptno 触发器:
ALTER TRIGGER biu_emp_deptno DISABLE;
以下代码演示了启用biu_emp_deptno 触发器:
ALTER TRIGGER biu_emp_deptno enable;
可以使用:
Alter table table_name{enable | disable} all triggers;
禁用或者起用在特定表上建立的所有触发器。
删除触发器:
Drop trigger trigger_name;
查看触发器信息,可以使用user_trigers 数据字典视图。
Desc user_triggers
内置程序包:
DBMS_OUTPUT 程序包允许显示PL/SQL 块和子程序的输出结果。
SET SERVEROUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(''打印三角形'');
FOR i IN 1..9 LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT(''*'');
END LOOP for_j;
DBMS_OUTPUT.NEW_LINE;
END LOOP for_i;
END;
/
DBMS_SQL 允许用户使用动态SQL,构造和执行任意DML或DDL 语句:
connect yyaccp/accp as sysdba;
create procedure anyddl (s1 varchar2) as
cursor1 integer;
begin
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, s1, dbms_sql.v7);
dbms_sql.close_cursor(cursor1);
end;
/
execute anyddl(''create table mytable(id number(8),name varchar2(20))'');
desc mytable;
execute anyddl(''drop table mytable'');
DBMS_RANDOM 用来生成随机数。以下代码演示了产生 10 个1 到 100 的随机数。
DECLARE
l_num NUMBER;
counter NUMBER;
BEGIN
counter:=1;
WHILE counter <= 10
LOOP
l_num := ABS((DBMS_RANDOM.RANDOM MOD 100)) + 1;
DBMS_OUTPUT.PUT_LINE(l_num);
counter := counter + 1;
END LOOP;
END;
/
UTIL_FILE 包用于从PL/SQL 程序中读写操作系统文件:
以下代码把数据写入文件:
在 init.ora 文件中最后行加上: UTL_FILE_DIR = C:\DEVELOP
在控制台中设置: UTL_FILE_DIR = *
connect yyaccp/accp as sysdba;
create or replace procedure write_txtfile( -- 写一个字符串到指定文本文件中
path in varchar2,
name in varchar2,
pstr in varchar2
)
as
l_output utl_file.file_type;
str varchar2(1000);
begin
l_output:=utl_file.fopen(path,name,''a'',2000); -- 每行最大字节数最多为32K bytes
--l_output:=utl_file.fopen(path,name,''w''); -- 每行最大字节数最多为1023 bytes
utl_file.put_line(l_output,pstr);
utl_file.fclose(l_output);
end;
/
execute write_txtfile(''C:\DEVELOP\'',''bfile.txt'','' bfile 写如文件测试'');
读取文件:
create or replace procedure read_txtfile(
path in varchar2, name in varchar2
)
as
l_output utl_file.file_type;
str varchar2(1000);
begin
l_output:=utl_file.fopen(path,name,''r'',2000); -- 每行最大字节数最多为32K bytes
--l_output:=utl_file.fopen(path,name,''r''); -- 每行最大字节数最多为1023 bytes
loop
utl_file.get_line(l_output,str);
dbms_output.put_line(str);
end loop;
exception
when no_data_found then
utl_file.fclose(l_output);
when others then
str:=sqlerrm(sqlcode);
dbms_output.put_line(str);
end;
/
execute read_txtfile(''C:\DEVELOP'',''bfile.txt'');
DBMS_ROWID 获得ROWID 的详细信息:
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := ''object_number :''||to_char(object_number)||'' ''||
''Relative_fno is :''||to_char(relative_fno)||'' ''||
''Block number is :''||to_char(block_number)||'' ''||
''Row number is :''||to_char(row_number);
end;
/
显示 ROWID
select rowid,employee_id from employees2 where employee_id=2;
select get_rowid(''AAAIA/AAKAAAADyAAB'') row_id from dual;
联系客服