带参数存储过程

例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()