动态SQL的dbms_sql.bind_variable无法触发绑定变量窥探

samt007

newkid 发表于 2017-3-6 23:59
第二个我理解错了，是游标缓存导致的，想要缓存就没法用上新功能，真是纠结。
http://oracle-randolf.blog ...

终于给我想了一个不错的办法，就是用PLSQL，输出动态游标的！
终于给完美解决了这个问题了。。。
原型：

1. 
   
2. DECLARE
   
3.    L_PLSQL VARCHAR2(20000);
   
4.    L_CUR sys_refcursor;
   
5.    R_EMP XYG_PROGRAM_ERROR_LOG%ROWTYPE;
   
6. BEGIN
   
7.    --DBMS_OUTPUT.PUT_LINE(L_SQL);
   
8.    L_PLSQL:='
   
9.    DECLARE
   
10.       L_CUR sys_refcursor;
    
11.       L_OBJ_NAME VARCHAR2(50);
    
12.    BEGIN
    
13.       L_OBJ_NAME:=''XYG_MTL_SYSTEM_ITEMS_TL_04'';
    
14.       OPEN L_CUR FOR ''SELECT * FROM XYG_PROGRAM_ERROR_LOG WHERE OBJECT_NAME=:1 '' USING L_OBJ_NAME;
    
15.       :X_CUR :=L_CUR;
    
16.    END;
    
17.    ';
    
18.    execute immediate L_PLSQL USING OUT L_CUR;
    
19.   LOOP
    
20.       FETCH l_cur INTO R_EMP;
    
21.       EXIT WHEN l_cur%NOTFOUND;
    
22.       --处理啊处理。。。
    
23.       DBMS_OUTPUT.PUT_LINE ('ID为' || TO_CHAR (R_EMP.ERROR_LOG_ID) || ' OBJECT_NAME为：' || R_EMP.OBJECT_NAME);
    
24.   END LOOP;
    
25. 
    
26.   CLOSE l_cur;
    
27. END;

复制代码

通过测试，可以完整做绑定变量窥探~~~

1. DECLARE
   
2.    L_PLSQL VARCHAR2(20000);
   
3.    L_CUR sys_refcursor;
   
4.    L_SQL_STATEMENT VARCHAR2(32767);
   
5.    L_SQL_STATEMENT_BV VARCHAR2(32767);
   
6.    L_COL_VALUE_TAB XYG_FND_COMMON_PKG.TAB_COL_VALUE;
   
7.    --
   
8.    l_retcode number;
   
9.    l_errbuf varchar2(4000);
   
10.    l_bin_idx  integer;
    
11.    l_declare_bin varchar2(32767);
    
12.    l_value_bin varchar2(32767);
    
13.    l_using_bin varchar2(32767);
    
14. BEGIN
    
15.    SELECT REPORT_STATEMENT
    
16.      INTO L_SQL_STATEMENT
    
17.      FROM XYG_FND_SUBMIT_REPORT_TEMP
    
18.     WHERE REPORT_TEMP_ID=9175;
    
19.    
    
20.    --DBMS_OUTPUT.PUT_LINE(L_SQL_STATEMENT);
    
21.   XYG_FND_COMMON_PKG.SQL_BIND_VARIABLE_CHANGE (
    
22.       L_SQL_STATEMENT--P_SQL_STATEMENT        IN VARCHAR2 ---要改变的SQL语句
    
23.      ,L_SQL_STATEMENT_BV--X_SQL_STATEMENT_BV     OUT VARCHAR2--变化之后的SQL语句
    
24.      ,L_COL_VALUE_TAB--X_COL_VALUE_TAB        OUT T_COL_VALUE ---绑定参数的输出
    
25.      ,l_retcode
    
26.      ,l_errbuf
    
27.    );
    
28.    DBMS_OUTPUT.PUT_LINE(l_retcode||'-'||l_errbuf);
    
29.    
    
30.     ---绑定变量，这里的数量不定
    
31.     l_bin_idx := L_COL_VALUE_TAB.FIRST;
    
32.     WHILE l_bin_idx IS NOT NULL LOOP
    
33.        l_declare_bin:=l_declare_bin||CHR(10)
    
34.            ||'L_'||L_COL_VALUE_TAB(l_bin_idx).COL_ID||' VARCHAR2('||(LENGTHB(L_COL_VALUE_TAB(l_bin_idx).COL_VALUE)+10)||');';
    
35.        l_value_bin:=l_value_bin||CHR(10)
    
36.            ||'L_'||L_COL_VALUE_TAB(l_bin_idx).COL_ID||' := '''||L_COL_VALUE_TAB(l_bin_idx).COL_VALUE||''';';
    
37.        l_using_bin:=CASE WHEN l_using_bin IS NULL THEN ' USING L_'||L_COL_VALUE_TAB(l_bin_idx).COL_ID
    
38.                     ELSE l_using_bin||','||'L_'||L_COL_VALUE_TAB(l_bin_idx).COL_ID END;
    
39.        l_bin_idx:= L_COL_VALUE_TAB.next(l_bin_idx);
    
40.     END LOOP;
    
41.    DBMS_OUTPUT.PUT_LINE('l_declare_bin:'||l_declare_bin);
    
42.    DBMS_OUTPUT.PUT_LINE('l_value_bin:'||l_value_bin);
    
43.    DBMS_OUTPUT.PUT_LINE('l_using_bin:'||l_using_bin);
    
44.    
    
45.    L_PLSQL:='
    
46.    DECLARE
    
47.       L_CUR sys_refcursor;
    
48.       '||l_declare_bin||'
    
49.    BEGIN
    
50.       '||l_value_bin||'
    
51.       OPEN L_CUR FOR q''['||L_SQL_STATEMENT_BV||']'''||l_using_bin||' ;
    
52.       :X_CUR :=L_CUR;
    
53.    END;
    
54.    ';
    
55.    
    
56.    DBMS_OUTPUT.PUT_LINE('L_PLSQL:'||L_PLSQL);
    
57. 
    
58.    execute immediate L_PLSQL USING OUT L_CUR;
    
59.    XYG_ALD_XLSX_PKG.G_DEBUG_MODE:=TRUE;
    
60.    XYG_ALD_XLSX_PKG.cursor2sheet
    
61.     ( L_CUR
    
62.     , true
    
63.     , 'XYG_EXPORT_DATA'
    
64.     , 'TEXT.xlsx'
    
65.     , null
    
66.     , true
    
67.     , l_retcode
    
68.     , l_errbuf
    
69.     );
    
70.    DBMS_OUTPUT.PUT_LINE('TOW:'||l_retcode||'-'||l_errbuf);
    
71.    
    
72. END;

复制代码

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=873): '106'
   2 - (VARCHAR2(30), CSID=873): 'DDN13050349'
   3 - (VARCHAR2(30), CSID=873): 'DDN13050349-0001'
   4 - (VARCHAR2(30), CSID=873): 'DB1'
   5 - (VARCHAR2(30), CSID=873): '2013/05/03 00:00:00'
   6 - (VARCHAR2(30), CSID=873): '2013/05/28 23:59:59'

samt007

newkid 发表于 2017-3-6 23:59
第二个我理解错了，是游标缓存导致的，想要缓存就没法用上新功能，真是纠结。
http://oracle-randolf.blog ...

抱歉！重复发帖了居然，破网络！！

samt007

抱歉！重复发帖了。。。。破网络

newkid

根本没必要两重动态，我上面已经说过了。

DECLARE
   L_PLSQL VARCHAR2(20000);
   L_CUR sys_refcursor;
   R_EMP XYG_PROGRAM_ERROR_LOG%ROWTYPE;
BEGIN
   --DBMS_OUTPUT.PUT_LINE(L_SQL);
   L_PLSQL:='
   DECLARE
      L_CUR sys_refcursor;
      L_OBJ_NAME VARCHAR2(50);
   BEGIN
      L_OBJ_NAME:=''XYG_MTL_SYSTEM_ITEMS_TL_04'';
      OPEN :X_CUR FOR SELECT * FROM XYG_PROGRAM_ERROR_LOG WHERE OBJECT_NAME=L_OBJ_NAME; --- 这里用静态
   END;
   ';
   execute immediate L_PLSQL USING OUT L_CUR;
  LOOP
      FETCH l_cur INTO R_EMP;
      EXIT WHEN l_cur%NOTFOUND;
      --处理啊处理。。。
      DBMS_OUTPUT.PUT_LINE ('ID为' || TO_CHAR (R_EMP.ERROR_LOG_ID) || ' OBJECT_NAME为：' || R_EMP.OBJECT_NAME);
  END LOOP;

  CLOSE l_cur;
END;

samt007

newkid 发表于 2017-3-9 23:05
根本没必要两重动态，我上面已经说过了。

DECLARE

嗯，版主说得对，确实没必要做2层动态。但是对于我来说，做的工作量是一样的。
因为我这边知道的只是一条静态的带具体查询值的SQL。
用变量，也还是要做替换：用变量替换对应的查询的值。
举个例子，这边只有下面这条SQL，然后这边就想尽办法用动态SQL执行它，并且考虑绑定变量：

1. SELECT /*+ SAMT003 monitor gather_plan_statistics */PATCH_STATUS_DESC "补片状态"
   
2. ,PATCH_LEVEL_TAG "补片层标"
   
3. ,' '||LEVEL_TAG "层标"
   
4. ,RETIRE_QUANTITY "报废数"
   
5. ,SCHEDULE_BOX_NUM "计划箱号"
   
6. ,EDGE_WIDTH "宽"
   
7. ,EDGE_HEIGHT "高"
   
8. ,MAIN_OPERATION_FNAME "发现人"
   
9. ,GROUP_CODE_DESC "生产班组描述"
   
10. ,OPERATION_DESCRIPTION "生产工序描述"
    
11. ,RETIRE_OPERAITON_DESC "责任工序描述"
    
12. ,RETIRE_CHARGE_FNAME "责任人"
    
13. ,RETIRE_REASON_DESC "报废原因描述"
    
14. ,PCS_AREA "单片面积"
    
15. ,RETIRE_DATE "报废日期"
    
16. ,RETIRE_PROCESS_DESC "本工序加工"
    
17. ,GLASS_LEVEL "玻层"
    
18. ,N_LEVEL "总层"
    
19. ,RESUME_DATE "再用日期"
    
20. ,RESUME_STATUS_DESC "再用状态"
    
21. ,RESUME_LEVEL_TAG "再用层标"
    
22. ,RESUME_OPERATION_DESC "再用工序"
    
23. ,PRINT_JOB_STATUS_DESC "打印状态"
    
24. ,PRINT_JOB_DATE "打印时间"
    
25. ,PRINT_JOB_USER_NAME "打印用户"
    
26. FROM XYG_JBW_RETIRE_QUERY_V
    
27.   WHERE (ORGANIZATION_ID=106) and (ORDER_NUMBER='DDN13050349') and (SCHEDULE_BOX_NUM  BETWEEN 'DDN13050349-0001' AND 'DDN13050349-0010') and (FACTORY_CODE='DB1') and (RETIRE_DATE  BETWEEN to_date('2013/05/03 00:00:00', 'YYYY/MM/DD HH24:MI:SS') AND to_date('2013/05/28 23:59:59', 'YYYY/MM/DD HH24:MI:SS'))

复制代码

samt007

newkid 发表于 2017-3-9 23:05
根本没必要两重动态，我上面已经说过了。

DECLARE

不过，版主，我跟踪SQL的时候，倒是发现了另外一个问题：
execute immediate L_PLSQL 这个步骤本身就会消耗资源去解析L_PLSQL。并且这个硬解析是无法公用的。因为有一些参数的值直接拼到动态SQL里面了。
这样子，执行一个动态SQL脚本：
PLSQL（游标产生）的时候会硬解析一次，游标的SQL执行返回数据的时候又会解析一次。
关键是PLSQL游标产生这个步骤是无法公用的，所以~还是有点麻烦，不知道有没有破解办法。主要还是USING后面带的参数必须是固定的，麻烦的。。。唉。------暂时的一个解决办法就是：
因为考虑到90%情况下都是10个绑定变量以内的。所以，先判断判断变量个数，如果小于10个的，则自动补到10个，就是为了满足Using的输入绑定变量个数固定的限制。
这样子，Using后面的绑定变量就可以固定了。
当然，固然超过10个的，则还是按照以前的逻辑处理。
下面的例子就是测试9个绑定变量的情况，程序自动生成的产生游标的动态PLSQL。最后一个输入绑定变量L_10只是一个空值的占位符。

1. DECLARE
   
2.   L_CUR sys_refcursor;
   
3.    L_1 VARCHAR2(29);
   
4.    L_2 VARCHAR2(28);
   
5.    L_3 VARCHAR2(13);
   
6.    L_4 NUMBER;
   
7.    L_5 NUMBER;
   
8.    L_6 NUMBER;
   
9.    L_7 NUMBER;
   
10.    L_8 NUMBER;
    
11.    L_9 NUMBER;
    
12.    L_10 VARCHAR2(30);
    
13. BEGIN
    
14.    L_1 := :1;
    
15.    L_2 := :2;
    
16.    L_3 := :3;
    
17.    L_4 := :4;
    
18.    L_5 := :5;
    
19.    L_6 := :6;
    
20.    L_7 := :7;
    
21.    L_8 := :8;
    
22.    L_9 := :9;
    
23.    L_10 := :10;
    
24.   OPEN L_CUR FOR q'[SELECT MAKE_DATE "生产日期"
    
25. ,WIP_ENTITY_NAME "任务单"
    
26. ,RT_NAME "地区"
    
27. ,LEVEL_TAG "层标"
    
28. ,XYG_MODEL "产品型号"
    
29. ,CHALFTONE "通用网板"
    
30. ,OPERATION_DESCRIPTION "生产工序描述"
    
31. ,JOB_QUANTITY "下单数"
    
32. ,SUGGEST_QUANTITY "订单数"
    
33. ,ORDER_QUANTITY "总合同数"
    
34. ,RESERVE_PIECE "内仓数量"
    
35. ,MOVE_TYPE "移动方向"
    
36. ,CUR_TRX_QUANTITY "完工"
    
37. ,CUR_TRX_SQM "完工面积"
    
38. ,CUR_DLV_QUANTITY "移交"
    
39. ,CUR_RETIRE_QUANTITY "报废"
    
40. ,FACTORY_DESC "厂区描述"
    
41. ,WORKSHOP_DESC "生产线描述"
    
42. ,GROUP_CODE "班组代码"
    
43. ,GROUP_DESC "班组描述"
    
44. ,MAIN_OPERATOR "主操手"
    
45. ,EQUIPMENT "设备代码"
    
46. ,EQUIPMENT_DESC "生产设备"
    
47. ,IN_INSTEAD "内片优化"
    
48. ,EX_INSTEAD "外片优化"
    
49. ,DESCRIPTION "备注"
    
50. ,SPEC "规格"
    
51. ,HEIGHT "厚度"
    
52. ,GLASSSORT "玻璃分类"
    
53. ,N_LEVEL "层数"
    
54. ,OEMNO "OEM编号"
    
55. ,CONTRACT_NUMBER "合同号"
    
56. ,PACK_NO "包装单号"
    
57. ,COATED "镀膜"
    
58. ,CMOULD "通用模具"
    
59. ,CMATERIAL "通用素板"
    
60. ,CREATION_DATE "创建日期"
    
61. ,MOVE_ATTRIB_DESC "生产属性"
    
62. ,MOVE_RETURN_TYPE_DESC "反向原因"
    
63. ,PACKING_ATTRIB_DESC "包装属性"
    
64. ,HOLE "单位孔数"
    
65.   FROM XYG_WIP_MOVE_TRANSACTION_V
    
66. WHERE 1=1
    
67. AND (MAKE_DATE  BETWEEN to_date( :1 , 'YYYY/MM/DD HH24:MI:SS')
    
68. AND to_date( :2 , 'YYYY/MM/DD HH24:MI:SS'))
    
69. and (FACTORY= :3 )
    
70. and (ORGANIZATION_ID= :4 )
    
71. and (ORGANIZATION_ID= :5 )
    
72. and (ORGANIZATION_ID= :6 )
    
73. and (ORGANIZATION_ID= :7 )
    
74. and (ORGANIZATION_ID= :8 )
    
75. and (ORGANIZATION_ID= :9 )
    
76. order by LINE_ID]' USING L_1,L_2,L_3,L_4,L_5,L_6,L_7,L_8,L_9 ;
    
77.   :X_CUR :=L_CUR;
    
78. END;

复制代码

newkid

你忘了还有SYS_CONTEXT这个大招。
http://www.itpub.net/thread-1019164-1-1.html

用它之后，你再OPEN游标就不用带USING了。

samt007

newkid 发表于 2017-3-10 21:59
你忘了还有SYS_CONTEXT这个大招。
http://www.itpub.net/thread-1019164-1-1.html

也确实是个不错的办法，我这个实现来说刚刚好可以用（因为之前发现直接使用COL=SYS_CONTEXT是不支持绑定变量窥探的）。所以，改成这样子应该可以。
BTW，SYS_CONTEXT是会话级的上下文，如果同一个会话不断使用动态游标，必须要考虑值的初始化，否则很容易有bug。so处理起来稍微麻烦点。
题外话：
主要是我这边系统的数据量较大，查询非常频繁，而且查询的字段是需要直方图的（数据分布不均匀）。这样子就显得绑定变量窥探尤其重要！否则执行计划是错得一塌糊涂的。
最近搞这个搞这么麻烦主要是为了解决这个问题。。。ACS真的很重要~

newkid

samt007 发表于 2017-3-11 01:09
也确实是个不错的办法，我这个实现来说刚刚好可以用（因为之前发现直接使用COL=SYS_CONTEXT是不支持绑定 ...

改成这样：

DECLARE
   L_PLSQL VARCHAR2(20000);
   L_CUR sys_refcursor;
   R_EMP XYG_PROGRAM_ERROR_LOG%ROWTYPE;
BEGIN
   --DBMS_OUTPUT.PUT_LINE(L_SQL);
   L_PLSQL:='
   DECLARE
      L_CUR sys_refcursor;
      L_OBJ_NAME VARCHAR2(50);
   BEGIN
      L_OBJ_NAME:=SYS_CONTEXT(.....); ------- 减少PL/SQL解析
      OPEN :X_CUR FOR SELECT * FROM XYG_PROGRAM_ERROR_LOG WHERE OBJECT_NAME=L_OBJ_NAME; --- 这里用静态
   END;
   ';
   execute immediate L_PLSQL USING OUT L_CUR;
  LOOP
      FETCH l_cur INTO R_EMP;
      EXIT WHEN l_cur%NOTFOUND;
      --处理啊处理。。。
      DBMS_OUTPUT.PUT_LINE ('ID为' || TO_CHAR (R_EMP.ERROR_LOG_ID) || ' OBJECT_NAME为：' || R_EMP.OBJECT_NAME);
  END LOOP;

  CLOSE l_cur;
END;

SYS_CONTEXT用在动态PLSQL而不是SQL, 所以没有窥视也无所谓。

samt007

newkid 发表于 2017-3-11 01:55
改成这样：

DECLARE

嗯嗯，是改成这样子~
昨晚写的和版主写的差不多一样，只是发帖的时候居然代码消失了~
我的情况刚刚好是可以适用SYS_CONTEXT的~而且是支持绑定变量的。
不过目前还是先用自己的办法，暂时不修改了。昨天简单跟踪了一下，基本上99% 的情况绑定变量都不会超过10个，所以也算是比较适用了。PLSQL游标共用概率还是挺高。。。上线2小时就有被执行300次的子游标！