oracel简单存储过程

游标两种循环方式

第一、不用打开和关闭游标

create or replace procedure pro_test is
cursor cursorVars is select login_name, user_name, user_code,reg_cell_phone from F_USERINFO where 1=1;
begin
for cursorVar in cursorVars loop
dbms_output.put_line('用户名为'|| cursorVar.login_name|| '的登录名为'||cursorVar.user_name|| '的手机号为'|| cursorVar.reg_cell_phone );
end loop;
end;

第二、使用loop循环

create or replace procedure pro_test is
v_login VARCHAR2(400);
v_name VARCHAR2(400);
v_code VARCHAR2(400);
v_phone VARCHAR2(400);
cursor cursorVars is select login_name, user_name, user_code,reg_cell_phone from F_USERINFO where 1=1;
begin
open cursorVars; -- 打开游标
loop
fetch cursorVar into v_login, v_name, v_code,v_phone; -- 取值
exit when cursorVar%notfound; --当没有记录时退出循环
dbms_output.put_line('用户名为'|| v_name|| '的登录名为'||v_login|| '的手机号为'|| v_phone );
end loop;

close cursorVar; -- 关闭游标
end;