存储过程使用详解

前言

最近一个项目上需要用存储过程从视图中向其他表里面插入数据，由于数据量都比较大，java程序执行的话，时间很长，导致程序会卡死，最后决定使用oracle的存储过程来处理数据；好久没写过存储过程了，在这里记录一下编写存储过程中遇到的问题。

什么是存储过程？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，
减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程的优点有哪些？

• 允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
• 允许更快执行，如果某操作需要执行大量SQL语句或重复执行，存储过程比SQL语句执行的要快。
• 减少网络流量，例如一个需要数百行的SQL代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
• 更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。

语法

create or replace prcedure 存储过程名称（参数1 in 数据类型，参数2 out 数据类型） as
-- 定义变量
	变量名称1 类型（数值范围）;
	变量名称2 类型（数值范围）;
-- 定义游标
cursor c_data_list is select ... form 表名 where 列名1=条件1 and 列名2=条件2;
--定义游标变量
c_data c_data%rowtype;

begin
	-- 打开游标	
	open c_data_list;
	fetch c_data into c_data;
	while c_data%notfound loop
		-- 循环插入数据
		insert into 表名(列名1,列名2,列名3,列名4,列名5,列名6) values(c_data.列1,c_data.列2,c_data.列3,c_data.列4,c_data.列5,c_data.列6);
	end loop;
	--关闭游标
	close c_data_list;
	commit;
end;

代码示例

CREATE OR REPLACE PROCEDURE AMS.QFA_900M_ASSET_AMS_TO_ERP2 IS
v_id NUMBER;-- 日志表id
BEGIN
	--先清除本表的数据
	DELETE FROM QFA_900M_ASSET_AMS_ERP2 WHERE 1=1;
	--插入日志
	SELECT QFA_900M_ASSET_AMS_ERP_LOG_seq.NEXTVAL INTO v_id FROM DUAL;
	INSERT INTO QFA_900M_ASSET_AMS_ERP_LOGS(id,RUNDATE,FLAG,DATASOURCE,TARGET)
	values(v_id,sysdate,'N','APPS.QFA_900M_ASSET_AMS_V@AMS_TO_ERP2','QFA_900M_ASSET_AMS_ERP2');
	COMMIT;
	
	INSERT INTO QFA_900M_ASSET_AMS_ERP2(PERIOD_NAME,ASSET_NUMBER,TAG_NUMBER,
			MANUFACTURER_NAME,MODEL_NUMBER,SERIAL_NUMBER,CURRENT_UNITS,
			ASSET_DESC,CATE_DESC,ATTRIBUTE_CATEGORY_CODE,ACCUMU_IMPAIRMENT,
			DEPRN_RESERVE,YTD_DEPRN,NET_COST,DEPRN_AMOUNT,BOOK_TYPE_CODE,
			ASSET_ID,COST,RETIREMENT_PENDING_FLAG,PROJECT_NAME,PROJECT_ID,
			PROJECT_NUMBER,PRORATE_DATE,RATE_ADJUSTMENT_FACTOR,START_DATE,
			CAT_SEGMENT3,UNIT_OF_MEASURE,ADJUSTED_COST,SALVAGE_VALUE,
			DEPRECIATE_FLAG,LIFE_IN_MONTHS,REMAINING_LIFE_MONTHS,
			DATE_PLACED_IN_SERVICE,SPLIT_FLAG,CREATION_DATE,ATTRIBUTE1,
			ATTRIBUTE5) 
	SELECT
		*
	FROM
		(
		SELECT period_name,asset_number,tag_number,manufacturer_name,
			model_number,serial_number,current_units,asset_desc,cate_desc,
			attribute_category_code,accumu_impairment,deprn_reserve,
			ytd_deprn,net_cost,deprn_amount,book_type_code,asset_id,
			cost,retirement_pending_flag,project_name,project_id,project_number,
			prorate_date,rate_adjustment_factor,start_date,cat_segment3,
			unit_of_measure,adjusted_cost,salvage_value,depreciate_flag,
			life_in_months,remaining_life_months,date_placed_in_service,
			split_flag,creation_date,attribute1,attribute5
		FROM
			APPS.QFA_900M_ASSET_AMS_V@AMS_TO_ERP2);
	COMMIT;
	
	--删除重复数据
	DELETE
	FROM
		QFA_900M_ASSET_AMS_ERP2
	WHERE ASSET_ID IN (
		SELECT ASSET_ID FROM QFA_900M_ASSET_AMS_ERP2 GROUP BY ASSET_ID HAVING count(ASSET_ID) >1
	) AND rowid NOT IN ( SELECT min(rowid) FROM QFA_900M_ASSET_AMS_ERP2 GROUP BY ASSET_ID HAVING count(ASSET_ID) >1
	);
	COMMIT;
	
	-- 更新日志表
	UPDATE QFA_900M_ASSET_AMS_ERP_LOGS SET FLAG='Y' WHERE id = v_id;
	COMMIT;
	
	-- 捕获异常
	EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
	RETURN;
END;