存储过程 的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;