oracle数据库如何利用存储过程向表中添加数据？

实际上存储过程向表中插入数据和sql执行的区别是不大的，只不过是存储过程是用loop等循环插入，之后顺序执行sql语句，不用命令行执行。

CREATE OR REPLACE PROCEDURE insert_data_4_pressure_3

 is

  --Result1  VARCHAR2(50)

  VAR_num number

begin

  VAR_num:=1

 while

   VAR_num< 1000000

   LOOP

      insert into rp_trans_log_day

        (trans_time,

         trans_province,

         trans_type,

         score_range,

         rule_name,

         trans_num)

        select to_date('2013/10/29', 'yyyy-mm-dd'),

               round(dbms_random.value(1, 300)) || '省',

               round(dbms_random.value(1, 800)) || '类型',

               round(dbms_random.value(1, 100)) || '风险分值',

               round(dbms_random.value(1, 300)) || '规则名称',

               '1'

          from dual

        commit

    VAR_num:=VAR_num+1

    end loop

end insert_data_4_pressure_3

create proc insertrecord

@count int,

@name varchar(20),

as

select @count=count(*) from dbo_A

if (@count>0)

begin

select* from dbo_A where column like '%'+@name+'%'

end

else

begin

insert into dbo_B (column name) select column name from dbo_A where dbo_A.column like '%'+@name+'%'

end

从存储过程返回表类型的值也有二种:1.存储过程使用浮标参数,即同时指定CURSOR VARYING OUTPUT项.调用者可以使用while及fetch循环遍历该浮标.2.直接将存储过程返回的结果集插入到表中,即使用insert into 表名 exec 存储过程.此种方式中注意存储过程返回的结果集列与insert的列要完全对应,可以在insert中指定列名来保证对应关系.------------------------------------------------------------------------------测试:----------------------------------------------------------------------------------建立测试用的临时表create table #tmp (colx int,coly int)insert into #tmp values(1,2)insert into #tmp values(2,3)insert into #tmp values(3,4)select * from #tmpGO----创建返回游标的存储过程create proc sp_c @cur CURSOR VARYING OUTPUTASbeginset @cur = CURSOR for select colx from #tmpopen @cur /*该过程返回游标,该游标为colx列的查询结果*/endGO----创建返回表的存储过程create proc sp_dasselect coly from #tmp /*该过程返回coly列的查询结果*/go----创建用于调用以上二个存储过程的存储过程create proc sp_easbegindeclare @x intdeclare @cur cursor----接收游标,并遍历游标EXEC sp_c @cur OUTPUTfetch next from @cur into @xwhile (@@FETCH_STATUS = 0)beginprint @xfetch next from @cur into @xENDclose @curdeallocate @cur----将存储过程返回的列值再重新插入源表中insert into #tmp(coly) EXEC sp_dselect * from #tmpendGOEXEC sp_edrop proc sp_cdrop proc sp_d

---