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