我有以下需求：插入和更新型別是表，插入是正確的，但現在我需要在插入之前，檢查是否已經有要更新的記錄，否則插入。
例子：
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;
...
uj5u.com熱心網友回復：
第一個解決方案：
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;
uj5u.com熱心網友回復：
嘗試以下方法之一：
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;
應該做這一切。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/329535.html