朱志明6-11.txt

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

1.从dept表打印出所有的部门名字

declare 
  dept_name dept.dname%type;
  cursor printname
  is
     select dname from dept;
begin
  open printname;
     loop
        fetch printname into dept_name;
        if printname%found then
            dbms_output.put_line('部门的名称是'||dept_name);
        else
            dbms_output.put_line('**************************************');
            dbms_output.put_line('printname:'||'已经处理完结果集了');
            exit;   
        end if;
     end loop;
  close printname;
end;             
******************************************************************************  
    
     部门的名称是aaaaaaaa
     部门的名称是ACCOUNTING
     部门的名称是RESEARCH
     部门的名称是SALES
     部门的名称是OPERATIONS
     部门的名称是pingxin
     **************************************
     printname:已经处理完结果集了
  
2.定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
  
  方式一:
declare
    type deptRe is record(
         dept_deptno dept.deptno%type,
         dept_dname dept.dname%type,
         dept_loc dept.loc%type); 
    v_deptrecord deptRe;                
    cursor deptrecord(v_dept number)
    is
    select * from dept where deptno<=v_dept;
    return deptRe;
begin
    open deptrecord(v_dept =>40);
      loop
        fetch deptrecord into v_deptrecord;
        exit when deptrecord%notfound;
        dbms_output.put_line('部门编号是'||v_deptrecord.deptno||'部门名                              称'||v_deptrecord.dname||'工作地方'||v_deptrecord.loc);
      end loop;  
   close deptrecord;
end; 

  方式二:
declare
   v_name dept.dname%type;
   v_loc dept.loc%type;
   cursor printname2(v_dept number)
is 
   select dname,loc from dept where deptno<=70;
  
begin
   open printname2(70);
     loop
        fetch printname2 into v_name,v_loc;
        exit when printname2%notfound;
          dbms_output.put_line('部门名称'||v_name||'工作位置'||v_loc);
     end loop; 
   close printname2;
end; 

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

create or replace trigger emp_tri
before insert
or update
on emp
for each row
when (new.sal=20000)
begin
     if :old.sal>:new.sal then
        raise_application_error(-20001,'员工的工资不能高于20000');
     end if;
end;                                 
                            
三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
  
 create or replace trigger delete_tri
 after
 delete
 on teacher
 for each row
 begin
    delete from student where id=(select tid from teacher);
 end;     

四，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列

 思路一:

 
create or replace trigger add_tri
after insert
on mytemp
for each row 

begin
  update mytemp set id =(select (max(id)+1) from mytemp)
  where id=null;
end;


 思路二: 

     
create or replace trigger add_tri
after insert
on mytemp
for each row 
declare
  max_id int;
begin
  select max(id) into max_id from mytemp;
  if max_id=0 then
    max_id:=max_id+1;
  end if;
  
end;