创建存储过程 create or replace procedure my_pro( p_emp in emp.empno%type; p_ename out emp.ename%type; p_sal out emp.sal%type) isbegin select empno,ename,sal into p_empno,p_ename,p_sal from emp where empno=p_empno; end;调用存储过程 declare --v_empno emp.empno%type; v_emp in out emp.empno%type; v_ename out emp.ename%type;begin my_pro(7521,p_name=>v_name,p_sal=>v_sal); dbms_output.put_line(v_name||v_sal);exception when others then dbms_output.put_line(sqlcode||sqlerrm); end;这就打印了一条记录,不过,思想是这样的。 前几条的话,拍一下序,用一个rownum<=5就行了!
SQL> set serveroutput onSQL> create or replace procedure p_fetch_5_row 2 ( 3 v_owner varchar2 4 ,ret_cur out sys_refcursor 5 ) 6 is 7 begin 8 open ret_cur for 9 select object_name 10 from dba_objects 11 where owner = v_owner 12 and rownum <= 5; 13 end p_fetch_5_row; 14 /过程已创建。SQL> create or replace procedure p_exec 2 is 3 v_cur sys_refcursor; 4 v_object_name varchar2(2000); 5 begin 6 p_fetch_5_row('SYS', v_cur); 7 loop 8 fetch v_cur into v_object_name; 9 exit when v_cur%notfound; 10 dbms_output.put_line(v_object_name); 11 end loop; 12 13 close v_cur; 14 end p_exec; 15 /过程已创建。SQL> exec p_exec /1005bd30_LnkdConstant /10076b23_OraCustomDatumClosur /10297c91_SAXAttrList /103a2e73_DefaultEditorKitEndP /1048734f_DefaultFolder PL/SQL 过程已成功完成。
create or replace procedure my_pro(
p_emp in emp.empno%type;
p_ename out emp.ename%type;
p_sal out emp.sal%type)
isbegin
select empno,ename,sal into p_empno,p_ename,p_sal from emp where empno=p_empno; end;调用存储过程
declare
--v_empno emp.empno%type;
v_emp in out emp.empno%type;
v_ename out emp.ename%type;begin
my_pro(7521,p_name=>v_name,p_sal=>v_sal);
dbms_output.put_line(v_name||v_sal);exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;这就打印了一条记录,不过,思想是这样的。
前几条的话,拍一下序,用一个rownum<=5就行了!
2 (
3 v_owner varchar2
4 ,ret_cur out sys_refcursor
5 )
6 is
7 begin
8 open ret_cur for
9 select object_name
10 from dba_objects
11 where owner = v_owner
12 and rownum <= 5;
13 end p_fetch_5_row;
14 /过程已创建。SQL> create or replace procedure p_exec
2 is
3 v_cur sys_refcursor;
4 v_object_name varchar2(2000);
5 begin
6 p_fetch_5_row('SYS', v_cur);
7 loop
8 fetch v_cur into v_object_name;
9 exit when v_cur%notfound;
10 dbms_output.put_line(v_object_name);
11 end loop;
12
13 close v_cur;
14 end p_exec;
15 /过程已创建。SQL> exec p_exec
/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder PL/SQL 过程已成功完成。