1、游标
ALTER TABLE "SYSTEM"."STUDENT" RENAME COLUMN "ID" TO "SID"
set serveroutput on size 1000000;
DECLARE
v_SID student.sid%TYPE;
v_NAME student.name%TYPE;
v_SEX student.sex%TYPE:=1;
CURSOR c_Students IS
SELECT sid, name FROM student WHERE sex= v_SEX;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_SID, v_NAME;
EXIT WHEN c_Students%NOTFOUND;
DBMS_OUTPUT.PUT(v_SID);
DBMS_OUTPUT.PUT('---------');
DBMS_OUTPUT.PUT_LINE(v_NAME);
END LOOP;
CLOSE c_Students;
END;
/
set serveroutput on size 1000000;
DECLARE
v_NAME student.name%TYPE;
v_SEX student.sex%TYPE;
CURSOR c_Students IS
SELECT name,sex FROM student;
BEGIN
OPEN c_Students;
LOOP
FETCH c_Students INTO v_NAME,v_SEX;
EXIT WHEN c_Students%NOTFOUND;
DBMS_OUTPUT.PUT(v_NAME);
DBMS_OUTPUT.PUT(',');
IF v_SEX=1 THEN
DBMS_OUTPUT.PUT_LINE('男');
ELSE
DBMS_OUTPUT.PUT_LINE('女');
END IF;
END LOOP;
CLOSE c_Students;
END;
/
2、触发器
2、1 简单使用
CREATE OR REPLACE TRIGGER OperatingStu
AFTER INSERT OR DELETE OR UPDATE ON student
BEGIN
DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/
insert into student(SID,name) values('000100','Jim');
update student set name=upper(name);
2、2详细定义
DML触发器的定义有三个要点
1）可以对三种DML操作进行定义，如insert、update和delete
2）可以指定两个时机，如after和before
3）可以指定行级或者语句级，默认为语句级，如为行级，则每条操作记录都会触发一次触发器，如：
CREATE OR REPLACE TRIGGER OperatingStu
AFTER INSERT OR DELETE OR UPDATE ON student
for each row
BEGIN
DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/
此时更新多条记录会导致多次触发，如：
update student set name=upper(name);
甚至可以指定触发条件
CREATE OR REPLACE TRIGGER OperatingStu
AFTER INSERT OR DELETE OR UPDATE ON student
for each row
when (new.sex='1')
BEGIN
DBMS_OUTPUT.PUT_LINE('OK!');
END OperatingStu;
/
update student set sex=1;
update student set sex=0;
2、3 :new表和:old表
每次插入记录，都会将新插入的记录存入:new表；
每次删除记录，都会将被删除的记录存入:old表；
每次更新记录，都会将旧记录存入:old表，再把新记录存入:new表。
例1：查看被删除的记录：
CREATE OR REPLACE TRIGGER OperatingStu
AFTER DELETE ON student
for each row
BEGIN
DBMS_OUTPUT.PUT_LINE(:old.name);
END OperatingStu;
/
delete from student where height<=1.75
delete from student where height>1.75
例2：查看被插入的记录：
CREATE OR REPLACE TRIGGER OperatingStu
AFTER INSERT ON student
for each row
BEGIN
DBMS_OUTPUT.PUT_LINE(:new.name);
END OperatingStu;
/
insert into student (sid,name,sex,birthday,height) values ( '000001','黎明',1,TO_DATE('15-4月 -1980','dd-Mon-
yyyy'),1.78);
insert into student (sid,name,sex,birthday,height) values ( '000002','赵怡春',0,TO_DATE('17-12月 -1982','dd-Mon-
yyyy'),1.77);
insert into student (sid,name,sex,birthday,height) values ( '000003','张富平',1,TO_DATE('1-2月 -1981','dd-Mon-
yyyy'),1.80);
insert into student (sid,name,sex,birthday,height) values ( '000004','白丽',0,TO_DATE('20-11月 -1986','dd-Mon-
yyyy'),1.73);
insert into student (sid,name,sex,birthday,height) values ( '000005','牛玉德',1,TO_DATE('1-5月 -1983','dd-Mon-
yyyy'),1.74);
insert into student (sid,name,sex,birthday,height) values ( '000006','姚华',0,TO_DATE('12-5月 -1983','dd-Mon-
yyyy'),1.77);
例3：查看更新前和更新后的记录：
CREATE OR REPLACE TRIGGER OperatingStu
AFTER UPDATE ON student
for each row
BEGIN
DBMS_OUTPUT.PUT_LINE(:old.name || '->' || :new.name);
END OperatingStu;
/
update student set name=substr(name,1,1);
例4：将删除记录自动备份（回收站）
CREATE TABLE stubak as select * from student where 1=0
CREATE OR REPLACE TRIGGER OperatingStu
AFTER DELETE ON student
for each row
BEGIN
INSERT INTO stubak values (:old."SID",:old.name,:old.sex,:old.birthday,:old.height);
END OperatingStu;
/
delete from student where sex=1
select * from stubak
例5：防止删除全部记录，当用户删除全部记录时，操作无效
CREATE OR REPLACE TRIGGER OperatingStu
AFTER DELETE ON student
declare
scount number;
BEGIN
select count(*) into scount from student;
if scount=0 then
RAISE_APPLICATION_ERROR(-20000, 'forbidden');
end if;
exception
when others then
raise_application_error(-20000,'forbidden');
END OperatingStu;
/
select * from student
delete from student
select * from student
delete from student where sex=1