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強調過程

Oracle總結【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

更多的文章可往：

文章的目錄導航

Oracle總結【PLSQL學習】PLSQL介紹遊标例外、異常存儲過程和存儲函數觸發器

PLSQL介紹

PLSQL文法

PLSQL變量

判斷體

循環

遊标

文法

例外、異常

存儲過程和存儲函數

存儲過程和函數的文法

過程與函數的使用場景

SQL與過程函數使用場景

觸發器

觸發器文法

繼續閱讀

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

Oracle 批量查詢傳入List 傳回List

scala (3) Function 和 Method