阅读导航
Oracle数据库将每一个执行SQL从开始解析到执行，再到获取数据分拆为多个步骤处理，这些步骤包括：
- create cursor
- parse statement
- descript query result(query only)
- define result output(query only)
- Bind variables and Parallelize
- Parallelize the statement(optional)
- define column
- Execute and Fetch
- Fetch rows of a query(query only)
- Cursor value
- Close the cursor
其中解析(parse)、绑定变量(binds)、执行(execute)是所有步骤中最为核心的部分，理解它们的工作方式和原理对于优化SQL非 常的重要。在实际工作中Oracle允许不同的程序接口、在不同的层面控制这些步骤，已达到最好的运行性能。下面的内容将会介绍和总结每个阶段的内容。
@myinfo
HOST_NAME INSTANCE_NAME VERSION STARTED SID SERIAL# USERNAME SPID
---------- --------------- --------------- -------------------- ---------- ---------- --------------- ----------
LinuxDB sydb 11.2.0.4.0 2016-04-18 19:48:30 442 23845 OPS$SYWU 24492
1 Cursor Step
1.1 Create cursor
数据库必须创建和打开游标处理SQL语句，当成功创建游标后，Oracle分配一个唯一的、只能同时被创建的进程访问的游标号，游标包含当前的行位置，并随着每个阶段的操作、提取到的行而向前移动，直到没有行为止。
variable l_cur number
exec :l_cur:=dbms_sql.open_cursor
print l_cur
L_CUR
----------
2125761307
创建了一个本地游标变量l_cur,该变量在创建游标是被赋值，这个游标号非常非常的重要，它将贯穿于SQL Cursor生命周期的所有阶段，这里系统分配2125761307作为本次的游标号。当执行open_cursor函数后，Oracle server实际创建了一个空的游标，状态为： NULL；
alter session set events 'immediate trace name errorstack,level 3';
----- Session Cursor Dump -----
Cursor#2(0x7f3a4bfe1c78) state=NULL curiob=(nil)
curflg=45 fl2=0 par=(nil) ses=0x71c5bb80
1.2 Parse statement
variable l_sql varchar2(300)
exec :l_sql:='select/*+ test_sql_cursor */ e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=:b_empno'
execute dbms_sql.parse(:l_cur,:l_sql,dbms_sql.native);
解析发生时，oracle server会先到share pool中查找匹配的，如果找到则发生软解析，没有找到则发生硬解析。解析阶段的工作包括：在共享池中分配空间、生成SQL HashValue,SQL ID、语义检查、对象检查、权限检查；在解析后，再dump，cursor的状态变为： PARSE；
Cursor#2(0x7ffb8e921c78) state=PARSE curiob=0x7ffb8e7d0450
curflg=4d fl2=0 par=(nil) ses=0x71980f80
----- Dump Cursor sql_id=bssx08n5xfsxx xsc=0x7ffb8e7d0450 cur=0x7ffb8e921c78 -----
LibraryHandle: Address=0x64895e30 Hash=bd763bd LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select/*+ test_sql_cursor */ e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=:b_empno
FullHashValue=dc2fade63f4e3fd7bc63a0450bd763bd Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=198665149 OwnerIdn=43
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x64895ee0(0, 1, 0, 0) Mutex=0x64895f70(0, 44, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x64895ec0[0x64895ec0,0x64895ec0]
Pin=0x64895ea0[0x64895ea0,0x64895ea0]
LoadLock=0x64895f18[0x64895f18,0x64895f18]
Timestamp: Current=04-18-2016 19:56:49
HandleReference: Address=0x64896050 Handle=(nil) Flags=[00]
LibraryObject: Address=0x622d7140 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x622d7ff0 Reference=0x622d7a30 Handle=0x673dc030
Children:
Child: childNum='0'
LibraryHandle: Address=0x673dc030 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x673dc0e0(0, 0, 0, 0) Mutex=0x64895f70(0, 44, 0, 0)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x673dc0c0[0x673dc0c0,0x673dc0c0]
Pin=0x673dc0a0[0x673dc0a0,0x673dc0a0]
LoadLock=0x673dc118[0x673dc118,0x673dc118]
LibraryObject: Address=0x617b2090 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x617b2130 Heap6=0x6ac68e10 Heap0 Load Time=04-18-2016 19:56:49 Heap6 Load Time=04-18-2016 19:56:49
NamespaceDump:
Parent Cursor: sql_id=bssx08n5xfsxx parent=0x622d71e0 maxchild=1 plk=y ppn=n kkscs=0x622d76c0 nxt=(nil) flg=14 cld=0 hd=0x673dc030 par=0x622d71e0
Mutex 0x622d76c0(0, 0) idn 3000000000
ct=0 hsh=0 unp=(nil) unn=0 hvl=622d8088 nhv=1 ses=0x71980f80
hsv[0]=0
hep=0x622d7758 flg=80 ld=1 ob=0x617b2090 ptr=0x6ac68e10 fex=0x6ac681d0
cursor instantiation=0x7ffb8e7d0450 used=1460980609 exec_id=0 exec=0
child#0((nil)) pcs=0x622d76c0
clk=(nil) ci=(nil) pn=(nil) ctx=(nil)
kgsccflg=0 llk[0x7ffb8e7d0458,0x7ffb8e7d0458] idx=0
xscflg=100032 fl2=40000 fl3=2062000 fl4=8000
No bind info: cannot access child information block
and the oacdefs are not stored in the instantiation
Frames pfr (nil) siz=0 efr (nil) siz=0
Cursor frame dump
1.3 descript and define
接着的cursor要进行descript和define；descript阶段根据上面的解析描述SQL要获得什么和那些列值信息，这里是ename ename、deptno、dname列；
define阶段根据descript阶段信息定义接住(hold)输出列的数据类型、大小、位置。
这两个步骤只在SELECT语句中发生，是内部操作完成的，不允许像SQL PLUS这样的客户端工具控制。
1.4 Bind variables and Parallelize
variable l_empno number
exec :l_empno:=7369
exec dbms_sql.bind_variable(:l_cur,':b_empno',:l_empno);
绑定变量需要告诉oracle server绑定变量的地址和实际值，这里定义变量l_empno，赋值7369，并绑定到:b_empno地址上(这里只是绑定到地址上，并没有put到SQL语句中)；此时在dump，cursor的状态为： BOUND；
Cursor#2(0x7ffb8e921c78) state=BOUND curiob=0x7ffb8e7d0450
curflg=4d fl2=0 par=(nil) ses=0x71980f80
----- Dump Cursor sql_id=bssx08n5xfsxx xsc=0x7ffb8e7d0450 cur=0x7ffb8e921c78 -----
LibraryHandle: Address=0x64895e30 Hash=bd763bd LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select/*+ test_sql_cursor */ e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=:b_empno
FullHashValue=dc2fade63f4e3fd7bc63a0450bd763bd Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=198665149 OwnerIdn=43
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x64895ee0(0, 1, 0, 0) Mutex=0x64895f70(0, 117, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x64895ec0[0x64895ec0,0x64895ec0]
Pin=0x64895ea0[0x64895ea0,0x64895ea0]
LoadLock=0x64895f18[0x64895f18,0x64895f18]
Timestamp: Current=04-18-2016 19:56:49
HandleReference: Address=0x64896050 Handle=(nil) Flags=[00]
LibraryObject: Address=0x622d7140 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x622d7ff0 Reference=0x622d7a30 Handle=0x673dc030
Children:
Child: childNum='0'
LibraryHandle: Address=0x673dc030 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 TotalLockCount=2 TotalPinCount=3
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x673dc0e0(0, 0, 0, 0) Mutex=0x64895f70(0, 117, 0, 0)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x673dc0c0[0x673dc0c0,0x673dc0c0]
Pin=0x673dc0a0[0x673dc0a0,0x673dc0a0]
LoadLock=0x673dc118[0x673dc118,0x673dc118]
LibraryObject: Address=0x617b2090 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x617b2130 Heap6=0x61784b78 Heap0 Load Time=04-18-2016 19:56:49 Heap6 Load Time=04-18-2016 22:53:11
NamespaceDump:
Parent Cursor: sql_id=bssx08n5xfsxx parent=0x622d71e0 maxchild=1 plk=y ppn=n kkscs=0x622d76c0 nxt=(nil) flg=14 cld=0 hd=0x673dc030 par=0x622d71e0
Mutex 0x622d76c0(0, 0) idn 3000000000
ct=1 hsh=0 unp=(nil) unn=0 hvl=622d7fd0 nhv=1 ses=0x71980f80
hsv[0]=0
hep=0x622d7758 flg=80 ld=1 ob=0x617b2090 ptr=0x61784b78 fex=0x61783f38
cursor instantiation=0x7ffb8e7d0450 used=1460980609 exec_id=0 exec=0
child#0((nil)) pcs=0x622d76c0
clk=(nil) ci=(nil) pn=(nil) ctx=(nil)
kgsccflg=0 llk[0x7ffb8e7d0458,0x7ffb8e7d0458] idx=0
xscflg=101432 fl2=1141800 fl3=2062140 fl4=8000
sharing failure(s)=1000000000000000
----- Bind Info (kxscoacd) -----
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=0001 frm=00 csi=00 siz=0 off=0
No bind buffers allocated
Frames pfr (nil) siz=0 efr (nil) siz=0
Cursor frame dump
kxscphp=0x7ffb8e8797e8 siz=984 inu=304 nps=176
如上所说，此时的绑定变量没有put到SQL语句中，只是在内存中，所以此时的Bind Info中绑定变量的值为 No bind buffers allocated；
Oracle server在解析阶段决定那些语句可以并行执行(比如有些SQL使用了Hint，或者对象被设置了默认并行>1)、那些不能、或者已经存在并行计 划，将总的工作分拆为多个并行子进程完成；如果有足够的资源，并行会提升SQL的执行效率和速度，这些信息在执行阶段非常的重要。
1.5 define column
在从cursor中取出值前，必须先进行定义，定义的内容为：名称、位置、数据类型、长度，这些信息决定了可以提取的值信息。
variable l_var_ename varchar2(30)
variable l_num_deptno number
variable l_var_dname varchar2(30)
exec DBMS_SQL.DEFINE_COLUMN(:l_cur, 1, :l_var_ename,30);
exec DBMS_SQL.DEFINE_COLUMN(:l_cur, 2, :l_num_deptno);
exec DBMS_SQL.DEFINE_COLUMN(:l_cur, 3, :l_var_dname,30);
1.6 Execute and Fetch
variable l_exec number
execute :l_exec:=dbms_sql.execute(:l_cur);
print l_exec
在EXECUTE阶段，Oracle server put所有的绑定变量信息到SQL语句，CBO优化器根据统计信息生成执行计划，执行语句，oracle server根据define阶段的定义产生结果集；execute和fetch阶段是一起执行的，所以在dump中，cursor的状态为： FETCH，而不是EXECUTE。
Cursor#2(0x7ffb8e921c78) state=FETCH curiob=0x7ffb8e7d0450
curflg=4f fl2=0 par=(nil) ses=0x71980f80
----- Dump Cursor sql_id=bssx08n5xfsxx xsc=0x7ffb8e7d0450 cur=0x7ffb8e921c78 -----
LibraryHandle: Address=0x64895e30 Hash=bd763bd LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select/*+ test_sql_cursor */ e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=:b_empno
FullHashValue=dc2fade63f4e3fd7bc63a0450bd763bd Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=198665149 OwnerIdn=43
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x64895ee0(0, 1, 0, 0) Mutex=0x64895f70(0, 173, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x64895ec0[0x64895ec0,0x64895ec0]
Pin=0x64895ea0[0x64895ea0,0x64895ea0]
LoadLock=0x64895f18[0x64895f18,0x64895f18]
Timestamp: Current=04-18-2016 19:56:49
HandleReference: Address=0x64896050 Handle=(nil) Flags=[00]
LibraryObject: Address=0x622d7140 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x622d7ff0 Reference=0x622d7a30 Handle=0x673dc030
Children:
Child: childNum='0'
LibraryHandle: Address=0x673dc030 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=7
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x673dc0e0(0, 0, 0, 0) Mutex=0x64895f70(0, 173, 0, 0)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x673dc0c0[0x673dc0c0,0x673dc0c0]
Pin=0x673dc0a0[0x673dc0a0,0x673dc0a0]
LoadLock=0x673dc118[0x673dc118,0x673dc118]
LibraryObject: Address=0x617b2090 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^bd763bd pins=0 Change=NONE
Heap=0x6955a030 Pointer=0x617b2130 Extent=0x617b2010 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=2.882812 Size=3.937500 LoadTime=18359013700
Block: #='6' name=SQLA^bd763bd pins=0 Change=NONE
Heap=0x622d7800 Pointer=0x61784b78 Extent=0x61783f38 Flags=I/-/P/A/-/E
FreedLocation=0 Alloc=13.390625 Size=15.820312 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x617b2130 Heap6=0x61784b78 Heap0 Load Time=04-18-2016 19:56:49 Heap6 Load Time=04-18-2016 23:36:16
NamespaceDump:
Parent Cursor: sql_id=bssx08n5xfsxx parent=0x622d71e0 maxchild=1 plk=y ppn=n kkscs=0x622d76c0 nxt=(nil) flg=18 cld=0 hd=0x673dc030 par=0x622d71e0
Mutex 0x622d76c0(0, 0) idn 3000000000
ct=1 hsh=0 unp=(nil) unn=0 hvl=622d7fd0 nhv=0 ses=(nil)
hep=0x622d7758 flg=80 ld=1 ob=0x617b2090 ptr=0x61784b78 fex=0x61783f38
cursor instantiation=0x7ffb8e7d0450 used=1460993776 exec_id=16777216 exec=1
child#0(0x673dc030) pcs=0x622d76c0
clk=0x6a05d410 ci=0x617b2130 pn=0x60ca97d8 ctx=0x61784b78
kgsccflg=0 llk[0x7ffb8e7d0458,0x7ffb8e7d0458] idx=0
xscflg=c0150476 fl2=45040001 fl3=42262108 fl4=100
sharing failure(s)=1000000000000000
----- Bind Byte Code (IN) -----
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 0
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7ffb8e93bf48 bln=22 avl=03 flg=05
value=7369
Frames pfr 0x7ffb8e8c91b8 siz=6944 efr 0x7ffb8e87e280 siz=6928
Cursor frame dump
enxt: 5.0x00000010 enxt: 4.0x00000af0 enxt: 3.0x000001f0 enxt: 2.0x00000020
enxt: 1.0x00000e10
pnxt: 1.0x00000010
kxscphp=0x7ffb8e8797e8 siz=1992 inu=1056 nps=928exec_id
kxscdfhp=0x7ffb8e930608 siz=984 inu=88 nps=0
kxscbhp=0x7ffb8e879368 siz=984 inu=168 nps=48
变量put到SQL语句中 value=7369；SQL也成功执行exec_id=16777216 exec=1；
fetch阶段使用array fetch的方式将一个或多个结果在一次fetch call操作取到集合中。
1.7 Fetch rows of a query
variable l_count number
exec :l_count:=DBMS_SQL.FETCH_ROWS(:l_cur);
print :l_count
L_COUNT
----------
1
当fetch阶段将数据fetch到集合后，调用fetch_rows函数提取行数据，函数返回集合中的行数。此时Cursor的状态为： ROW；
Cursor#2(0x7f3a4bfe1c78) state=ROW curiob=0x7f3a4bfa2e10
curflg=4f fl2=0 par=(nil) ses=0x71c5bb80
----- Dump Cursor sql_id=a0848wwrb3qvj xsc=0x7f3a4bfa2e10 cur=0x7f3a4bfe1c78 -----
LibraryHandle: Address=0x60a0e4a8 Hash=2eb1db71 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.empno=:b_empno
FullHashValue=67d402fc9643a2b2a02088e72eb1db71 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=783407985 OwnerIdn=43
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x60a0e558(0, 1, 0, 0) Mutex=0x60a0e5e8(0, 27, 0, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x60a0e538[0x60a0e538,0x60a0e538]
Pin=0x60a0e518[0x60a0e518,0x60a0e518]
LoadLock=0x60a0e590[0x60a0e590,0x60a0e590]
Timestamp: Current=04-13-2016 22:41:59
HandleReference: Address=0x60a0e6b0 Handle=(nil) Flags=[00]
LibraryObject: Address=0x672eb6b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x672ec560 Reference=0x672ebfa0 Handle=0x6134c810
Children:
Child: childNum='0'
LibraryHandle: Address=0x6134c810 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=7
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x6134c8c0(0, 0, 0, 0) Mutex=0x60a0e5e8(0, 27, 0, 0)
Flags=RON/PIN/PN0/EXP/CHD/[10012111]
WaitersLists:
Lock=0x6134c8a0[0x6134c8a0,0x6134c8a0]
Pin=0x6134c880[0x6134c880,0x6134c880]
LoadLock=0x6134c8f8[0x6134c8f8,0x6134c8f8]
LibraryObject: Address=0x66c9bbe8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x66c9bc88 Heap6=0x60c76ca0 Heap0 Load Time=04-13-2016 22:41:59 Heap6 Load Time=04-13-2016 23:07:59
NamespaceDump:
Parent Cursor: sql_id=a0848wwrb3qvj parent=0x672eb750 maxchild=1 plk=y ppn=n kkscs=0x672ebc30 nxt=(nil) flg=18 cld=0 hd=0x6134c810 par=0x672eb750
Mutex 0x672ebc30(0, 0) idn 3000000000
ct=0 hsh=0 unp=(nil) unn=0 hvl=672ec5f8 nhv=0 ses=(nil)
hep=0x672ebcc8 flg=80 ld=1 ob=0x66c9bbe8 ptr=0x60c76ca0 fex=0x60c76060
cursor instantiation=0x7f3a4bfa2e10 used=1460560079 exec_id=0 exec=1
child#0(0x6134c810) pcs=0x672ebc30
clk=0x6a33d640 ci=0x66c9bc88 pn=(nil) ctx=0x60c76ca0
kgsccflg=0 llk[0x7f3a4bfa2e18,0x7f3a4bfa2e18] idx=0
xscflg=81150436 fl2=5040001 fl3=2062108 fl4=8000
----- Bind Byte Code (IN) -----
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 0
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
No bind buffers allocated
Frames pfr 0x7f3a4bf77120 siz=6944 efr 0x7f3a4be6ed58 siz=6928
Cursor frame dump
enxt: 5.0x00000010
pnxt: 1.0x00000010
kxscphp=0x7f3a4bf84a90 siz=1992 inu=1056 nps=928
kxscdfhp=0x7f3a4bf84cd0 siz=984 inu=88 nps=0
1.8 Cursor value
fetch rows后调用column_value取数据；
variable l_out_ename varchar2(30)
variable l_out_deptno number
variable l_out_dname varchar2(30)
exec DBMS_SQL.COLUMN_VALUE(:l_cur, 1,:l_out_ename);
exec DBMS_SQL.COLUMN_VALUE(:l_cur, 2,:l_out_deptno);
exec DBMS_SQL.COLUMN_VALUE(:l_cur, 3,:l_out_dname);
exec dbms_output.put_line('name:'||:l_out_ename||',deptno:'||:l_out_deptno||',dname:'||:l_out_dname)
name:SMITH,deptno:20,dname:RESEARCH
1.9 Close the cursor
在执行关闭前，cursor可以再做除创建和打开之后的操作，比如再解析，再执行，再Fetch；
最后调用close_cursor函数关闭cursor；
exec DBMS_SQL.close_cursor(:l_cur);
到此，整个SQL cursor生命周期结束。