oracle的存儲(chǔ)過程查詢整張表返回結(jié)果？

創(chuàng)建返回結(jié)果集的函數(shù)SQL> create or replace package pkg_HelloWorld as 2 -- 定義ref cursor類型 3 type myrctype is ref cursor; 4 --函數(shù)申明 5 function getHelloWorld return myrctype; 6 end pkg_HelloWorld; 7 /程序包已創(chuàng)建。SQL> CREATE OR REPLACE package body pkg_HelloWorld as 2 function getHelloWorld return myrctype 3 IS 4 return_cursor myrctype; 5 BEGIN 6 OPEN return_cursor FOR 7 SELECT 'Hello

1' AS a, 'World 1' AS B FROM dual 8 UNION ALL 9 SELECT 'Hello

2' AS a, 'World 2' AS B FROM dual; 10 return return_cursor; 11 END getHelloWorld; 12 end pkg_HelloWorld; 13 /程序包體已創(chuàng)建。注：Oracle 這里的函數(shù)，是一個(gè)返回游標(biāo)類型的函數(shù)， 不是像 SQL Server 的那種叫 “表值函數(shù)” 的東西。因此下面的寫法會(huì)報(bào)錯(cuò)。SQL> SELECT * FROM pkg_HelloWorld.getHelloWorld();SELECT * FROM pkg_HelloWorld.getHelloWorld() *第 1 行出現(xiàn)錯(cuò)誤:ORA-00933: SQL 命令未正確結(jié)束SQL> SELECT pkg_HelloWorld.getHelloWorld() FROM dual;PKG_HELLOWORLD.GETHE--------------------CURSOR STATEMENT : 1CURSOR STATEMENT : 1A B------- -------Hello 1 World 1Hello 2 World 2