------ 给个例子给你: -- i_address 是存储过程的输入参数,o_cur是存储过程的输出游标参数,用以获取返回的结果集! CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR) IS sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句 BEGIN sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率! OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值 END; /set serveroutput on; var c_cur refcursor; exec userinfo_proc('北京',:c_cur); print c_cur;
var v_ss varchar(20); begin select ID into :v_ss from table where ID='&ID'; end; /在过程里的话,定义好变量,用select .. into ..来取 或者写到游标里
oracle还是SqlServer @呢?
1.游标 2.存储过程的 out 参数
给个经典范例给你看。create or replace procedure pro_two ( tempno in dept.deptno%type, tempdname out dept.dname%type, temploc in out dept.loc%type ) as locl dept.loc%type; dnamel dept.dname%type; begin select loc into locl from dept where deptno=tempno;
select dname into dnamel from dept where deptno=tempno;
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率!
OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/set serveroutput on;
var c_cur refcursor;
exec userinfo_proc('北京',:c_cur);
print c_cur;
begin
select ID into :v_ss from table where ID='&ID';
end;
/在过程里的话,定义好变量,用select .. into ..来取
或者写到游标里
2.存储过程的 out 参数
(
tempno in dept.deptno%type,
tempdname out dept.dname%type,
temploc in out dept.loc%type
)
as
locl dept.loc%type;
dnamel dept.dname%type;
begin
select loc into locl
from dept
where deptno=tempno;
select dname into dnamel
from dept
where deptno=tempno;
temploc:='地址:'||locl;
tempdname:='姓名:'||dnamel;
end;
--调用
declare
myno dept.deptno%type;
mydname dept.dname%type;
myloc dept.loc%type;
begin
myno:=10;
mydname:='';
myloc:='';
pro_two(myno,mydname,myloc);
dbms_output.put_line(myno);
dbms_output.put_line(mydname);
dbms_output.put_line(myloc);
end;
v_ss varchar(20);
begin
select ID into v_ss from table where ID=@ID;
dbms_output.put_line(v_ss);
end