Oracle系列：（28）PLSQL

1、SQL對比PLSQL

SQL99是什么

（1）是操作所有關系型數據庫的規則

（2）是第四代語言

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

（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;

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

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

輸出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;
/

輸出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;
/

何時使用%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;
/

從鍵盤接收值，使用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;
/

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;
/

使用while循環顯示1-10

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

使用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;
/

使用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;
/

使用帶參光標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;
/

動態圖如下：

使用無參光標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;
/