PL/SQL How to select and update type IS TABLE OF-CodePudding

I have the following need: Insert and update in a type IS TABLE OF, the insert is correct, but now I need that before inserting, check if there is already a record to update, otherwise insert.

Example:

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;
                      ...

CodePudding user response：

Try

SELECT COUNT(*)
INTO v_ACCOUNT
FROM TABLE(v_FLUXO_OBJ)
WHERE DT_VENC = v_VENC
  AND TYPE = v_TYPE;

UPDATE TABLE(v_FLUXO_OBJ) 
SET ...

or maybe

UPDATE (SELECT * FROM TABLE(v_FLUXO_OBJ) WHERE DT_VENC = v_VENC AND TYPE = v_TYPE) 
SET ...

CodePudding user response：

Solution: 01)

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;