我知道已经有关于此主题的其他文章,但是我找不到使它起作用的方法。有人可以照我的密码吗?我在这个问题上受阻。
所以基本上,由于以下三个错误,我无法正确创建我的过程:
PL / SQL:忽略SQL语句PL / SQL:ORA-00904:“ BCM_CURSOR”。“ ORDER_REF”:无效的标识符PLS-00225:子程序ref或游标'BCM_CURSOR'超出范围
这是我的SUBORDERS TABLE的架构:
CREATE TABLE SUBORDERS (
IDSUBORDER VARCHAR(128) PRIMARY KEY,
StatusSubOrders VARCHAR(128) DEFAULT 'Open',
DescriptionSubOrders VARCHAR(128)
);
我的XXBCM_ORDER_MGT表的架构是:
CREATE OR REPLACE PROCEDURE pr_MigrateSubOrders AS
CURSOR BCM_cursor IS select * FROM XXBCM_ORDER_MGT where ORDER_REF LIKE '%-%'; -- DECLARING MY CURSOR
v_current_max NUMBER;
r_current_row XXBCM_ORDER_MGT%ROWTYPE; -- ROWTYPE USED BY THE CURSOR
BEGIN
OPEN BCM_cursor;
FETCH BCM_cursor INTO r_current_row;
WHILE BCM_cursor%NOTFOUND != FALSE
LOOP
SELECT MAX(TO_NUMBER(regexp_replace(IDSUBORDER, '.*-(.*)','\1'))) INTO v_current_max FROM SUBORDERS WHERE IDSUBORDER LIKE regexp_replace(BCM_cursor.ORDER_REF, '(.*)-.*','\1') || '%'; -- working
IF v_current_max = NULL THEN
v_current_max := 1;
ELSE
v_current_max := v_current_max + 1;
END IF;
DBMS_OUTPUT.PUT_LINE(v_current_max);
FETCH BCM_cursor INTO r_current_row;
END LOOP;
CLOSE BCM_cursor;
END;
我已经测试了算法的主要部分,并且看来工作正常!(SELECT MAX
...)
CREATE OR REPLACE PROCEDURE pr_MigrateSubOrders AS
CURSOR BCM_cursor IS select * FROM XXBCM_ORDER_MGT where ORDER_REF LIKE '%-%'; -- DECLARING MY CURSOR
v_current_max NUMBER;
r_current_row XXBCM_ORDER_MGT%ROWTYPE; -- ROWTYPE USED BY THE CURSOR
BEGIN
OPEN BCM_cursor;
LOOP
FETCH BCM_cursor INTO r_current_row;
EXIT WHEN BCM_cursor%NOTFOUND;
SELECT MAX(TO_NUMBER(regexp_replace(IDSUBORDER, '.*-(.*)','\1'))) INTO v_current_max
FROM SUBORDERS
WHERE IDSUBORDER LIKE regexp_replace(r_current_row.ORDER_REF, '(.*)-.*','\1') || '%'; -- working
v_current_max := NVL( v_current_max, 0) + 1;
DBMS_OUTPUT.PUT_LINE(v_current_max);
END LOOP;
CLOSE BCM_cursor;
END;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句