移动互联网 网站建设,飞机选做网站,手机微信一体网站建设,深圳做网站的公司哪个好文章目录oracle存储过程的使用基本结构管理存储过程调用存储过程的方法存储过程参数关键词#xff1a; IN 和outin/out测试案例调用in/out测试案例存储过程语法DECLARE声明关键词赋值使用in/out将值作为子程序的参数分配给变量#xff0c;看上面的案例为布尔变量赋值表达式串…
文章目录oracle存储过程的使用基本结构管理存储过程调用存储过程的方法存储过程参数关键词 IN 和outin/out测试案例调用in/out测试案例存储过程语法DECLARE声明关键词赋值使用in/out将值作为子程序的参数分配给变量看上面的案例为布尔变量赋值表达式串联符||运算符优先级逻辑运算符短运算符or 和and比较运算符is null /is not null关系运算符LIKE BETWEEN 和INcase when条件语句ifif语法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 : a10; -- 为 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 : 82*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运算符优先级
算子操作**幂,-同一性否定*,/乘法、除法, ,-, , , , , , , , , , , ,!~ ^ISNULLLIKEBETWEENIN比较NOT否定AND连接OR包含
逻辑运算符
xyx AND yx OR yNOT xTRUETRUETRUETRUEFALSETRUEFALSEFALSETRUEFALSETRUENULLNULLTRUEFALSEFALSETRUEFALSETRUETRUEFALSEFALSEFALSEFALSETRUEFALSENULLFALSENULLTRUENULLTRUENULLTRUENULLNULLFALSEFALSENULLNULLNULLNULLNULLNULLNULL
短运算符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
FALSEBETWEEN 和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 a2 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_hereDBMS_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 TrueWHEN FALSE THEN FalseEND);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.methodDELETE程序从集合中删除元素。TRIM程序从阵列或嵌套表的末尾删除元素。EXTEND程序将元素添加到阵列或嵌套表的末尾。EXISTS功能当且仅当 varray 或嵌套表的指定元素存在时返回。TRUEFIRST功能返回集合中的第一个索引。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 异常”
遍历隐式游标的结果
直接使用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;
/设置只读事务
您可以使用该语句开始只读或读写事务、建立隔离级别或将当前事务分配给指定的回滚段。SETTRANSACTION
只读事务对于在其他用户更新相同表时运行多个查询非常有用。
在只读事务期间所有查询都引用数据库的同一快照从而提供多表、多查询、只读一致性视图。其他用户可以像往常一样继续查询或更新数据。提交或回滚将结束事务。
该语句必须是只读事务中的第一个 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..5LOOPGO_BACK -- 定义一个返回点-- 将 COUNT(*) 结果放入变量 APP_COUNTSELECT COUNT(*) INTO APP_COUNT FROM TEST_LOCK;IF APP_COUNT 0 THENUPDATE TEST_LOCK SET VERSIONSVERSIONS1 WHERE IDAPP_SERVERS_LOCK;-- -- 跳出本次循环
-- CONTINUE;-- 结束循环EXIT;ELSEGOTO GO_BACK;END IF;END LOOP;END ;案例所需建表语句
CREATE TABLE TEST_LOCK
(ID VARCHAR2(16) NOT NULLCONSTRAINT TEST_LOCK_pkPRIMARY 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;
