Oracle系列：（28）PLSQL

1、SQL對比PLSQL

SQL99是什么

（1）是操作所有關(guān)系型數(shù)據(jù)庫的規(guī)則

（2）是第四代語言

（3）是一種結(jié)構(gòu)化查詢語言

（4）只需發(fā)出合法合理的命令，就有對應(yīng)的結(jié)果顯示

SQL的特點(diǎn)

（1）交互性強(qiáng)，非過程化

（2）數(shù)據(jù)庫操縱能力強(qiáng)，只需發(fā)送命令，無需關(guān)注如何實(shí)現(xiàn)

（3）多表操作時，自動導(dǎo)航簡單，例如：

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

（4）容易調(diào)試，錯誤提示，直接了當(dāng)

（5）SQL強(qiáng)調(diào)結(jié)果 

PLSQL是什么

     是專用于Oracle服務(wù)器，在SQL基礎(chǔ)之上，添加了一些過程化控制語句，叫PLSQL

     過程化包括有：類型定義，判斷，循環(huán)，游標(biāo)，異?；蚶馓幚?。。。

     PLSQL強(qiáng)調(diào)過程

為什么要用PLSQL

     因?yàn)镾QL是第四代命令式語言，無法顯示處理過程化的業(yè)務(wù)，所以得用一個過程化程序設(shè)計(jì)語言來彌補(bǔ)SQL的不足之處，

     SQL和PLSQL不是替代關(guān)系，是彌補(bǔ)關(guān)系

PLSQL程序的完整組成結(jié)構(gòu)如下：

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

注意：在PLSQL程序中，；號表示每條語句的結(jié)束，/表示整個PLSQL程序結(jié)束

書寫PLSQL的工具有：

（1）SQLPLUS工具

（2）SQLDeveloper工具

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

PLSQL與SQL執(zhí)行有什么不同：

（1）SQL是單條執(zhí)行的

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

2、PLSQL類型

寫一個PLSQL程序，輸出"hello world"字符串，語法：dbms_output.put_line('需要輸出的字符串');

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

注意：

dbms_output是oracle中的一個輸出對象

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

設(shè)置顯示PLSQL程序的執(zhí)行結(jié)果，默認(rèn)情況下，不顯示PLSQL程序的執(zhí)行結(jié)果，語法：set serveroutput on/off;

set serveroutput on;

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

declare
    --定義變量
    mysum number(3) := 0;
    tip varchar2(10) := '結(jié)果是';
begin
    /*業(yè)務(wù)算法*/   
    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？

當(dāng)定義變量時，該變量的類型與表中某字段的類型相同時，可以使用%type

當(dāng)定義變量時，該變量與整個表結(jié)構(gòu)完全相同時，可以使用%rowtype，此時通過變量名.字段名，可以取值變量中對應(yīng)的值

項(xiàng)目中，常用%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循環(huán)

使用loop循環(huán)顯示1-10

declare
    i number(2) := 1;
begin
    loop
        --當(dāng)i>10時，退出循環(huán)
        exit when i>10;
        --輸出i的值
        dbms_output.put_line(i);
        --變量自加
        i := i + 1;  
    end loop;
end;
/

使用while循環(huán)顯示1-10

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

使用while循環(huán)，向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循環(huán)，從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循環(huán)顯示20-30

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

5、PLSQL游標(biāo)

什么是光標(biāo)/游標(biāo)/cursor

類似于JDBC中的ResultSet對象的功能，從上向下依次獲取每一記錄的內(nèi)容

使用無參光標(biāo)cursor，查詢所有員工的姓名和工資【如果需要遍歷多條記錄時，使用光標(biāo)cursor，無記錄找到使用cemp%notfound】

declare
    --定義游標(biāo)
    cursor cemp is select ename,sal from emp;
    --定義變量
    vename emp.ename%type;
    vsal   emp.sal%type;
begin
    --打開游標(biāo)，這時游標(biāo)位于第一條記錄之前
    open cemp;
    --循環(huán)
    loop
       --向下移動游標(biāo)一次
       fetch cemp into vename,vsal; 
       --退出循環(huán),當(dāng)游標(biāo)下移一次后，找不到記錄時，則退出循環(huán)
       exit when cemp%notfound;
       --輸出結(jié)果
       dbms_output.put_line(vename||'--------'||vsal);
    end loop;
    --關(guān)閉游標(biāo)
    close cemp;
end;
/

使用帶參光標(biāo)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;
/

動態(tài)圖如下：

使用無參光標(biāo)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;
        --循環(huán)退出條件一定要寫
        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系統(tǒng)內(nèi)置例外，演示除0例外【zero_divide】

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

使用oracle系統(tǒng)內(nèi)置例外，查詢100號部門的員工姓名，演示沒有找到數(shù)據(jù)【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;
/

使用用戶自定義例外，使用光標(biāo)cursor，查詢10/20/30/100號部門的員工姓名，演示沒有找到數(shù)據(jù)【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
  --打開游標(biāo)，這時游標(biāo)位于第一條記錄之前
  open cemp(&xx);
  -- 向下移動游標(biāo)，指向第一條記錄
  fetch cemp into pename;
  -- 判斷 
  if cemp%notfound then
     -- 拋異常
     raise nohave_emp_found;
  else
     -- 輸出變量pename的值 
     dbms_output.put_line(pename);
     -- 循環(huán)
     loop
       -- 向下移動游標(biāo)一次，指向第二條記錄
       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;
/