/*********实例一*********/
create or replace procedure users_procedure is
cursor users_cursor is select * from users;--声明动态游标
v_id users.id%type;--定义变量，与表中变量类型同步
v_username users.username%type;
v_password users.password%type;
begin
open users_cursor;--打开游标
fetch users_cursor into v_id, v_username, v_password;
while users_cursor%found
loop
dbms_output.put_line('v_id = ' || v_id || 'v_username = ' || v_username || 'v_password = ' || v_password);
fetch users_cursor into v_id, v_username, v_password;
end loop;
close users_cursor;
end;
/
/*********实例二*********/
create or replace procedure users_batch_insert_procedure is
v_id users.id%type;
v_username users.username%type;
v_password users.password%type;
begin
for i in 0..1000
loop
v_id := i;
v_username := 'abc' || i;
v_password := 'efg' || i;
insert into users values(v_id, v_username, v_password);
commit;
end loop;
end;
/
/**********实例三 弱类型游标**************/
create or replace procedure users_a is
type users_cursor_type is ref cursor; --return users%rowtype;
type users_record_type is record (v_id users.id%type, v_username users.username%type, v_password users.password%type);
v_sql varchar2(2000);
users_cursor_a users_cursor_type;
users_record users_record_type;
begin
v_sql := 'select * from users';
open users_cursor_a for v_sql;
fetch users_cursor_a into users_record;
while users_cursor_a%found
loop
dbms_output.put_line('v_id = ' || users_record.v_id || 'v_username = ' || users_record.v_username || 'v_password = ' || users_record.v_password);
fetch users_cursor_a into users_record;
end loop;
close users_cursor_a;
end;
/
/**********实例四 强类型游标**************/
create or replace procedure users_a2 is
type users_cursor_type is ref cursor return users%rowtype;
v_row users%rowtype;
v_sql varchar2(2000);
users_cursor_a users_cursor_type;
begin
open users_cursor_a for select * from users;
fetch users_cursor_a into v_row;
while users_cursor_a%found
loop
dbms_output.put_line('v_id = ' || v_row.id || 'v_username = ' || v_row.username || 'v_password = ' || v_row.password);
fetch users_cursor_a into v_row;
end loop;
close users_cursor_a;
end;
/
set serveroutput on size 1000000;
/**********实例五 for语句 procedure**************/
create or replace procedure update_procedure is
v_province_name varchar2(100);
v_city_name varchar2(100);
v_county_name varchar2(100);
v_town_name varchar2(100);
begin
for i in (select t.id from area t where t.parent_id = 0)
loop
select t.shortname into v_province_name from area t where t.id = i.id;
v_province_name := v_province_name;
update area t set t.fullname = v_province_name where t.id = i.id;
for j in (select t.id from area t where t.parent_id = i.id)
loop
select t.shortname into v_city_name from area t where t.id = j.id;
v_city_name := v_province_name || v_city_name;
update area t set t.fullname = v_city_name where t.id = j.id;
for k in (select t.id from area t where t.parent_id = j.id)
loop
select t.shortname into v_county_name from area t where t.id = k.id;
v_county_name := v_city_name || v_county_name;
update area t set t.fullname = v_county_name where t.id = k.id;
for l in (select t.id from area t where t.parent_id = k.id)
loop
select t.shortname into v_town_name from area t where t.id = l.id;
v_town_name := v_county_name || v_town_name;
update area t set t.fullname = v_town_name where t.id = l.id;
end loop;
end loop;
end loop;
end loop;
end;
/