pl/sql练习

----while循环
declare 
i number(2);
begin
i:=1;
while i<10
  loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;


-----loop循环
declare 
i number(2):=1;
begin
  loop 
    dbms_output.put_line(i);
    i:=i+1;
  exit when i>10;
   end loop;
end;


---for循环
begin
for i in 1..10
  loop  
  dbms_output.put_line(i);
  end loop;
end;


-----------------嵌套循环-------------------------------------
declare


begin 
  for i in 1..10  loop
    
    for j in 1..10
      loop 
        dbms_output.put_line(i||'+++++'||j);
        exit when j=2;
    end loop;  
    
  dbms_output.put_line('I is '|| i);
   end loop;
end;




返回结果多少行？？ 2*10+10=30行


---------------普通显示游标-----------------------------
declare 
cursor c1 is select * from emp;
c_row c1%rowtype;


begin
  open c1;
  loop
    fetch c1 into c_row;
    exit when c1%notfound;
    dbms_output.put_line(c_row.sal||',,,'||c_row.empno||',,,'||c_row.ename);
  end loop;
  
end;


--=------------删除某个用户下的所有表--------------------------
declare
v_sql varchar2(1024);
cursor c1 is select table_name from user_tables; 
v_cur c1%rowtype;
begin
open c1;
loop
   fetch c1 into v_cur;
   exit when c1%notfound;
   v_sql := 'drop table ' ||v_cur.table_name;
   execute immediate v_sql;
   end loop;
   close c1;
end;




--------------------游标for循环-----------------------------
declare 
cursor c2 is select * from emp;
begin
  for c_row in c2
  loop
    dbms_output.put_line(c_row.sal||',,,'||c_row.empno||',,,'||c_row.ename);
  end loop;
end;


--------------------游标例子-------------------------
create table tmp(
 name varchar2(10),
 sal number(10)
 );
 
 
declare 
cursor c3 is select t.ename,t.sal from (select * from emp order by sal desc) t where rownum<=&n;
v_ename emp.ename%type;
v_sal emp.sal%type;


begin
  open c3;
  loop
    fetch c3 into v_ename,v_sal;
    exit when c3%notfound;
    insert into tmp values(v_ename,v_sal);
   commit;
  end loop;
end;

--------------------------高级显示游标-----------------------------------------
declare 
cursor c2(v_deptno emp.deptno%type) is select * from emp where deptno=v_deptno;--高级游标，与过程很像
begin
  for c_row in c2(&n)--输入想要查询的部门编号
  loop
    dbms_output.put_line(c_row.sal||',,,'||c_row.deptno||',,,'||c_row.ename);
  end loop;
end;




declare 
cursor c_row is select * from (select empno,ename,job,sal from emp order by sal desc) where rownum<&n;
begin
for c1 in c_row loop
dbms_output.put_line(c1.empno||':::'||c1.ename||'::::'||c1.job||':::'||c1.sal);
end loop;
end;




declare 
cursor c_row2 is select * from (select empno,ename,job,sal from emp order by sal desc) where rownum<&n;
c2 c_row2%rowtype;
begin
open c_row2;
loop
fetch c_row2 into c2;
exit when c_row2%Notfound;
dbms_output.put_line(c2.empno||':::'||c2.ename||'::::'||c2.job||':::'||c2.sal);
end loop;
end;


-------------------游标 for update与where current of子句的应用--更新30号部门员工的工资
declare 
cursor c3 is select * from emp2  where deptno=30 for update of sal;
begin 
  for c_row in c3
    loop
      update emp2 set sal=c_row.sal/1.2 where current of c3;
      dbms_output.put_line(sql%rowcount);
    end loop;
    commit;
    
 end;
 
----------------------------存储过程----------------------------------------
create or replace procedure up(v_empno emp2.empno%type) 
is
v_sal emp2.sal%type;
v_ename  emp2.ename%type;
v_deptno  emp2.deptno%type;
begin
  select sal,ename,deptno into v_sal,v_ename,v_deptno from emp2 where empno=v_empno;
  if v_sal<2000 then
    update emp2 set sal=v_sal+10000 where empno=v_empno;
    dbms_output.put_line(v_sal||',,,'||v_ename||',,,,'||v_deptno);
  else 
    update emp2  set sal=v_sal+20000 where empno=v_empno;


   dbms_output.put_line(v_sal||',,,'||v_ename||',,,,'||v_deptno);
   end if;
   commit;


end;
--------


declare
v_ename emp2.ename%type;
v_deptno emp2.deptno%type;
begin
up(7900,v_ename,v_deptno);
end;
/
--------------------函数------------------------------------
create or replace function f7(v_empno emp2.empno%type,v_sal out emp2.sal%type) return number is newsal number;
  
begin
  select sal into v_sal from emp2 where empno=v_empno;
  
  if v_sal<800 then 
     newsal:=v_sal+1111;
     return newsal;
     dbms_output.put_line(newsal||'XXXXX');
  else 
     newsal:=v_sal+2222;
     return newsal;
     dbms_output.put_line(newsal||'YYYYY');
  end if;  
end;
 


---- 


select * from emp2;


declare 
v_sal2 emp2.sal%type; --实参的名称可以随便定义，只要类型和上面一样就行
newsal number;


begin
--newsal:=f7(7788,v_sal2); --位子法
newsal:=f7(v_empno=>7788,v_sal=>v_sal2);--名称法 就是传参
 dbms_output.put_line(newsal||'YYYYY');
end;
----------------------------包------------------------------------
-----创建包头
create or replace package mypkg is
  procedure up(v_empno emp2.empno%type,v_ename out emp2.ename%type,v_deptno out emp2.deptno%type);
  function f7(v_empno emp2.empno%type,v_sal out emp2.sal%type) return number;
end;
  
------创建包体
 create or replace package body mypkg is
        procedure up(v_empno emp2.empno%type,v_ename out emp2.ename%type,v_deptno out emp2.deptno%type) is v_sal emp2.sal%type;
        begin
        select sal,ename,deptno into v_sal,v_ename,v_deptno from emp2 where empno=v_empno;
        if v_sal<2000 then
         update emp2 set sal=v_sal+10000 where empno=v_empno;
          dbms_output.put_line(v_sal||',,,'||v_ename||',,,,'||v_deptno);
           else 
            update emp2  set sal=v_sal+20000 where empno=v_empno;


             dbms_output.put_line(v_sal||',,,'||v_ename||',,,,'||v_deptno);
              end if;
               commit;


               end;


function f7(v_empno emp2.empno%type,v_sal out emp2.sal%type) return number is newsal number;
  
    begin
      select sal into v_sal from emp2 where empno=v_empno;
      
      if v_sal<800 then 
         newsal:=v_sal+1111;
         return newsal;
         dbms_output.put_line(newsal||'XXXXX');
      else 
         newsal:=v_sal+2222;
         return newsal;
         dbms_output.put_line(newsal||'YYYYY');
      end if;  
    end;
 


end;


-------包的调用-----




declare
v_ename emp2.ename%type;
v_deptno emp2.deptno%type;
begin
mypkg.up(7900,v_ename,v_deptno);
/


declare 
v_sal2 emp2.sal%type; --实参的名称可以随便定义，只要类型和上面一样就行
newsal number;


begin
--newsal:=f7(7788,v_sal2); --位子法
newsal:=mypkg.f7(v_empno=>7788,v_sal=>v_sal2);--名称法 就是传参
 dbms_output.put_line(newsal||'YYYYY');
end;


-----------自定义异常----------
declare
myexce exception;   --自定义一个异常


begin 
delete  from emp2 ;


--if sql%notfound then  --判断语句是否执行了
raise myexce;         --抛出异常
--end if;                
commit;


exception
 when myexce then       ---异常处理部分
 dbms_output.put_line('you can''t delete');
 dbms_output.put_line(sqlcode);  ---异常错误编号
 dbms_output.put_line(sqlerrm);  --异常错误信息
 
end;


-----------异常的传递--------------
declare




 begin 
 
  ----------内嵌异常
          declare 
            begin
              --raise too_many_rows;  --自己处理异常
              --raise no_data_found;  --自己处理不了，抛给外部异常
              -- raise zero_divide;   -内部外部都处理不了，抛给编译器
              exception 
              when too_many_rows then
               dbms_output.put_line('too_many_rows is be handle1');
            
            end;


 
 exception  
    when no_data_found then
    dbms_output.put_line('too_many_rows is be handle2');
  
 end;
--------------------触发器------------------
create table depthist as select * from dept where 1=2;


create or replace trigger trigger_8    ---定义触发器 行级  :old  :new
after                              --时间
insert or update or delete  on dept2   --事件
for each row    --行级
begin    ---触发体


  if inserting then


    dbms_output.put_line('执行insert ');
     insert into depthist values(:new.deptno,:new.dname,:new.loc);
   dbms_output.put_line(:new.deptno||:new.dname||:new.loc|| ' 插入成功 ');


   elsif updating then
           dbms_output.put_line('执行update ');
            insert into depthist values(:new.deptno,:new.dname,:new.loc);
           insert into depthist values(:old.deptno,:old.dname,:old.loc); 


    elsif deleting then  
        dbms_output.put_line('执行delete ');   
            insert into depthist values(:old.deptno,:old.dname,:old.loc); 
               dbms_output.put_line(:old.deptno||:old.dname||:old.loc|| ' 删除成功 ');
  end if;


end trigger_8;


insert into dept2 values(60,'BEIJING','BEIJING');
commit;


delete from dept2 where deptno=50;
commit;


update dept2 set dname=dname||'XX' where deptno=10;
commit;
---------------------------------------------------
------------instead of 触发器-------------------------
create or replace trigger 7

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1467299/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1467299/