oracle存儲過程的使用

來源：互聯網轉載時間：2023-09-09 09:18:01

文章目錄

- oracle存儲過程的使用
- - 基本結構
  - 管理存儲過程
  - 調用存儲過程的方法
  - 存儲過程參數關鍵詞： `IN` 和`out`
  - - `in/out`測試案例
    - 調用`in/out`測試案例
- 存儲過程語法
- - `DECLARE`聲明關鍵詞
  - 賦值
  - - 使用`in/out`將值作為子程序的參數分配給變量，看上面的案例
    - 為布爾變量賦值
  - 表達式
  - - 串聯符`||`
    - 運算符優先級
    - 邏輯運算符
    - 短運算符`or` 和`and`
    - 比較運算符
    - - `is null /is not null`
      - 關系運算符
      - `LIKE `
      - `BETWEEN `和`IN`：
  - `case when`：
  - 條件語句`if`
  - - if語法
    - if條件命令案例
  - 循環
  - - 循環的3種方式
    - 跳出循環的關鍵詞
  - `GOTO`跳轉
- 數據類型
- 集合
- - 創建集合table
  - - 全局使用的集合
    - 局部使用的集合
    - sql查詢內容放入集合中
  - 集合比較
  - - 可以和null比較，可以集合之間比較
    - 其他比較
  - 集合方法
  - - 刪除案例
- 游標`CURSOR`
- - 隱式游標
  - - 遍歷隱式游標的結果
  - 顯式游標
  - - 定義
    - 調用游標`CURSOR`
  - 顯式游標`Cursor `的屬性調用
  - - 使用`FETCH`提取游標查詢的數據
    - - 案例1
      - 案例2：將相同的顯式游標提取到不同的變量中
    - 帶參數調用游標`CURSOR`
- 動態參數SQL
- - 動態sql使用占位符的方式
  - - - 使用`EXECUTE IMMEDIATE`調用動態sql或子程序
      - 使用`open`調用動態sql
  - 使用拼接符生成動態sql
  - - 拼接案例
- 事務管理
- - 語法詞
  - 事務案例
  - 設置事務級別
  - 設置私有事務
  - - 聲明私有事務
    - 調用私有事務的測試
  - 設置只讀事務
- 觸發器
- - DML 觸發器
  - 觸發器案例
- 異常處理
- 存儲過程demo
- - 案例所需建表語句

oracle存儲過程的使用

存儲過程官方文檔

PLSQL的語法大全

語法|標識符|分隔符等等文檔

基本結構

存儲過程也可以調用匿名存儲過程，具體看官網

-- CREATE OR REPLACE 創建或者替換 CREATE OR REPLACE PROCEDURE P_TEST(-- 這個可以是多個參數，用',' 分割testParams IN varchar2,returnMsg2233 IN OUT varchar2 -- 返回信息的方式111：推薦來個返回信息，比較友好，可以在存儲過程的邏輯設置該值，在調用結束后就可以得到值的內容 ) IS-- 定義的變量LOOP_COUNT number DEFAULT 5; error_message varchar2(512) DEFAULT '發生錯誤鴨！'; BEGIN-- 代碼邏輯SELECT * FROM DUAL;returnMsg2233 := '我執行成功了哦哦！！！'; -- 返回信息的方式111DBMS_OUTPUT.PUT_LINE(returnMsg2233);-- 打印輸出信息-- 異常處理部分開始EXCEPTION WHEN VALUE_ERROR THENDBMS_OUTPUT.PUT_LINE(error_message); -- 輸出錯誤信息 END;

管理存儲過程

CREATE OR REPLACE ... xxx ... -- 創建或者替換 DROP PROCEDURE TEST.P_TEST; -- 刪除 ALTER PROCEDURE hr.remove_emp ... xxx ...; -- 更新

調用存儲過程的方法

-- 使用call CALL P_TEST('test','returnMsg'); -- 使用begin end; BEGINP_TEST('test','returnMsg'); END;

存儲過程參數關鍵詞： IN 和out

用這個關鍵詞可以控制，過程調用之前、期間和之后的參數值

IN你可以使用，但是無法賦值
OUT為 OUT 參數賦值

in/out測試案例

CREATE OR REPLACE PROCEDURE p (a PLS_INTEGER, -- IN by defaultb IN PLS_INTEGER,c OUT PLS_INTEGER,d IN OUT BINARY_FLOAT ) AUTHID DEFINER IS BEGIN-- 打印參數值DBMS_OUTPUT.PUT_LINE('Inside procedure p:');DBMS_OUTPUT.PUT('IN a = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(a), 'NULL'));DBMS_OUTPUT.PUT('IN b = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(b), 'NULL'));DBMS_OUTPUT.PUT('OUT c = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(c), 'NULL'));DBMS_OUTPUT.PUT_LINE('IN OUT d = ' || TO_CHAR(d));-- 可以引用 IN 參數 a 和 b，-- 但不能為它們賦值。c := a+10; -- 為 OUT 參數賦值d := 10/b; -- 將值賦給 IN OUT 參數 END;

調用in/out測試案例

-- 調用 out類型參數，必須先用DECLARE進行定義 DECLAREaa CONSTANT PLS_INTEGER := 1;bb PLS_INTEGER := 2;cc PLS_INTEGER := 3;dd BINARY_FLOAT := 4;ee PLS_INTEGER;ff BINARY_FLOAT := 5; BEGINDBMS_OUTPUT.PUT_LINE('Before invoking procedure p:');DBMS_OUTPUT.PUT('aa = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL'));DBMS_OUTPUT.PUT('bb = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL'));DBMS_OUTPUT.PUT('cc = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL'));DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));p(aa, -- constantbb, -- initialized variablecc, -- initialized variabledd -- initialized variable);DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');DBMS_OUTPUT.PUT('aa = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL'));DBMS_OUTPUT.PUT('bb = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL'));DBMS_OUTPUT.PUT('cc = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL'));DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:');DBMS_OUTPUT.PUT('ee = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL'));DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff));p(1, -- literal(bb + 3) * 4, -- expressionee, -- uninitialized variableff -- initialized variable);DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');DBMS_OUTPUT.PUT('ee = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL'));DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff)); END;

存儲過程語法

DECLARE聲明關鍵詞

非空約束
DECLARE acct_id INTEGER(4) NOT NULL := 9999;
變量
DECLARE part_number NUMBER(6);
常量CONSTANT
DECLARE CONSTANT part_number NUMBER(6);
變量和常量的初始值
DECLAREhours_worked INTEGER := 40;pi CONSTANT REAL := 3.14159;
%TYPE：聲明時使用上一個變量的類型

該屬性允許您聲明與以前聲明的變量或列具有相同數據類型的數據項（不知道該類型是什么）。如果引用項的聲明發生更改，則引用項的聲明也會相應更改
DECLAREname VARCHAR(25) NOT NULL := 'Smith';surname name%TYPE := 'Jones';

賦值

使用賦值語句
variable_name ：= 表達式; DECLARE money number := 8+2*3
使用select into為變量賦值
-- 為變量賦值 SELECT select_item [, select_item ]... -- 查詢的字段 INTO variable_name [, variable_name ]... --變量 FROM table_name; -- 為集合賦值SELECT employee_id, last_name BULK COLLECT INTO enums, names; -- enums 和names是聲明的集合
使用in/out將值作為子程序的參數分配給變量，看上面的案例
為布爾變量賦值
DECLAREdone BOOLEAN; -- 初始值默認為 NULLcounter NUMBER := 0; BEGINdone := (counter > 500); -- 用表達式賦值 END;

表達式

串聯符||

DECLAREx VARCHAR2(4) := 'suit';y VARCHAR2(4) := 'case'; BEGINDBMS_OUTPUT.PUT_LINE (x || y);DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce'); END; -- 輸出結果： suitcase applesauce

運算符優先級

算子操作

**	冪
+,-	同一性，否定
*,/	乘法、除法
+, ,`-``
=, , , , , , , , , , , ,<``>``<=``>=``<>``!=``~=`` ^=``IS``NULL``LIKE``BETWEEN``IN	比較
NOT	否定
AND	連接
OR	包含

邏輯運算符

xyx AND yx OR yNOT x

TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

短運算符or 和and

or，前面的不成立，就不執行，and都必須成立

DECLAREon_hand INTEGER := 0;on_order INTEGER := 100; BEGIN-- 不會導致被零除錯誤;-- 求值在第一次表達式后停止IF (on_hand = 0) OR ((on_order / on_hand) < 5) THENDBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');END IF; END;

比較運算符

算術比較：數字比較而已
布爾比較：布爾值比較
字符比較：默認情況下，如果一個字符的二進制值較大，則該字符大于另一個字符
日期比較：日期打就打

is null /is not null

關系運算符

算子意義

=	等于
<>, , ,!=``~=`` ^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

LIKE

DECLAREPROCEDURE compare (value VARCHAR2,pattern VARCHAR2) ISBEGINIF value LIKE pattern THENDBMS_OUTPUT.PUT_LINE ('TRUE');ELSEDBMS_OUTPUT.PUT_LINE ('FALSE');END IF;END; BEGINcompare('Johnson', 'J%s_n');compare('Johnson', 'J%S_N'); END; -- 輸出結果 TRUE FALSE

BETWEEN 和IN：

和平常sql一樣

case when：

和平常sql一樣

CASE selector WHEN selector_value_1 THEN result_1 WHEN selector_value_2 THEN result_2 ... WHEN selector_value_n THEN result_n [ ELSEelse_result ] END

條件語句if

if語法

$IF boolean_static_expression $THENtext [ $ELSIF boolean_static_expression $THENtext ]... [ $ELSEtext $END ]

if條件命令案例

BEGIN$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- 選擇命令判斷$ERROR 'unsupported database release' $END -- 錯誤命令，輸出錯誤信息$ELSEDBMS_OUTPUT.PUT_LINE ('Release ' || DBMS_DB_VERSION.VERSION || '.' ||DBMS_DB_VERSION.RELEASE || ' is supported.');-- 10.2版本支持下面的commit語法 :COMMIT WRITE IMMEDIATE NOWAIT;$END -- 終結命令 END;

循環

循環的3種方式

LOOP簡單循環

LOOP-- 代碼邏輯 END LOOP;

WHILE循環

-- 使用condition，這個會一直循環，只能手動退出循環 WHILE condition LOOP -- 代碼邏輯 END LOOP; -- 條件不成立會退出循環 WHILE a>2 LOOP -- 代碼邏輯 END LOOP;

FOR循環

-- 語法 FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOPstatements END LOOP; -- 案例一，不使用 REVERSE ，loop_variable初始值=下限值1 FOR loop_variable IN 1..5 LOOPstatements END LOOP; -- 案例二，使用 REVERSE ，loop_variable初始值=上限值5 FOR loop_variable IN REVERSE 1..5 LOOPstatements END LOOP;

跳出循環的關鍵詞

CONTINUE：跳出本次循環
EXIT：結束循環
CONTINUE WHEN ：條件跳出本次循環
EXIT WHEN ：條件結束循環
RETURN：結束代碼

GOTO跳轉

可以跳到某個定義的聲明標簽<<gotp_here>>，這個標簽可以在GOTO的前面也可以在后面

DECLAREdone BOOLEAN; BEGINFOR i IN 1..5 LOOPIF done THENDBMS_OUTPUT.PUT_LINE('這里是1111');GOTO gotp_here; --可以跳到某個定義的聲明標簽END IF;<<gotp_here>>DBMS_OUTPUT.PUT_LINE('跳到22222');END LOOP; END;

數據類型

數據類型，就用數據庫的，要多的去看官網

集合

集合的官網

創建集合table

聲明一個table，可以用 declare聲明局部使用，也可以直接創建這么一個類型，全局使用

全局使用的集合

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER ISi NUMBER; BEGINi := nt.FIRST;IF i IS NULL THENDBMS_OUTPUT.PUT_LINE('nt is empty');ELSEWHILE i IS NOT NULL LOOPDBMS_OUTPUT.PUT('nt.(' || i || ') = ');DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL'));i := nt.NEXT(i);END LOOP;END IF;DBMS_OUTPUT.PUT_LINE('---'); END print_nt; / DECLAREnt nt_type := nt_type(); -- nested table variable initialized to empty BEGINprint_nt(nt);nt := nt_type(90, 9, 29, 58);print_nt(nt); END;

局部使用的集合

DECLARETYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type-- 使用構造函數初始化的嵌套表變量：names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');...

sql查詢內容放入集合中

DECLARETYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE;enums NumTab; names NameTab;PROCEDURE print_first_n (n POSITIVE) IS BEGINIF enums.COUNT = 0 THENDBMS_OUTPUT.PUT_LINE ('Collections are empty.');ELSEDBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');FOR i IN 1 .. n LOOPDBMS_OUTPUT.PUT_LINE (' Employee #' || enums(i) || ': ' || names(i));END LOOP;END IF; END;BEGINSELECT employee_id, last_nameBULK COLLECT INTO enums, namesFROM employeesORDER BY employee_id;print_first_n(3);print_first_n(6); END;

集合比較

可以和null比較，可以集合之間比較

DECLARETYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record typedept_names1 dnames_tab :=dnames_tab('Shipping','Sales','Finance','Payroll');dept_names2 dnames_tab :=dnames_tab('Sales','Finance','Shipping','Payroll');dept_names3 dnames_tab :=dnames_tab('Sales','Finance','Payroll');BEGIN-- 判斷 is nullIF dept_names1 IS NOT NULL THENDBMS_OUTPUT.PUT_LINE('dept_names1 IS NOT NULL');ELSEDBMS_OUTPUT.PUT_LINE('dept_names1 IS NULL');END IF;-- 判斷集合相等IF dept_names1 = dept_names2 THENDBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2');END IF;IF dept_names2 != dept_names3 THENDBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3');END IF; END;

其他比較

DECLARETYPE nested_typ IS TABLE OF NUMBER;nt1 nested_typ := nested_typ(1,2,3);nt2 nested_typ := nested_typ(3,2,1);nt3 nested_typ := nested_typ(2,3,1,3);nt4 nested_typ := nested_typ(1,2,4);PROCEDURE testify (truth BOOLEAN := NULL,quantity NUMBER := NULL) ISBEGINIF truth IS NOT NULL THENDBMS_OUTPUT.PUT_LINE (CASE truthWHEN TRUE THEN 'True'WHEN FALSE THEN 'False'END);END IF;IF quantity IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(quantity);END IF;END; BEGINtestify(truth => (nt1 IN (nt2,nt3,nt4))); -- Truetestify(truth => (nt1 SUBMULTISET OF nt3)); -- Truetestify(truth => (nt1 NOT SUBMULTISET OF nt4)); -- Truetestify(truth => (4 MEMBER OF nt1)); -- Falsetestify(truth => (nt3 IS A SET)); -- Falsetestify(truth => (nt3 IS NOT A SET)); -- Truetestify(truth => (nt1 IS EMPTY)); -- Falsetestify(quantity => (CARDINALITY(nt3))); -- 4testify(quantity => (CARDINALITY(SET(nt3)))); -- 3 END;

集合方法

collection_name.method DELETE程序從集合中刪除元素。

TRIM	程序	從陣列或嵌套表的末尾刪除元素。
EXTEND	程序	將元素添加到陣列或嵌套表的末尾。
EXISTS	功能	當且僅當 varray 或嵌套表的指定元素存在時返回。TRUE
FIRST	功能	返回集合中的第一個索引。
LAST	功能	返回集合中的最后一個索引。
COUNT	功能	返回集合中的元素數。
LIMIT	功能	返回集合可以具有的最大元素數。
PRIOR	功能	返回指定索引前面的索引。
NEXT	功能	返回成功指定索引的索引。

刪除案例

collection.DELETE； -- 刪除所有 collection.DELETE(2,4)； -- range刪除index 2到5的, 包含左右2和5，都會刪除 collection.DELETE('A','C')； -- range刪除字符串索引，'A'到'C' 包含左右A\C，都會刪除

游標CURSOR

由 PL/SQL 構造和管理的游標是隱式游標。您構造和管理的游標是顯式游標。

隱式游標

隱式游標是由 PL/SQL 構造和管理的會話游標。PL/SQL 每次運行或 DML 語句時都會打開一個隱式游標。您無法控制隱式游標，但可以從其屬性中獲取信息。

隱式游標屬性包括，**注：**這個了解有即可，沒啥意思。

SQL%ISOPEN ：游標是否打開？
SQL%FOUND：執行上一個sql后是否有任何行受到影響？
SQL%NOTFOUND：執行上一個sql后沒有受影響的行嗎？
SQL%ROWCOUNT ：執行上一個sql后受影響的行數是多少？
SQL%BULK_ROWCOUNT（請參閱“獲取受 FORALL 語句影響的行數”
SQL%BULK_EXCEPTIONS（請參閱“在 FORALL 語句完成后處理 FORALL 異?！?/li>

遍歷隱式游標的結果

直接使用sql語句結果作為遍歷的對象。

BEGINFOR item IN (SELECT last_name, job_idFROM employeesWHERE job_id LIKE '%CLERK%'AND manager_id > 120ORDER BY last_name)LOOPDBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);END LOOP; END;

顯式游標

定義

可以先聲明顯式游標，然后再在同一塊、子程序或包中定義它，也可以同時聲明和定義它

-- 僅聲明游標 CURSOR cursor_name [ parameter_list ] RETURN return_type; -- 聲明并定義 CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]IS select_statement; DECLARECURSOR c1 RETURN departments%ROWTYPE; -- 聲明 c1CURSOR c2 IS -- 聲明 and 定義 c2SELECT employee_id, job_id, salary FROM employeesWHERE salary > 2000; CURSOR c1 RETURN departments%ROWTYPE IS -- 定義 c1,SELECT * FROM departments -- 重復返回類型WHERE department_id = 110;CURSOR c3 RETURN locations%ROWTYPE; -- 聲明 c3CURSOR c3 IS -- 定義 c3,SELECT * FROM locations -- 省略返回類型WHERE country_id = 'JP'; BEGINNULL; END;

調用游標CURSOR

open cursor_name; close cursor_name;

顯式游標Cursor 的屬性調用

%ISOPEN : 是否打開
%FOUND : 是否提取到行內容
%NOTFOUND : 是否未提取到任何內容
%ROWCOUNT : 提取了多少行數據

使用FETCH提取游標查詢的數據

/*語法*/ FETCH cursor_name INTO into_clause;

案例1

DECLARECURSOR c1 ISSELECT last_name, job_id FROM employeesWHERE ORDER BY last_name;v_lastname employees.last_name%TYPE; -- last_name變量v_jobid employees.job_id%TYPE; -- job_id變量CURSOR c2 ISSELECT * FROM employeesWHERE ORDER BY job_id;v_employees employees%ROWTYPE; -- 表行的記錄變量BEGINOPEN c1;LOOP -- 將 2 列提取到變量中FETCH c1 INTO v_lastname, v_jobid;EXIT WHEN c1%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );END LOOP;CLOSE c1;DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );OPEN c2;LOOP -- 將整行提取到v_employees記錄中FETCH c2 INTO v_employees;EXIT WHEN c2%NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||v_employees.job_id );END LOOP;CLOSE c2; END;

案例2：將相同的顯式游標提取到不同的變量中

DECLARECURSOR c ISSELECT e.job_id, j.job_titleFROM employees e, jobs jWHERE e.job_id = j.job_id AND e.manager_id = 100ORDER BY last_name;-- 記錄游標結果集行的變量:job1 c%ROWTYPE;job2 c%ROWTYPE;job3 c%ROWTYPE;job4 c%ROWTYPE;job5 c%ROWTYPE;BEGINOPEN c;FETCH c INTO job1; -- fetches first rowFETCH c INTO job2; -- fetches second rowFETCH c INTO job3; -- fetches third rowFETCH c INTO job4; -- fetches fourth rowFETCH c INTO job5; -- fetches fifth rowCLOSE c;DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')'); END;

帶參數調用游標CURSOR

使用DEFAULT修飾，可以為CURSOR設置默認值參數，就可以選擇傳參或者不傳參數進行調用。

DECLARECURSOR c (job VARCHAR2, max_sal NUMBER 10000,hired DATE DEFAULT TO_DATE('2022-01-01', 'yyyy-mm-dd')) ISSELECT last_name, first_name, (salary - max_sal) overpaymentFROM employeesWHERE job_id = jobAND salary > max_salAND hire_date > hiredORDER BY salary;PROCEDURE print_overpaid ISlast_name_ employees.last_name%TYPE;first_name_ employees.first_name%TYPE;overpayment_ employees.salary%TYPE;BEGINLOOPFETCH c INTO last_name_, first_name_, overpayment_;EXIT WHEN c%NOTFOUND;DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||' (by ' || overpayment_ || ')');END LOOP;END print_overpaid;BEGINDBMS_OUTPUT.PUT_LINE('-------------------------------');DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');DBMS_OUTPUT.PUT_LINE('-------------------------------');OPEN c('22', 10000); -- 不傳參數print_overpaid;CLOSE c;DBMS_OUTPUT.PUT_LINE('------------------------------------------------');DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2022-12-12:');DBMS_OUTPUT.PUT_LINE('------------------------------------------------');OPEN c('22', 10000, TO_DATE('2022-12-12', 'yyyy-mm-dd')); -- 傳入參數-- new referenceprint_overpaid;CLOSE c; END;

動態參數SQL

動態sql使用占位符的方式

使用EXECUTE IMMEDIATE調用動態sql或子程序

EXECUTE IMMEDIATE 存儲過程子程序/sql USING IN OUT[參數:params111,params222...] INTO [結果:result] ; -- 動態調用sqlsql_stmt := 'INSERT INTO payroll VALUES (:a, :b, :c, :d)';EXECUTE IMMEDIATE sql_stmt USING a,b,c,d INTO result_22333; CREATE OR REPLACE PROCEDURE create_dept (deptid IN OUT NUMBER,dname IN VARCHAR2,mgrid IN NUMBER,locid IN NUMBER ) AUTHID DEFINER AS BEGINdeptid := departments_seq.NEXTVAL;INSERT INTO departments (department_id,department_name,manager_id,location_id)VALUES (deptid, dname, mgrid, locid); END; / DECLAREplsql_block VARCHAR2(500);new_deptid NUMBER(4);new_dname VARCHAR2(30) := 'Advertising';new_mgrid NUMBER(6) := 200;new_locid NUMBER(4) := 1700; BEGIN-- 動態調用sqlsql_stmt := 'INSERT INTO payroll VALUES (:a, :b, :c, :d)';EXECUTE IMMEDIATE sql_stmt USING new_deptid, new_dname, new_mgrid, new_locid;-- 動態 PL/SQL 塊調用子程序plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';/* 在 USING 子句中指定綁定變量。指定第一個參數的模式。默認情況下，其他參數的模式是正確的 */EXECUTE IMMEDIATE plsql_blockUSING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END;

使用open調用動態sql

方式一：open 調用游標查詢數據庫數據
DECLARETYPE EmpCurTyp IS REF CURSOR;v_emp_cursor EmpCurTyp;emp_record employees%ROWTYPE;v_stmt_str VARCHAR2(200);v_e_job employees.job%TYPE; BEGIN-- 帶占位符的動態 SQL 語句：v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';-- 打開光標并在 USING 子句中指定綁定變量：OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';-- 一次從結果集中獲取一行：LOOPFETCH v_emp_cursor INTO emp_record;EXIT WHEN v_emp_cursor%NOTFOUND;END LOOP;-- Close cursor:CLOSE v_emp_cursor; END;
方式二：open查詢集合中數據
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER ASTYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));TYPE mytab IS TABLE OF rec INDEX BY pls_integer; END; / DECLAREv1 pkg.mytab; -- collection of recordsv2 pkg.rec;c1 SYS_REFCURSOR; BEGINOPEN c1 FOR 'SELECT * FROM TABLE(:1)' USING v1;FETCH c1 INTO v2;CLOSE c1;DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2); END;

使用拼接符生成動態sql

這種方式如果參數是外部的，那么會有安全隱患，盡量不要使用

拼接案例

CREATE OR REPLACE PROCEDURE get_recent_record (user_name IN VARCHAR2,service_type IN VARCHAR2,rec OUT VARCHAR2 ) AUTHID DEFINERISquery VARCHAR2(4000); BEGIN/* 以下 SELECT 語句很容易被修改因為它使用串聯來構建 WHERE 子句。 */query := 'SELECT value FROM secret_records WHERE user_name='''|| user_name|| ''' AND service_type='''|| service_type|| ''' AND date_created> DATE '''|| TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')|| '''';DBMS_OUTPUT.PUT_LINE('Query: ' || query);EXECUTE IMMEDIATE query INTO rec;DBMS_OUTPUT.PUT_LINE('Rec: ' || rec); END;

事務管理

語法詞

COMMIT;：提交事務
ROLLBACK;：回滾事務
SAVEPOINT xxxx;：設置保存點
ROLLBACK TO xxxxx;：回滾至保存點
COMMIT WRITE IMMEDIATE NOWAIT;：提交并立即寫入

保存點允許您回滾部分事務而不是整個事務。每個會話的活動保存點數不受限制。

回滾到保存點時，將擦除在該保存點之后標記的任何保存點。不會擦除回滾到的保存點。簡單的回滾或提交會擦除所有保存點。

事務案例

DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name FROM employees;CREATE UNIQUE INDEX empname_ixON emp_name (employee_id);DROP TABLE emp_sal; CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees;CREATE UNIQUE INDEX empsal_ixON emp_sal (employee_id);DROP TABLE emp_job; CREATE TABLE emp_job AS SELECT employee_id, job_id FROM employees;CREATE UNIQUE INDEX empjobid_ixON emp_job (employee_id);DECLAREemp_id NUMBER(6);emp_lastname VARCHAR2(25);emp_salary NUMBER(8, 2);emp_jobid VARCHAR2(10); BEGINSELECT employee_id, last_name, salary, job_idINTO emp_id, emp_lastname, emp_salary, emp_jobidFROM employeesWHERE employee_id = 120;INSERT INTO emp_name (employee_id, last_name)VALUES (emp_id, emp_lastname);-- 保存點SAVEPOINT do_insert;INSERT INTO emp_sal (employee_id, salary)VALUES (emp_id, emp_salary);IF SQL%ROWCOUNT <= 0 THEN-- 回滾到保存點ROLLBACK TO do_insert;END IF;INSERT INTO emp_job (employee_id, job_id)VALUES (emp_id, emp_jobid);-- 提交事務,提交事務，并且立即寫入 COMMIT WRITE IMMEDIATE NOWAIT; EXCEPTIONWHEN DUP_VAL_ON_INDEX THEN-- 回滾事務ROLLBACK;DBMS_OUTPUT.PUT_LINE('Inserts were rolled back'); END;

設置事務級別

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

設置私有事務

可以給函數、存儲過程等等設置私有事務，進入帶有私有事務的函數、存儲過程時，主事務將被掛起，私有事務不影響主事務。

啟動后，自治事務是完全獨立的。它不與主事務共享鎖、資源或提交依賴項。您可以記錄事件、遞增重試計數器等，即使主事務回滾也是如此。

自主事務可幫助您構建模塊化、可重用的軟件組件。您可以將自治事務封裝在存儲的子程序中。調用應用程序不需要知道該存儲子程序執行的操作是成功還是失敗。

PRAGMA AUTONOMOUS_TRANSACTION;

聲明私有事務

-- 案例1：函數私有事務 CREATE OR REPLACE PACKAGE emp_actions AUTHID DEFINER AS -- package specification FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)RETURN NUMBER; END emp_actions; / CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body -- code for function raise_salaryFUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)RETURN NUMBER ISPRAGMA AUTONOMOUS_TRANSACTION;new_sal NUMBER(8,2);BEGINUPDATE employees SET salary =salary + sal_raise WHERE employee_id = emp_id;COMMIT;SELECT salary INTO new_sal FROM employeesWHERE employee_id = emp_id;RETURN new_sal;END raise_salary; END emp_actions; / -- 案例二：存儲過程私有事務 CREATE OR REPLACE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER)AUTHID DEFINER ASPRAGMA AUTONOMOUS_TRANSACTION; BEGINUPDATE employeesSET salary = salary - amountWHERE employee_id = emp_id;COMMIT; END lower_salary; / -- 案例3：聲明私有事務 DROP TABLE emp; CREATE TABLE emp AS SELECT * FROM employees;DECLAREPRAGMA AUTONOMOUS_TRANSACTION;emp_id NUMBER(6) := 200;amount NUMBER(6,2) := 200; BEGINUPDATE employeesSET salary = salary - amountWHERE employee_id = emp_id;COMMIT; END;

調用私有事務的測試

DROP TABLE debug_output; CREATE TABLE debug_output (message VARCHAR2(200));CREATE OR REPLACE PACKAGE debugging AUTHID DEFINER ASFUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2; END debugging; / CREATE OR REPLACE PACKAGE BODY debugging ASFUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 ISPRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO debug_output (message) VALUES (msg);COMMIT;RETURN msg;END; END debugging; / -- 查詢時調用包函數 DECLAREmy_emp_id NUMBER(6);my_last_name VARCHAR2(25);my_count NUMBER; BEGINmy_emp_id := 120;SELECT debugging.log_msg(last_name)INTO my_last_nameFROM employeesWHERE employee_id = my_emp_id;/* 即使您在此這里回滾，插入“debug_output”的操作依然還是提交了，因為它是自主事務，不受外部事務影響。 */ROLLBACK; END; /

設置只讀事務

您可以使用該語句開始只讀或讀寫事務、建立隔離級別或將當前事務分配給指定的回滾段。SET``TRANSACTION

只讀事務對于在其他用戶更新相同表時運行多個查詢非常有用。

在只讀事務期間，所有查詢都引用數據庫的同一快照，從而提供多表、多查詢、只讀一致性視圖。其他用戶可以像往常一樣繼續查詢或更新數據。提交或回滾將結束事務。

該語句必須是只讀事務中的第一個 SQL 語句，并且在事務中只能出現一次。如果將事務設置為，則后續查詢僅看到事務開始之前提交的更改。的使用不會影響其他用戶或交易

DECLAREdaily_order_total NUMBER(12,2);weekly_order_total NUMBER(12,2);monthly_order_total NUMBER(12,2); BEGINCOMMIT; -- 提交當前事務 -- 設置 READ ONLY 事務SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';SELECT SUM (order_total)INTO daily_order_totalFROM ordersWHERE order_date = SYSDATE;SELECT SUM (order_total)INTO weekly_order_totalFROM ordersWHERE order_date = SYSDATE - 7;SELECT SUM (order_total)INTO monthly_order_totalFROM ordersWHERE order_date = SYSDATE - 30; -- 結束 read-only transactionCOMMIT; END;

觸發器

觸發器根據觸發語句及其作用的項目來指定觸發事件，觸發器有： DML 觸發器、系統觸發器、條件觸發器。

觸發器的作用：

自動生成虛擬列值
記錄事件
收集有關表訪問的統計信息
針對視圖發出 DML 語句時修改表數據
當子表和父表位于分布式數據庫的不同節點上時強制實施參照完整性
將有關數據庫事件、用戶事件和 SQL 語句的信息發布到訂閱應用程序
防止在正常工作時間之后對表執行 DML 操作
防止無效交易
強制實施無法使用約束定義的復雜業務或參照完整性規則

DML 觸發器

一個簡單的 DML 觸發器正好在以下時間點之一觸發：

在觸發語句運行之前

（觸發器稱為 BEFORE 語句觸發器或語句級 ``**BEFORE 觸發器*。*)
觸發語句運行后

（該觸發器稱為 AFTER 語句觸發器或語句級 ``**AFTER 觸發器*。*)
在觸發語句影響的每一行之前

（該觸發器稱為每行*``*觸發器之前或行級別 BEFORE 觸發器。)
在觸發語句影響的每一行之后

（觸發器稱為*每行觸發器或行級別* AFTER 觸發器。)

觸發器案例

大量案例在這里

/* 設置一個拋異常終止的觸發器 */ CREATE OR REPLACE TRIGGER dept_restrictBEFORE DELETE OR UPDATE OF Deptno ON deptFOR EACH ROW--在從部門中刪除行或更新部門的主鍵（DEPTNO）之前，-- 檢查 EMP 中的從屬外鍵值;-- 如果找到任何內容，請回滾。DECLAREDummy INTEGER; -- Use for cursor fetchemployees_present EXCEPTION;employees_not_present EXCEPTION;PRAGMA EXCEPTION_INIT (employees_present, -4094);PRAGMA EXCEPTION_INIT (employees_not_present, -4095);-- Cursor used to check for dependent foreign key values.CURSOR Dummy_cursor (Dn NUMBER) ISSELECT Deptno FROM emp WHERE Deptno = Dn;BEGINOPEN Dummy_cursor (:OLD.Deptno);FETCH Dummy_cursor INTO Dummy;-- 如果找到依賴外鍵，則引發用戶指定的外鍵錯誤代碼和消息，通過拋異常終止或回滾事務。如果未找到，關閉光標-- 在允許觸發語句完成之前。IF Dummy_cursor%FOUND THENRAISE employees_present; -- Dependent rows existELSERAISE employees_not_present; -- No dependent rows existEND IF;CLOSE Dummy_cursor;EXCEPTIONWHEN employees_present THENCLOSE Dummy_cursor;-- 通過拋異常終止或回滾事務Raise_application_error(-20001, 'Employees Present in'|| ' Department ' || TO_CHAR(:OLD.DEPTNO));WHEN employees_not_present THENCLOSE Dummy_cursor; END; /* 設置一個監聽sql后插入日志的觸發器 */ CREATE OR REPLACE TRIGGER log_salary_increaseAFTER UPDATE OF salary ON employeesFOR EACH ROW BEGININSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary'); END;

異常處理

-- 代碼邏輯 ... EXCEPTIONWHEN ex_name_1 THENstatements_1 -- Exception handlerWHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handlerWHEN OTHERS THENROLLBACK;RAISE; -- 再向外面拋出異常 END; ... -- 如果只是處理異常，繼續執行，還是可以在EXCEPTION后加代碼邏輯

存儲過程demo

CREATE OR REPLACE PROCEDURE P_TEST(testParams IN varchar2, -- 這個可以是多個參數，用',' 分割returnMsg IN OUT varchar2 -- 這個作為返回信息 ) ISLOOP_COUNT number DEFAULT 5; /* 獲取全局鎖的最大重試次數 */ID VARCHAR2(16) DEFAULT ''; /* 存儲鎖的主鍵ID */LOCK_VERSION INTEGER DEFAULT 0; /* 存儲鎖的版本變量 */UP_LOCK_SQL varchar2(512) DEFAULT ''; /* 更新鎖的sql */UP_NUM number DEFAULT 0; /* 更新鎖的行數，作為是否更新成功的標志 */CURSOR VERSIONS IS SELECT ID,VERSIONSFROM TEST_LOCKWHERE ID = 'APP_SERVERS_LOCK'; /* 獲取鎖版本的游標sql，游標可以查詢多個數據，并進行存儲 */APP_COUNT INTEGER DEFAULT 0; /* 測試查詢sql */ BEGIN-- 使用 WHILE 循環WHILE LOOP_COUNT > 0LOOP-- ============ 使用游標 ========OPEN VERSIONS; -- 打開游標/*游標無法打開*/IF VERSIONS%ISOPEN = FALSE THENreturnMsg := '異常原因:游標打開失敗';CLOSE VERSIONS;RETURN;END IF;FETCH VERSIONS INTO ID,LOCK_VERSION; -- 使用FETCH獲取游標結果 -- EXIT WHEN VERSIONS%NOTFOUND; -- 當VERSIONS是數組需要遍歷時，，可以用這個判斷是否遍歷完成，從而退出循環-- 使用完畢后，關閉游標IF VERSIONS%ISOPEN THENCLOSE VERSIONS;END IF; -- ========== 執行自定義sql ===========-- 為了使用變量參數，這里使用 || 進行拼接，UP_LOCK_SQL :='UPDATE TEST_LOCK SET VERSIONS = ' || LOCK_VERSION ||' + 1 WHERE ID = ''APP_SERVERS_LOCK'' AND VERSIONS = ' || LOCK_VERSION || ' ';-- 執行sqlEXECUTE IMMEDIATE UP_LOCK_SQL;-- 獲取更新結果UP_NUM := SQL%ROWCOUNT;-- 如果更新成功，跳出循環IF UP_NUM = 1 THEN-- -- 跳出本次循環 -- CONTINUE;-- 結束循環EXIT;END IF;-- 代碼邏輯，查詢版本，更新，重試次數5次LOOP_COUNT := LOOP_COUNT - 1;END LOOP; -- ============ 使用for 循環FOR v_counter2 IN 1..5LOOP<<GO_BACK>> -- 定義一個返回點-- 將 COUNT(*) 結果放入變量 APP_COUNTSELECT COUNT(*) INTO APP_COUNT FROM TEST_LOCK;IF APP_COUNT > 0 THENUPDATE TEST_LOCK SET VERSIONS=VERSIONS+1 WHERE ID='APP_SERVERS_LOCK';-- -- 跳出本次循環 -- CONTINUE;-- 結束循環EXIT;ELSEGOTO GO_BACK;END IF;END LOOP;END ;

案例所需建表語句

CREATE TABLE TEST_LOCK (ID VARCHAR2(16) NOT NULLCONSTRAINT "TEST_LOCK_pk"PRIMARY KEY,VERSIONS NUMBER(16) NOT NULL ) /COMMENT ON TABLE TEST_LOCK IS '全局鎖的表' /COMMENT ON COLUMN TEST_LOCK.ID IS '唯一id' /COMMENT ON COLUMN TEST_LOCK.VERSIONS IS '版本號'; /INSERT INTO SUNCPS.TEST_LOCK(ID, VERSIONS) VALUES ('APP_SERVERS_LOCK', 0); DECLARE c PLS_INTEGER; d BINARY_FLOAT;BEGIN /p(1, 2, c, d);/dbms_output.PUT_LINE(c);/dbms_output.PUT_LINE(d);/ END;

本文由 貴州做網站公司 整理發布，部分圖文來源于互聯網，如有侵權，請聯系我們刪除，謝謝！

上一篇：微信公眾號開發基本流程

下一篇：怎樣去做網站運營推廣？

oracle存儲過程的使用

文章目錄