存储过程 的is与as,oracle存储过程大数据量insert

一直纠结于存储过程的is与as最近在网上查了巨多资料，总结如下：
在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
从其定义也可以看出没什么区别，要是有估计暂时也很少有人发下：
create [or replace] procedure procedure_name
[(parameter_name [in | out | in out] type [,........])]
  {is | as}
begin
procedure_body
end procedure_name;
======================================
oracle存储过程大数据量insert ,分批提交(转) [

Oracle中不是提交越频繁越好，批量提交可以得到更好的性能

逐行提交于批量提交两者之间的性能差别
一种可以极大改变性能的方法

1.
create table t_ref as select * from all_objects;

insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;
insert into t_ref select * from t;

2.模拟逐行提交的情况，注意观察执行时间
目标是将t_ref表中的数据全部插入到t中
set timing on

DECLARE
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
COMMIT;
END LOOP;
END;
/


3分

3.模拟批量提交
truncate table t;

DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM t_ref) LOOP
INSERT INTO t VALUES cur;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/


1分多，大约是逐行提交方法一半的时间

4.更高级的方法，体验一下极限速度。
DECLARE
CURSOR cur IS
SELECT * FROM t_ref;
TYPE rec IS TABLE OF t_ref%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
INSERT INTO t VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/


不到10秒的时间就完成了曾经需要几分钟才能完成的任务。

5.小结
频繁的COMMIT会引起大量Redo Log的物理I/O，会极大的限制数据库的性能
因此，为提高数据库性能，尽可能的批量提交。
Oracle有很多优秀的方法值得尝试

==================================
Oracle存储过程返回数据集，并将数据集插入表

wangmusen 发布于 2年前，共有 0 条评论

-- ==================实例：Oracle存储过程返回数据集，并将数据集插入表========================   
CREATE table userinfo(id number(18,0), name varchar2(30), sex varchar2(10), age number(3,0), address varchar2(100));  
insert into userinfo(id,name,sex,age,address) values(1,'luoyoumou','男',33,'湖南省衡阳市');  
insert into userinfo(id,name,sex,age,address) values(2,'miaoxiaoming','男',32,'江西省吉安市');  
insert into userinfo(id,name,sex,age,address) values(3,'hanqiguang','男',28,'江西省赣州市');  
commit;  
create table userinfo2 as select * from userinfo where 1=2;  
-- i_address 是存储过程的输入参数，o_cur是存储过程的输出游标参数，用以获取返回的结果集!  
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)  
IS 
  sqlstr VARCHAR2(200); -- 定义变量，用以存放SQL语句  
BEGIN 
  -- 给SQL变量赋值，其中 :i_address 是绑定变量，以提高执行效率!  
  sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; 
  OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值  
END; 
   
create or replace procedure userinfo_proc_get  
IS 
  --自定义数据类型 
  type userinfo_rec_type is record (id number(18,0), name varchar2(30), sex varchar2(10), age number(3,0), address varchar2(100));  
  --创建userinfo_rec_type类型的数组 
  type userinfo_tab_type is table of userinfo_rec_type; 
  --定义数组变量  
  userinfo_tab userinfo_tab_type;  
  --%rowtype 是强数据类型, 定义后数据类型与源表保持一致，其实它就是一个RECORD 
  type uif_tab_type is table of userinfo%rowtype;  
  --定义数组变量 
  uif_tab uif_tab_type:=uif_tab_type();  
  v_cnt number(10,0); 
  --定义返回游标  
  rs sys_refcursor;  
begin 
  userinfo_proc('江西省吉安市',rs); 
  --将游标数据一次性加入到数组中  
  fetch rs bulk collect into userinfo_tab;  
  -- 处理要插入表的数据=====>此步骤不需要  
  for i in 1..userinfo_tab.count loop  
    uif_tab.extend;  
    uif_tab(i).id := userinfo_tab(i).id;  
    uif_tab(i).name := userinfo_tab(i).name;  
    uif_tab(i).sex := userinfo_tab(i).sex;  
    uif_tab(i).age := userinfo_tab(i).age;  
    uif_tab(i).address := userinfo_tab(i).address;  
  end loop;  
  --将数据批量插入表  
  forall i in 1..uif_tab.count 
  insert into userinfo2 values uif_tab(i);  
  close rs;  
  commit;  
end; 
exec userinfo_proc_get;  
   
-- 验证数据是否插入成功：  
select * from userinfo2;