时间戳

首页 > 解决方案 > 更新语句内的 DBMS_SQL.NUMBER_TABLE 引发无效数据类型错误

sql - 更新语句内的 DBMS_SQL.NUMBER_TABLE 引发无效数据类型错误

问题描述

为什么选择有效但更新会抛出无效数据？

DECLARE
    L_NUMBER NUMBER;
    L_NUMBER_TABLE DBMS_SQL.NUMBER_TABLE;
    L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
    L_NUMBER_TABLE(0) := 1033000;

-- THIS WORKS
SELECT TB.COLUMN_VALUE
INTO L_NUMBER
FROM TABLE(L_NUMBER_TABLE) TB;

-- THIS DOES NOT WORK
--  ERROR AT LINE 1
--  ORA-00902: INVALID DATATYPE
--  ORA-06512: AT LINE 13
UPDATE SCHEMA.REAL_NUMBER_TABLE
SET    REAL_NUMBER_DATE           = L_LAST_PRINTED_DATE
WHERE  EXISTS (  SELECT TB.COLUMN_VALUE
                    FROM TABLE( L_NUMBER_TABLE ) TB
                    WHERE TB.COLUMN_VALUE = REAL_NUMBER_COLUMN );


END;

我正在尝试遍历游标并更新在游标内找到的主序列的最后打印日期。我尝试通过游标循环，但是当我将游标返回给客户端时，它会抛出一个超出索引的错误。所以我被迫做两个游标，一个循环，一个返回。我的目标是了解保存用于更新表的集合的最简单和最可维护的方法是什么。

DECLARE
    CURSOR L_ORIGINAL_CURSOR IS SELECT ...

    L_CURSOR_COLUMN_1    PLS_INTEGER;
    L_CURSOR_COLUMN_2    PLS_INTEGER;
    L_CURSOR_COLUMN_3    PLS_INTEGER;
    -- Keep adding or removing the number of columns to match...1/2 09182019515PM
    -- L_CURSOR_COLUMN_4    PLS_INTEGER;
    L_NUMBER_TABLE DBMS_SQL.NUMBER_TABLE;
    L_COUNTER PLS_INTEGER;
    L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN    
    OPEN L_ORIGINAL_CURSOR;
        LOOP
            -- Keep adding or removing the number of columns to match... 2/2 09182019515PM
            FETCH L_ORIGINAL_CURSOR INTO L_CURSOR_COLUMN_1, L_CURSOR_COLUMN_2, L_CURSOR_COLUMN_3; -- , L_CURSOR_COLUMN_4;
                IF L_ORIGINAL_CURSOR%NOTFOUND THEN
                    EXIT;
                END IF;

                IF L_ORIGINAL_CURSOR%FOUND THEN
                        -- CURRENT SOLUTION IS TO UPDATE HERE BOUNCING BETWEEN SQL AND PLSQL ENGINES
                        -- UPDATE ....
                    -- WANTED IMPLEMENTATION
                    L_COUNTER := L_COUNTER + 1;
                END IF;
                -- WANTED IMPLEMENTATION STORE PK IN MY COLLECTION
                L_NUMBER_TABLE(L_COUNTER) := L_CURSOR_COLUMN_1;
        END LOOP;

    -- IF COLLECTION IS BIGGER THAN 0
    IF L_NUMBER_TABLE.COUNT > 0 THEN
        -- SCRIPT BREAKS HERE
        UPDATE ...
        SET ... = L_LAST_PRINTED_DATE
        WHERE EXISTS (  SELECT TB.COLUMN_VALUE
                        FROM TABLE(L_NUMBER_TABLE) TB
                        WHERE TB.COLUMN_VALUE = ...   );
    END IF;
    CLOSE L_ORIGINAL_CURSOR;

    OPEN L_CURSOR FOR SELECT ...
END SP_GET_PM_WORK_ORDERS;
/
SHOW ERRORS;

目前，内联单 sql 更新语句有效，但需要 SQL 和 PLSQL 引擎之间的多个引擎跃点。为什么我可以使用我的数字表进行选择但不能更新？

数据库版本：12.1.0.2.0

标签： sqloracleplsqloracle12c

解决方案

DBMS_SQL.NUMBER_TABLE是一个 PL/SQL 关联数组，在DBMS_SQL包中定义为：

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

这是一种 PL/SQL 数据类型，不应该在 SQL 语句中工作（我还没有弄清楚为什么你的第一个语句工作）。

如果你想要一个在 SQL 中工作的数据类型，那么你需要使用一个集合（没有INDEX BY; 也称为嵌套表数据类型）：

CREATE TYPE number_table IS TABLE OF NUMBER;

或固定长度VARRAY：

CREATE TYPE number_array IS VARRAY(10) OF NUMBER;

例如：

甲骨文设置：

CREATE TABLE real_number_table ( real_number_column, real_number_date ) AS
  SELECT 1033000, DATE '2019-01-01' FROM DUAL;

CREATE TYPE number_table IS TABLE OF NUMBER;

PL/SQL 语句 1：

然后您的 SQL 语句将使用集合数据类型：

DECLARE
  L_NUMBER NUMBER;
  L_NUMBER_TABLE NUMBER_TABLE;
  L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
  L_NUMBER_TABLE := NUMBER_TABLE();
  L_NUMBER_TABLE.EXTEND;
  L_NUMBER_TABLE( L_NUMBER_TABLE.COUNT ) := 1033000;

  UPDATE REAL_NUMBER_TABLE
  SET    REAL_NUMBER_DATE = L_LAST_PRINTED_DATE
  WHERE  EXISTS (
    SELECT TB.COLUMN_VALUE
    FROM TABLE( L_NUMBER_TABLE ) TB
    WHERE TB.COLUMN_VALUE = REAL_NUMBER_COLUMN
  );
END;
/

接着：

SELECT * FROM real_number_table;

输出：

REAL_NUMBER_COLUMN | REAL_NUMBER_DATE
-----------------: | :---------------
           1033000 | 09-10-19

PL/SQL 语句 2：

或者您可以简化它并使用MEMBER OF运算符（这仅适用于集合数据类型而不适用于VARRAYs）：

DECLARE
  L_NUMBER NUMBER;
  L_NUMBER_TABLE NUMBER_TABLE := NUMBER_TABLE( 1033000 );
  L_LAST_PRINTED_DATE DATE := SYSDATE + 1;
BEGIN
  UPDATE REAL_NUMBER_TABLE
  SET    REAL_NUMBER_DATE = L_LAST_PRINTED_DATE
  WHERE  REAL_NUMBER_COLUMN MEMBER OF L_NUMBER_TABLE;
END;
/

接着：

SELECT * FROM real_number_table;

输出：

REAL_NUMBER_COLUMN | REAL_NUMBER_DATE
-----------------: | :---------------
           1033000 | 19 年 10 月 10 日

db<>在这里摆弄

您最终的 PL/SQL 匿名块可以重写为：

DECLARE
  L_NUMBER_TABLE NUMBER_TABLE;
  L_LAST_PRINTED_DATE DATE := SYSDATE;
BEGIN
  SELECT column1
  BULK COLLECT INTO L_NUMBER_TABLE
  FROM   your_table; -- as per L_ORIGINAL_CURSOR

  IF L_NUMBER_TABLE.COUNT > 0 THEN
    UPDATE other_table
    SET    date_column = L_LAST_PRINTED_DATE
    WHERE  number_column MEMBER OF L_NUMBER_TABLE;
  END IF;
END;
/

推荐阅读