SQL2BIND-VARIABLE

--ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'; DECLARE v_sql VARCHAR2 (2000); v_sql2 VARCHAR2 (2000); v_sql3 VARCHAR2 (2000); v_sql4 VARCHAR2 (2000); v_sql5 VARCHAR2 (2000); v_sql6 VARCHAR2 (2000); v_sql7 VARCHAR2 (2000); o_sql VARCHAR2 (1000); cnt PLS_INTEGER; v_1 VARCHAR2 (50); dt_1 DATE; v_2 VARCHAR2 (50); v_3 VARCHAR2 (50); V_JSON JSON_ARRAY_T; MY_CURSOR SYS_REFCURSOR; dml_cnt NUMBER; v_x PLS_INTEGER; RES VARCHAR2 (500); BEGIN v_sql := 'SELECT /*+dynamic_sampling(A 8)*/ DISTINCT A.E_NAME FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE(''2023-10-23 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-10-23 23:59'',''YYYY-MM-DD HH24:MI:SS'') AND A.LINE_NAME = ''4線'' AND A.DEPT = ''HUB1G'''; v_sql2 := 'SELECT /*+dynamic_sampling(A 8)*/ A.E_NAME, A.E_DESC, A.SECTION FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE ( ''2023-04-02 07:30'',''YYYY-MM-DD HH24:MI:SS'' ) AND TO_DATE ( ''2023-08-30 23:59'',''YYYY-MM-DD HH24:MI:SS'' ) AND A.LINE_NAME = ''塗裝週邊'' AND A.DEPT = ''NME-KQ'' ; '; v_sql3 := 'SELECT /*+dynamic_sampling(A 8)*/ DISTINCT A.E_NAME FROM R_EQUIPMENT_CHECK_DETAIL_T B,C_EQUIPMENT_DESC_T A,APN_USER C WHERE B.EMP_NO=C.NAME(+) AND A.EQUIP_ID=B.EQUIP_ID AND B.CONFIRM_NO IS NULL AND B.CHECK_DATE BETWEEN TO_DATE(''2023-10-26 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-10-26 23:59'',''YYYY-MM-DD HH24:MI:SS'') AND A.LINE_NAME IN(SELECT /*+no_unnest*/ line_name FROM (SELECT DISTINCT LINE_NAME FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5'' MINUS (SELECT SCAN_DATA FROM TPM.C_ROLE_MANAGE_T WHERE SCAN_DATA IN(SELECT DISTINCT LINE_NAME FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5'')AND ROLE_TYPE=''CONFIRM'' MINUS (SELECT SCAN_DATA FROM TPM.C_ROLE_MANAGE_T WHERE SCAN_DATA IN(SELECT DISTINCT LINE_NAME FROM C_EQUIPMENT_DESC_T WHERE DEPT= ''NME-L5'')AND ROLE_TYPE=''CONFIRM''AND EMP_NO= ''F3662002'')))) AND A.DEPT = ''NME-L5'' ORDER BY CHECK_DATE DESC, E_DESC, SECTION, MODEL_NAME;'; --(1) v_sql4 := 'SELECT APPLY_TIME FROM (SELECT ROWNUM V_NO,A.* FROM ( SELECT * FROM C_REPAIR_T WHERE APPLY_TIME BETWEEN TO_DATE ( ''20230901080000'' , ''YYYYMMDDHH24MISS'' ) AND TO_DATE ( TO_CHAR ( ADD_MONTHS ( TO_DATE ( ''202309'' , ''YYYYMM'' ) , 1 ), ''YYYYMM'' )|| ''01075959'' , ''YYYYMMDDHH24MISS'') AND ERROR_MEMO = ''機故'' AND MACHINE_CODE IN(SELECT EQUIP_ID FROM C_EQUIPMENT_DESC_T WHERE GROUP_MARK=''EIC'' AND APPLY_TIME BETWEEN TO_DATE ( ''20230901080000'' , ''YYYYMMDDHH24MISS'' ) AND TO_DATE ( TO_CHAR(SYSDATE,''YYYYMMDDHH24MISS'') , ''YYYYMMDDHH24MISS'' ) AND GROUP_MARK LIKE ''%SKDF%'' AND LINE_NAME IN(SELECT LINE_NAME FROM C_LINE_TYPE_T WHERE LINE_TYPE= ''TOTAL'' )) ORDER BY APPLY_TIME)A) WHERE V_NO=6;'; v_sql6 := 'SELECT DISTINCT B.E_DESC, B.SECTION, B.LINE_NAME,B.GROUP_NAME,D.USERNAME,A.MODEL_NAME,A.FLAG CYCLE_CODE,A.CHECK_SECTION, TO_CHAR(A.CHECK_DATE,''YY/MM/DD HH24:MI'') CHECK_DATE, C.CYCLE_DESC CYCLE_DESC FROM R_EQUIPMENT_CHECK_DETAIL_T A, C_EQUIPMENT_DESC_T B, APN_USER D, C_CHECK_CYCLE_T C WHERE A.FLAG=C.CYCLE_CODE AND A.EMP_NO=D.NAME AND A.EQUIP_ID=B.EQUIP_ID AND A.CHECK_DATE=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE>=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE<=TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE>TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE<TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE between TO_DATE(''2023-11-06 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-08 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CHECK_DATE between TO_DATE(to_char(sysdate-10),''YYYY-MM-DD HH24:MI:SS'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-08 07:34:29'',''YYYY-MM-DD HH24:MI:SS'') AND A.CONFIRM_NO IS NULL AND A.FLAG=''C'' AND A.MODEL_NAME<''MODEL ALL'' AND A.EQUIP_ID=''ZDH-AGV09'' AND A.CONFIRM_NO IS NULL AND A.FLAG>=''C'' AND A.MODEL_NAME<=''MODEL ALL'' AND A.EQUIP_ID>''ZDH-AGV09'' AND A.MODEL_NAME LIKE ''%TTTT%'' and a.flag= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag>= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag<= 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag> 10 AND A.MODEL_NAME<=''MODEL ALL'' and a.flag< 10 AND A.MODEL_NAME<=''MODEL ALL'' '; v_sql5 := 'UPDATE tpm.t1 A SET A.CONFIRM_NO=''xx'', A.CONFIRM_TIME=SYSDATE WHERE A.EQUIP_ID IN(SELECT B.EQUIP_ID FROM C_EQUIPMENT_DESC_T B WHERE B.DEPT = ''TE3F'' ) AND TO_DATE(TO_CHAR(A.CHECK_DATE,''HH24:MI:SS''),''HH24:MI:SS'') BETWEEN TO_DATE(''00:30:00'',''HH24:MI:SS'') AND TO_DATE(''23:29:59'',''HH24:MI:SS'') AND A.CHECK_DATE BETWEEN TO_DATE(''2023-10-13 07:30'',''YYYY-MM-DD HH24:MI:SS'') AND TO_DATE(''2023-11-14 07:29'',''YYYY-MM-DD HH24:MI:SS'') '; -- v_sql7 := -- 'SELECT A.DEPT -- --FROM C_EQUIPMENT_DESC_T A, CHECK_INFO_DETAIL B --WHERE B.FLAG=''1'' AND B.UPDATE_TIME BETWEEN TO_DATE(''2023-11-15 07:30'',''YYYY-MM-DD HH24:MI'') AND TO_DATE(''2023-11-15 23:59'',''YYYY-MM-DD HH24:MI'') AND --A.EQUIP_ID=B.EQUIP_ID AND A.STATUS=''稼動'' AND A.DEPT = ''CBE235'' --ORDER BY A.SECTION,A.LINE_NAME,A.E_DESC,A.EQUIP_ID,B.MODEL_NAME,B.FLAG,B.START_TIME'; v_sql7:='SELECT DISTINCT B.E_DESC FROM R_EQUIPMENT_CHECK_DETAIL_T A, C_EQUIPMENT_DESC_T B, APN_USER D, C_CHECK_CYCLE_T C WHERE A.FLAG=C.CYCLE_CODE AND A.EMP_NO=D.NAME AND A.EQUIP_ID=B.EQUIP_ID AND A.CHECK_DATE=TO_DATE(''2023-11-16 08:17:20'',''YYYY-MM-DD HH24:MI:SS'') AND A.CONFIRM_NO IS NULL AND A.FLAG= 167 AND A.MODEL_NAME=''hello'' AND A.EQUIP_ID= 1099 '; TPM.SQL2BIND_VARIABLE_V2_TEST (v_sql7, MY_CURSOR); -- DBMS_OUTPUT.put_line('DML_CNT='||dml_cnt); （1） TPM.SQL2BIND_VARIABLE(v_sql4,MY_CURSOR); ---- DBMS_OUTPUT.put_line ('----------------------------------------'); --LOOP --FETCH MY_CURSOR INTO v_1;--,v_2,v_3; -- EXIT WHEN MY_CURSOR%NOTFOUND; -- DBMS_OUTPUT.put_line (v_1 );--||','||v_2||','||v_3); --END LOOP; --close MY_CURSOR; （2）TPM.SQL2BIND_VARIABLE_FOR_DML(v_sql5,dml_cnt); commit; //外面提交 END;