oracle存储过程的使用

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	包含

逻辑运算符

x	y	x AND y	x OR y	NOT 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种方式

1. LOOP简单循环

   LOOP-- 代码逻辑
   END LOOP;
   

2. WHILE循环

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

3. 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 异常”

遍历隐式游标的结果

直接使用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;