代码:
使用loop fetch into 遍历,需要手动打开和关闭cursor
create or replace procedure list_user_info(uname in varchar2) is
cursor u_list is select t.user_name,t.gender,t.telephone from sys_usermanage t where t.user_name like '%'||uname||'%';
cmd1 varchar2(100);
cmd2 varchar2(100);
u u_list%rowtype;
begin
--cmd1:='set serveroutput on;';
--execute immediate 'desc sys_user';
open u_list;
loop
fetch u_list into u;
exit when u_list%NOTFOUND;
dbms_output.put_line('user_name: ' || u.user_name || ', sex: ' || u.gender || ', telephone: ' || u.telephone);
end loop;
close u_list;
--execute immediate 'set serveroutput off;';
end;
测试发现存储过程不能执行set serveroutput on;命令,譬如 execute immediate 'set serveroutput off;';
报错提示:SQL> exec list_user_info('宋');
begin list_user_info('宋'); end;
ORA-00900: 无效 SQL 语句
发现exec 过程名(参数) 其实就是一个简单的begin ... end过程。
这种方式也可以,
create or replace procedure list_user_info(uname in varchar2) is
cursor u_list is select t.user_name,t.gender,t.telephone from sys_usermanage t where t.user_name like '%'||uname||'%';
cmd1 varchar2(100);
cmd2 varchar2(100);
u u_list%rowtype;
name varchar2(30);
sex varchar2(2);
telephone varchar2(20);
begin
open u_list;
loop
fetch u_list into name,sex,telephone;
exit when u_list%NOTFOUND;
dbms_output.put_line('user_name: ' || name || ', sex: ' || sex || ', telephone: ' || telephone);
end loop;
close u_list;
end;
第二种方法:使用for in 遍历cursor,默认系统会打开cursor,不需要我们打开,关闭
create or replace procedure list_user_info(uname in varchar2) is
cursor u_list is select t.user_name,t.gender,t.telephone from sys_usermanage t where t.user_name like '%'||uname||'%';
cmd1 varchar2(100);
cmd2 varchar2(100);
u u_list%rowtype;
begin
for u in u_list loop
dbms_output.put_line('user_name: ' || u.user_name || ', sex: ' || u.gender || ', telephone: ' || u.telephone);
end loop;
end;
个人已验证,变量名声明时小写,引用时大写,可以取到值,notfound小写大写都可以编译执行,预测过程代码不区分大小写。
看论坛讲dbms_output 函数还会引起内存泄露,后期工作中少用,我们只是个人学习测试就无所谓。