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