PL/SQL入門
2024-07-21 02:34:27
供稿：網友
1.1 PL/SQL簡介
PL/SQL是Oracle的過程化語言，包括一整套的數據類型、條件結構、循環結構和異常處理結構，PL/SQL可以執行SQL語句，SQL語句中也可以使用PL/SQL函數。
默認安裝完Oracle后，初學者應該了解下面的一些SQL語句:
1、連接
SQL*Plus system/manager
2、顯示當前連接用戶
SQL> show user
3、查看系統擁有哪些用戶
SQL> select * from all_users;
4、新建用戶并授權
SQL> create user a identified by a;（默認建在SYSTEM表空間下）
SQL> grant connect,resource to a;
5、連接到新用戶
SQL> conn a/a
6、查詢當前用戶下所有對象
SQL> select * from tab;
7、建立第一個表
SQL> create table a(a number);
8、查詢表結構
SQL> desc a
9、插入新記錄
SQL> insert into a values(1);
10、查詢記錄
SQL> select * from a;
11、更改記錄
SQL> update a set a=2;
12、刪除記錄
SQL> delete from a;
13、回滾
SQL> roll;
SQL> rollback;
14、提交
SQL> commit;
1.2 創建PL/SQL程序塊
DECLARE
…
BEGIN
…
EXCEPTION
END;
1.3 PL/SQL數據類型
名稱 類型 說明
NUMBER 數字型 能存放整數值和實數值，并且可以定義精度和取值范圍
BINARY_INTEGER 數字型 可存儲帶符號整數，為整數計算優化性能
DEC 數字型 NUMBER的子類型，小數
DOUBLE PRECISION 數字型 NUMBER的子類型，高精度實數
INTEGER 數字型 NUMBER的子類型，整數
INT 數字型 NUMBER的子類型，整數
NUMERIC 數字型 NUMBER的子類型，與NUMBER等價
REAL 數字型 NUMBER的子類型，與NUMBER等價
SMALLINT 數字型 NUMBER的子類型，取值范圍比INTEGER小
VARCHAR2 字符型 存放可變長字符串，有最大長度
CHAR 字符型 定長字符串
LONG 字符型 變長字符串，最大長度可達32,767
DATE 日期型 以數據庫相同的格式存放日期值
BOOLEAN 布爾型 TRUE OR FALSE
ROWID ROWID 存放數據庫的行號
例子：
DECLARE
ORDER_NO NUMBER(3);
CUST_NAME VARCHAR2(20);
ORDER_DATE DATE;
EMP_NO INTEGER:=25;
PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;
1.4 處理PL/SQL的異常
1.4.1 PL/SQL的異常
例如：
DECLARE
X NUMBER;
BEGIN
X:= 'yyyy';--Error Here
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;
實現技術：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*THERS異常處理器必須排在最后，它處理所有沒有明確列出的異常。*/
…
END;
1.4.2 預定義異常
異常名稱 ORACLE錯誤 說明
CURSOR_ALREADY_OPEN ORA-6511 試圖打開一個已打開的光標
DUP_VAL_ON_INDEX ORA-0001 試圖破壞一個唯一性限制
INVALID_CURSOR ORA-1001 試圖使用一個無效的光標
INVALID_NUMBER ORA-1722 試圖對非數字值進行數字操作
LOGIN_DENIED ORA-1017 無效的用戶名或者口令
NO_DATA_FOUND ORA-1403 查詢未找到數據
NOT_LOGGED_ON ORA-1012 還未連接就試圖數據庫操作
PROGRAM_ERROR ORA-6501 內部錯誤
ROWTYPE_MISMATCH ORA-6504 主變量和光標的類型不兼容
STORAGE_ERROR ORA-6500 內部錯誤
TIMEOUT_ON_RESOURCE ORA-0051 發生超時
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT ORA-006 由于死鎖提交被退回
VALUE_ERROR ORA-6502 轉換或者裁剪錯誤
ZERO_DIVIDE ORA-1476 試圖被零除
1.4.3 自定義異常處理
DECLARE
BAD_ROWID EXCEPTION;
X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
SELECT ROWID INTO X FROM TAB
WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;
注重：-01445 因為PRAGMA EXCEPTION_INIT命令把這個變量(-01455)連接到
這個ORACLE錯誤，該語句的語法如下：
PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是負數，因為錯誤號被認為負數，當定義錯誤時記住使用負號
1.4.4 自定義異常
異常不一定必須是oracle返回的系統錯誤，用戶可以在自己的應用程序中創
建可觸發及可處理的自定義異常
DECLARE
SALARY_CODE VARCHAR2(1);
INVALID_SALARY_CODE EXCEPTION;
BEGIN
SALARY_CODE:='X';
IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
RAISE INVALID_SALARY_CODE;
END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;
1.5 在PL/SQL中單條記錄的查詢
在PL/SQL內，有時在沒有定義顯式光標的情況下需要查詢單條記錄，并把記錄的數據賦給變量。
DECLARE
ln_dno NUMBER;
lvs_dname VARCHAR2(40);
BEGIN
SELECT DEPT_NO,DEPT_NAME
INTO ln_dno,lvs_dname
FROM dept
WHERE DEPT_NO=1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)'.'lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
1.6 用光標查詢多條記錄
光標（CURSOR）是指向一個稱為上下文相關區的區域的指針，這個區域在服務器的處理過程全局區（PGA）內，當服務器上執行了一個查詢后，查詢返回的記錄集存放在上下文相關區，通過光標上的操作可以把這些記錄檢索到客戶端的應用程序。
1.6.1 使用光標的基本方法
DECLARE
CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)' 'VNAME);
FETCH C1 INTO VNAME;
END LOOP;
END;
屬性
含量
%FOUND
布爾型屬性，當最近一次該記錄時成功返回，則值為TRUE
%NOTFOUND
布爾型屬性，它的值總與%FOUND屬性的值相反
%ISOPEN
布爾型屬性，當光標是打開時返回TRUE
%ROWCOUNT
數字型屬性，返回已從光標中讀取的記錄數
1.6.2 使用光標FOR循環
DECLARE
CURSOR C1 IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
1.6.3 帶參數的光標
DECLARE
CURSOR C1(VIEW_PATTERN VARCHAR2) IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE VIEW_PATTERN'%' AND
ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
FOR I IN C1('USER_AR') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE();
FOR I IN C1('USER') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AAA');
END;
1.7 創建代表數據庫記錄和列的變量
變量名 基表名.列名%TYPE
DECLARE
D_NO DEPT.DEPT_NO%TYPE;
D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME
