主要是几个对于sqlServer中没有的oracle特有函数,表里面的数据是别人弄好了的,不太好修改,代码有点长,有心的人可以看看,给点意见
CREATE OR REPLACE PROCEDURE PROC_UPDATE_COMMON_COA_ID IS P_AS_OF_DATE DATE := FUNC_AS_OF_DATE();
PROC_ID VARCHAR2(50) := 'proc_update_common_coa_id';
PROC_DESC VARCHAR2(200) := '根据产品映射规则,更新分户表中的产品编号';
RCOUNT NUMBER := 0; TYPE DEST_TABLE_COND IS RECORD(
GKCPH VARCHAR2(60),
GKCPM VARCHAR2(60),
TAB_NAME VARCHAR2(60),
COL_NAME VARCHAR2(60),
COL_INDEX VARCHAR2(60));
DEST_TABLE_DETAILS DEST_TABLE_COND; CURSOR C1 IS
SELECT TRIM(PRODUCT_ID)
,TRIM(PRODUCT_NAME)
,TRIM(UPDATE_TABLE)
,TRIM(UPDATE_FIELD)
,TRIM(UPDATE_INDEX)
FROM P_CPYS_UPDATE
-- WHERE PRODUCT_ID IN ('470541')
ORDER BY UPDATE_SORT ASC; TYPE COND_TABLE IS RECORD(
GKCPH VARCHAR2(60),
GKCPSM VARCHAR2(60),
COND_TAB_NAME VARCHAR2(4000),
COND_COL_NAME VARCHAR2(60),
OPERATER VARCHAR2(10),
OPERATE_VALUE VARCHAR2(300),
CONN_OPERATE VARCHAR2(10));
-- cond_table_details cond_table;
TYPE COLLECTION IS TABLE OF COND_TABLE;
TEMP_OBJECTS COLLECTION; T_SQL VARCHAR2(2000) := '';
T_TABLE VARCHAR2(500) := '';
T_VALUE VARCHAR2(500) := '';
TYPE TEMP_TABLE_COLLECTION IS TABLE OF VARCHAR2(4000);
VIRTUAL_TABLE TEMP_TABLE_COLLECTION; DEBUG_POSITION VARCHAR2(20) := '';
V_STARTTIME DATE;
V_ENDTIME DATE;
V_POSITION VARCHAR2(20);
V_SQL VARCHAR2(2000);
V_TEMP VARCHAR2(200);
V_BEGIN VARCHAR2(1000) := NULL;
V_END VARCHAR2(1000) := NULL;
V_HANDLE VARCHAR2(2);
V_EXE_SEQ NUMBER(6);
CURSOR C2 IS
SELECT DEBUG_POSITION, DEBUG_DESC, HAND_FLAG, EXE_SEQ
FROM DEBUG_INFO
WHERE CREATE_STATUS = 'SQL-CREATED'
--- and LOWER(DEBUG_DESC) like '%xd%'
ORDER BY EXE_SEQ, DEBUG_POSITION ASC;
ROWS NUMBER := 0;
STR VARCHAR2(50) := '';BEGIN --清空系统生成的SQL
DELETE FROM DEBUG_INFO T
WHERE TRIM(T.HAND_FLAG) = 'Y'
OR T.HAND_FLAG IS NULL;
--清除手工导入的SQL上一次执行的信息
UPDATE DEBUG_INFO T
SET T.EXEC_STATUS = NULL, T.RUN_TIME = NULL, T.ROWCOUNT = NULL;
DEBUG_POSITION := 'd1';
OPEN C1;
LOOP
FETCH C1
INTO DEST_TABLE_DETAILS;
EXIT WHEN C1%NOTFOUND;
DEBUG_POSITION := 'd2';
T_SQL := ' update ' || DEST_TABLE_DETAILS.TAB_NAME || ' set ' ||
DEST_TABLE_DETAILS.COL_NAME || ' = ' || CHR(39) ||
TRIM(DEST_TABLE_DETAILS.GKCPH) || CHR(39);
DEBUG_POSITION := 'd3';
SELECT TRIM(PRODUCT_ID)
,TRIM(PRODUCT_NAME)
,TRIM(WHERE_TABLE)
,TRIM(WHERE_FIELD)
,TRIM(YS_FH)
,TRIM(FIELD_VALUE)
,TRIM(UNION_FH) BULK COLLECT
INTO TEMP_OBJECTS
FROM P_CPYS_WHERE
WHERE PRODUCT_ID = TRIM(DEST_TABLE_DETAILS.GKCPH)
ORDER BY UPDATE_SORT ASC;
DEBUG_POSITION := 'd4';
SELECT DISTINCT (WHERE_TABLE) BULK COLLECT
INTO VIRTUAL_TABLE
FROM P_CPYS_WHERE
WHERE PRODUCT_ID = TRIM(DEST_TABLE_DETAILS.GKCPH)
ORDER BY PRODUCT_ID ASC;
DEBUG_POSITION := 'd5';
IF VIRTUAL_TABLE.COUNT = 0 THEN
INSERT INTO DEBUG_INFO
(DEBUG_POSITION, DEBUG_DESC, CREATE_STATUS)
VALUES
(DEST_TABLE_DETAILS.GKCPH
,T_SQL
,'d5-关联条件表(p_cpys_where)出错');
CONTINUE;
ELSE
FOR J IN 1 .. VIRTUAL_TABLE.COUNT LOOP
T_TABLE := T_TABLE || ' ' || VIRTUAL_TABLE(J) || ' ,';
END LOOP;
T_TABLE := SUBSTR(T_TABLE, 1, LENGTH(T_TABLE) - 1);
END IF;
DEBUG_POSITION := 'd6';
IF TEMP_OBJECTS.COUNT = 0 THEN
CONTINUE;
END IF;
T_SQL := T_SQL || ' where ' || DEST_TABLE_DETAILS.COL_INDEX ||
' in ( select ' || DEST_TABLE_DETAILS.COL_INDEX ||
' from ' || T_TABLE || ' where ';
DEBUG_POSITION := 'd7';
FOR I IN 1 .. TEMP_OBJECTS.COUNT LOOP
--字母开头,含有.号
IF REGEXP_LIKE(TEMP_OBJECTS(I).OPERATE_VALUE, '^[[:alpha:]]*\.') THEN
T_VALUE := TEMP_OBJECTS(I).OPERATE_VALUE;
--处理操作符为between的情况
ELSIF /*regexp_like(temp_objects(i).operate_value,
'(\w{1,})(\s{1,}and\s{1,})(\w{1,})')*/
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'between' THEN
T_VALUE := REGEXP_REPLACE(TEMP_OBJECTS(I).OPERATE_VALUE,
'(\w{1,})(\s{1,}\w{1,}\s{1,})(\w{1,})',
'''\1'' \2 ''\3''');
--处理操作符为in的情况
ELSIF /*regexp_like(temp_objects(i).operate_value, '^\(\w{1,}')*/
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'in' THEN
T_VALUE := REGEXP_REPLACE(TEMP_OBJECTS(I).OPERATE_VALUE,
'(\w{1,})',
'''\1''');
--处理null值
ELSIF LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'is' THEN
--regexp_like(lower(trim(temp_objects(i).operater)), '[=is=]') then
T_VALUE := TRIM(TEMP_OBJECTS(I).OPERATE_VALUE);
DEBUG_POSITION := 'd9';
--处理 (odw_hx_ck.....这样的情况
IF REGEXP_LIKE(TEMP_OBJECTS(I).COND_COL_NAME, '[=(=]') THEN
V_BEGIN := ' (' || SUBSTR(TEMP_OBJECTS(I).COND_COL_NAME, 2);
END IF;
ELSE
STR := LOWER(TRIM(TEMP_OBJECTS(I).OPERATE_VALUE));
IF STR <> 'null' THEN
T_VALUE := CHR(39) || TRIM(TEMP_OBJECTS(I).OPERATE_VALUE) ||
CHR(39);
ELSE
T_VALUE := STR;
END IF;
END IF;
--处理 is not null 情况
/* str := lower(TRIM(temp_objects(i).operater));
IF regexp_like(str, '[=is=]')
AND regexp_like(str, '[=not=]') THEN
t_value := TRIM(temp_objects(i).operate_value);
--t_value := substr(t_value, 1, length(t_value) - 1);
END IF;*/
--处理 product_id = 122211 )这样的情况
IF REGEXP_LIKE(TEMP_OBJECTS(I).OPERATE_VALUE, '[=)=]') AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'between' AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'in' AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'is' THEN
V_TEMP := TRIM(TEMP_OBJECTS(I).OPERATE_VALUE);
V_END := TRIM(SUBSTR(V_TEMP, 1, LENGTH(V_TEMP) - 1));
V_END := CHR(39) || V_END || CHR(39) || ' )';
T_VALUE := V_END;
END IF;
--在这里拼接sql
T_SQL := T_SQL || NVL(V_BEGIN, TEMP_OBJECTS(I).COND_COL_NAME) || ' ' || TEMP_OBJECTS(I)
.OPERATER || ' ' || T_VALUE || ' ' || TEMP_OBJECTS(I)
.CONN_OPERATE || ' ';
V_BEGIN := NULL;
V_END := NULL;
STR := NULL;
END LOOP;
T_SQL := T_SQL || ' ) ';
INSERT INTO DEBUG_INFO
(DEBUG_POSITION, DEBUG_DESC, CREATE_STATUS, HAND_FLAG)
VALUES
(TRIM(DEST_TABLE_DETAILS.GKCPH), T_SQL, 'SQL-CREATED', 'Y');
DEBUG_POSITION := 'd10';
T_SQL := NULL;
T_TABLE := NULL;
T_VALUE := NULL;
STR := NULL;
COMMIT;
END LOOP;
CLOSE C1;
COMMIT;
END LOOP;
CLOSE C2;
COMMIT;END PROC_UPDATE_COMMON_COA_ID;SQL ServerOracle存储SQL调试
CREATE OR REPLACE PROCEDURE PROC_UPDATE_COMMON_COA_ID IS P_AS_OF_DATE DATE := FUNC_AS_OF_DATE();
PROC_ID VARCHAR2(50) := 'proc_update_common_coa_id';
PROC_DESC VARCHAR2(200) := '根据产品映射规则,更新分户表中的产品编号';
RCOUNT NUMBER := 0; TYPE DEST_TABLE_COND IS RECORD(
GKCPH VARCHAR2(60),
GKCPM VARCHAR2(60),
TAB_NAME VARCHAR2(60),
COL_NAME VARCHAR2(60),
COL_INDEX VARCHAR2(60));
DEST_TABLE_DETAILS DEST_TABLE_COND; CURSOR C1 IS
SELECT TRIM(PRODUCT_ID)
,TRIM(PRODUCT_NAME)
,TRIM(UPDATE_TABLE)
,TRIM(UPDATE_FIELD)
,TRIM(UPDATE_INDEX)
FROM P_CPYS_UPDATE
-- WHERE PRODUCT_ID IN ('470541')
ORDER BY UPDATE_SORT ASC; TYPE COND_TABLE IS RECORD(
GKCPH VARCHAR2(60),
GKCPSM VARCHAR2(60),
COND_TAB_NAME VARCHAR2(4000),
COND_COL_NAME VARCHAR2(60),
OPERATER VARCHAR2(10),
OPERATE_VALUE VARCHAR2(300),
CONN_OPERATE VARCHAR2(10));
-- cond_table_details cond_table;
TYPE COLLECTION IS TABLE OF COND_TABLE;
TEMP_OBJECTS COLLECTION; T_SQL VARCHAR2(2000) := '';
T_TABLE VARCHAR2(500) := '';
T_VALUE VARCHAR2(500) := '';
TYPE TEMP_TABLE_COLLECTION IS TABLE OF VARCHAR2(4000);
VIRTUAL_TABLE TEMP_TABLE_COLLECTION; DEBUG_POSITION VARCHAR2(20) := '';
V_STARTTIME DATE;
V_ENDTIME DATE;
V_POSITION VARCHAR2(20);
V_SQL VARCHAR2(2000);
V_TEMP VARCHAR2(200);
V_BEGIN VARCHAR2(1000) := NULL;
V_END VARCHAR2(1000) := NULL;
V_HANDLE VARCHAR2(2);
V_EXE_SEQ NUMBER(6);
CURSOR C2 IS
SELECT DEBUG_POSITION, DEBUG_DESC, HAND_FLAG, EXE_SEQ
FROM DEBUG_INFO
WHERE CREATE_STATUS = 'SQL-CREATED'
--- and LOWER(DEBUG_DESC) like '%xd%'
ORDER BY EXE_SEQ, DEBUG_POSITION ASC;
ROWS NUMBER := 0;
STR VARCHAR2(50) := '';BEGIN --清空系统生成的SQL
DELETE FROM DEBUG_INFO T
WHERE TRIM(T.HAND_FLAG) = 'Y'
OR T.HAND_FLAG IS NULL;
--清除手工导入的SQL上一次执行的信息
UPDATE DEBUG_INFO T
SET T.EXEC_STATUS = NULL, T.RUN_TIME = NULL, T.ROWCOUNT = NULL;
DEBUG_POSITION := 'd1';
OPEN C1;
LOOP
FETCH C1
INTO DEST_TABLE_DETAILS;
EXIT WHEN C1%NOTFOUND;
DEBUG_POSITION := 'd2';
T_SQL := ' update ' || DEST_TABLE_DETAILS.TAB_NAME || ' set ' ||
DEST_TABLE_DETAILS.COL_NAME || ' = ' || CHR(39) ||
TRIM(DEST_TABLE_DETAILS.GKCPH) || CHR(39);
DEBUG_POSITION := 'd3';
SELECT TRIM(PRODUCT_ID)
,TRIM(PRODUCT_NAME)
,TRIM(WHERE_TABLE)
,TRIM(WHERE_FIELD)
,TRIM(YS_FH)
,TRIM(FIELD_VALUE)
,TRIM(UNION_FH) BULK COLLECT
INTO TEMP_OBJECTS
FROM P_CPYS_WHERE
WHERE PRODUCT_ID = TRIM(DEST_TABLE_DETAILS.GKCPH)
ORDER BY UPDATE_SORT ASC;
DEBUG_POSITION := 'd4';
SELECT DISTINCT (WHERE_TABLE) BULK COLLECT
INTO VIRTUAL_TABLE
FROM P_CPYS_WHERE
WHERE PRODUCT_ID = TRIM(DEST_TABLE_DETAILS.GKCPH)
ORDER BY PRODUCT_ID ASC;
DEBUG_POSITION := 'd5';
IF VIRTUAL_TABLE.COUNT = 0 THEN
INSERT INTO DEBUG_INFO
(DEBUG_POSITION, DEBUG_DESC, CREATE_STATUS)
VALUES
(DEST_TABLE_DETAILS.GKCPH
,T_SQL
,'d5-关联条件表(p_cpys_where)出错');
CONTINUE;
ELSE
FOR J IN 1 .. VIRTUAL_TABLE.COUNT LOOP
T_TABLE := T_TABLE || ' ' || VIRTUAL_TABLE(J) || ' ,';
END LOOP;
T_TABLE := SUBSTR(T_TABLE, 1, LENGTH(T_TABLE) - 1);
END IF;
DEBUG_POSITION := 'd6';
IF TEMP_OBJECTS.COUNT = 0 THEN
CONTINUE;
END IF;
T_SQL := T_SQL || ' where ' || DEST_TABLE_DETAILS.COL_INDEX ||
' in ( select ' || DEST_TABLE_DETAILS.COL_INDEX ||
' from ' || T_TABLE || ' where ';
DEBUG_POSITION := 'd7';
FOR I IN 1 .. TEMP_OBJECTS.COUNT LOOP
--字母开头,含有.号
IF REGEXP_LIKE(TEMP_OBJECTS(I).OPERATE_VALUE, '^[[:alpha:]]*\.') THEN
T_VALUE := TEMP_OBJECTS(I).OPERATE_VALUE;
--处理操作符为between的情况
ELSIF /*regexp_like(temp_objects(i).operate_value,
'(\w{1,})(\s{1,}and\s{1,})(\w{1,})')*/
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'between' THEN
T_VALUE := REGEXP_REPLACE(TEMP_OBJECTS(I).OPERATE_VALUE,
'(\w{1,})(\s{1,}\w{1,}\s{1,})(\w{1,})',
'''\1'' \2 ''\3''');
--处理操作符为in的情况
ELSIF /*regexp_like(temp_objects(i).operate_value, '^\(\w{1,}')*/
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'in' THEN
T_VALUE := REGEXP_REPLACE(TEMP_OBJECTS(I).OPERATE_VALUE,
'(\w{1,})',
'''\1''');
--处理null值
ELSIF LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) = 'is' THEN
--regexp_like(lower(trim(temp_objects(i).operater)), '[=is=]') then
T_VALUE := TRIM(TEMP_OBJECTS(I).OPERATE_VALUE);
DEBUG_POSITION := 'd9';
--处理 (odw_hx_ck.....这样的情况
IF REGEXP_LIKE(TEMP_OBJECTS(I).COND_COL_NAME, '[=(=]') THEN
V_BEGIN := ' (' || SUBSTR(TEMP_OBJECTS(I).COND_COL_NAME, 2);
END IF;
ELSE
STR := LOWER(TRIM(TEMP_OBJECTS(I).OPERATE_VALUE));
IF STR <> 'null' THEN
T_VALUE := CHR(39) || TRIM(TEMP_OBJECTS(I).OPERATE_VALUE) ||
CHR(39);
ELSE
T_VALUE := STR;
END IF;
END IF;
--处理 is not null 情况
/* str := lower(TRIM(temp_objects(i).operater));
IF regexp_like(str, '[=is=]')
AND regexp_like(str, '[=not=]') THEN
t_value := TRIM(temp_objects(i).operate_value);
--t_value := substr(t_value, 1, length(t_value) - 1);
END IF;*/
--处理 product_id = 122211 )这样的情况
IF REGEXP_LIKE(TEMP_OBJECTS(I).OPERATE_VALUE, '[=)=]') AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'between' AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'in' AND
LOWER(TRIM(TEMP_OBJECTS(I).OPERATER)) <> 'is' THEN
V_TEMP := TRIM(TEMP_OBJECTS(I).OPERATE_VALUE);
V_END := TRIM(SUBSTR(V_TEMP, 1, LENGTH(V_TEMP) - 1));
V_END := CHR(39) || V_END || CHR(39) || ' )';
T_VALUE := V_END;
END IF;
--在这里拼接sql
T_SQL := T_SQL || NVL(V_BEGIN, TEMP_OBJECTS(I).COND_COL_NAME) || ' ' || TEMP_OBJECTS(I)
.OPERATER || ' ' || T_VALUE || ' ' || TEMP_OBJECTS(I)
.CONN_OPERATE || ' ';
V_BEGIN := NULL;
V_END := NULL;
STR := NULL;
END LOOP;
T_SQL := T_SQL || ' ) ';
INSERT INTO DEBUG_INFO
(DEBUG_POSITION, DEBUG_DESC, CREATE_STATUS, HAND_FLAG)
VALUES
(TRIM(DEST_TABLE_DETAILS.GKCPH), T_SQL, 'SQL-CREATED', 'Y');
DEBUG_POSITION := 'd10';
T_SQL := NULL;
T_TABLE := NULL;
T_VALUE := NULL;
STR := NULL;
COMMIT;
END LOOP;
CLOSE C1;
COMMIT;
END LOOP;
CLOSE C2;
COMMIT;END PROC_UPDATE_COMMON_COA_ID;SQL ServerOracle存储SQL调试
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货