oracle用存储过程查询一张表中的信息

Oracle
需要通过
返回一个游标来处理。
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
/
Package
created.
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
'SELECT
''Hello''
AS
a,
''World''
AS
B
FROM
dual';
7
return
return_cursor;
8
END
getHelloWorld;
9
end
pkg_HelloWorld;
10
/
Package
body
created.
SQL>
SELECT
pkg_HelloWorld.getHelloWorld
FROM
dual;
GETHELLOWORLD
--------------------
CURSOR
STATEMENT
:
1
CURSOR
STATEMENT
:
1
A
B
----------
----------
Hello
World
上面的是使用动态SQL处理的。
下面是正常SQL处理的。
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
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
/
程序包体已创建。
返回游标的函数，不是
“表值函数”
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
:
1
CURSOR
STATEMENT
:
1
A
B
-------
-------
Hello
1
World
1
Hello
2
World
2

先定义一个系统游标（sys_refcursor），然后将返回的记录集放到这个游标中。例如：
create
or
replace
procedure
test
(ret
out
sys_refcursor)
as
begin
open
ret
for
select
col_name
from
table;
end
test;