Oracle 游标及存储过程实例
/*********实例一*********/ 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;
/