段湘南.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 56 行

一：
1，从dept表打印出所有的部门名字
declare
   cursor name 
   is
   select dname from dept;
   v_name dept.dname%type;
begin
   open name;
   loop
   fetch name into v_name;
   dbms_output.put_line(v_name);
   exit when name%notfound;
   end loop;
   close name;
end;
2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
declare
  type deptrecord is record(deptno dept.deptno%type,
                         dname dept.dname%type,
                         loc dept.loc%type,
                         deptname dept.deptname%type);
  v_dept deptrecord;
  --v_dept dept%rowtype;
  cursor dept1
  is
  select * from dept;  
begin
  open dept1;
  loop
     fetch dept1 into v_dept;
     dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc||' '||v_dept.deptname);
     exit when dept1%notfound;
  end loop;
  close dept1;
end;
二，建一个触发器，完成一个约束：emp表的薪水sal 不能大于20000
create or replace trigger sal_set
before 
update or insert
on emp
for each row
begin
    if(:new.sal >= 20000) then
    raise_application_error(-20001,'薪水不能超过2万元');
   end if;
end;
三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
create or replace trigger del_teacher
after
delete
on teacher
for each row
begin
   delete from teacher where tid = :new.tid;
   delete from student where lid = :old.tid;