oracle - 对象类型中 %ROWTYPE 的解决方法

Question

我是 PL/SQL 的新手，并尝试创建一个引用表行的对象:

CREATE OR REPLACE TYPE my_object AS OBJECT(
  table_row my_table%ROWTYPE
);

编译器给了我错误消息 PLS-00329。
好的，现在我知道我不允许引用这样的表格。但是有解决方法吗？

Answer 1

您必须列出对象类型声明中的所有字段。你提到你担心容易出错。您可以通过 PL/SQL 块(或您将表和对象名称传入的过程)半自动地创建对象。

假设你有一个定义为的表；

CREATE TABLE my_table (
  ID NUMBER(38),
  col_1 DATE,
  col_2 NUMBER(3, 2),
  col_3 VARCHAR2(10),
  col_4 CLOB
);

您可以从数据字典中提取列名和数据类型并构建您的对象创建语句:

DECLARE
  v_stmt VARCHAR2(4000);
BEGIN
  v_stmt := 'CREATE OR REPLACE TYPE my_object AS OBJECT(';

  FOR r IN (
    SELECT column_name,
    CAST (data_type || CASE 
      WHEN data_type IN ('VARCHAR', 'VARCHAR2', 'NVARCHAR2', 'RAW', 'CHAR')
        THEN '(' || data_length || ')'
      WHEN data_type IN ('NUMBER')
          AND (data_precision IS NOT NULL OR data_scale IS NOT NULL)
        THEN '(' || data_precision || CASE
          WHEN data_scale > 0 THEN ',' || data_scale
        END || ')'
      END AS VARCHAR2(30)) AS data_type,
      CASE WHEN column_id < MAX(column_id) OVER () THEN ',' END AS comma
    FROM user_tab_columns
    WHERE table_name = 'MY_TABLE'
    ORDER BY column_id
  )
  LOOP
    v_stmt := v_stmt || r.column_name || ' ' || r.data_type || r.comma;
  END LOOP;

  v_stmt := v_stmt || ')';

  dbms_output.put_line(v_stmt); -- just for debugging
  EXECUTE IMMEDIATE v_stmt;
END;
/

如果需要，您也可以提取和使用可空标志，但它在这里可能没有用。 (这是改编自 a describe replacement 。可能有一些数据类型没有正确处理，但它涵盖了常见的数据类型；如果您有包含 UDT 或其他奇怪的列，则需要扩展以正确包含这些数据类型。)
dbms_output只显示生成的语句，以便于调试:

CREATE OR REPLACE TYPE my_object AS OBJECT(ID NUMBER(38),COL_1 DATE,COL_2 NUMBER(3,2),COL_3 VARCHAR2(10),COL_4 CLOB)

PL/SQL procedure successfully completed.

随着该语句也被执行，对象被创建:

desc my_object;

Name  Null? Type         
----- ----- ------------ 
ID          NUMBER(38)   
COL_1       DATE         
COL_2       NUMBER(3,2)  
COL_3       VARCHAR2(10) 
COL_4       CLOB         

如果您想添加函数，您可以使用生成的 create 语句作为起点，并在手动运行之前对其进行编辑以添加您需要的任何内容，而不是使用 execute immediate .