PLSql語句學習(二)

plsql中sql語句的運用
select 語句不用遊標必須有into 還必須保證有且只有一條記錄多了沒有都不行
declare
         v_ename emp.ename %type;
         v_sal emp.sal %type;
begin
         select ename,sal into v_ename,v_sal from emp where empno=9999;
         dbms_output.put_line(v_ename||''||v_sal);
end;

declare
          v_deptno emp2.deptno%type :=50;
begin
          update emp2 set sal=sal/2 where deptno=v_deptno;
          dbms_output.put_line(sql%rowcount||'條記錄被影響');
commit;
end;
--這裡sql是關鍵字代表剛被執行的SQL語句%rowcount是SQL的屬性工作表示影響了多少條記錄

------------DDL語句如建表賦許可權 immediate立即的..
begin
         EXECUTE IMMEDIATE 'create table t(nnn varchar(20) default ''aa'')';
end;

注意:DDL語句要加 execute immediate就OK了.

-------------------------------------------------------------------------------------
----------------plsql   迴圈.分支.......
--if語句
---取出7369的薪水,如果<1200 則輸出'low' 如果<2000則輸出'middle' 否則'high'
declare
       v_sal emp.sal%type;
begin
       select sal into v_sal from emp where empno=7369;
       if(v_sal<1200) then
          dbms_output.put_line('low');
       elsif(v_sal<2000) then---注意是elsif  不是else if
             dbms_output.put_line('middle');
       else dbms_output.put_line('high');---else後沒有then
       end if;
end;

-----迴圈---------------------------------------
--do while迴圈
declare
         i binary_integer:=1;
begin
        loop
              dbms_output.put_line(i);
              i:=i+1;
        exit when(i>=11);
        end loop;
end;
-----while迴圈
 
declare
          i binary_integer:=1;
begin
          while i<11 loop
          dbms_output.put_line(i);
          i:=i+1;
          end loop;
end;

---------for 迴圈
begin
        for k in 1..10 loop
             dbms_output.put_line(i);
        end loop;

----從10到1逆序
        for k in reverse 1..10 loop
            dbms_output.put_line(i);
        end loop;
end;

----------錯誤處理
declare
          v_temp number(4);
begin
           select cmpno into v_temp from emp where deptno=10;--deptno=10會返回多條記錄
           exception
              when too_many_rows then----返回多條記錄的異常名字是too_many_rows
                        dbms_output.put_line('太多記錄了');
              when others then
                        dbms_output.put_line('error');
end;

declare
          v_temp number(4);
begin
          select cmpno into v_temp from emp where deptno=10;--deptno=10會返回多條記錄
exception
               when no_data_found then---沒有找到資料異常
               dbms_output.put_line('no data');
end;

----以下介紹DBA通常記錄錯誤的做法 先建立一個錯誤記錄檔表
create table errorlog
(
   id number primary key,
   errcode number,---出錯代碼
   errmeg varchar2(1024),--出錯資訊
   errdate date--出錯時間
);
--主鍵自動遞增
create sequence seq_errorlog_id start 1 increment by 1;

-----------
declare
          v_teptno dept.deptno%type:=10;
          v_errcode number;
          v_errmsg varchar2(1024);
begin
        delete from dept where deptno=v_deptno;
         commit;
exception
             when others then
             rollback;
             v_errcode:=SQLCODE;---SQLCODE是oracle關鍵字 表示出錯代碼
             v_errmsg:=SQLERRM;---SQLERRM是oracle關鍵字 表示出錯資訊
             insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errrmsg,sysdate);
             commit;
end;

 
-----------cursor 遊標是一個指標 指向一個結果集就象迭代器 最開始指向結果集的top
declare
cursor c is
               select * from emp;---申明一個遊標 這時候ORACLE並不執行SELECT 只是申明
               v_emp c%rowtype;
begin
        open c;--開啟遊標ORACLE才執行SELECT
        fetch c into v_emp;--從遊標拿出一條資料遊標自動往下移一格.. fetch取出的意思
        dbms_output.put_line(v_emp.ename);
        close c;
end;

declare
          cursor c is
                        select * from emp;
           v_emp c%rowtype;
begin
        open c;
        loop
               fetch c into v_emp;
        exit when (c%notfound);
               dbms_output.put_line(v_emp.ename);
        end loop;
        close c;
end;

----while 迴圈
declare
          cursor c is
                         select * from emp;
          v_emp c%rowtype;
begin
          open c;
                    fetch c into v_emp;
          while (c%found) loop
                    dbms_output.put_line(v_emp.ename);
          end loop;
          close c;
end;
------------for 迴圈 不需要 定義v_emp 也不需要開啟關閉遊標 都是系統自動幫你做所以FOR迴圈最簡單
declare
          cursor c is
                        select * from emp;
begin
          for v_emp in c loop
               dbms_output.put_line(v_emp.ename);
          end loop;
end;

------------帶參數的遊標

declare
          cursor c(v_dempno emp.deptno%type,v_job emp.job%type)
          is
             select ename,sal from emp where deptno=v_dempno and job=v_job;
begin
          for v_emp in c(30,'clerk') loop
               dbms_output.put_line(v_emp.ename);
          end loop;
end;

-----可更新的遊標 一般遊標是在SELECT中遍曆結果集,有一種不常用的遊標
----- 可做修改刪除等操作... 關鍵字 forupdate->為了更新 current of c->當前遊標所在這行
declare
          cursor c is
                         select * from emp;
          v_emp c%rowtype;
begin
          for v_temp in c loop
              if(v_temp.sal<2000) then
                     update emp2 set sal=sal*2 where current of c;
              elsif (v_temp.sal=5000) then----=在這裡是等號賦值是:=
                     delete from emp2 where current of c;
              end if;
          end loop;
          commit;
end;

----------------預存程序和一般的PLSQL塊的區別就是------------------------
---create or replace procedure p is 代替了declare

create or replace procedure p
is
       cursor c is
                      select * from emp2 for update;
begin
       for v_emp in c loop
          if(v_emp.deptno=10) then
                update emp2 set sal =sal+10 where current of c;
          elsif (v_emp.deptno=10) then
                update emp2 set sal =sal+20 where current of c;
          else
                update emp2 set sal =sal+40 where current of c;
          end if;
       end loop;
             commit;
end;
--執行過程有2個辦法
--1 exec p;
---2 begin
p;
end;
-------------帶參數的預存程序 參數類型 in 傳入 out 傳出 in out 傳入傳出  不寫的話預設是in
create or replace procedure p
(v_a in number,v_b number,v_ret out number,v_temp in out number)
is
begin
         if(v_a>v_b) then
               v_ret:=v_b;--把傳入的值賦給傳出值v_ret
         else
               v_ret:=v_b;
         end if;
               v_temp:=v_temp+1;--v_temp即是傳入又是傳出 所以可以自己給自己賦值
end;
---調用
declare
          v_a number:=3;
          v_b number:=4;
          v_ret number;
          v_temp number:=5;
begin
          p(v_a,v_b,v_ret,v_temp);
          dbms_output.put_line(v_ret);
          dbms_output.put_line(v_temp);
end;

---注意建立預存程序出錯系統不會告訴你哪裡錯了只會警告:建立的過程帶有編譯錯誤
--如果想知道出錯位置和原因 輸入命令:show error

----------------function 必須有傳回值--------
create or replace function sal_tax
(v_sal number)  return number
is
begin
        if(v_sal<2000) then
            return 0.1;
        elsif(v_sal<2750) then
            return 0.15;
        else
            return 0.20;
        end if;
end;

--調用函數
select sal_tax(sal) from emp;

-----------------觸發器  觸發器不能單獨執行必須依附在某張表上
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);-----此表是用來記錄的即用觸發器來記錄表

create or replace trigger trig
after insert or delete or update on emp2 for each row--當在emp2這張表上插入更新刪除時候會觸發此觸發器
--這裡狀態有after或berore 表示動作之後或之前
begin
        if inserting then--inserting關鍵字代表當前正在插入操作
               insert into emp2 values(USER,'insert',sysdate);--USER是關鍵字代表目前使用者是誰
        elsif updating then--updating關鍵字代表當前正在更新操作
               insert into emp2 values(USER,'update',sysdate);
        elsif deleting then--deleting關鍵字代表當前正在刪除操作
               insert into emp2 values(USER,'delete',sysdate);
        end if;
end;
--注意for each row 是每一行都觸發觸發器比如更新6行觸發6次觸發器
--如果不加for each row 更新6行只觸發1次觸發器
--使用觸發器 此觸發器是在emp2這張表上插入更新刪除時候會觸發
update emp2 set sal=sal*2 where deptno=30;

--------------觸發器的副作用
如:update dept set deptno=99  where deptno=10;
這個SQL語句是不能執行的 但是觸發器可以實現

create or replace trigger trig
after update on dept for each row
begin
update emp set deptno=:NEW.deptno where deptno =:OLD.deptno;
end;

----
由此得知先觸發觸發器後檢查完整性條件約束

 

-----------樹狀結構的儲存與展示
就是字典表 有父ID
create table article
(
id number primary key,
cont varchar2(4000),
pid number,---父ID
isleal number(1),--0代表分葉節點 1代表子節點
vlevel number(2)---表示在哪一層
);

insert into article values(1,'螞蟻大站大象',0,0,0);
insert into article values(2,'大象被大怕下',1,0,1);
insert into article values(3,'螞蟻也不好過',2,1,2);
insert into article values(4,'亂說',2,0,2);
insert into article values(5,'沒有亂說',4,1,3);
insert into article values(6,'怎麼可能',1,0,1);
insert into article values(7,'怎麼不可能',6,1,2);
insert into article values(8,'可能性很大',6,1,2);
insert into article values(9,'大象死了',2,0,2);
insert into article values(10,'螞蟻士大夫',9,1,3);
-----用預存程序調用自己來實現遞迴樹

create or replace procedure p (v_pid article.pid%type,v_level binary_integer)
is
cursor c is select * from article where pid=v_pid;
v_prestr varchar2(1024) :='';
begin
for i in 1..v_level loop
v_prestr:=v_prestr||'********';
end loop;

for v_article in c loop
dbms_output.put_line(v_prestr||v_article.cont);
if(v_article.isleal=0) then
p(v_article.id,v_level+1);
end if;
end loop;
end;

執行:exec p(0,0);