深入解析Cursor和绑定变量

Cursor的种类
• explicit cursor（续）
Demo: p_demo_rowtype.prc p_demo_explicit_cursor_attr.prc p_demo_explicit_cursor_std.prc
Cursor的种类
• ref cursor
like a cursor, a ref cursor points to the current row in the result set of a multi-row query. A ref cursor is more flexible because it is not tied to a specific query. You can open a ref cursor for any query that returns the right set of columns. this is a PL/SQL-only datatype declared. A ref cursor may be used to declare a variable, a formal parameter for a subprogram, or a function’s return value.
• 所谓的PL/SQL中的动态SQL就是指你要执行的SQL直到真 正执行的时候PL/SQL引SQL通常与绑定变量有关，使用绑定变量 的SQL，根据绑定方式的不同又可分为普通绑定与批量绑 定。 • 为什么要使用绑定变量（普通绑定和批量绑定）？ 减少硬解析和PL/SQL引擎和SQL引擎上下文切换的次数。
Cursor的种类
• ref cursor（续） open-for fetch close CURSORNAME%FOUND CURSORNAME%NOTFOUND CURSORNAME%ISOPEN CURSORNAME%ROWCOUNT Demo: p_demo_ref_cursor.prc
绑定变量的含义
四种标准模板的代码生成器
• Demo
P_SYS_GEN_CODE_TEMPLATE1.prc P_SYS_GEN_CODE_TEMPLATE2.prc P_SYS_GEN_CODE_TEMPLATE3.prc P_SYS_GEN_CODE_TEMPLATE4.prc
根据标准模板调整代码架构的实例
写好PL/SQL代码的一些原则
• 写cursor的sql语句的时候先执行一下这个sql，看一下它 的执行计划，如果其执行计划里有不必要的全表扫描，想 办法消除它。即尽量避免不必要的全表扫描！ • 如果有可能就用forall，而不是用while..loop和for • 用fetch .. bulk collect into代替fetch .. Into • varchar2(4000)与varchar2(32767) • 改正你的存储过程里的所有的warning信息 • out与out nocopy, in out与in out nocopy
Cursor的种类
• implicit cursor（续）
Implicit cursor attributes return information about the execution of DML statements, such as insert, update, delete and select into statements. The values of the cursor attributes always refer to the most recently executed SQL statement. Before Oracle opens the implicit cursor, the implicit cursor attributes yield NULL. SQL%FOUND SQL%NOTFOUND SQL%ISOPEN SQL%ROWCOUNT SQL%BULK_ROWCOUNT
深入解析Cursor和绑定变量
崔华（dbsnake)
About Me
• • • • 中航信工程师 恩墨科技特邀顾问 Oracle ACE ACOUG成员
Cursor的定义
• sharable sql structure
This is the object that lives in the shared pool and whose metadata is exposed in the v$sqlarea and v$sql views
Cursor的种类
• implicit cursor（续）
Demo: p_demo_implicit_cursor_attr.prc
Cursor的种类
• explicit cursor
An explicit cursor cannot be defined using dynamic SQL; embedded SQL is the only possibility. Critically, though the programmer invents the name of an explicit cursor, this is not a variable: it cannot be used as an actual argument in a subprogram invocation; nor can it be returned by a function. In this way, it is very much like a procedure; it can be forward declared and the declaration and the definition can be split between a package and its body; and it can have formal parameters.
Cursor的种类
• implicit cursor
Implicit cursors are managed automatically by PL/SQL so you are not required to write any code to handle these cursors. However, you can track information about the execution of an implicit cursor through its cursor attributes. It is the PL/SQL run-time system that manages the session cursor without the help of explicit language constructs that specify operations like open, parse, bind, execute, fetch, and close.
Cursor的种类
• ref cursor（续）
type typ_cur_dep is ref cursor return dep%rowtype; cur_dep typ_cur_dep; type typ_result is record(pk t.pk%type, v1 t.v1%type); type typ_cur_strong is ref cursor return typ_result; cur_strong typ_cur_stong; type typ_cur_weak is ref cursor; cur_weak typ_cur_weak; cur_weak_sys SYS_REFCURSOR;
• Demo
P_IIBTTOWBT_NC.prc P_IWB_IBT_TO_WBT_NC.prc
写好PL/SQL代码的一些原则
• “常用的PLSQL开发原则.doc” 中有我总结出来的常用的 PL/SQL开发原则。 • 如果你的SQL是固定的，不涉及到变量，那就直接写SQL 吧，这是最快的方式！比用execute immediate ‘SQLTEXT’要快。这种固定的SQL一般是出现在cursor所 在的循环之外，不应该出现在循环里面。 • cursor中的带变量的sql一定要使用execute immediate＋ 绑定变量。 • 请使用上述四个标准模板之一来搭建你的PL/SQL代码。 • 使用显式cursor的时候不要使用open for update或者open for update nowait，使用rowid完全可以用来代替它。
绑定变量
• Demo
p_demo_bind_common_use.prc p_demo_bind_no_semicolon.prc p_demo_bind_with_semicolon.prc
cursor与绑定变量混合使用的标准模板
• Demo
p_demo_cursor_bind_template1.prc p_demo_cursor_bind_template2.prc p_demo_cursor_bind_template3.prc p_demo_cursor_bind_template4.prc
绑定变量的注意事项
• 使用绑定变量的基本注意事项： 1、你不能用占位符代替表或者视图的名称，一般来说， 占位符通常用来代替where字句中的条件 2、绑定变量通常只适用于数值型或者字符型变量， BOOLEAN不能用于绑定变量 3、要注意动态SQL语句中什么时候能有分号，什么时候 又不能有分号 4、对于不带分号的动态SQL，占位符的命名是无所谓 的，这种情况using时传入的绑定变量的值取决于占位符 的位置，跟占位符的命名无关。但对于带分号的动态SQL ，占位符的命名就有所谓了。
Cursor的种类
• explicit cursor（续）
you use three commands to control a explicit cursor: OPEN, FETCH, and CLOSE. do not use ‘for update’ or ‘for update nowait’ when you open explicit cursor. p_demo_read_and_curforupd.prc ‘where current of cursorname’ equal to rowid! Every explicit cursor and cursor variable has four attributes: CURSORNAME%FOUND CURSORNAME%NOTFOUND CURSORNAME%ISOPEN CURSORNAME%ROWCOUNT