Oracle SQL创建函数或返回表的过程
问题描述:
在SQL Server中,我只能使用'RETURNS TABLE',它会完成这项工作。但我怎么也找不到做同样用Oracle SQLOracle SQL创建函数或返回表的过程
我有一个需要放在一个函数或过程下面的SELECT语句:
SELECT a.CodAcord, a.Descr
FROM FreqSoce f
LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
WHERE f.codSoce = codSoce;
codSoce
在参数整数,我需要返回a.CodAcord
和a.Descr
作为我的函数/过程的结果。
有没有简单的方法来做到这一点?而不必应付临时变量和/或高级内容...
编辑: aditional的信息:
- 我需要返回a.CodAcord
和a.Descr
,但是当我做了一些研究,懂得回报超过一个使用SQL函数或过程的变量,我能找到的仅仅是返回一个TABLE。如果有一种方法可以从函数或过程中返回多个项目,请让我知道。
- 严格要求使用函数或程序。
- 我正在使用SQL Developer。
答
看看下面的代码模板:
CREATE OR REPLACE PACKAGE tacord AS
TYPE ttabAcord IS TABLE OF ACord%ROWTYPE;
END tacord;
/
show err
CREATE OR REPLACE PACKAGE BODY tacord AS
BEGIN
NULL;
END tacord;
/
show err
CREATE OR REPLACE FUNCTION demo RETURN tacord.ttabAcord AS
to_return tacord.ttabAcord;
BEGIN
SELECT a.*
BULK COLLECT INTO to_return
FROM FreqSoce f
LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
WHERE f.codSoce = codSoce
;
RETURN to_return;
END demo;
/
show err
要点:
-
%ROWTYPE
代表一个数据库表中的记录的数据类型 -
BULK COLLECT INTO
插入一个完整的结果集到一个PLSQL数据结构 - 对表内容的迭代由plsql收集方法提供,即
.FIRST
,.LAST
,.NEXT
。
答
通过使用流水线表函数,可以实现一个表作为函数的返回值。请看下面的例子:
-- Create synthetic case
CREATE TABLE Acord AS
SELECT rownum CodAcord, 'Description ' || rownum Descr
FROM dual CONNECT BY LEVEL <= 5;
CREATE TABLE FreqSoce AS
SELECT rownum CodSoce, rownum CodAcord
FROM dual CONNECT BY LEVEL <= 10;
-- Test dataset
SELECT a.CodAcord, a.Descr
FROM FreqSoce f
LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
WHERE f.CodSoce = 10;
-- Here begins actual code
-- Create an object type to hold each table row
CREATE OR REPLACE TYPE typ_acord AS OBJECT(
CodAcord NUMBER,
Descr VARCHAR2(40)
);
/
-- Create a collection type to hold all result set rows
CREATE OR REPLACE TYPE tab_acord AS TABLE OF typ_acord;
/
-- Our function that returns a table
CREATE OR REPLACE FUNCTION getAcord(pCodSoce IN NUMBER)
RETURN tab_acord PIPELINED
AS
BEGIN
FOR x IN (SELECT a.CodAcord, a.Descr
FROM FreqSoce f
LEFT JOIN Acord a ON a.CodAcord = f.CodAcord
WHERE f.CodSoce = pCodSoce)
LOOP
PIPE ROW (typ_acord(x.CodAcord, x.Descr));
END LOOP;
END;
/
-- Testing the function (please note the TABLE operator)
SELECT * FROM TABLE(getAcord(5));
你需要返回a.CodAcord和a.Descr对于给定codScoce,或者你需要返回表?我没有看到连接。另外:你需要一个这样的程序,还是使用绑定变量足够?你用什么来与Oracle接口:SQL * Plus?蟾蜍? SQL Developer?每种方法都允许您“输入”一个绑定变量并基于此生成输出。请澄清,我们可以从那里采取。 – mathguy
提供的附加信息,感谢您的帮助。 –