[20260108]sqlplus下exec以及execute immediate执行方式.txt
--//在sqlplus下exec以及execute immediate执行方式，两者执行非常容易混淆，做一些例子说明。
--//exec 实际上相当于begin .... end;的简写。
--//execute immediate 相当于动态执行sql语句，以及一些ddl语句。
--//通过例子说明：
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.exec例子：
SCOTT@book01p> variable c number;
SCOTT@book01p> exec select count(*) into :C from emp;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
2152714258 5y6f34y04zn0k 0 118802 804fd012 2026-01-08 17:23:06 16777217
SCOTT@book01p> @ sql_id 5y6f34y04zn0k
-- SQL_ID = 5y6f34y04zn0k come from shared pool
BEGIN select count(*) into :C from emp; END;
;
SCOTT@book01p> print c
C
----------
14
SCOTT@book01p> print :c
C
----------
14
--//可以直接想象成BEGIN .. END;
--//注意一定要定义1个变量into接收值，还可以发现一个特点无法使用返回多值。
--//也可以使用它调用存储过程。例子：
SCOTT@book01p> set timing on
SCOTT@book01p> exec dbms_session.sleep(2)
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.01
SCOTT@book01p> set timing off
3.execute immediate方式：
--//该方式可以一般用于在PL/SQL中动态执行sql语句，语句执行查询多个字段，可以"返回多条记录"。还可以使用比如truncate等ddl语
--//句，或者修改会话参数等待操作。
--//一般写在begin ..end之内。
SCOTT@book01p> exec 'create table deptx as select * from dept where 2 = 0';
BEGIN 'create table deptx as select * from dept where 2 = 0'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "create table deptx as select * from dept where 2 = 0" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
exec execute immediate 'select * from dept where deptno =:1 or dname=:2' using 10 , 'SALES';
PL/SQL procedure successfully completed.
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
2805684608 f265cyqmmqqc0 0 88448 a73b5980 2026-01-08 17:30:51 16777218
SCOTT@book01p> @ sql_id f265cyqmmqqc0
-- SQL_ID = f265cyqmmqqc0 come from shared pool
BEGIN execute immediate 'select * from dept where deptno =:1 or dname=:2' using 10 , 'SALES'; END;
;
--//注意最后的分号实际上不存在，我写的脚本自动加上的。
SCOTT@book01p> exec execute immediate 'select * from dept where deptno =:1 or dname=:2' using :c , 'SALES';
PL/SQL procedure successfully completed.
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
3142490339 3sgf0juxnx673 0 39139 bb4e98e3 2026-01-08 17:31:23 16777217
SCOTT@book01p> @ sql_id 3sgf0juxnx673
-- SQL_ID = 3sgf0juxnx673 come from shared pool
BEGIN execute immediate 'select * from dept where deptno =:1 or dname=:2' using :c , 'SALES'; END;
;
--//有一个小缺点没有信息显示，实际上没有fetch操作。
--//如果返回单个值，可以使用into接收，当然这样一定有fetch操作，写成如下：
SCOTT@book01p> exec execute immediate 'sElect count(*) from dept where deptno =:1 or dname=:2' into :c using 10 , 'SALES';
PL/SQL procedure successfully completed.
SCOTT@book01p> print :c
C
----------
2
--//看了相关文档，如果返回多行记录报错，仅仅接收一行记录。如果返回多个字段值，可以定义多个变量接收。
SCOTT@book01p> variable v_deptno number ;
SCOTT@book01p> variable v_dname varchar2(14) ;
SCOTT@book01p> variable v_loc varchar2(14) ;
SCOTT@book01p> exec execute immediate 'select * from dept ' into :v_deptno,:v_dname,:v_loc;
BEGIN execute immediate 'select * from dept ' into :v_deptno,:v_dname,:v_loc; END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 1
SCOTT@book01p> exec execute immediate 'select * from dept where deptno=:1' into :v_deptno,:v_dname,:v_loc using 20;
PL/SQL procedure successfully completed.
SCOTT@book01p> select :v_deptno,:v_dname,:v_loc from dual ;
:V_DEPTNO :V_DNAME :V_LOC
---------- -------------------------------- --------------------------------
20 RESEARCH DALLAS
4.通过执行计划验证是否产生fetch操作：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> exec execute immediate 'sElect * from dept where deptno =:1 or dname=:2' using 10 , 'SALES';
PL/SQL procedure successfully completed.
--//查询到sql_id=brwrfqhszr5qz
SCOTT@book01p> @ dpc brwrfqhszr5qz '' ''
SQL_ID brwrfqhszr5qz, child number 0
-------------------------------------
sElect * from dept where deptno =:1 or dname=:2
Plan hash value: 3383998547
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 2 | 40 | 3 (0)| 00:00:01 | 0 |00:00:00.01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "DEPT"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 10
2 - (CHAR(30), CSID=852): 'SALES'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPTNO"=:1 OR "DNAME"=:2))
64 rows selected.
--//注意A-Rows=0，看实际的情况返回2条记录。
--//也就是这样执行计划并没有真正执行!!使用它来优化sql语句会存在误导的情况，后面看到A-Time很小，另外写一篇文章说明。
5.可以通过10046跟踪简单验证看看：
SCOTT@book01p> @ 10046on 12
Session altered.
SCOTT@book01p> Select * from dept where deptno =10 or dname='SALES';
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
SCOTT@book01p> exec execute immediate 'select * from dept where deptno =:1 or dname=:2' using 10 , 'SALES';
PL/SQL procedure successfully completed.
SCOTT@book01p> @ 10046off
Session altered.
--//检查跟踪文件：
=====================
PARSING IN CURSOR #139837539320992 len=52 dep=0 uid=109 oct=3 lid=109 tim=8389298925 hv=1006258892 ad='6a8ccce8' sqlid='bmh0kp4xznkqc'
Select * from dept where deptno =10 or dname='SALES'
END OF STMT
PARSE #139837539320992:c=3946,e=4473,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3383998547,tim=8389298923
EXEC #139837539320992:c=46,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=8389299036
WAIT #139837539320992: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8389299081
FETCH #139837539320992:c=162,e=162,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3383998547,tim=8389299274
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #139837539320992: nam='SQL*Net message from client' ela= 1275 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8389300602
WAIT #139837539320992: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8389300667
FETCH #139837539320992:c=50,e=50,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=3383998547,tim=8389300702
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #139837539320992 id=1 cnt=2 pid=0 pos=1 obj=76191 op='TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 str=1 time=131 us cost=3 size=40 card=2)'
*** 2026-01-08T17:36:27.234751+08:00 (BOOK01P(3))
WAIT #139837539320992: nam='SQL*Net message from client' ela= 11549057 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8400849861
CLOSE #139837539320992:c=10,e=11,dep=0,type=0,tim=8400849973
=====================
--//看到2次fetch操作，第1次取1条(r=1)，第2次再取1条。语句执行可以看到fetch操作。
PARSING IN CURSOR #139837539320992 len=99 dep=0 uid=109 oct=47 lid=109 tim=8400850094 hv=2805684608 ad='6ac08d08' sqlid='f265cyqmmqqc0'
BEGIN execute immediate 'select * from dept where deptno =:1 or dname=:2' using 10 , 'SALES'; END;
END OF STMT
PARSE #139837539320992:c=66,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=8400850093
=====================
PARSING IN CURSOR #139837539316392 len=47 dep=1 uid=109 oct=3 lid=109 tim=8400850334 hv=2933785555 ad='6aad6018' sqlid='85f31r6rdw0ym'
select * from dept where deptno =:1 or dname=:2
END OF STMT
PARSE #139837539316392:c=78,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3383998547,tim=8400850333
BINDS #139837539316392:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=56 off=0
kxsbbbfp=7f2e76db6bb0 bln=22 avl=02 flg=05
value=10
Bind#1
oacdty=96 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=0 off=24
kxsbbbfp=7f2e76db6bc8 bln=32 avl=05 flg=01
value="SALES"
EXEC #139837539316392:c=2110,e=2547,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3383998547,tim=8400853048
STAT #139837539316392 id=1 cnt=0 pid=0 pos=1 obj=76191 op='TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 str=1 time=4 us cost=3 size=40 card=2)'
CLOSE #139837539316392:c=167,e=77,dep=1,type=3,tim=8400853179
EXEC #139837539320992:c=2729,e=3075,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=8400853232
WAIT #139837539320992: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8400853313
*** 2026-01-08T17:36:32.379674+08:00 (BOOK01P(3))
WAIT #139837539320992: nam='SQL*Net message from client' ela= 5141406 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8405994788
CLOSE #139837539320992:c=20,e=21,dep=0,type=1,tim=8405994898
=====================
--//没有fetch操作。
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
