Oracle系列：（28）PLSQL

標籤：oracle

準備篇

col empno for 9999; col ename for a10; col job for a10; col mgr for 9999; col hiredate for a12; col sal for 9999; col comm for 9999; col deptno for 99; col tname for a40; set pagesize 80;

1、SQL對比PLSQL

SQL99是什麼

（1）是操作所有關係型資料庫的規則

（2）是第四代語言

（3）是一種結構化查詢語言 (SQL)

（4）只需發出合法合理的命令，就有對應的結果顯示

SQL的特點

（1）互動性強，非過程化

（2）資料庫操縱能力強，只需發送命令，無需關注如何?

（3）多表操作時，自動導航簡單，例如：

     select emp.empno,emp.sal,dept.dname     from emp,dept     where emp.deptno = dept.deptno

（4）容易調試，錯誤提示，直接了當

（5）SQL強調結果 

PLSQL是什麼

     是專用於Oracle伺服器，在SQL基礎之上，添加了一些過程化控制語句，叫PLSQL

     過程化包括有：類型定義，判斷，迴圈，遊標，異常或例外處理。。。

     PLSQL強調過程

為什麼要用PLSQL

     因為SQL是第四代命令式語言，無法顯示處理過程化的業務，所以得用一個過程化程式設計語言來彌補SQL的不足之處，

     SQL和PLSQL不是替代關係，是彌補關係

PLSQL程式的完整組成結構如下：

[declare]    變數聲明;    變數聲明;begin    DML/TCL操作;    DML/TCL操作;[exception]    例外處理;    例外處理;end;/

注意：在PLSQL程式中，；號表示每條語句的結束，/表示整個PLSQL程式結束

書寫PLSQL的工具有：

（1）SQLPLUS工具

（2）SQLDeveloper工具

（3）第三方工具（PLSQL & 其它）

PLSQL與SQL執行有什麼不同：

（1）SQL是單條執行的

（2）PLSQL是整體執行的，不能單條執行，整個PLSQL結束用/，其中每條語句結束用；號

2、PLSQL類型

寫一個PLSQL程式，輸出"hello world"字串，文法：dbms_output.put_line(‘需要輸出的字串‘);

begin    --向SQLPLUS用戶端工具輸出字串    dbms_output.put_line(‘hello 你好‘);end;/

注意：

dbms_output是oracle中的一個輸出對象

put_line是上述對象的一個方法，用於輸出一個字串自動換行 

設定顯示PLSQL程式的執行結果，預設情況下，不顯示PLSQL程式的執行結果，文法：set serveroutput on/off;

set serveroutput on;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/87/1C/wKiom1fUkinCpbvTAABVv3ShYF8827.jpg" title="001.jpg" alt="wKiom1fUkinCpbvTAABVv3ShYF8827.jpg" />

使用基本類型變數,常量和注釋，求10+100的和

declare    --定義變數    mysum number(3) := 0;    tip varchar2(10) := ‘結果是‘;begin    /*業務演算法*/       mysum := 10 + 100;    /*輸出到控制器*/    dbms_output.put_line(tip || mysum);end;/

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/87/1A/wKioL1fUk3zTGG2rAABFWF9neIg042.jpg" title="002.jpg" alt="wKioL1fUk3zTGG2rAABFWF9neIg042.jpg" />

輸出7369號員工姓名和工資，格式如下：7369號員工的姓名是SMITH,薪水是800，文法：使用表名.欄位%type

declare    --定義二個變數，分別裝姓名和工資    pename emp.ename%type;    psal   emp.sal%type;begin      --SQL語句    --select ename,sal from emp where empno = 7369;    --PLSQL語句，將ename的值放入pename變數中，sal的值放入psal變數中        select ename,sal into pename,psal from emp where empno = 7369;    --輸出    dbms_output.put_line(‘7369號員工的姓名是‘||pename||‘,薪水是‘||psal);    end;/

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/87/1C/wKiom1fUlIvjU5bSAABhI0WVgfE232.jpg" title="003.jpg" alt="wKiom1fUlIvjU5bSAABhI0WVgfE232.jpg" />

輸出7788號員工姓名和工資，格式如下：7788號員工的姓名是SMITH,薪水是3000，文法：使用表名%rowtype

declare    emp_record emp%rowtype;begin    select * into emp_record from emp where empno = 7788;    dbms_output.put_line(‘7788號員工的姓名是‘||emp_record.ename||‘,薪水是‘||emp_record.sal);end;/

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/87/1C/wKiom1fUlo3yDZVGAABom-tuo3c402.jpg" title="004.jpg" alt="wKiom1fUlo3yDZVGAABom-tuo3c402.jpg" />

何時使用%type，何時使用%rowtype？

當定義變數時，該變數的類型與表中某欄位的類型相同時，可以使用%type

當定義變數時，該變數與整個表結構完全相同時，可以使用%rowtype，此時通過變數名.欄位名，可以取值變數中對應的值

項目中，常用%type

3、PLSQL判斷

使用if-else-end if顯示今天星期幾，是"工作日"還是"休息日"

declare    pday varchar2(10);begin    select to_char(sysdate,‘day‘) into pday from dual;    dbms_output.put_line(‘今天是‘||pday);    if pday in (‘星期六‘,‘星期日‘) then            dbms_output.put_line(‘休息日‘);    else            dbms_output.put_line(‘工作日‘);    end if;end;/

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/87/1A/wKioL1fUmN6zvWOUAABxKO1wkQk816.jpg" title="005.jpg" alt="wKioL1fUmN6zvWOUAABxKO1wkQk816.jpg" />

從鍵盤接收值，使用if-elsif-else-end if顯示"age<16"，"age<30"，"age<60"，"age<80"

declare    age number(3) := &age;begin    if age < 16 then       dbms_output.put_line(‘你未成人‘);    elsif age < 30 then       dbms_output.put_line(‘你青年人‘);    elsif age < 60 then       dbms_output.put_line(‘你奮鬥人‘);    elsif age < 80 then        dbms_output.put_line(‘你享受人‘);    else       dbms_output.put_line(‘未完再繼‘);    end if;end;/

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/87/1C/wKiom1fUmlujds98AAB0ewN2Zgs788.jpg" title="006.jpg" alt="wKiom1fUmlujds98AAB0ewN2Zgs788.jpg" />

4、PLSQL迴圈

使用loop迴圈顯示1-10

declare    i number(2) := 1;begin    loop        --當i>10時，退出迴圈        exit when i>10;        --輸出i的值        dbms_output.put_line(i);        --變數自加        i := i + 1;      end loop;end;/

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/87/1A/wKioL1fUm0qzuTEuAABFsdlqIWc599.jpg" title="007.jpg" alt="wKioL1fUm0qzuTEuAABFsdlqIWc599.jpg" />

使用while迴圈顯示1-10

declare    i number(2) := 1;begin    while i<11     loop        dbms_output.put_line(i);        i := i + 1;    end loop;end;/

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/87/1A/wKioL1fUm-7RjeLvAABCpMHB7sI558.jpg" title="008.jpg" alt="wKioL1fUm-7RjeLvAABCpMHB7sI558.jpg" />

使用while迴圈，向emp表中插入999條記錄

declare    i number(4) := 1;begin     while( i < 1000 )    loop        insert into emp(empno,ename) values(i,‘哈哈‘);        i := i + 1;    end loop;   end;/

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/87/1C/wKiom1fUnwCzZ9KhAABJzcaUWq4357.jpg" title="009.jpg" alt="wKiom1fUnwCzZ9KhAABJzcaUWq4357.jpg" />

使用while迴圈，從emp表中刪除999條記錄

declare    i number(4) := 1;begin     while i<1000    loop        delete from emp where empno = i;        i := i + 1;    end loop;end;/

使用for迴圈顯示20-30

declare    i number(2) := 20;begin    for i in 20 .. 30    loop        dbms_output.put_line(i);    end loop;end;/

5、PLSQL遊標

什麼是游標/遊標/cursor

類似於JDBC中的ResultSet對象的功能，從上向下依次擷取每一記錄的內容

使用無參游標cursor，查詢所有員工的姓名和工資【如果需要遍曆多條記錄時，使用游標cursor，無記錄找到使用cemp%notfound】

declare    --定義遊標    cursor cemp is select ename,sal from emp;    --定義變數    vename emp.ename%type;    vsal   emp.sal%type;begin    --開啟遊標，這時遊標位於第一條記錄之前    open cemp;    --迴圈    loop       --向下移動遊標一次       fetch cemp into vename,vsal;        --退出迴圈,當遊標下移一次後，找不到記錄時，則退出迴圈       exit when cemp%notfound;       --輸出結果       dbms_output.put_line(vename||‘--------‘||vsal);    end loop;    --關閉遊標    close cemp;end;/

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M02/87/1A/wKioL1fUoTjD7VBBAACxqVXKSps634.jpg" title="010.jpg" alt="wKioL1fUoTjD7VBBAACxqVXKSps634.jpg" />

使用帶參游標cursor，查詢10號部門的員工姓名和工資

declare    cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;    pename emp.ename%type;    psal emp.sal%type; begin     open cemp(&deptno);    loop        fetch cemp into pename,psal;         exit when cemp%notfound;        dbms_output.put_line(pename||‘的薪水是‘||psal);    end loop;    close cemp;end;/

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M01/87/1C/wKiom1fUpTTgyfD_AACqLI4V2Jo010.jpg" style="float:none;" title="011.jpg" alt="wKiom1fUpTTgyfD_AACqLI4V2Jo010.jpg" />

動態圖如下：

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M00/87/1C/wKiom1fUpTXjzUiQAAFhr8AArGw798.gif" style="float:none;" title="011.gif" alt="wKiom1fUpTXjzUiQAAFhr8AArGw798.gif" />

使用無參游標cursor，真正給員工漲工資，ANALYST漲1000，MANAGER漲800，其它漲400，要求顯示編號，姓名，職位，薪水

declare    cursor cemp is select empno,ename,job,sal from emp;    pempno emp.empno%type;    pename emp.ename%type;    pjob   emp.job%type;    psal   emp.sal%type;begin    open cemp;    loop        fetch cemp into pempno,pename,pjob,psal;        --迴圈允出準則一定要寫        exit when cemp%notfound;        if pjob=‘ANALYST‘ then            update emp set sal = sal + 1000 where empno = pempno;        elsif pjob=‘MANAGER‘ then            update emp set sal = sal + 800 where empno = pempno;        else     update emp set sal = sal + 400 where empno = pempno;        end if;    end loop;    commit;    close cemp;end;/

6、PLSQL例外

使用oracle系統內建例外，示範除0例外【zero_divide】

declare    myresult number;begin    myresult := 1/0;    dbms_output.put_line(myresult);exception    when zero_divide then          dbms_output.put_line(‘除數不能為0‘);         delete from emp;  end;/

使用oracle系統內建例外，查詢100號部門的員工姓名，示範沒有找到資料【no_data_found】

declare    pename varchar2(20);begin    select ename into pename from emp where deptno = 100;    dbms_output.put_line(pename);exception    when NO_DATA_FOUND then          dbms_output.put_line(‘查無該部門員工‘);         insert into emp(empno,ename) values(1111,‘ERROR‘);end;/

使用使用者自訂例外，使用游標cursor，查詢10/20/30/100號部門的員工姓名，示範沒有找到資料【nohave_emp_found】

declare  cursor cemp(pdeptno number) is select ename from emp where deptno=pdeptno;  nohave_emp_found exception;  pename emp.ename%type;begin  --開啟遊標，這時遊標位於第一條記錄之前  open cemp(&xx);  -- 向下移動遊標，指向第一條記錄  fetch cemp into pename;  -- 判斷   if cemp%notfound then     -- 拋異常     raise nohave_emp_found;  else     -- 輸出變數pename的值      dbms_output.put_line(pename);     -- 迴圈     loop       -- 向下移動遊標一次，指向第二條記錄       fetch cemp into pename;       -- 如果找不到記錄的話，就退出       exit when cemp%notfound;       dbms_output.put_line(pename);     end loop;  end if;    close cemp;exception  when nohave_emp_found then    dbms_output.put_line(‘查無此部門員工‘);end;/

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/87/1D/wKiom1fUrIaxnpGaAADT7DYjcEw819.jpg" title="012.jpg" alt="wKiom1fUrIaxnpGaAADT7DYjcEw819.jpg" />

Oracle系列：（28）PLSQL