sql - DML 子查询中的 NESTED 表
问题描述
使用 Oracle 12c EE,如何在 DML 子查询中使用 PL/SQL 包类型而不引发异常“ORA-00902:无效数据类型”?
示例架构
--PL/SQL package types.
create or replace package test_pkg as
TYPE type_record IS RECORD(
column1 NUMBER,
column2 NUMBER,
column3 NUMBER);
TYPE type_table IS TABLE OF type_record;
end;
/
--For comparison, the same types but as SQL objects.
CREATE OR REPLACE TYPE type_record IS OBJECT(
column1 NUMBER,
column2 NUMBER,
column3 NUMBER);
CREATE OR REPLACE TYPE type_table IS TABLE OF type_record;
--Table for testing DML.
create table tableX(a number);
SQL SELECT 中的 PL/SQL 类型 - WORKS
从 PL/SQL 类型到 SQL 的转换适用于 SELECTS。下面的代码运行良好:
declare
vt test_pkg.type_table;
v_count number;
begin
select count(*)
into v_count
from dual
where not exists(select column1 from table(vt));
end;
/
SQL UPDATE 中的 PL/SQL 类型 - 失败
但是在 DML 语句中使用相同的类型和子查询会引发异常:“ORA-00902: invalid datatype/ORA-06512: at line 4”。
declare
vt test_pkg.type_table;
begin
update tableX set a = 1
where not exists (select column1 from table(vt));
end;
/
SQL UPDATE 中的 SQL 类型 - WORKS
作为比较,在子查询中使用 SQL 对象在 DML 中效果很好:
declare
vt type_table;
begin
update tableX set a = 1
where not exists (select column1 from table(vt));
end;
/
为每个查询创建 SQL 对象是一种解决方法,但这会创建很多不必要的模式对象。有没有办法让 PL/SQL 包类型在 DML 子查询中工作?
解决方案
我想这是一个意见问题,因为我使用 db 级别类型,因为(恕我直言)这是保持代码清洁的原因。但这只是看待它的另一种方式。但是,至少在这种情况下,您要寻找的东西并不难。您已经在创建一个使用这些定义的包。您可以将定义移动到包规范中。然后将需要很少或不需要代码更改。此外,这些定义仍然可以在包本身之外使用。
create or replace package pkg_t1 as
type type_record is record(
column1 number,
column2 number,
column3 number);
type type_table is table of type_record;
function build_type_records(rows_to_build in integer)
return type_table;
function build_pipe_records(rows_to_build in integer)
return type_table
pipelined;
end pkg_t1;
/
create or replace package body pkg_t1 as
function build_type_records(rows_to_build in integer)
return type_table
is
v_type type_table := type_table();
begin
for i in 1 .. rows_to_build
loop
v_type.extend;
v_type(i).column1 := trunc(dbms_random.value(1,100));
v_type(i).column2 := trunc(dbms_random.value(100,500));
v_type(i).column3 := dbms_random.value();
end loop ;
return v_type;
end build_type_records;
function build_pipe_records(rows_to_build in integer)
return type_table
pipelined
is
v_rec type_table;
begin
v_rec:= build_type_records(rows_to_build);
for i in 1 .. v_rec.count
loop
pipe row (v_rec(i));
end loop;
end build_pipe_records;
end pkg_t1;
/
declare
tt pkg_t1.type_table;
num_of_rows integer := &Number_of_rows;
begin
tt := pkg_t1.build_type_records( num_of_rows );
for i in 1 .. tt.count
loop
dbms_output.put_line( 'Result: '
|| 'Column1==>' || tt(i).column1 || ', '
|| 'Column2==>' || tt(i).column2 || ', '
|| 'Column3==>' || tt(i).column3
) ;
end loop;
end ;
/
select * from table(pkg_t1.build_pipe_records(&Rows_Desirded));
推荐阅读
- python - Rioxarray 维度缺少坐标
- python - 如何在熊猫中读取具有完整路径的csv文件
- powershell - Powershell - 替换匹配的多个正则表达式条件
- javascript - 从 Google Apps 脚本 Web 应用程序使用剪贴板 API
- php - 如何仅提取子目录名称?
- r - R中是否有代码可以更改ggboxplot中的x轴?
- python - 如何解决 TypeError:“NoneType”类型的对象没有 len()
- python - 使用 QTableView 和 QSelectionModel 选择日历样式
- html - 尝试使用 css 隐藏弹出窗口
- python - wget 不是内部或外部命令、可运行程序或批处理文件