Question

oracle存储过程如何返回一个结果集，一定要用ref cursor吗？存储过程在pl/sql中是怎么调用的，越详细越好

Answer 1

知道包这个概念么？
---创建包头

CREATE OR REPLACE PACKAGE PKG_User IS

TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GET(p_user_id in varchar2,cur_out OUT T_CURSOR);
END PKG_User;
--创建包体

CREATE OR REPLACE PACKAGE BODY PKG_User IS
----Get by KeyId----------
PROCEDURE GET(p_user_id in varchar2,cur_out OUT T_CURSOR) IS
BEGIN
OPEN cur_out FOR
select user_id,
user_pid,
uname,
status,
nvl(company_id,-1) company_id,
nvl(dept_id,-1) dept_id,
nvl(station_id,-1) station_id,
nvl(subsys,-1) subsys,
nvl(user_type,-1) user_type,
keys
from TB_USER
where upper(user_id)=upper(p_user_id);
END ;
end PKG_User;
调用使用PKG_User.GET

Answer 2

CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/
CREATE OR REPLACE FUNCTION sf_split_string (string VARCHAR2, substring VARCHAR2) RETURN Varchar2Varray IS
len integer := LENGTH(substring);
lastpos integer := 1 - len;
pos integer;
num integer;
i integer := 1;
ret Varchar2Varray := Varchar2Varray(NULL);
BEGIN
LOOP
pos := instr(string, substring, lastpos + len);
IF pos > 0 THEN --found
num := pos - (lastpos + len);
ELSE --not found
num := LENGTH(string) + 1 - (lastpos + len);
END IF;
IF i > ret.LAST THEN
ret.EXTEND;
END IF;
ret(i) := SUBSTR(string, lastpos + len, num);
EXIT WHEN pos = 0;
lastpos := pos;
i := i + 1;
END LOOP;
RETURN ret;
END;
/

测试：
SQL> select * from table (cast (
sf_split_string('ABC,DEFGH,IJKLMN,OPQRST', ',') as Varchar2Varray) );

Answer 3

execute 过程

Answer 4

--1.存储过程
create or replace procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;

--1.1 调用存储过程
execute print('helloworld');

追问

应该是数据集，返回值类似于sql server中的select * from table_name,如果在oracle存储过程里面这样写的话就会报错

追答

"dbms_output" 是oracle存储过程的输出容器,
"cursor" 是常用的查询结果集合遍历方法;

不定义游标在存储过程中使用查询结果的另一中方法示例:

FOR rec IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put_line (rec.table_name);
END LOOP;