例1 : 传入指定学生姓名 将其年龄加一岁 ,并打印加年龄前后学生信息。
--带输入参数in ,用于接收参数,在子程序内部,不能进行修改。默认的参数模式:in。
create or replace procedure increase_age(p_name IN tstudent.sname%TYPE) as
mystudent tstudent%ROWTYPE;
begin
select * into mystudent from tstudent where sname = p_name;
dbms_output.put_line(mystudent.sname || mystudent.sage);
update tstudent set sage = sage + 1 where sname = p_name;
commit;
select * into mystudent from tstudent where sname = p_name;
dbms_output.put_line(mystudent.sname || mystudent.sage);
end;
调用
call increase_age('张三')
例二:输入学生姓名 ,返回这一行记录给别的存储过程。
--out 用于输出值,会忽略传入的值。在子程序内部可以对其进行修改。
create or replace procedure returninfo (
p_name in tstudent.sname%type,p_info out tstudent%rowtype) is
begin
select * into p_info from tstudent where sname = p_name;
end;
create or replace procedure testinout as
pname tstudent.sname%type;
pinfo tstudent%rowtype;
begin
pname:='李四';
returninfo(pname,pinfo);
dbms_output.put_line('年龄:'||pinfo.sage||'性别为:'||pinfo.ssex);
end;
call testinout()
例三 存储过程返回值有多条数据
update tstudent set sname = '李四' where sno = 's004'
程序要做如下更改
create or replace procedure returninfo (
p_name in tstudent.sname%type,p_info out sys_refcursor) is
begin
open p_info for select * from tstudent where sname = p_name;
end;
create or replace procedure testinout as
pname tstudent.sname%type;
pinfo sys_refcursor;
prow tstudent%rowtype;
begin
pname:='李四';
returninfo(pname,pinfo);
loop
fetch pinfo into prow;
exit when pinfo%notfound;
dbms_output.put_line('年龄'||prow.sage||'姓名'||prow.sname);
end loop;
end;
call testinout()