pl/sql编程 (八)
- 不带参数的存储过程
定义
create procedure pro_goods as begin update goods set remark = 'sss' where goodsid = 1; end;
执行
begin pro_goods; end;
使用游标的存储过程
select * from goods where goodsid = 1 create or replace procedure pro_goods_cursor as v_goodsinfo goods%rowtype; cursor cursor_goods is select * from goods where goodsid <10; begin open cursor_goods; loop fetch cursor_goods into v_goodsinfo; exit when cursor_goods%notfound; if(v_goodsinfo.goodsid > 5)then update goods set remark = 'ccc' where goodsid = v_goodsinfo.goodsid; end if; end loop; close cursor_goods; end;
使用中间变量
create or replace procedure pro_createtable as tableexist varchar2(2); my_createtable varchar2(400); begin select count(1) into tableexist from all_tables where table_name = 'test_table'; my_createtable :='create global temporary table test_table (test varchar2(20) not null) on commit preserve rows'; dbms_output.put_line(tableexist); if tableexist = 0 then execute immediate my_createtable ; dbms_output.put_line('临时表创建成功……'); else execute immediate 'delete from test_table'; dbms_output.put_line('清空临时表……'); end if; end;