PL/SQL存储过程编译报错求助：嵌套表与管道函数问题

Let's work through your problems step by step, starting with the initial error and then resolving the PLS-00629 issue in your refactored code.

1. Initial Code: Why the SELECT INTO Failed #

Your first error came from misusing the TABLE() operator. The cartLineList(i) expression already returns a single CartLine object—you don't need to wrap it in TABLE() to access its attributes. Instead, you can directly reference the object's properties like you would with any other PL/SQL object.

Here's the fixed version of your initial procedure (with proper formatting and no unnecessary SELECT INTO):

create or replace type CartLine as object ( 
    offeringId OfferingIdList ,
    productLine varchar2(50) ,
    equipment char(1) ,
    installment CHAR(1) ,
    cartItemProcess varchar2(50) ,
    minimalPrice decimal 
);
/
create or replace type CartLineType is table of CartLine;
/
create or replace PROCEDURE GetOfferingRecommendation ( 
    cartLineList IN CartLineType, 
    user IN UserType, 
    customer IN CustomerType, 
    processContext IN ProcessContextType, 
    recommendation out SYS_REFCURSOR 
) IS 
    prodLine VARCHAR2(20); 
    prodPrice NUMBER(5,0);
    
    -- Temporary collection to store all recommendation results
    TYPE RecommendationRec IS RECORD (
        recommendationId VARCHAR2(100),
        offeringId VARCHAR2(50), -- Adjust data type to match your table's ID_REKOM_OFERTA
        priority NUMBER(5,0)
    );
    TYPE RecommendationList IS TABLE OF RecommendationRec;
    v_recommendations RecommendationList := RecommendationList();
BEGIN 
    FOR i IN cartLineList.FIRST .. cartLineList.LAST LOOP
        -- Directly access the CartLine object's attributes
        prodLine := cartLineList(i).productLine;
        prodPrice := cartLineList(i).minimalPrice;
        
        -- Bulk collect results for the current cart line into our temporary collection
        SELECT 
            CAST(REKOM_ID_SEQ.NEXTVAL AS VARCHAR(10)) ||'_'||cp.ID_REKOM_OFERTA ||'_'||TO_CHAR(SYSDATE, 'yyyymmdd') AS recommendationId,
            cp.ID_REKOM_OFERTA AS offeringId,
            cp.PRIORYTET AS priority
        BULK COLLECT INTO v_recommendations
        FROM REKOM_CROSS_PROM cp 
        WHERE cp.LINIA_PROD = prodLine 
          AND prodPrice BETWEEN cp.CENA_MIN AND cp.CENA_MAX;
    END LOOP;
    
    -- Open the cursor to return all collected recommendations
    OPEN recommendation FOR
        SELECT * FROM TABLE(v_recommendations);
END GetOfferingRecommendation;
/

2. Refactored Code: Resolving PLS-00629 Error #

The PLS-00629 error ("Pipelined functions must be implemented as table functions") occurs because you tried using PIPE ROW in a regular stored procedure—this keyword is exclusive to pipelined table functions, not standard procedures.

Your refactored code also had several other issues:

• Unmatched END IF with no corresponding IF statement
• SQL injection risk and syntax errors from string concatenation in dynamic SQL
• Redundant EXIT WHEN CUR_TAB%NOTFOUND (cursor FOR loops handle termination automatically)
• Result overwriting (each loop replaced v_tst instead of appending to it)

Here's the corrected version of your refactored procedure (with dynamic SQL done safely, though static SQL is better here):

create or replace TYPE tst AS OBJECT ( 
    rekom_id varchar2(50) ,
    rekom_priorytet number(5,4) 
);
/
create or replace TYPE tst_list IS TABLE OF tst;
/
CREATE OR REPLACE PROCEDURE GetOfferingRecommendation (
    cartLineList IN CartLineType, 
    recommendation out SYS_REFCURSOR 
) IS 
    CURSOR CUR_TAB IS 
        SELECT productLine, minimalPrice 
        FROM TABLE(cartLineList); 
    v_tst tst_list := tst_list(); -- Initialize the main collection
    v_temp tst_list;
BEGIN 
    FOR i IN CUR_TAB LOOP
        -- Use bind variables to avoid SQL injection and syntax errors
        EXECUTE IMMEDIATE 'SELECT tst(CAST(REKOM_ID_SEQ.NEXTVAL AS VARCHAR(10))||''_''||cp.ID_REKOM_OFERTA||''_''||TO_CHAR(SYSDATE, ''yyyymmdd''), cp.PRIORYTET) 
                           FROM REKOM_CROSS_PROM cp 
                           WHERE cp.LINIA_PROD = :prodLine 
                             AND :prodPrice BETWEEN cp.CENA_MIN AND cp.CENA_MAX'
        BULK COLLECT INTO v_temp
        USING i.productLine, i.minimalPrice;
        
        -- Append temporary results to the main collection
        IF v_temp.COUNT > 0 THEN
            v_tst.extend(v_temp.COUNT);
            FOR j IN 1..v_temp.COUNT LOOP
                v_tst(v_tst.LAST - v_temp.COUNT + j) := v_temp(j);
            END LOOP;
        END IF;
    END LOOP;
    
    -- Return all results via the output cursor
    OPEN recommendation FOR
        SELECT * FROM TABLE(v_tst);
END GetOfferingRecommendation;
/

Key Takeaways #

• Avoid unnecessary TABLE() usage: When accessing a single object from a collection, directly reference its attributes instead of wrapping it in TABLE().
• PIPE ROW is for pipelined functions only: If you need to stream results, use a pipelined table function instead of a stored procedure.
• Dynamic SQL best practices: Always use bind variables (USING clause) instead of string concatenation to prevent SQL injection and syntax errors.
• Collect results properly: Use a persistent collection to accumulate results across loops, instead of overwriting the same variable each time.

内容的提问来源于stack exchange，提问作者anton1009