罗彭宇.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

一,创建四个游标
    1，从dept表打印出所有的部门名字
    2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
二，建一个触发器，完成一个约束：emp表的薪水sal 不能大于20000
三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
四，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列



一,创建四个游标
1，从dept表打印出所有的部门名字

先在Editor下输入以下内容，然后执行。
declare
     cursor dept_cursor  --声明游标1,命名为dept_cursor。
     is
     select dname from dept;
     
     tempdname dept.dname%type;
     
     cursor dept_cursor2 --声明游标2,命名为dept_cursor2。
     is
     select * from dept;
     
     tempcursor dept_cursor%rowtype;
     
     cursor dept_cursor3 --声明游标3,命名为dept_cursor3。
     is
     select deptno from dept;
     
     cursor dept_cursor4--声明游标4,命名为dept_cursor4。
     is
     select loc from dept;
     
begin      
     open dept_cursor;--打开游标1。
     
     loop
     fetch dept_cursor into tempdname;--提取游标1。
       if dept_cursor%found then
          dbms_output.put_line('部门名称为： '||tempdname);
       else 
          dbms_output.put_line('------------------------------');
          dbms_output.put_line('输出的已经结束');
       exit;
       end if; 
     end loop;
     
     /*loop
     fetch emp_fetch into tempname ;
     dbms_output.put_line(tempname);
     exit when emp_fetch%notfound;  
     end loop;*/
     
     close dept_cursor;--关闭游标1。
End;  


在Dialog下执行步骤和结果如下：
SQL> /
PL/SQL procedure successfully completed

SQL> set serveroutput on;
SQL> /

部门名称为： aaaaaaaa
部门名称为： ACCOUNTING
部门名称为： RESEARCH
部门名称为： SALES
部门名称为： OPERATIONS
部门名称为： pingxin
------------------------------
输出的已经结束

PL/SQL procedure successfully completed





 2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)

（1）
declare
  type dept_record is record 
  ( deptno dept.deptno%type,
    dname dept.dname%type,
    loc dept.loc%type);

cursor dept_cursor1 
return  dept_record
is
  select * from dept;
  tempdept dept_record;
begin
     open dept_cursor1;
     loop
     fetch dept_cursor1 into tempdept;
     if dept_cursor1%found then
     dbms_output.put_line('部门号为:'||tempdept.deptno||'     部门名称为:'||tempdept.dname||'     地点为:'||tempdept.loc);
     else 
          dbms_output.put_line('--------------------------------------------------------------------');
          dbms_output.put_line('输出的已经结束');
     exit;
     end if;
     end loop;
     /*
     for tempdept in dept_cursor1
     loop
     dbms_output.put_line('部门号为:'||tempdept.deptno||'     部门名称为:'||tempdept.dname||'     地点为:'||tempdept.loc);
     end loop;
     */
 end;
     
        
2.(2)
declare
cursor dept_cursor2
return dept%rowtype
is
  select * from dept;
  tempdept dept_cursor2%rowtype;
begin
     open dept_cursor2;
     loop
         fetch dept_cursor2 into tempdept;
         if dept_cursor2%found then
         dbms_output.put_line('部门号为：'||tempdept.deptno||'     部门名称为：'||tempdept.dname||'     工作地点为：'||tempdept.loc);
         else
         dbms_output.put_line('--------------------------------------------------------------------------------');
         dbms_output.put_line('输出已结束');
         exit;
        end if;
     end loop;
     close dept_cursor2;
 end;


SQL> /
PL/SQL procedure successfully completed

SQL> set serveroutput on;
SQL> /
     
        

     
        


二，建一个触发器，完成一个约束：emp表的薪水sal 不能大于20000。


  在Editor下写以下语句：
create or replace trigger emp_saltrigger
before                     --事前触发。
insert or update of sal    
on emp
for each row               --行级触发器。
begin 
case
     when inserting then        --inserting不能加列名。
     if :new.sal>20000 then     --行级触发器提供对列值进行访问。(:new.sal)表示变化后的工资。
     raise_application_error(-20001,'插入的工资不能大于20000',true);
     --调用oracle提供的DBMS--STANDARD程序包中的raise_application_error过程。
     --(true,是一个可选项，用当前的消息增加到错误信息栈中)。
     end if;
     when updating ('sal') then  --updating必须加要修改的字段名('sal')。
     if :new.sal>20000 then 
     raise_application_error(-20002,'更改的值工资不能大于20000',false);
     /*(-20002是自行安排的错误代码，必须在-20000到-20999之间）
       ('更改的值工资不能大于20000',自己定义的消息文本，最长为2048字节)
       (false 默认值,是一个可选项，用当前的消息代替原来的消息)
     */
     end if;
   end case;
end;
       
 
在Dialog会话中，执行以下：
SQL> /

Trigger created

SQL> insert into emp (sal) values(20020); 

insert into emp (sal) values(20020)

ORA-20001: 插入的工资不能大于20000
ORA-06512: 在 "SCOTT.EMP_SALTRIGGER", line 5
ORA-04088: 触发器 'SCOTT.EMP_SALTRIGGER' 执行过程中出错


SQL> update emp set sal=20021 where empno=1234;

update emp set sal=20021 where empno=1234

ORA-20002: 更改的值工资不能大于20000
ORA-06512: 在 "SCOTT.EMP_SALTRIGGER", line 9
ORA-04088: 触发器 'SCOTT.EMP_SALTRIGGER' 执行过程中出错


       

三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生） 

建立lpy_teacher表
create table lpy_teacher(
 tno int,
 name varchar2(10),
 sno int not null,
 cno int not null
 );

给lpy_teacher设主键
alter table lpy_teacher add constraint primary_lpyteacher primary key(tno);

给lpy_student表增加一个字段
alter table lpy_student add tno int;

update lpy_student set tno=1 where sno=1;
update lpy_student set tno=2 where sno=2;
insert into lpy_teacher (tno,name,sno,cno) values(1,'罗彭宇',1,3);
insert into lpy_teacher (tno,name,sno,cno) values(2,'lpy',2,4);


建立触发器
create or replace trigger triggerdel
after               --事后触发。
delete          
on lpy_teacher
for each row        --行级触发器。
begin 
     case
         when deleting then
         delete from lpy_student where tno = :old.tno;
     end case;
end;