1。搜索“结果集” CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); END pkg_test; /CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE get (p_id NUMBER, p_rc OUT myrctype) IS sqlstr VARCHAR2 (500); BEGIN IF p_id = 0 THEN OPEN p_rc FOR SELECT ID, NAME, sex, address, postcode, birthday FROM student; ELSE sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; OPEN p_rc FOR sqlstr USING p_id; END IF; END get; END pkg_test; /
还有一个问题就是: 以前在SQL中存储过程执行时,如果有错。可以通过一个传出参数解决。 比如: create proc test @bz varchar(20) as begin ..... if (@@error=0) select @bz='过程处理成功!' else select @bz='过程处理失败!' end前台程序想通过传出的参数,进行判断,可如何得到这个参数呢?
如果是ado 通过commmad对象执行得到参数集,取回返回参数值
问题一: create package test_age as type mycursor is ref cursor; end; / create proc test(p_rc out test_age.mycursor) as begin open p_rc for 'select * from temp'; end; / 问题二: create procedure get_value(str1 in number,str2 in number,str3 out number) as begin str3:=str1+str2; end; SQL运行时: set serveroutput on declare v_str number; begin get_value (1,2,v_str); dbms_output.put_line(v_str); end; /
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
www.51delphi.com
以前在SQL中存储过程执行时,如果有错。可以通过一个传出参数解决。
比如:
create proc test
@bz varchar(20)
as
begin
.....
if (@@error=0)
select @bz='过程处理成功!'
else select @bz='过程处理失败!'
end前台程序想通过传出的参数,进行判断,可如何得到这个参数呢?
通过commmad对象执行得到参数集,取回返回参数值
create package test_age
as
type mycursor is ref cursor;
end;
/
create proc test(p_rc out test_age.mycursor)
as
begin
open p_rc for 'select * from temp';
end;
/
问题二:
create procedure get_value(str1 in number,str2 in number,str3 out number)
as
begin
str3:=str1+str2;
end;
SQL运行时:
set serveroutput on
declare
v_str number;
begin
get_value (1,2,v_str);
dbms_output.put_line(v_str);
end;
/