[20260108]sqlplus下exec以及execute immediate执行方式.txt

[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 <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge standard pipe purge json_object The symbol "return" was substituted for create table SCOTT@book01p> exec  execute immediate  'create table deptx as select * from dept where 2 = 0'; PL/SQL procedure successfully completed. --//如果偷懒可以采用exec+execute immediate： SCOTT@book01p> 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操作。