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