主题：[推荐]关于Oracle中SQL语法的部分整理资料之三

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