oracle 存储过程例子

oracle存储例子 create or replace procedure proc_test( p_id in number, p_info out varchar) is v_row users%rowtype; cursor v_cur is select * from users; begin if p_id > 0 then select * into v_row from users where id = p_id; p_info := v_row.username||'='||v_row.name; else open v_cur; loop fetch v_cur into v_row; exit when v_cur%notfound; if p_info is null then p_info := v_row.username||'='||v_row.name; else p_info := p_info||','||v_row.username||'='||v_row.name; end if; end loop; close v_cur; end if; exception when too_many_rows then DBMS_OUTPUT.PUT_LINE('返回值多于1行'); when others then DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); end; 在PL/SQL中调用存储过程如下 -- Test statements here declare v_id number := 0; v_info varchar2(4000); begin proc_test(v_id, v_info); DBMS_OUTPUT.PUT_LINE(v_info); end;