Question

I have created this type: create or replace type PRODTABLE as table of PROD_OBJ;

并且我使用《个人生活标准法典》中的《程序法》:

    FUNCTION  INSERT_PRODUCTS (

      a_supplier_id IN FORNECEDOR.ID_FORNECEDOR%TYPE,

      a_prodArray IN PRODTABLE

      ) 

     RETURN NUMBER IS

        v_error_code    NUMBER;
        v_error_message VARCHAR2(255);
        v_result        NUMBER:= 0;
        v_prod_id       PRODUTO.ID_PROD%TYPE;
        v_supplier      FORNECEDOR%ROWTYPE;
        v_prodInserted  PROD_OBJ;
        type nestedTable is table of PROD_OBJ;
        newList   nestedTable := nestedTable();

     BEGIN

     SELECT  FORNEC_OBJ(ID_FORNECEDOR,NOME_FORNECEDOR,MORADA,ARMAZEM,EMAIL,TLF,TLM,FAX) into v_supplier from fornecedor where id_fornecedor = a_supplier_id;

      FOR i IN a_prodArray.FIRST .. a_prodArray.LAST LOOP

          INSERT INTO PRODUTO (PRODUTO.ID_PROD,PRODUTO.NOME_PROD,PRODUTO.PREC_COMPRA_PROD,PRODUTO.IVA_PROD,PRODUTO.PREC_VENDA_PROD,PRODUTO.QTD_STOCK_PROD,PRODUTO.QTD_STOCK_MIN_PROD) 
          VALUES (S_PRODUTO.nextval,a_prodArray(i).NOME_PROD,a_prodArray(i).PREC_COMPRA_PROD,a_prodArray(i).IVA_PROD,NULL,NULL,NULL);

          SELECT ID_PROD into v_prod_id from PRODUTO where NOME_PROD = a_prodArray(i).NOME_PROD;

          INSERT INTO PROD_FORNECIDO VALUES (a_supplier_id, v_prod_id,a_prodArray(i).PREC_COMPRA_PROD);

          SELECT PROD_OBJ(ID_PROD,NOME_PROD,PREC_COMPRA_PROD,PREC_VENDA_PROD,QTD_STOCK_PROD,QTD_STOCK_MIN_PROD,IVA_PROD) into v_prodInserted from PRODUTO where ID_PROD= v_prod_id;
          newList.extend;
          newList(newList.last):= v_prodinserted;

        END LOOP;

        /*the next line generates Error(43,63): PLS-00642: local collection types not      allowed in SQL statements,
Error(43,63): PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR */
        INSERT INTO FORNECPRODS2 VALUES (a_supplier_id,v_supplier,newList);
        v_result:= 1; 
        RETURN v_result;
        COMMIT;
    (...)
    END;

I want to do something like a_prodArray(i):= v_prodInserted, but I can t because it s a nested table, so I need to retrieve another one to use in that insert OR update each element of nested table with the new inserted product. I have to do this because each prod_obj in PRODTABLE comes with an id=0, from JAVA. Maybe there s another way, like making the default value for primary key equals SEQUENCE.nextval, I don t know. Could someone please enlight me?

感谢!

Answer 1

页: 1 由于它被宣布为<代码>IN参数(因此不能用作转让目标)。会计师联合会宣布该参数为IN OUT。

SQL> CREATE TYPE PROD_OBJ AS OBJECT
  2  (
  3     ID_PROD   NUMBER,
  4     NOME_PROD VARCHAR2(1)
  5  )
  6  ;
  7  /     
Type created

SQL> CREATE TYPE PRODTABLE as table of PROD_OBJ;
  2  /     
Type created

SQL> CREATE FUNCTION INSERT_PRODUCTS(a_prodArray IN OUT PRODTABLE)
  2     RETURN NUMBER IS
  3  BEGIN
  4     a_prodArray(1) := prod_obj(1, NULL);
  5     RETURN 0;
  6  END;
  7  /     
Function created

SQL> DECLARE
  2     l_prod_table prodtable := prodtable();
  3     dummy NUMBER;
  4  BEGIN
  5     l_prod_table.extend();
  6     dummy := INSERT_PRODUCTS(l_prod_table);
  7  END;
  8  /     
PL/SQL procedure successfully completed

现在,关于<代码>PLS-00642的错误,你必须使用EXACT与表格定义相同的数据类型。在该案中,我假定类型为<代码>。 PRODTABLE 页: 1 页: 1 名单作为<条码>。 (而不是PLSQL类nestedTable)。

友情链接