1、基礎(chǔ)知識(shí)
PLSQL是一種類Pascal語言宰掉，每一段程序都是由Block（代碼塊）組成
declare
變量定
begin
sql語句
pl語句
exception
異常處理
end;
PLSQL塊分為三種：
- 匿名塊 (Anonymous)
- 存儲(chǔ)過程 (Procedure)
- 函數(shù) (function)
上面舉得例子就是匿名塊,因?yàn)闆]有名字呵哨，所以叫匿名塊。存儲(chǔ)過程和函數(shù)都是有名字的轨奄，函數(shù)有返回值孟害。
2、PLSQL變量
PLSQL變量主要有以下四種：
- 系統(tǒng)內(nèi)置的常規(guī)簡(jiǎn)單變量類型
- 用戶自定義復(fù)雜類型變量
- 引用類型變量（保存了一個(gè)指針值）
- 大對(duì)象類型（LOB）
變量類型舉例：
- 布爾類型
- 日期類型
- BFILE的二進(jìn)制文件類型
- 日期類型
- BLOB類型
- long類型挪拟，長(zhǎng)字符串
- 字符串類型
變量聲明舉例：
DECLARE v_hiredateDATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
- 變量聲明后再?zèng)]有賦值之前其值為NULL
- 同一個(gè)塊中挨务，應(yīng)該避免多個(gè)變量使用相同的名字
常規(guī)變量的申明：
DECLARE
v_job VARCHAR2(9);
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER(9,2) := 0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;
...
-- 特殊申明變量方式：
v_name employees.last_name%TYPE;
v_min_balance v_balance%TYPE := 10;
begin
null;
end;
PLSQL中的游標(biāo)
游標(biāo)概論：
游標(biāo)是一個(gè)私有的SQL工作區(qū)域，Oracle數(shù)據(jù)庫(kù)中有兩種游標(biāo)玉组，分別是隱式游標(biāo)和顯式游標(biāo)谎柄，隱式游標(biāo)不易被用戶和程序員察覺和意識(shí)到，實(shí)際上Oracle服務(wù)器使用隱式游標(biāo)來解析和執(zhí)行我們提交的SQL語句惯雳； 而顯式游標(biāo)是程序員在程序中顯式聲明的谷誓；通常我們說的游標(biāo)均指顯式游標(biāo)。
顯示游標(biāo)
對(duì)于返回多行結(jié)果的SQL語句的返回結(jié)果吨凑，可使用顯式游標(biāo)獨(dú)立的處理器中每一行的數(shù)據(jù)捍歪。
此處圖片缺失
顯式游標(biāo)控制的一般過程：
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS --1.創(chuàng)建游標(biāo)
SELECT employee_id, last_name FROM employees;
BEGIN
OPEN emp_cursor; --2.打開游標(biāo)
LOOP
FETCH emp_cursor
INTO v_empno, v_ename; --3.提取變量 fetch cursor_name into value1,value2;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND; --emp_cursor%NOTFOUND 4.用來測(cè)試是否有數(shù)據(jù)
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno) || ' ' || v_ename);
END LOOP;
CLOSE emp_cursor; --5.關(guān)閉游標(biāo)
END;
for循環(huán)控制游標(biāo)
舉例1：直接使用for+sql創(chuàng)建并適用游標(biāo)
--一般格式：
--for record_name in cursor_name loop ... end loop;
BEGIN
FOR emp_record IN (SELECT last_name, department_id
FROM employees) LOOP
-- implicit open and implicit fetch occur
IF emp_record.department_id = 80 THEN
...
END LOOP; -- implicit close occurs
END;
舉例2：先創(chuàng)建游標(biāo)emp_cursor，再用for調(diào)用
--這種情況適用于多次調(diào)用游標(biāo)的情況
DECLARE
CURSOR emp_cursor IS --先創(chuàng)建游標(biāo)
SELECT last_name, department_id
FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP --再調(diào)用游標(biāo)訪問
-- implicit open and implicit fetch occur
IF emp_record.department_id = 80 THEN
...
END LOOP; -- implicit close occurs
END;
游標(biāo)帶參數(shù)
直接返回和參數(shù)相關(guān)的查詢結(jié)果
DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS
SELECT employee_id, last_name
FROM employees
WHERE department_id = p_deptno
AND job_id = p_job;
BEGIN
OPEN emp_cursor (80, 'SA_REP');
. . .
CLOSE emp_cursor;
OPEN emp_cursor (60, 'IT_PROG');
. . .
END;
在游標(biāo)中使用FOR UPDATE NOWAIT
有的時(shí)候我們打開一個(gè)游標(biāo)是為了更新或者刪除一些記錄鸵钝，這種情況下我們希望
在打開游標(biāo)的時(shí)候即鎖定相關(guān)記錄糙臼，應(yīng)該使用for update nowait語句，倘若鎖定失敗我們就停止不再繼續(xù)恩商，以免出現(xiàn)長(zhǎng)時(shí)間等待資源的死鎖情況变逃。
舉例：
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name, department_name
FROM employees,departments
WHERE employees.department_id =
departments.department_id
AND employees.department_id = 80
FOR UPDATE OF salary NOWAIT;
PLSQL中的異常
PLSQL中一般有兩種異常，一種是由Oracle內(nèi)部錯(cuò)誤拋出的異常怠堪，分為預(yù)定義和非預(yù)定義兩種揽乱；另外一種是由程序員顯式的拋出名眉。
常見的異常：
| 異常代碼 | 異常類型 |
|---|---|
| NO_DATA_FOUND | 沒有發(fā)現(xiàn)數(shù)據(jù) |
| INVALID_CURSOR | 游標(biāo)在被打開以前，引用%NOTFOUND屬性會(huì)產(chǎn)生此異常 |
| TOO_MANY_ROWS | 返回?cái)?shù)據(jù)過多 |
| ZERO_DIVIDE | 除數(shù)為零凰棉，出現(xiàn)異常 |
| DUP_VAL_ON_INDEX | 唯一索引上有重復(fù)值 |
對(duì)othres的處理
others表明我們未能預(yù)計(jì)的錯(cuò)誤损拢，所以全部歸到othres中去，單發(fā)生這種情況時(shí)撒犀，我們希望了解錯(cuò)誤號(hào)和相關(guān)信息福压，可以使用Oracle內(nèi)置的函數(shù)SQLCODE和SQLERRM來返回錯(cuò)誤號(hào)和錯(cuò)誤描述，舉例見下：
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors
VALUES(v_error_code, v_error_message);
END;
處理用戶自定義異常
此處圖片缺失
舉例：
DECLARE
e_invalid_department EXCEPTION;
BEGIN
UPDATE departments
SET department_name = &p_department_desc
WHERE department_id = &p_department_number;
IF SQL%NOTFOUND THEN
RAISE e_invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
傳遞異常到外層代碼塊
DECLARE
. . .
e_no_rows exception;
e_integrity exception;
PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
FOR c_record IN emp_cursor LOOP
--內(nèi)層代碼塊
BEGIN
SELECT ...
UPDATE ...
IF SQL%NOTFOUND THEN
RAISE e_no_rows; --將異常扔到外層代碼塊中或舞，使用關(guān)鍵字：RAISE
END IF;
END;
END LOOP;
EXCEPTION
WHEN e_integrity THEN ...
WHEN e_no_rows THEN ...
END;
存儲(chǔ)過程
存儲(chǔ)過程基本語法
語法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;
舉例：
CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN employees.employee_id%TYPE)
IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = p_id;
END raise_salary;
存儲(chǔ)過程參數(shù)模式
| IN | OUT | IN OUT |
|---|---|---|
| 默認(rèn)模式 | 必須顯示指定 | 必須顯示指定 |
| 用以把值傳給過程 | 用以把值從過程返回給調(diào)用環(huán)境 | 用以把變量傳遞給過程荆姆，并返回給調(diào)用環(huán)境 |
| 參數(shù)可以是常數(shù)、變量映凳、表 | 必須是個(gè)變量 | 必須是個(gè)變量 |
| 必須是個(gè)變量 | 不能賦予默認(rèn)值 | 不能賦予默認(rèn)值 |
舉例：IN OUT型變量的應(yīng)用
--程序功能為：將傳入的電話號(hào)碼字符串按xxx-xxx-xxxxx的形式返回
CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7);
END format_phone;
/
存儲(chǔ)過程的參數(shù)傳遞
- 使用默認(rèn)值
- 按順序傳遞
- 使用
=>傳遞
舉例：
首先創(chuàng)建存儲(chǔ)過程add_dept胆筒，設(shè)定了兩個(gè)參數(shù)，p_name和p_loc
--設(shè)定默認(rèn)參數(shù)使用關(guān)鍵字：DEFAULT
CREATE OR REPLACE PROCEDURE add_dept
(p_name IN departments.department_name%TYPE DEFAULT 'unknown',
p_loc IN departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/
然后诈豌，使用調(diào)用add_dept腐泻，分別使用三種方式傳遞參數(shù)
BEGIN
add_dept; --1.默認(rèn)參數(shù)
add_dept ('TRAINING', 2500); --2.順序傳遞
add_dept ( p_loc => 2400, p_name =>'EDUCATION');--3.使用=>傳遞
add_dept ( p_loc => 1200) ;
END;
/
SELECT department_id, department_name, location_id
FROM departments; --sql查詢驗(yàn)證
存儲(chǔ)過程處理異常
此處圖片缺失
函數(shù)
自治事務(wù)
關(guān)鍵字：
一個(gè)事務(wù)A在另一個(gè)事務(wù)B內(nèi)被調(diào)用，那個(gè)事務(wù)A是自治事務(wù)队询，自治事務(wù)A執(zhí)行過程中會(huì)脫離其session內(nèi)未執(zhí)行完畢的事務(wù)的影響派桩。如果session從B事務(wù)開始——A事務(wù)開始和結(jié)束——B事務(wù)結(jié)束，上述的A事務(wù)不受沒有完成的B事務(wù)的影響蚌斩，然后A事務(wù)執(zhí)行完畢后再次回到B事務(wù)執(zhí)行沒有完成的B事務(wù)铆惑。
創(chuàng)建自治事務(wù)語法：
Create or replace procedure pro_name as pragma AUTONOMOUS_TRANSACTION;
Begin
null;
End;
下面通過一個(gè)例子來對(duì)自治事務(wù)進(jìn)行說明和解釋，首先創(chuàng)建一個(gè)procedure—noautonomous_transaction01：
Create or replace procedure noautonomous_transaction01 as
Begin
Insert into test01 select * from test01 where rownum=1;
Commit;
End;
--進(jìn)行查詢測(cè)試
Select count（*） from test01
--結(jié)果：Count(*)|25
此時(shí)送膳，發(fā)現(xiàn)test01有25條數(shù)據(jù)
declare
begin
Insert into test01 select * from test01 where rownum=1;
Noautonomous_transaction01;--調(diào)用事務(wù)
Rollback;
End;
上述pl/sql程序塊內(nèi)嵌套了一個(gè)dml的procedure過程员魏。執(zhí)行pl/sql程序塊結(jié)束后發(fā)現(xiàn)test01有27條數(shù)據(jù)，也就說明了procedure中的commit完成了上述procedure和pl/sql程序塊的提交叠聋。最后的rollback也就沒有任何效果撕阎。
結(jié)論：非自治事務(wù)內(nèi)的procedure中得commit或者rollback會(huì)影響前面所有dml的影響。
現(xiàn)在創(chuàng)建一個(gè)自治事務(wù)：
--Create Procedure的autonomous_transaction01
Create or replace procedure autonomous_transaction01 as pragma autonomous_transaction01;
Begin
Insert into test01select * from test01 where rownum=1;
Commit;
End;
--查詢進(jìn)行測(cè)試
Select count(*) from test01
--Count(*)|25
此時(shí)test01有25條數(shù)據(jù)碌补。然后虏束，在pl/sql程序塊中調(diào)用自治事務(wù)autonomous_transaction01：
Begin
Insert into test01 select * from test01 where rownum=1;
Autonomous_transaction01;
Rollback;--rollback并沒有影響到自治事務(wù)內(nèi)的操作
End;
自治事務(wù)內(nèi)的commit只會(huì)影響自治事務(wù)內(nèi)的沒有提交的dml，調(diào)用自治事務(wù)完畢后又會(huì)回到調(diào)用自治事務(wù)的事務(wù)內(nèi)厦章，此時(shí)最后的rollback也只能回滾自治事務(wù)外的dml镇匀，所以此時(shí)的test01中還是只有25條數(shù)據(jù)。
自治事務(wù)與被調(diào)用事務(wù)完全獨(dú)立袜啃，不能共享調(diào)用者事務(wù)使用的鎖和其他資源汗侵，而且自治事務(wù)內(nèi)還可以調(diào)用其他自治事務(wù)。自治事務(wù)與其調(diào)用者可能會(huì)發(fā)生死鎖，oracle會(huì)檢測(cè)此類死鎖并返回錯(cuò)誤信息晰韵。
文件操作
系統(tǒng)函數(shù)：
utl_file.fopen(三個(gè)參數(shù)：文件對(duì)象发乔，文件名，打開方式) 打開文件
utl_file.put_line(文件對(duì)象雪猪，字符串) 輸出文件
utl_file.get_line() 讀取文件
utl_file.fclose() 關(guān)閉文件
utl_file.fflush() 強(qiáng)制輸出緩沖
文件打開方式
也就是fopen()的第三個(gè)參數(shù)：
r -- read text
w -- write text
a -- append text
rb -- read byte mode
wb -- write byte mode
ab -- append byte mode
--如果指定'a'或者'ab'但是文件不存在會(huì)先創(chuàng)建栏尚。
舉例：
CREATE OR REPLACE PROCEDURE FILE_TEMP IS
FILEHANDLE UTL_FILE.FILE_TYPE;--定義文件對(duì)象
BEGIN
--打開文件
FILEHANDLE := UTL_FILE.fopen('FILENAME' ,'FILE_TEMP.TXT' ,'W' );
--寫入文件
UTL_FILE.put_line(FILEHANDLE,'這是一個(gè)練習(xí)和測(cè)試');
FOR TEMP_1 IN (SELECT * FROM EMPLOYEES) LOOP
--寫入文件
UTL_FILE.put_line(FILEHANDLE,TEMP_1.LAST_NAME || '|' || TEMP_1.SALARY);
END LOOP;
UTL_FILE.FCLOSE(FILEHANDLE);--關(guān)閉文件
END;
引用游標(biāo)
create or replace procedure test_ref1
is
type s_table is ref cursor;
f s_table;
f_rec t1% ;
stmt varchar2(100) := 'select empno,ename from emp';
begin
insert into t1 select empno,ename,sal from emp;
open f for select * from t1;
loop
fetch f into f_rec;
dbms_output.put_line(f_rec.empno ||' '||f_rec.ename);
exit when f%rowcount=3 ;
end loop;
close f;
end;
/
上課記錄
-
begin ...end;為一個(gè)程序塊
- 匿名塊不能被調(diào)用，因?yàn)闆]有名字浪蹂，一般用于測(cè)試抵栈，可以直接執(zhí)行
- 存儲(chǔ)過程和函數(shù)相比告材，沒有返回值坤次，兩者都需要編譯后進(jìn)行調(diào)用，才可以執(zhí)行斥赋。
- 返回值和參數(shù)定義不需要指定長(zhǎng)度缰猴，定義變量需要指定長(zhǎng)度
