PLSQL介紹

PLSQL是Oracle對SQL99的一種擴展，基本每一種資料庫都會對SQL進行擴展，Oracle對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和exception都是可以省略的，begin和end;/是不能省略的。

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

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

PLSQL與SQL執行有什麼不同：

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

PLSQL變數

既然PLSQL是註重過程的，那麼寫過程的程式就肯定有基本的語法，首先我們來介紹PLSQL的變數

PLSQL的變數有4種

• number
• varchar2
• 與列名類型相同
• 與整個表的列類型相同

寫一個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

判斷體

語法：

值得註意的是：eslif並沒有寫錯的，它是少了一個e的

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

迴圈

在PLSQL中，迴圈的語法有三種：

WHILE迴圈：

• while後面跟的是迴圈條件，與java的差不多，LOOP和END LOOP是關鍵字**

WHILE  total  <= 25000  

LOOP
    total : = total + salary;
END  LOOP;

LOOP迴圈：

• exit後面的條件成立了才退出迴圈【有點繞】

Loop
   exit [when 條件成立];
   total:=total+salary;
end loop;

FOR迴圈：

• 迴圈的遞增只能是1，不能自定義步長

FOR   I   IN   1 . . 3  

LOOP

語句序列 ;

END    LOOP ; 

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

游標

Oracle中的游標其實就是類似JDBC中的resultSet，就是一個指針的概念。

既然是類似與resultSet，那麼游標僅僅是在查詢的時候有效的。

語法

CURSOR  游標名  [ (參數名  數據類型[,參數名 數據類型]...)]
      IS  SELECT   語句；

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

例外、異常

我們在上面看PLSQL中的語法已經知道，有一個exception，這個在Oracle中稱為例外，我們也可以簡單看成就是Java中的異常。。。

語法：

在declare節中定義例外   
out_of   exception ;

 在begin節中可行語句中拋出例外  
raise out_of ；

 在exception節處理例外
when out_of then …

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

存儲過程和存儲函數

在Oracle中，存儲過程和存儲函數的概念其實是差不多的，一般地，我們都可以混合使用。只不過有的時候有的情況使用過程好一些，有的情況時候函數的時候好一些。下麵會講解在什麼時機使用過程還是函數的。

首先，我們在學習存儲過程和存儲函數之前，先要明白我們為什麼要學他....

其實存儲過程和函數就是類似與我們在Java中的函數的概念....

到目前為止，我們的PLSQL是有幾個缺點的：

• PLSQL不能將其封裝起來，每次調用的時候都要將整片代碼複製來調用
• 有的時候，我們想要將PLSQL的代碼保存起來，只能自己手動保存在硬碟中，非常麻煩
• 我們學資料庫就是為了讓程式能夠調用的，但是PLSQL不能讓程式（java）調用

因此，存儲過程和存儲函數就能解決上面的問題了，能夠將代碼封裝起來，保存在資料庫之中，讓編程語言進行調用....

存儲過程和函數的語法

過程的語法：

create [or replace] procedure 過程名[(參數列表)]  
as
        PLSQL程式體；【begin…end;/】

函數的語法：

CREATE [OR REPLACE] FUNCTION 函數名【(參數列表) 】
 RETURN  返回值類型
AS
PLSQL子程式體；

【begin…end;/】

無論是過程還是函數，as關鍵字都代替了declare關鍵字。

創建第一個過程：

CREATE OR REPLACE PROCEDURE hello
AS
  BEGIN
    dbms_output.put_line('hello world');
  END;

調用過程的三種方式：

• exec過程名【SQLPLUS中使用】
• PLSQL程式調用
• Java調用

PLSQL調用

BEGIN
  hello();

END;

創建有參存儲過程raiseSalary(編號)，為7369號員工漲10%的工資，演示in的用法，預設in，大小寫不敏感

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
  AS
  BEGIN
    UPDATE EMP
    SET sal = sal * 1.2
    WHERE empno = pempno;

  END;

調用：

  BEGIN
    bb(7369);
  END;

創建有參存儲過程findEmpNameAndSalAndJob(編號)，查詢7788號員工的的姓名，職位，月薪，返回多個值，演示out的用法

創建過程：在過程中的參數，預設值是IN，如果是輸出的話，那麼我們要指定為OUT。

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
AS
  BEGIN
    SELECT
      ename,
      sal,
      job
    INTO pename, psal, pjob
    FROM emp
    WHERE empno = pempno;
  END;

調用：在調用的時候，使用到的psal，pname，pjob在調用的時候都沒有定義的，因此我們需要先定義變數後使用！

DECLARE

  psal   emp.sal%TYPE;
  pename emp.ename%TYPE;
  pjob   emp.job%TYPE;

BEGIN
  find(7369, psal, pename, pjob);

  dbms_output.put_line(psal || pename || pjob);

END;/

創建有參存儲函數findEmpIncome(編號)，查詢7369號員工的年收入，演示in的用法，預設in

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER)
  --這裡指定的是返回值類型
  RETURN NUMBER
AS
  income NUMBER;
  BEGIN
    SELECT sal * 12
    INTO income
    FROM emp
    WHERE empno = pempno;

    /*在PLSQL中一定要有return語句*/
    RETURN income;
  END;

調用：在PLSQL中，賦值的語句不是直接“=”，而是:=

DECLARE
  income number;
BEGIN
  income := findEmpIncome(7369);
  dbms_output.put_line(income);

END;/

如果寫的是=號，那麼就會出現以下的錯誤：

[2017-07-11 13:58:14] [65000][6550] ORA-06550: 第 4 行, 第 10 列: 
PLS-00103: 出現符號 "="在需要下列之一時：
 := . ( @ % ;
ORA-06550: 第 4 行, 第 31 列: 
PLS-00103: 出現符號 ";"在需要下列之一時：
 . ( ) , * % & -
   + / at mod remainder rem <an exponent (**)> and or ||
   multiset
ORA-06550: 第 7 行, 第 4 列: 
PLS-00103: 出現符號 "end-of-file"在需要下列之一時：
 end
   not pragma final instantiable order overriding static member
   constructor map

創建有參存儲函數findEmpNameAndJobAndSal(編號)，查詢7788號員工的的姓名(return)，職位(out)，月薪(out)，返回多個值

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER)
  --這裡指定的是返回值類型
  RETURN VARCHAR
AS
  /*查詢出來的欄位與列名相同，就使用列名相同的類型就行了。*/
  pename emp.ename%TYPE;
  BEGIN
    SELECT
      sal,
      ename,
      job
    INTO income, pename, pjob
    FROM emp
    WHERE empno = pempno;

    /*在PLSQL中一定要有return語句*/
    RETURN pename;
  END;

調用函數：

DECLARE

  /*輸出的欄位與列名的類型是相同的。*/
  income emp.sal%TYPE;
  pjob   emp.job%TYPE;
  pename emp.ename%TYPE;
BEGIN
  pename := findEmpNameAndJobAndSal(7369, pjob, income);
  dbms_output.put_line(pename || pjob || income);

END;/

過程與函數的使用場景

我們發現過程與函數的區別其實是不大的，一般我們都可以用函數來實現的時候， 也可以使用過程來實現....

但是，總有些情況，使用函數比使用過程要好，使用過程比使用函數要好，那什麼時候使用過程，什麼時候使用函數呢？？？

不難發現的是，函數是必定要有一個返回值的，當我們在調用的時候，接受返回值就直接獲取就行了。

也就是說

• 當返回值只有一個參數的時候，那麼就使用存儲函數！
• 當返回值沒有參數或者多於一個參數的時候，那麼就使用過程！

SQL與過程函數使用場景

【適合使用】過程函數：

• 》需要長期保存在資料庫中

•       》需要被多個用戶重覆調用

•       》業務邏輯相同，只是參數不一樣

• 》批操作大量數據，例如：批量插入很多數據

【適合使用】SQL：

• 》凡是上述反面，都可使用SQL

• 》對錶，視圖，序列，索引，等這些還是要用SQL 

觸發器

在PLSQL中也有個類似與我們Java Web中過濾器的概念，就是觸發器...觸發器的思想和Filter的思想幾乎是一樣的....

值得註意的是：對於觸發器而言，是不針對查詢操作的。也就是說：觸發器只針對刪除、修改、插入操作！

觸發器語法

   CREATE  [or REPLACE] TRIGGER  觸發器名
   {BEFORE | AFTER}
   { INSERT | DELETE|-----語句級
      UPDATE OF 列名}----行級
   ON  表名

    -- 遍歷每一行記錄
   [FOR EACH ROW]
   PLSQL 塊【declare…begin…end;/】

創建語句級觸發器insertEmpTrigger，當對錶【emp】進行增加【insert】操作前【before】，顯示"hello world"

CREATE OR REPLACE TRIGGER insertempTiriger
BEFORE
INSERT
  ON EMP
  BEGIN
    dbms_output.put_line('helloword');

  END;

調用：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

結果：

星期一到星期五，且9-20點能向資料庫emp表插入數據，否則使用函數拋出異常,
語法：raise_application_error('-20000','例外原因')

CREATE OR REPLACE TRIGGER securityTrigger
BEFORE
INSERT
  ON EMP
  DECLARE
    pday  VARCHAR2(10);
    ptime NUMBER;
  BEGIN
    /*得到星期幾*/
    SELECT to_char(sysdate, 'day')
    INTO pday
    FROM dual;

    /*得到時間*/
    SELECT to_char(sysdate, 'hh24')
    INTO ptime
    FROM dual;

    IF pday IN ('星期六', '星期日') OR ptime NOT BETWEEN 7 AND 23
    THEN
      RAISE_APPLICATION_ERROR('-20000', '非工作事件，請工作時間再來！');

    END IF;

  END;

插入數據、響應觸發器：

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (3, '2', '3', 4, NULL, NULL, NULL, 10);

創建行級觸發器checkSalaryTrigger，漲後工資這一列，確保大於漲前工資，語法：for each row/:new.sal/:old.sal

可以使用:new.sal/:old.sal來對比插入之前的值和插入之後的值

CREATE OR REPLACE TRIGGER checkSalTrigger
BEFORE
UPDATE OF sal
  ON EMP
FOR EACH ROW
  BEGIN
    IF :new.sal <= :old.sal
    THEN
      RAISE_APPLICATION_ERROR('-20001', '你漲的工資也太少了把！！！！');

    END IF;

  END;

調用：

UPDATE emp
SET sal = sal - 1
WHERE empno = 7369;

如果文章有錯的地方歡迎指正，大家互相交流。習慣在微信看技術文章，想要獲取更多的Java資源的同學，可以關註微信公眾號:Java3y