创建返回结果集的函数SQL> create or replace package pkg_HelloWorld as 2 — 定义ref cursor类型 3 type myrctype is ref cursor; 4 –函数申明 5 function getHelloWorld return myrctype; 6 end pkg_HelloWorld; 7 /程序包已创建。SQL> CREATE OR REPLACE package body pkg_HelloWorld as 2 function getHelloWorld return myrctype 3 IS 4return_cursor myrctype; 5 BEGIN 6OPEN return_cursor FOR 7SELECT ‘Hello
1′ AS a, ‘World 1’ AS B FROM dual 8UNION ALL 9SELECT ‘Hello
2′ AS a, ‘World 2’ AS B FROM dual; 10return return_cursor; 11 END getHelloWorld; 12 end pkg_HelloWorld; 13 /程序包体已创建。注：Oracle 这里的函数，是一个返回游标类型的函数， 不是像 SQL Server 的那种叫 “表值函数” 的东西。因此下面的写法会报错。SQL> SELECT * FROM pkg_HelloWorld.getHelloWorld();SELECT * FROM pkg_HelloWorld.getHelloWorld() *第 1 行出现错误:ORA-00933: SQL 命令未正确结束SQL> SELECT pkg_HelloWorld.getHelloWorld() FROM dual;PKG_HELLOWORLD.GETHE——————–CURSOR STATEMENT : 1CURSOR STATEMENT : 1A B——- ——-Hello 1 World 1Hello 2 World 2
