0
0
0
1. 云栖社区>
2. 博客>
3. 正文

CREATE TABLE T_BG_20170610_LHR(N NUMBER(10),V VARCHAR2(3000));

--SQL_TEXT1:硬解析

DECLARE

N NUMBER(10) :=1;--分配22字节的内存空间

V VARCHAR2(32) :='XIAOMAIMIAO1';--分配32字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

--SQL_TEXT2:硬解析

DECLARE

N NUMBER(10) :=2;--分配22字节的内存空间

V VARCHAR2(33) :='XIAOMAIMIAO2';--分配128字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

--SQL_TEXT3:硬解析

DECLARE

N NUMBER(10) :=3;--分配22字节的内存空间

V VARCHAR2(129) :='XIAOMAIMIAO3';--分配2000字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

--SQL_TEXT4:软解析

DECLARE

N NUMBER(10) :=4;--分配22字节的内存空间

V VARCHAR2(2001) :='XIAOMAIMIAO4';--分配2000字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

--SQL_TEXT5:软解析

DECLARE

N NUMBER(10) :=5;--分配22字节的内存空间

V VARCHAR2(32767) :='XIAOMAIMIAO5';--分配2000字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

--SQL_TEXT6: 硬解析

DECLARE

N NUMBER(10) :=6;  --分配22字节的内存空间

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)' USING N, V;

COMMIT;

END;

/

LHR@orclasm > col v format a13

LHR@orclasm > select * from T_BG_20170610_LHR T WHERE T.N<=5;

N V

---------- -------------

1 XIAOMAIMIAO1

2 XIAOMAIMIAO2

3 XIAOMAIMIAO3

4 XIAOMAIMIAO4

5 XIAOMAIMIAO5

LHR@orclasm > SELECT T.N,LENGTH(T.V) FROM T_BG_20170610_LHR T;

N LENGTH(T.V)

---------- -----------

1          12

2          12

3          12

4          12

5          12

6        2002

LHR@orclasm > col sql_text format a60

LHR@orclasm > SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,EXECUTIONS FROM V\$SQLAREA WHERE SQL_TEXT LIKE 'INSERT INTO T_BG_20170610_LHR VALUES%';

SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS

------------------------------------------------------------ ------------- ------------- ----------

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc             4          6

LHR@orclasm > SELECT SQL_TEXT,SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,EXECUTIONS FROM V\$SQL D WHERE SQL_ID = '01g03pruhphqc';

------------------------------------------------------------ ------------- ------------ ---------------- ----------

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            0 00000000AA902CE8          1    <<----对应PL/SQL代码1

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            1 00000000AAA47348          1    <<----对应PL/SQL代码2

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            2 00000000AAAF7A28          3    <<----对应PL/SQL代码345

INSERT INTO T_BG_20170610_LHR VALUES(:N,:V)                  01g03pruhphqc            3 0000000095DA4B00          1    <<----对应PL/SQL代码6

LHR@orclasm > SELECT d.SQL_ID,D.CHILD_NUMBER,D.BIND_LENGTH_UPGRADEABLE FROM V\$SQL_SHARED_CURSOR D WHERE D.SQL_ID = '01g03pruhphqc';

SQL_ID        CHILD_NUMBER B

------------- ------------ -

01g03pruhphqc            0 N

01g03pruhphqc            1 Y

01g03pruhphqc            2 Y

01g03pruhphqc            3 Y

CHILD_NUMBER CHILD_ADDRESS    BIND_NAME                        POSITION   DATATYPE MAX_LENGTH

------------ ---------------- ------------------------------ ---------- ---------- ----------

0 00000000AA902CE8 N                                       1          2         22

0 00000000AA902CE8 V                                       2          1         32

1 00000000AAA47348 N                                       1          2         22

1 00000000AAA47348 V                                       2          1        128

2 00000000AAAF7A28 N                                       1          2         22

2 00000000AAAF7A28 V                                       2          1       2000

3 0000000095DA4B00 N                                       1          2         22

3 0000000095DA4B00 V                                       2          1       4000

>

>

>

>

>
>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

><

>

>

>

>>>>

>

>
>

>

>

+ 关注