需要存储包示例中的Oracle存储过程接受array(table)参数 [英] An Oracle stored procedure accept array(table) parameter in package example needed
问题描述
This question is a part of my question how to pass javascript array to oracle store procedure by ado parameter object
我认为将其分为三个小部分可以更快地得到答案.
I think divide it to 3 small parts will get answer faster.
这个问题.
我知道我们可以声明一个表类型并使用
I know we can declare a table type and use
select last_name
from employees in (select * from table(cast(my_table_type_var as my_table_type));
但是从创建包含程序的新程序包中我总是会出错.我读了一个线程说我需要清除一个sql(在模式中)类型,因为sql和pl/slq没有使用相同的引擎.我尝试通过spl * plus在架构中创建类型,但仍然遇到相同的错误 所以请首先为此提供一个工作包示例.
but I always get error from create new package which include a procedure has this. and I read some thread said I need to declear a sql(in schema) type because sql and pl/slq are not using same engine. I try create type in schema by spl*plus, but still get same errors so I need a working package example for this first please.
推荐答案
您需要一个SQL对象才能从SELECT
调用TABLE
运算符.这是一个小例子(9iR2):
You need a SQL object to call the TABLE
operator from a SELECT
. Here's a small example (9iR2):
SQL> CREATE TYPE table_number is TABLE OF NUMBER;
2 /
Type created.
SQL> SELECT * FROM TABLE(table_number(1,2,3));
COLUMN_VALUE
------------
1
2
3
您可以在此处改用函数(请注意,我的TYPE仍在包外部定义):
You can use a function here instead (note that my TYPE is still defined outside the package):
SQL> CREATE OR REPLACE PACKAGE pkg AS
2 FUNCTION f RETURN table_number;
3 END;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
2 FUNCTION f RETURN table_number IS
3 BEGIN
4 RETURN table_number(4,5,6);
5 END;
6 END;
7 /
Package body created.
SQL> SELECT * FROM table(pkg.f);
COLUMN_VALUE
------------
4
5
6
这篇关于需要存储包示例中的Oracle存储过程接受array(table)参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!