PL/SQL如何选择和更新类型IS TABLE OF

Question

我有以下需求:插入和更新的类型是表的，插入是正确的，但现在我需要插入之前，检查是否已经有记录更新，否则插入。

的例子:

CREATE OR REPLACE TYPE FLUXO_OBJ IS OBJECT (
DT_VENC CHAR(10),
TYPE CHAR(2),
VALOR NUMBER);
CREATE OR REPLACE TYPE FLUXO_TAB IS TABLE OF FLUXO_OBJ;
CREATE OR REPLACE FUNCTION FUNC_FLUXO (
P_DATE1 TABLE1.DT_VENC%TYPE, 
P_DATE2 TABLE1.DT_VENC%TYPE) 
RETURN FLUXO_TAB IS

v_COUNT INTEGER; v_FLUXO INTEGER := 0; v_VALOR NUMBER := 0;
v_VENC VARCHAR2(10); v_TYPE VARCHAR2(2); 
v_SCRIPT VARCHAR2(100); v_SELECT SYS_REFCURSOR;
v_FLUXO_OBJ FLUXO_TAB := FLUXO_TAB();

BEGIN
v_SCRIPT := 'SELECT DT_VENC, TYPE, VALOR FROM TABLE1';
BEGIN
OPEN v_SELECT FOR v_SCRIPT;
LOOP
FETCH v_SELECT INTO v_VENC, v_TYPE, v_VALOR;
EXIT WHEN v_SELECT%NOTFOUND;
BEGIN
BEGIN
/* HERE PROBLEM 1 */
SELECT COUNT(*)
INTO v_COUNT
FROM ??????
WHERE DT_VENC = v_VENC 
AND TYPE = v_TYPE;
END;
IF v_COUNT = 0 THEN /* INSERT */
v_FLUXO := v_FLUXO + 1;
v_FLUXO_OBJ.EXTEND;
v_FLUXO_OBJ(v_FLUXO) := FLUXO_OBJ(v_VENC,                                                           v_TYPE,v_VALOR);
ELSE
/* HERE PROBLEM 2 */
UPDATE ????
SET VALOR = VALOR + v_VALOR
WHERE DT_VENC = v_VENC
AND TYPE = v_TYPE;
...

Answer 1

第一个解:

SELECT ROWNUM
INTO v_COUNT
FROM TABLE(v_FLUXO_OBJ)
WHERE DT_VENC = v_VENC
AND TYPE = v_TYPE;

第二个解决方案:

v_FLUXO_OBJ(v_COUNT).VALOR := v_FLUXO_OBJ(v_COUNT).VALOR + v_VALOR;

Answer 2

试试这些:

SELECT COUNT(*)
INTO v_COUNT
FROM TABLE(v_FLUXO_OBJ)
WHERE DT_VENC = v_VENC
AND TYPE = v_TYPE;
v_COUNT := v_FLUXO_OBJ.COUNT;   
v_COUNT := CARDINALITY(v_FLUXO_OBJ);    

你不能UPDATE一个PL/SQL表，要么使用循环和逐个更新，要么这样做:

v_FLUXO_OBJ FLUXO_TAB := FLUXO_TAB();
v_FLUXO_OBJ_updated FLUXO_TAB;
BEGIN
SELECT FLUXO_OBJ(DT_VENC, TYPE, VALOR)
BULK COLLECT INTO v_FLUXO_OBJ
FROM TABLE1;
SELECT FLUXO_OBJ(DT_VENC, TYPE, VALOR + 1)
BULK COLLECT INTO v_FLUXO_OBJ_updated 
FROM TABLE(v_FLUXO_OBJ);

实际上，当我仔细看你的代码时，一个简单的

SELECT FLUXO_OBJ(DT_VENC, TYPE, SUM(VALOR))
BULK COLLECT INTO v_FLUXO_OBJ
FROM TABLE1
GROUP BY DT_VENC, TYPE;

应该做所有的。