oracle存储过程的使用

文章目录

oracle存储过程的使用基本结构管理存储过程调用存储过程的方法存储过程参数关键词： `IN` 和`out``in/out`测试案例调用`in/out`测试案例存储过程语法`DECLARE`声SEO靠我明关键词赋值使用`in/out`将值作为子程序的参数分配给变量，看上面的案例为布尔变量赋值表达式串联符`||`运算符优先级逻辑运算符短运算符`or` 和`and`比较运算符`is null /is nSEO靠我ot null`关系运算符`LIKE ``BETWEEN `和`IN`：`case when`：条件语句`if`if语法if条件命令案例循环循环的3种方式跳出循环的关键词`GOTO`跳转数据类型集合创SEO靠我建集合table全局使用的集合局部使用的集合sql查询内容放入集合中集合比较可以和null比较，可以集合之间比较其他比较集合方法删除案例游标`CURSOR`隐式游标遍历隐式游标的结果显式游标定义调用游SEO靠我标`CURSOR`显式游标`Cursor `的属性调用使用`FETCH`提取游标查询的数据案例1案例2：将相同的显式游标提取到不同的变量中带参数调用游标`CURSOR`动态参数SQL动态sql使用占位SEO靠我符的方式使用`EXECUTE IMMEDIATE`调用动态sql或子程序使用`open`调用动态sql使用拼接符生成动态sql拼接案例事务管理语法词事务案例设置事务级别设置私有事务声明私有事务调用私有SEO靠我事务的测试设置只读事务触发器DML 触发器触发器案例异常处理存储过程demo案例所需建表语句

oracle存储过程的使用

存储过程官方文档

PLSQL的语法大全

语法|标识符|分隔符等等文档

基本结构

存储过程也SEO靠我可以调用匿名存储过程，具体看官网

-- CREATE OR REPLACE 创建或者替换 CREATE OR REPLACE PROCEDURE P_TEST(-- 这个可以是多个参数，SEO靠我用, 分割testParams IN varchar2,returnMsg2233 IN OUT varchar2 -- 返回信息的方式111： 推荐来个返回信息，比较友好，可以在存储过程的逻辑设置该SEO靠我值，在调用结束后就可以得到值的内容 ) IS-- 定义的变量LOOP_COUNT number DEFAULT 5; error_message varchar2(512) DEFAUSEO靠我LT 发生错误鸭！; BEGIN-- 代码逻辑SELECT * FROM DUAL;returnMsg2233 := 我执行成功了哦哦！！！; -- 返回信息的方式111DBMS_OUSEO靠我TPUT.PUT_LINE(returnMsg2233);-- 打印输出信息-- 异常处理部分开始EXCEPTION WHEN VALUE_ERROR THENDBMS_OUTPUT.PUT_LINESEO靠我(error_message); -- 输出错误信息 END;

管理存储过程

CREATE OR REPLACE ... xxx ... -- 创建或者替换 DROP PRSEO靠我OCEDURE TEST.P_TEST; -- 删除 ALTER PROCEDURE hr.remove_emp ... xxx ...; -- 更新

调用存储过程的方法

-- 使用calSEO靠我l CALL P_TEST(test,returnMsg); -- 使用begin end; BEGINP_TEST(test,returnMsg); SEO靠我 END;

存储过程参数关键词： IN 和out

用这个关键词可以控制，过程调用之前、期间和之后的参数值

IN你可以使用，但是无法赋值OUT为 OUT 参数赋值 in/out测SEO靠我试案例 CREATE OR REPLACE PROCEDURE p (a PLS_INTEGER, -- IN by defaultb IN PLS_INTEGER,c OUT PLSSEO靠我_INTEGER,d IN OUT BINARY_FLOAT ) AUTHID DEFINER IS BEGIN-- 打印参数值DBMS_OUTPUT.PUT_LINESEO靠我(Inside procedure p:);DBMS_OUTPUT.PUT(IN a = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(a), NULL));DBMS_OUTPSEO靠我UT.PUT(IN b = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(b), NULL));DBMS_OUTPUT.PUT(OUT c = );DBMS_OUTPUT.PUSEO靠我T_LINE(NVL(TO_CHAR(c), NULL));DBMS_OUTPUT.PUT_LINE(IN OUT d = || TO_CHAR(d));-- 可以引用 IN 参数 a 和 b，-- SEO靠我但不能为它们赋值。c := a+10; -- 为 OUT 参数赋值d := 10/b; -- 将值赋给 IN OUT 参数 END; 调用in/out测试案例 SEO靠我 -- 调用 out类型参数，必须先用DECLARE进行定义 DECLAREaa CONSTANT PLS_INTEGER := 1;bb PLS_INTEGER := 2;cSEO靠我c PLS_INTEGER := 3;dd BINARY_FLOAT := 4;ee PLS_INTEGER;ff BINARY_FLOAT := 5; BEGINDBMS_OUTPUSEO靠我T.PUT_LINE(Before invoking procedure p:);DBMS_OUTPUT.PUT(aa = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa)SEO靠我, NULL));DBMS_OUTPUT.PUT(bb = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), NULL));DBMS_OUTPUT.PUT(cc = );SEO靠我DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), NULL));DBMS_OUTPUT.PUT_LINE(dd = || TO_CHAR(dd));p(aa, -- consSEO靠我tantbb, -- initialized variablecc, -- initialized variabledd -- initialized variable);DBMS_OUTPUT.PUSEO靠我T_LINE(After invoking procedure p:);DBMS_OUTPUT.PUT(aa = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), NULSEO靠我L));DBMS_OUTPUT.PUT(bb = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), NULL));DBMS_OUTPUT.PUT(cc = );DBMS_SEO靠我OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), NULL));DBMS_OUTPUT.PUT_LINE(dd = || TO_CHAR(dd));DBMS_OUTPUT.PUT_LISEO靠我NE(Before invoking procedure p:);DBMS_OUTPUT.PUT(ee = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), NULL))SEO靠我;DBMS_OUTPUT.PUT_LINE(ff = || TO_CHAR(ff));p(1, -- literal(bb + 3) * 4, -- expressionee, -- uninitiaSEO靠我lized variableff -- initialized variable);DBMS_OUTPUT.PUT_LINE(After invoking procedure p:);DBMS_OUTSEO靠我PUT.PUT(ee = );DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), NULL));DBMS_OUTPUT.PUT_LINE(ff = || TO_CHAR(ff)SEO靠我); END;

存储过程语法

DECLARE声明关键词

非空约束

DECLARE acct_id INTEGER(4) NOT NULL := 9999;

变量

DECLARE part_numbSEO靠我er NUMBER(6);

常量CONSTANT

DECLARE CONSTANT part_number NUMBER(6);

变量和常量的初始值

DECLAREhours_worked INTEGER :SEO靠我= 40;pi CONSTANT REAL := 3.14159;

%TYPE：声明时使用上一个变量的类型

该属性允许您声明与以前声明的变量或列具有相同数据类型的数据项（不知道该类型是什么）。如果引用项的SEO靠我声明发生更改，则引用项的声明也会相应更改

DECLAREname VARCHAR(25) NOT NULL := Smith;surname name%TYPE := Jones;

赋值

使用赋值语句

varSEO靠我iable_name ：= 表达式; DECLARE money number := 8+2*3

使用select into为变量赋值

-- 为变量赋值 SELECT seSEO靠我lect_item [, select_item ]... -- 查询的字段 INTO variable_name [, variable_name ]... --变量 SEO靠我 FROM table_name; -- 为集合赋值SELECT employee_id, last_name BULK COLLECT INTO enums, names; -- eSEO靠我nums 和names是声明的集合 使用in/out将值作为子程序的参数分配给变量，看上面的案例 为布尔变量赋值 DECLAREdone BOOLEAN; -- 初始值默认为 NULLSEO靠我counter NUMBER := 0; BEGINdone := (counter > 500); -- 用表达式赋值 END;

表达式

串联符|| DESEO靠我CLAREx VARCHAR2(4) := suit;y VARCHAR2(4) := case; BEGINDBMS_OUTPUT.PUT_LINE (x || y);DBMS_OUSEO靠我TPUT.PUT_LINE (apple || NULL || NULL || sauce); END; -- 输出结果： suitcase SEO靠我 applesauce 运算符优先级 算子操作**幂+,-同一性，否定*,/乘法、除法+, ,`-``=, , , , , , , , , , , ,<``>``SEO靠我<=``>=``<>``!=``~=`` ^=``IS``NULL``LIKE``BETWEEN``IN比较NOT否定AND连接OR包含 逻辑运算符 xyx AND ySEO靠我x OR yNOT xTRUETRUETRUETRUEFALSETRUEFALSEFALSETRUEFALSETRUENULLNULLTRUEFALSEFALSETRUEFALSETRUETRUEFASEO靠我LSEFALSEFALSEFALSETRUEFALSENULLFALSENULLTRUENULLTRUENULLTRUENULLNULLFALSEFALSENULLNULLNULLNULLNULLNUSEO靠我LLNULL 短运算符or 和and

or，前面的不成立，就不执行，and都必须成立

DECLAREon_hand INTEGER := 0;on_order INTEGER := 100SEO靠我; BEGIN-- 不会导致被零除错误;-- 求值在第一次表达式后停止IF (on_hand = 0) OR ((on_order / on_hand) < 5) THENDBMS_OSEO靠我UTPUT.PUT_LINE(On hand quantity is zero.);END IF; END; 比较运算符 算术比较：数字比较而已布尔比SEO靠我较：布尔值比较字符比较：默认情况下，如果一个字符的二进制值较大，则该字符大于另一个字符日期比较：日期打就打 is null /is not null 关系运算符 SEO靠我 算子意义=等于<>, , ,!=``~=`` ^=不等于<小于>大于<=小于或等于>=大于或等于 LIKE DECLAREPROCEDURE compare SEO靠我(value VARCHAR2,pattern VARCHAR2) ISBEGINIF value LIKE pattern THENDBMS_OUTPUT.PUT_LINE (TRUE);ELSEDSEO靠我BMS_OUTPUT.PUT_LINE (FALSE);END IF;END; BEGINcompare(Johnson, J%s_n);compare(Johnson, J%S_N)SEO靠我; END; -- 输出结果 TRUE FALSE BETWEEN 和IN：

和平常sql一样

case when：

和平常SEO靠我sql一样

CASE selector WHEN selector_value_1 THEN result_1 WHEN selector_value_2 THEN reSEO靠我sult_2 ... WHEN selector_value_n THEN result_n [ ELSEelse_result ] ESEO靠我ND

条件语句if

if语法 $IF boolean_static_expression $THENtext [ $ELSIF boolean_static_expressSEO靠我ion $THENtext ]... [ $ELSEtext $END ] if条件命令案例 BEGISEO靠我N$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- 选择命令判断$ERROR unsupported database release $END -- 错误命令，输出错SEO靠我误信息$ELSEDBMS_OUTPUT.PUT_LINE (Release || DBMS_DB_VERSION.VERSION || . ||DBMS_DB_VERSION.RELEASE || iSEO靠我s supported.);-- 10.2版本支持下面的commit语法 :COMMIT WRITE IMMEDIATE NOWAIT;$END -- 终结命令 END;

循环

循环的3种SEO靠我方式

LOOP简单循环

LOOP-- 代码逻辑 END LOOP;

WHILE循环

-- 使用condition，这个会一直循环，只能手动退出循环 WHILE conditioSEO靠我n LOOP -- 代码逻辑 END LOOP; -- 条件不成立会退出循环 WHILE a>2 LOOP -- 代码逻辑 END LOSEO靠我OP;

FOR循环

-- 语法 FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOPstatements SEO靠我 END LOOP; -- 案例一，不使用 REVERSE ，loop_variable初始值=下限值1 FOR loop_variable IN 1..5 LOOSEO靠我Pstatements END LOOP; -- 案例二，使用 REVERSE ，loop_variable初始值=上限值5 FOR loop_variSEO靠我able IN REVERSE 1..5 LOOPstatements END LOOP; 跳出循环的关键词 CONTINUE：跳出本次循环EXIT：SEO靠我结束循环CONTINUE WHEN ：条件跳出本次循环EXIT WHEN ：条件结束循环RETURN：结束代码

GOTO跳转

可以跳到某个定义的声明标签<<gotp_here>>，这个标签可以在GOTO的SEO靠我前面也可以在后面

DECLAREdone BOOLEAN; BEGINFOR i IN 1..5 LOOPIF done THENDBMS_OUTPUT.PUT_LINE(这里是1111SEO靠我);GOTO gotp_here; --可以跳到某个定义的声明标签END IF;<<gotp_here>>DBMS_OUTPUT.PUT_LINE(跳到22222);END LOOP; SEO靠我 END;

数据类型

数据类型，就用数据库的，要多的去看官网

集合

集合的官网

创建集合table

声明一个table，可以用 declare声明局部使用，也可以直接创建这么一个类型，全局使用

全局使用的集合 SEO靠我 CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDUSEO靠我RE print_nt (nt nt_type) AUTHID DEFINER ISi NUMBER; BEGINi := nt.FIRST;IF i IS NULL THENDBMSSEO靠我_OUTPUT.PUT_LINE(nt is empty);ELSEWHILE i IS NOT NULL LOOPDBMS_OUTPUT.PUT(nt.( || i || ) = );DBMS_OUSEO靠我TPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), NULL));i := nt.NEXT(i);END LOOP;END IF;DBMS_OUTPUT.PUT_LINE(---); SEO靠我 END print_nt; / DECLAREnt nt_type := nt_type(); -- nested table variable iniSEO靠我tialized to empty BEGINprint_nt(nt);nt := nt_type(90, 9, 29, 58);print_nt(nt); END; SEO靠我 局部使用的集合 DECLARETYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type-- 使用构造函数初SEO靠我始化的嵌套表变量：names Roster := Roster(D Caruso, J Hamil, D Piro, R Singh);... sql查询内容放入集合中 SEO靠我 DECLARETYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employSEO靠我ees.last_name%TYPE;enums NumTab; names NameTab;PROCEDURE print_first_n (n POSITIVE) IS SEO靠我 BEGINIF enums.COUNT = 0 THENDBMS_OUTPUT.PUT_LINE (Collections are empty.);ELSEDBMS_OUTPUT.PUT_LINSEO靠我E (First || n || employees:);FOR i IN 1 .. n LOOPDBMS_OUTPUT.PUT_LINE ( Employee # || enums(i) || : SEO靠我|| names(i));END LOOP;END IF; END;BEGINSELECT employee_id, last_nameBULK COLLECT INTO enums,SEO靠我 namesFROM employeesORDER BY employee_id;print_first_n(3);print_first_n(6); END;

集合比较

可以和null比SEO靠我较，可以集合之间比较 DECLARETYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record tySEO靠我pedept_names1 dnames_tab :=dnames_tab(Shipping,Sales,Finance,Payroll);dept_names2 dnames_tab :=dnameSEO靠我s_tab(Sales,Finance,Shipping,Payroll);dept_names3 dnames_tab :=dnames_tab(Sales,Finance,Payroll);BEGSEO靠我IN-- 判断 is nullIF dept_names1 IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(dept_names1 IS NOT NULL);ELSEDBMSSEO靠我_OUTPUT.PUT_LINE(dept_names1 IS NULL);END IF;-- 判断集合相等IF dept_names1 = dept_names2 THENDBMS_OUTPUT.PSEO靠我UT_LINE(dept_names1 = dept_names2);END IF;IF dept_names2 != dept_names3 THENDBMS_OUTPUT.PUT_LINE(depSEO靠我t_names2 != dept_names3);END IF; END; 其他比较 DECLARETYPE nested_typ IS TABLE SEO靠我OF NUMBER;nt1 nested_typ := nested_typ(1,2,3);nt2 nested_typ := nested_typ(3,2,1);nt3 nested_typ := SEO靠我nested_typ(2,3,1,3);nt4 nested_typ := nested_typ(1,2,4);PROCEDURE testify (truth BOOLEAN := NULL,quaSEO靠我ntity NUMBER := NULL) ISBEGINIF truth IS NOT NULL THENDBMS_OUTPUT.PUT_LINE (CASE truthWHEN TRUE THENSEO靠我 TrueWHEN FALSE THEN FalseEND);END IF;IF quantity IS NOT NULL THENDBMS_OUTPUT.PUT_LINE(quantity);ENDSEO靠我 IF;END; BEGINtestify(truth => (nt1 IN (nt2,nt3,nt4))); -- Truetestify(truth => (nt1 SUBMULTSEO靠我ISET OF nt3)); -- Truetestify(truth => (nt1 NOT SUBMULTISET OF nt4)); -- Truetestify(truth => (4 MEMSEO靠我BER OF nt1)); -- Falsetestify(truth => (nt3 IS A SET)); -- Falsetestify(truth => (nt3 IS NOT A SET))SEO靠我; -- Truetestify(truth => (nt1 IS EMPTY)); -- Falsetestify(quantity => (CARDINALITY(nt3))); -- 4testSEO靠我ify(quantity => (CARDINALITY(SET(nt3)))); -- 3 END;

集合方法

collection_name.method DELETSEO靠我E程序从集合中删除元素。TRIM程序从阵列或嵌套表的末尾删除元素。EXTEND程序将元素添加到阵列或嵌套表的末尾。EXISTS功能当且仅当 varray 或嵌套表的指定元素存在时返回。TRUEFIRSSEO靠我T功能返回集合中的第一个索引。LAST功能返回集合中的最后一个索引。COUNT功能返回集合中的元素数。LIMIT功能返回集合可以具有的最大元素数。PRIOR功能返回指定索引前面的索引。NEXT功能返回SEO靠我成功指定索引的索引。 删除案例 collection.DELETE； -- 删除所有 collection.DELETE(2,4)； -- range删SEO靠我除index 2到5的, 包含左右2和5，都会删除 collection.DELETE(A,C)； -- range删除字符串索引，A到C 包含左右A\C，都会删除

游标CURSOR

由 SEO靠我PL/SQL 构造和管理的游标是隐式游标。您构造和管理的游标是显式游标。

隐式游标

隐式游标是由 PL/SQL 构造和管理的会话游标。PL/SQL 每次运行 或 DML 语句时都会打开一个隐式游标。您无法控SEO靠我制隐式游标，但可以从其属性中获取信息。

隐式游标属性包括，**注：**这个了解有即可，没啥意思。

SQL%ISOPEN ：游标是否打开？SQL%FOUND：执行上一个sql后是否有任何行受到影响？SQL%SEO靠我NOTFOUND：执行上一个sql后没有受影响的行吗？SQL%ROWCOUNT ：执行上一个sql后受影响的行数是多少？SQL%BULK_ROWCOUNT（请参阅“获取受 FORALL 语句影响的行数SEO靠我”SQL%BULK_EXCEPTIONS（请参阅“在 FORALL 语句完成后处理 FORALL 异常” 遍历隐式游标的结果

直接使用sql语句结果作为遍历的对象。

BEGINFOR itSEO靠我em IN (SELECT last_name, job_idFROM employeesWHERE job_id LIKE %CLERK%AND manager_id > 120ORDER BY lSEO靠我ast_name)LOOPDBMS_OUTPUT.PUT_LINE(Name = || item.last_name || , Job = || item.job_id);END LOOP; SEO靠我 END;

显式游标

定义

可以先声明显式游标，然后再在同一块、子程序或包中定义它，也可以同时声明和定义它

-- 仅声明游标 CURSOR cursor_name [ parameterSEO靠我_list ] RETURN return_type; -- 声明并定义 CURSOR cursor_name [ parameter_list ] [ RETURN SEO靠我return_type ]IS select_statement; DECLARECURSOR c1 RETURN departments%ROWTYPE; -- 声明 c1CURSSEO靠我OR c2 IS -- 声明 and 定义 c2SELECT employee_id, job_id, salary FROM employeesWHERE salary > 2000; CURSORSEO靠我 c1 RETURN departments%ROWTYPE IS -- 定义 c1,SELECT * FROM departments -- 重复返回类型WHERE department_id = SEO靠我110;CURSOR c3 RETURN locations%ROWTYPE; -- 声明 c3CURSOR c3 IS -- 定义 c3,SELECT * FROM locations -- 省略返SEO靠我回类型WHERE country_id = JP; BEGINNULL; END; 调用游标CURSOR open cursor_naSEO靠我me; close cursor_name;

显式游标Cursor 的属性调用

%ISOPEN : 是否打开%FOUND : 是否提取到行内容%NOTFOUND : 是否未提取到任何内容%SEO靠我ROWCOUNT : 提取了多少行数据 使用FETCH提取游标查询的数据 /*语法*/ FETCH cursor_name INTO into_clauSEO靠我se; 案例1 DECLARECURSOR c1 ISSELECT last_name, job_id FROM employeesWHERE ORDER BY laSEO靠我st_name;v_lastname employees.last_name%TYPE; -- last_name变量v_jobid employees.job_id%TYPE; -- job_id变SEO靠我量CURSOR c2 ISSELECT * FROM employeesWHERE ORDER BY job_id;v_employees employees%ROWTYPE; -- 表行的记录变量BSEO靠我EGINOPEN c1;LOOP -- 将 2 列提取到变量中FETCH c1 INTO v_lastname, v_jobid;EXIT WHEN c1%NOTFOUND;DBMS_OUTPUT.PSEO靠我UT_LINE( RPAD(v_lastname, 25, ) || v_jobid );END LOOP;CLOSE c1;DBMS_OUTPUT.PUT_LINE( ---------------SEO靠我---------------------- );OPEN c2;LOOP -- 将整行提取到v_employees记录中FETCH c2 INTO v_employees;EXIT WHEN c2%SEO靠我NOTFOUND;DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ) ||v_employees.job_id );END LOOP;CLOSEO靠我SE c2; END; 案例2：将相同的显式游标提取到不同的变量中 DECLARECURSOR c ISSELECT e.job_id, j.job_SEO靠我titleFROM employees e, jobs jWHERE e.job_id = j.job_id AND e.manager_id = 100ORDER BY last_name;-- 记SEO靠我录游标结果集行的变量:job1 c%ROWTYPE;job2 c%ROWTYPE;job3 c%ROWTYPE;job4 c%ROWTYPE;job5 c%ROWTYPE;BEGINOPEN c;FESEO靠我TCH c INTO job1; -- fetches first rowFETCH c INTO job2; -- fetches second rowFETCH c INTO job3; -- fSEO靠我etches third rowFETCH c INTO job4; -- fetches fourth rowFETCH c INTO job5; -- fetches fifth rowCLOSESEO靠我 c;DBMS_OUTPUT.PUT_LINE(job1.job_title || ( || job1.job_id || ));DBMS_OUTPUT.PUT_LINE(job2.job_titleSEO靠我 || ( || job2.job_id || ));DBMS_OUTPUT.PUT_LINE(job3.job_title || ( || job3.job_id || ));DBMS_OUTPUTSEO靠我.PUT_LINE(job4.job_title || ( || job4.job_id || ));DBMS_OUTPUT.PUT_LINE(job5.job_title || ( || job5.SEO靠我job_id || )); END; 带参数调用游标CURSOR

使用DEFAULT修饰，可以为CURSOR设置默认值参数，就可以选择传参或者不传参数进行调用。

DECLSEO靠我ARECURSOR c (job VARCHAR2, max_sal NUMBER 10000,hired DATE DEFAULT TO_DATE(2022-01-01, yyyy-mm-dd)) SEO靠我ISSELECT last_name, first_name, (salary - max_sal) overpaymentFROM employeesWHERE job_id = jobAND saSEO靠我lary > max_salAND hire_date > hiredORDER BY salary;PROCEDURE print_overpaid ISlast_name_ employees.lSEO靠我ast_name%TYPE;first_name_ employees.first_name%TYPE;overpayment_ employees.salary%TYPE;BEGINLOOPFETCSEO靠我H c INTO last_name_, first_name_, overpayment_;EXIT WHEN c%NOTFOUND;DBMS_OUTPUT.PUT_LINE(last_name_ SEO靠我|| , || first_name_ || (by || overpayment_ || ));END LOOP;END print_overpaid;BEGINDBMS_OUTPUT.PUT_LISEO靠我NE(-------------------------------);DBMS_OUTPUT.PUT_LINE(Overpaid Sales Representatives:);DBMS_OUTPUSEO靠我T.PUT_LINE(-------------------------------);OPEN c(22, 10000); -- 不传参数print_overpaid;CLOSE c;DBMS_OUSEO靠我TPUT.PUT_LINE(------------------------------------------------);DBMS_OUTPUT.PUT_LINE(Overpaid Sales SEO靠我Representatives Hired After 2022-12-12:);DBMS_OUTPUT.PUT_LINE(--------------------------------------SEO靠我----------);OPEN c(22, 10000, TO_DATE(2022-12-12, yyyy-mm-dd)); -- 传入参数-- new referenceprint_overpaiSEO靠我d;CLOSE c; END;

动态参数SQL

动态sql使用占位符的方式

使用EXECUTE IMMEDIATE调用动态sql或子程序 EXECUTE IMMEDIATE SEO靠我存储过程子程序/sql USING IN OUT[参数:params111,params222...] INTO [结果:result] ; -- 动态调用sqlsql_stmt :SEO靠我= INSERT INTO payroll VALUES (:a, :b, :c, :d);EXECUTE IMMEDIATE sql_stmt USING a,b,c,d INTO result_2SEO靠我2333; CREATE OR REPLACE PROCEDURE create_dept (deptid IN OUT NUMBER,dname IN VARCHAR2,mgridSEO靠我 IN NUMBER,locid IN NUMBER ) AUTHID DEFINER AS BEGINdeptid := departments_seq.NEXTVASEO靠我L;INSERT INTO departments (department_id,department_name,manager_id,location_id)VALUES (deptid, dnamSEO靠我e, mgrid, locid); END; / DECLAREplsql_block VARCHAR2(500);new_deptid NUMBER(SEO靠我4);new_dname VARCHAR2(30) := Advertising;new_mgrid NUMBER(6) := 200;new_locid NUMBER(4) := 1700; SEO靠我 BEGIN-- 动态调用sqlsql_stmt := INSERT INTO payroll VALUES (:a, :b, :c, :d);EXECUTE IMMEDIATE sql_stSEO靠我mt USING new_deptid, new_dname, new_mgrid, new_locid;-- 动态 PL/SQL 块调用子程序plsql_block := BEGIN create_SEO靠我dept(:a, :b, :c, :d); END;;/* 在 USING 子句中指定绑定变量。 指定第一个参数的模式。 默认情况下，其他参数的模式是正确的 */EXECUTE IMMEDIATE pSEO靠我lsql_blockUSING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; 使用open调用动态SEO靠我sql

方式一：open 调用游标查询数据库数据

DECLARETYPE EmpCurTyp IS REF CURSOR;v_emp_cursor EmpCurTyp;emp_record employeSEO靠我es%ROWTYPE;v_stmt_str VARCHAR2(200);v_e_job employees.job%TYPE; BEGIN-- 带占位符的动态 SQL 语句：v_stmSEO靠我t_str := SELECT * FROM employees WHERE job_id = :j;-- 打开光标并在 USING 子句中指定绑定变量：OPEN v_emp_cursor FOR vSEO靠我_stmt_str USING MANAGER;-- 一次从结果集中获取一行：LOOPFETCH v_emp_cursor INTO emp_record;EXIT WHEN v_emp_cursorSEO靠我%NOTFOUND;END LOOP;-- Close cursor:CLOSE v_emp_cursor; END;

方式二：open查询集合中数据

CREATE OR REPLACE SEO靠我PACKAGE pkg AUTHID DEFINER ASTYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));TYPE mytab IS TABLE OF rSEO靠我ec INDEX BY pls_integer; END; / DECLAREv1 pkg.mytab; -- collection of recordSEO靠我sv2 pkg.rec;c1 SYS_REFCURSOR; BEGINOPEN c1 FOR SELECT * FROM TABLE(:1) USING v1;FETCH c1 INTSEO靠我O v2;CLOSE c1;DBMS_OUTPUT.PUT_LINE(Values in record are || v2.f1 || and || v2.f2); END;

使用拼接符SEO靠我生成动态sql

这种方式如果参数是外部的，那么会有安全隐患，尽量不要使用

拼接案例 CREATE OR REPLACE PROCEDURE get_recent_record (user_SEO靠我name IN VARCHAR2,service_type IN VARCHAR2,rec OUT VARCHAR2 ) AUTHID DEFINERISquery VARCHAR2(SEO靠我4000); BEGIN/* 以下 SELECT 语句很容易被修改 因为它使用串联来构建 WHERE 子句。 */query := SELECT value FROM secret_rSEO靠我ecords WHERE user_name=|| user_name|| AND service_type=|| service_type|| AND date_created> DATE || TSEO靠我O_CHAR(SYSDATE - 30,YYYY-MM-DD)|| ;DBMS_OUTPUT.PUT_LINE(Query: || query);EXECUTE IMMEDIATE query INTSEO靠我O rec;DBMS_OUTPUT.PUT_LINE(Rec: || rec); END;

事务管理

语法词

COMMIT;：提交事务 ROLLBACK;：回滚事务SAVEPOINT xxxSEO靠我x;：设置保存点ROLLBACK TO xxxxx;：回滚至保存点COMMIT WRITE IMMEDIATE NOWAIT;：提交并立即写入

保存点允许您回滚部分事务而不是整个事务。每个会话的活动保存SEO靠我点数不受限制。

回滚到保存点时，将擦除在该保存点之后标记的任何保存点。不会擦除回滚到的保存点。简单的回滚或提交会擦除所有保存点。

事务案例

DROP TABLE emp_name; CREASEO靠我TE TABLE emp_name AS SELECT employee_id, last_name FROM employees;CREATE UNIQUE INDESEO靠我X empname_ixON emp_name (employee_id);DROP TABLE emp_sal; CREATE TABLE emp_sal AS SESEO靠我LECT employee_id, salary FROM employees;CREATE UNIQUE INDEX empsal_ixON emp_sal (employee_idSEO靠我);DROP TABLE emp_job; CREATE TABLE emp_job AS SELECT employee_id, job_id FROSEO靠我M employees;CREATE UNIQUE INDEX empjobid_ixON emp_job (employee_id);DECLAREemp_id NUMBER(6);emp_lastSEO靠我name VARCHAR2(25);emp_salary NUMBER(8, 2);emp_jobid VARCHAR2(10); BEGINSELECT employee_id, lSEO靠我ast_name, salary, job_idINTO emp_id, emp_lastname, emp_salary, emp_jobidFROM employeesWHERE employeeSEO靠我_id = 120;INSERT INTO emp_name (employee_id, last_name)VALUES (emp_id, emp_lastname);-- 保存点SAVEPOINTSEO靠我 do_insert;INSERT INTO emp_sal (employee_id, salary)VALUES (emp_id, emp_salary);IF SQL%ROWCOUNT <= 0SEO靠我 THEN-- 回滚到保存点ROLLBACK TO do_insert;END IF;INSERT INTO emp_job (employee_id, job_id)VALUES (emp_id, SEO靠我emp_jobid);-- 提交事务,提交事务，并且立即写入 COMMIT WRITE IMMEDIATE NOWAIT; EXCEPTIONWHEN DUP_VAL_ON_INDEXSEO靠我 THEN-- 回滚事务ROLLBACK;DBMS_OUTPUT.PUT_LINE(Inserts were rolled back); END;

设置事务级别

SET TRANSACTISEO靠我ON ISOLATION LEVEL SERIALIZABLE;

设置私有事务

可以给函数、存储过程等等设置私有事务，进入带有私有事务的函数、存储过程时，主事务将被挂起，私有事务不影响主事务。

启动后，自治SEO靠我事务是完全独立的。它不与主事务共享锁、资源或提交依赖项。您可以记录事件、递增重试计数器等，即使主事务回滚也是如此。

自主事务可帮助您构建模块化、可重用的软件组件。您可以将自治事务封装在存储的子程序中。调SEO靠我用应用程序不需要知道该存储子程序执行的操作是成功还是失败。

PRAGMA AUTONOMOUS_TRANSACTION; 声明私有事务 -- 案例1：函数私有事务 SEO靠我 CREATE OR REPLACE PACKAGE emp_actions AUTHID DEFINER AS -- package specification FUNCTSEO靠我ION raise_salary (emp_id NUMBER, sal_raise NUMBER)RETURN NUMBER; END emp_actions; / SEO靠我 CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body -- code for function rSEO靠我aise_salaryFUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)RETURN NUMBER ISPRAGMA AUTONOMOUS_SEO靠我TRANSACTION;new_sal NUMBER(8,2);BEGINUPDATE employees SET salary =salary + sal_raise WHERE employee_SEO靠我id = emp_id;COMMIT;SELECT salary INTO new_sal FROM employeesWHERE employee_id = emp_id;RETURN new_saSEO靠我l;END raise_salary; END emp_actions; / -- 案例二：存储过程私有事务 CREATE OR REPSEO靠我LACE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER)AUTHID DEFINER ASPRAGMA AUTONOMOUSSEO靠我_TRANSACTION; BEGINUPDATE employeesSET salary = salary - amountWHERE employee_id = emp_id;COSEO靠我MMIT; END lower_salary; / -- 案例3：声明私有事务 DROP TABLE emp; CREASEO靠我TE TABLE emp AS SELECT * FROM employees;DECLAREPRAGMA AUTONOMOUS_TRANSACTION;emp_id NUMBER(6) := 200SEO靠我;amount NUMBER(6,2) := 200; BEGINUPDATE employeesSET salary = salary - amountWHERE employee_SEO靠我id = emp_id;COMMIT; END; 调用私有事务的测试 DROP TABLE debug_output; CREATE SEO靠我TABLE debug_output (message VARCHAR2(200));CREATE OR REPLACE PACKAGE debugging AUTHID DEFINER ASFUNCSEO靠我TION log_msg (msg VARCHAR2) RETURN VARCHAR2; END debugging; / CREATE OR REPLSEO靠我ACE PACKAGE BODY debugging ASFUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 ISPRAGMA AUTONOMOUS_TRASEO靠我NSACTION;BEGININSERT INTO debug_output (message) VALUES (msg);COMMIT;RETURN msg;END; END debSEO靠我ugging; / -- 查询时调用包函数 DECLAREmy_emp_id NUMBER(6);my_last_name VARCHAR2(25);mSEO靠我y_count NUMBER; BEGINmy_emp_id := 120;SELECT debugging.log_msg(last_name)INTO my_last_nameFRSEO靠我OM employeesWHERE employee_id = my_emp_id;/* 即使您在此这里回滚，插入“debug_output”的操作依然还是提交了，因为它是自主事务，不受外部事务影响。SEO靠我 */ROLLBACK; END; /

设置只读事务

您可以使用该语句开始只读或读写事务、建立隔离级别或将当前事务分配给指定的回滚段。SET``TRANSACTION

只读事SEO靠我务对于在其他用户更新相同表时运行多个查询非常有用。

在只读事务期间，所有查询都引用数据库的同一快照，从而提供多表、多查询、只读一致性视图。其他用户可以像往常一样继续查询或更新数据。提交或回滚将结束事务。SEO靠我

该语句必须是只读事务中的第一个 SQL 语句，并且在事务中只能出现一次。如果将事务设置为 ，则后续查询仅看到事务开始之前提交的更改。的使用不会影响其他用户或交易

DECLAREdaily_order_tSEO靠我otal NUMBER(12,2);weekly_order_total NUMBER(12,2);monthly_order_total NUMBER(12,2); BEGINCOMSEO靠我MIT; -- 提交当前事务 -- 设置 READ ONLY 事务SET TRANSACTION READ ONLY NAME Calculate Order Totals;SELECSEO靠我T SUM (order_total)INTO daily_order_totalFROM ordersWHERE order_date = SYSDATE;SELECT SUM (order_totSEO靠我al)INTO weekly_order_totalFROM ordersWHERE order_date = SYSDATE - 7;SELECT SUM (order_total)INTO monSEO靠我thly_order_totalFROM ordersWHERE order_date = SYSDATE - 30; -- 结束 read-only transactionCOMMISEO靠我T; END;

触发器

触发器根据触发语句及其作用的项目来指定触发事件，触发器有： DML 触发器、系统触发器、条件触发器。

触发器的作用：

自动生成虚拟列值记录事件收集有关表访问的统计信息针对SEO靠我视图发出 DML 语句时修改表数据当子表和父表位于分布式数据库的不同节点上时强制实施参照完整性将有关数据库事件、用户事件和 SQL 语句的信息发布到订阅应用程序防止在正常工作时间之后对表执行 DML SEO靠我操作防止无效交易强制实施无法使用约束定义的复杂业务或参照完整性规则

DML 触发器

一个简单的 DML 触发器正好在以下时间点之一触发：

在触发语句运行之前

（触发器称为 BEFORE 语句触发器或语句级 `SEO靠我`**BEFORE 触发器*。*)

触发语句运行后

（该触发器称为 AFTER 语句触发器或语句级 ``**AFTER 触发器*。*)

在触发语句影响的每一行之前

（该触发器称为每行*``*触发器之前或行级别SEO靠我 BEFORE 触发器。)

在触发语句影响的每一行之后

（触发器称为*每行触发器或行级别* AFTER 触发器。)

触发器案例

大量案例在这里

/* 设置一个抛异常终止的触发器 */ CREATSEO靠我E OR REPLACE TRIGGER dept_restrictBEFORE DELETE OR UPDATE OF Deptno ON deptFOR EACH ROW--在从部门中删除行或更新SEO靠我部门的主键 （DEPTNO） 之前，-- 检查 EMP 中的从属外键值;-- 如果找到任何内容，请回滚。DECLAREDummy INTEGER; -- Use for cursor fetchempSEO靠我loyees_present EXCEPTION;employees_not_present EXCEPTION;PRAGMA EXCEPTION_INIT (employees_present, -SEO靠我4094);PRAGMA EXCEPTION_INIT (employees_not_present, -4095);-- Cursor used to check for dependent forSEO靠我eign key values.CURSOR Dummy_cursor (Dn NUMBER) ISSELECT Deptno FROM emp WHERE Deptno = Dn;BEGINOPENSEO靠我 Dummy_cursor (:OLD.Deptno);FETCH Dummy_cursor INTO Dummy;-- 如果找到依赖外键，则引发用户指定的外键 错误代码和消息，通过抛异常终止或回滚事SEO靠我务。如果未找到，关闭光标-- 在允许触发语句完成之前。IF Dummy_cursor%FOUND THENRAISE employees_present; -- Dependent rows exisSEO靠我tELSERAISE employees_not_present; -- No dependent rows existEND IF;CLOSE Dummy_cursor;EXCEPTIONWHEN SEO靠我employees_present THENCLOSE Dummy_cursor;-- 通过抛异常终止或回滚事务Raise_application_error(-20001, Employees PrSEO靠我esent in|| Department || TO_CHAR(:OLD.DEPTNO));WHEN employees_not_present THENCLOSE Dummy_cursor; SEO靠我 END; /* 设置一个监听sql后插入日志的触发器 */ CREATE OR REPLACE TRIGGER log_salary_increaseAFTSEO靠我ER UPDATE OF salary ON employeesFOR EACH ROW BEGININSERT INTO Emp_log (Emp_id, Log_date, NewSEO靠我_salary, Action)VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, New Salary); END;

异常处理

-- 代码逻辑SEO靠我 ... EXCEPTIONWHEN ex_name_1 THENstatements_1 -- Exception handlerWHEN ex_name_2 OR SEO靠我ex_name_3 THEN statements_2 -- Exception handlerWHEN OTHERS THENROLLBACK;RAISE; -- 再向外面抛出异常 SEO靠我END; ... -- 如果只是处理异常，继续执行，还是可以在EXCEPTION后加代码逻辑

存储过程demo

CREATE OR REPLACE PROCEDURE P_TEST(tesSEO靠我tParams IN varchar2, -- 这个可以是多个参数，用, 分割returnMsg IN OUT varchar2 -- 这个作为返回信息 ) ISLOOP_COUNT SEO靠我number DEFAULT 5; /* 获取全局锁的最大重试次数 */ID VARCHAR2(16) DEFAULT ; /* 存储锁的主键ID */LOCK_VERSION INTEGER DEFSEO靠我AULT 0; /* 存储锁的版本变量 */UP_LOCK_SQL varchar2(512) DEFAULT ; /* 更新锁的sql */UP_NUM number DEFAULT 0; /* 更SEO靠我新锁的行数，作为是否更新成功的标志 */CURSOR VERSIONS IS SELECT ID,VERSIONSFROM TEST_LOCKWHERE ID = APP_SERVERS_LOCK; SEO靠我/* 获取锁版本的游标sql，游标可以查询多个数据，并进行存储 */APP_COUNT INTEGER DEFAULT 0; /* 测试查询sql */ BEGIN-- 使用 WHILSEO靠我E 循环WHILE LOOP_COUNT > 0LOOP-- ============ 使用游标 ========OPEN VERSIONS; -- 打开游标/*游标无法打开*/IF VERSIONSSEO靠我%ISOPEN = FALSE THENreturnMsg := 异常原因:游标打开失败;CLOSE VERSIONS;RETURN;END IF;FETCH VERSIONS INTO ID,LOCSEO靠我K_VERSION; -- 使用FETCH获取游标结果 -- EXIT WHEN VERSIONS%NOTFOUND; -- 当VERSIONS是数组需要遍历时，，可以用这个判断是否遍SEO靠我历完成，从而退出循环-- 使用完毕后，关闭游标IF VERSIONS%ISOPEN THENCLOSE VERSIONS;END IF; -- ========== 执行自定义sql SEO靠我===========-- 为了使用变量参数，这里使用 || 进行拼接，UP_LOCK_SQL :=UPDATE TEST_LOCK SET VERSIONS = || LOCK_VERSION ||SEO靠我 + 1 WHERE ID = APP_SERVERS_LOCK AND VERSIONS = || LOCK_VERSION || ;-- 执行sqlEXECUTE IMMEDIATE UP_LOCSEO靠我K_SQL;-- 获取更新结果UP_NUM := SQL%ROWCOUNT;-- 如果更新成功，跳出循环IF UP_NUM = 1 THEN-- -- 跳出本次循环 -- CONTINSEO靠我UE;-- 结束循环EXIT;END IF;-- 代码逻辑，查询版本，更新，重试次数5次LOOP_COUNT := LOOP_COUNT - 1;END LOOP; -- ======SEO靠我====== 使用for 循环FOR v_counter2 IN 1..5LOOP<<GO_BACK>> -- 定义一个返回点-- 将 COUNT(*) 结果放入变量 APP_COUNTSELECT SEO靠我COUNT(*) INTO APP_COUNT FROM TEST_LOCK;IF APP_COUNT > 0 THENUPDATE TEST_LOCK SET VERSIONS=VERSIONS+1SEO靠我 WHERE ID=APP_SERVERS_LOCK;-- -- 跳出本次循环 -- CONTINUE;-- 结束循环EXIT;ELSEGOTO GO_BACK;END IF;END SEO靠我LOOP;END ;

案例所需建表语句

CREATE TABLE TEST_LOCK (ID VARCHAR2(16) NOT NULLCONSTRAINT "TEST_LOCK_pk"PSEO靠我RIMARY KEY,VERSIONS NUMBER(16) NOT NULL ) /COMMENT ON TABLE TEST_LOCK IS 全局锁的表 SEO靠我 /COMMENT ON COLUMN TEST_LOCK.ID IS 唯一id /COMMENT ON COLUMN TEST_LOCK.VERSIONS IS 版本号; SEO靠我 /INSERT INTO SUNCPS.TEST_LOCK(ID, VERSIONS) VALUES (APP_SERVERS_LOCK, 0); DECLARE c PLSSEO靠我_INTEGER; d BINARY_FLOAT;BEGIN /p(1, 2, c, d);/dbms_output.PUT_LINE(c);/dbms_output.PUT_LINE(d);/ SEO靠我 END;