「深度学习福利」大神带你进阶工程师，立即查看>>>
1.基本结构： CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字
2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ...


3.IF 判断
IF V_TEST=1 THEN BEGIN do something END; END IF;


4.while 循环
WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP;


5.变量赋值
V_TEST := 123;

6.用for in 使用cursor
... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;


7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER;

简单示例： CREATE OR REPLACE NONEDITIONABLE PROCEDURE PRC_MDS1_TO_MDS2 (parameter IN VARCHAR2, exitcode OUT INT) AS -- 定义一个变量用来保存此存储过程的名字 v_logic_proc_name VARCHAR2(30) := 'PRC_MDS1_TO_MDS2'; -- 声明显式游标, 查询udt_mds1_input中item不为null的数据 CURSOR c_udt_mds1_input_item_notnull IS SELECT * FROM scpomgr.udt_mds1_input WHERE item IS NOT NULL AND TRIM(item) IS NOT NULL; -- 定义游标变量，该变量的类型为基于游标c_udt_mds1_input_item_notnull的记录 c_mds1_item_notnull_row c_udt_mds1_input_item_notnull%ROWTYPE; -- 声明一个游标，查询udt_mds1_input中item为null的数据 -- MARKET、itemgroup、package、config不为NULL CURSOR c_udt_mds1_input_item_null IS SELECT * FROM scpomgr.udt_mds1_input WHERE (item IS NULL OR TRIM(item) IS NULL) AND market IS NOT NULL AND TRIM(market) IS NOT NULL AND itemgroup IS NOT NULL AND TRIM(itemgroup) IS NOT NULL AND package IS NOT NULL AND TRIM(package) IS NOT NULL AND config IS NOT NULL AND TRIM(config) IS NOT NULL; -- 定义游标变量 c_mds1_item_null_row c_udt_mds1_input_item_null%ROWTYPE; -- 再声明一个游标，查询udt_mds1_input中item为null -- MARKET、ITEMGROUP、PACKAGE、CONFIG有为NULL的 CURSOR c_udt_mds1_item_null_other IS SELECT * FROM scpomgr.udt_mds1_input WHERE (item IS NULL OR TRIM(item) IS NULL) AND (market IS NULL OR TRIM(market) IS NULL OR itemgroup IS NULL OR TRIM(itemgroup) IS NULL OR package IS NULL OR TRIM(package) IS NULL OR config IS NULL OR TRIM(config) IS NULL); -- 定义游标变量 c_mds1_item_null_other_row c_udt_mds1_item_null_other%ROWTYPE; BEGIN -- 设置要返回的变量值 exitcode := -20999; -- 如果传入的参数是BYPASS, 则表示不运行 IF parameter = 'BYPASS' THEN exitcode := 0; -- 返回0表示执行成功 RETURN; END IF; -- 记录具体逻辑开始执行 logger.info (v_logic_proc_name || ':' || parameter || ' ,Start'); ------------------- 具体的业务逻辑 ------------------- -- 1. 将UDT_MDS1_INPUT所有字段输入到MDS1_BCKUP对应同名字段中 pkg_log.info(v_logic_proc_name || ':' || '步骤1,将UDT_MDS1_INPUT的数据备份到MDS1_BCKUP表中'); INSERT INTO scpomgr.MDS1_BCKUP SELECT * FROM scpomgr.UDT_MDS1_INPUT; -- 2. 是否要先清空中间表和UDT_MDS2_REVIEW表中的数据 pkg_log.info(v_logic_proc_name || ':' || '步骤2,删除mid_mds1_mds2和udt_mds2_review表中的数据'); DELETE FROM scpomgr.mid_mds1_mds2; -- 删除中间表数据 DELETE FROM scpomgr.udt_mds2_review; -- 删除UDT_MDS2_REVIEW表数据 -- 3. 循环处理ITEM不为NULL的数据 pkg_log.info(v_logic_proc_name || ':' || '步骤3,循环处理udt_mds1_input中item不为null的数据'); FOR c_mds1_item_notnull_row IN c_udt_mds1_input_item_notnull LOOP -- 使用嵌套内部块 declare -- 定义变量 v_country scpomgr.udt_country_ratio.country%TYPE; -- 定义一个国家变量 v_color scpomgr.udt_basic_info.color%TYPE; -- 定义一个颜色变量 begin -- 3.1 查询udt_country_ratio.country信息，并赋值给v_country BEGIN SELECT country INTO v_country FROM ( SELECT ucr.country FROM scpomgr.udt_country_ratio ucr WHERE ucr.market=c_mds1_item_notnull_row.market AND ucr.itemgroup=c_mds1_item_notnull_row.itemgroup ) WHERE ROWNUM=1; EXCEPTION WHEN no_data_found THEN pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_notnull_row.market || '和' || c_mds1_item_notnull_row.itemgroup || '没有在scpomgr.udt_country_ratio查询到对应的国家信息'); prc_udt_error_log(c_mds1_item_notnull_row.itemgroup, c_mds1_item_notnull_row.market, '', '没有在scpomgr.udt_country_ratio查询到对应的国家信息', 1, 'PRC_MDS1_TO_MDS2'); END; -- 3.2 查询udt_basic_info.color信息，并赋值给v_color BEGIN SELECT color INTO v_color FROM ( SELECT ubi.color FROM scpomgr.udt_basic_info ubi WHERE ubi.item = c_mds1_item_notnull_row.item ) WHERE ROWNUM=1; EXCEPTION WHEN no_data_found THEN pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_notnull_row.item || '没有在scpomgr.udt_basic_info查询到对应的颜色信息'); prc_udt_error_log(c_mds1_item_notnull_row.item, '', '', '没有在scpomgr.udt_basic_info查询到对应的颜色信息', 1, 'PRC_MDS1_TO_MDS2'); END; -- 3.3 将数据保存到中间表中 INSERT INTO scpomgr.mid_mds1_mds2(market,brand,itemgroup,item,descr,status,loc,package,config,dmdtype,version_no,country,color, w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27) VALUES (c_mds1_item_notnull_row.market, c_mds1_item_notnull_row.brand, c_mds1_item_notnull_row.itemgroup, c_mds1_item_notnull_row.item, c_mds1_item_notnull_row.descr, c_mds1_item_notnull_row.status, c_mds1_item_notnull_row.loc, c_mds1_item_notnull_row.package, c_mds1_item_notnull_row.config, c_mds1_item_notnull_row.dmdtype, c_mds1_item_notnull_row.version_no, v_country, v_color, c_mds1_item_notnull_row.w0, c_mds1_item_notnull_row.w1, c_mds1_item_notnull_row.w2, c_mds1_item_notnull_row.w3, c_mds1_item_notnull_row.w4, c_mds1_item_notnull_row.w5, c_mds1_item_notnull_row.w6, c_mds1_item_notnull_row.w7, c_mds1_item_notnull_row.w8, c_mds1_item_notnull_row.w9, c_mds1_item_notnull_row.w10, c_mds1_item_notnull_row.w11, c_mds1_item_notnull_row.w12, c_mds1_item_notnull_row.w13, c_mds1_item_notnull_row.w14, c_mds1_item_notnull_row.w15, c_mds1_item_notnull_row.w16, c_mds1_item_notnull_row.w17, c_mds1_item_notnull_row.w18, c_mds1_item_notnull_row.w19, c_mds1_item_notnull_row.w20, c_mds1_item_notnull_row.w21, c_mds1_item_notnull_row.w22, c_mds1_item_notnull_row.w23, c_mds1_item_notnull_row.w24, c_mds1_item_notnull_row.w25, c_mds1_item_notnull_row.w26, c_mds1_item_notnull_row.w27); end; END LOOP; -- 4. 循环处理ITEM为NULL的数据 pkg_log.info(v_logic_proc_name || ':' || '步骤4,循环处理udt_mds1_input中item为null,但MARKET/itemgroup/package/config不为NULL的数据'); FOR c_mds1_item_null_row IN c_udt_mds1_input_item_null LOOP -- 使用嵌套内部块 declare -- 定义变量 v_item scpomgr.udt_basic_info.item%TYPE; -- 定义item v_descr scpomgr.udt_basic_info.descr%TYPE; -- 定义descr -- 定义国家和国家占比 v_country scpomgr.udt_country_ratio.country%TYPE; -- 国家 v_country_ratio scpomgr.udt_country_ratio.country_ratio%TYPE; -- 国家占比 -- 使用数组定义15种颜色及占比 type v_color_varray is varray(15) of scpomgr.udt_country_ratio.color1%TYPE; -- 颜色 v_color v_color_varray := v_color_varray('', '', '', '', '', '', '', '', '', '', '', '', '', '', ''); type v_color_ratio_varray is varray(15) of scpomgr.udt_country_ratio.color1_ratio%TYPE; -- 颜色占比 v_color_ratio v_color_ratio_varray := v_color_ratio_varray(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); -- 使用数组定义28周的数据量变量 type v_w_varray is varray(28) of scpomgr.udt_mds1_input.w0%TYPE; v_w v_w_varray := v_w_varray(c_mds1_item_null_row.w0,c_mds1_item_null_row.w1,c_mds1_item_null_row.w2,c_mds1_item_null_row.w3,c_mds1_item_null_row.w4,c_mds1_item_null_row.w5, c_mds1_item_null_row.w6,c_mds1_item_null_row.w7,c_mds1_item_null_row.w8,c_mds1_item_null_row.w9,c_mds1_item_null_row.w10,c_mds1_item_null_row.w11, c_mds1_item_null_row.w12,c_mds1_item_null_row.w13,c_mds1_item_null_row.w14,c_mds1_item_null_row.w15,c_mds1_item_null_row.w16,c_mds1_item_null_row.w17, c_mds1_item_null_row.w18,c_mds1_item_null_row.w19,c_mds1_item_null_row.w20,c_mds1_item_null_row.w21,c_mds1_item_null_row.w22,c_mds1_item_null_row.w23, c_mds1_item_null_row.w24,c_mds1_item_null_row.w25,c_mds1_item_null_row.w26,c_mds1_item_null_row.w27); begin -- 4.1 查询udt_country_ratio中抓取对应的country、 country_ratio、color1..15、color1..15_ratio，并赋值给相应的变量 BEGIN SELECT country,country_ratio,color1,color1_ratio,color2,color2_ratio,color3,color3_ratio,color4,color4_ratio,color5,color5_ratio,color6,color6_ratio, color7,color7_ratio,color8,color8_ratio,color9,color9_ratio,color10,color10_ratio,color11,color11_ratio,color12,color12_ratio,color13,color13_ratio, color14,color14_ratio,color15,color15_ratio INTO v_country,v_country_ratio,v_color(1),v_color_ratio(1),v_color(2),v_color_ratio(2),v_color(3),v_color_ratio(3),v_color(4),v_color_ratio(4), v_color(5),v_color_ratio(5),v_color(6),v_color_ratio(6),v_color(7),v_color_ratio(7),v_color(8),v_color_ratio(8),v_color(9),v_color_ratio(9), v_color(10),v_color_ratio(10),v_color(11),v_color_ratio(11),v_color(12),v_color_ratio(12),v_color(13),v_color_ratio(13),v_color(14),v_color_ratio(14), v_color(15),v_color_ratio(15) FROM ( SELECT ucr.country,ucr.country_ratio,ucr.color1,ucr.color1_ratio,ucr.color2,ucr.color2_ratio,ucr.color3,ucr.color3_ratio,ucr.color4,ucr.color4_ratio, ucr.color5,ucr.color5_ratio,ucr.color6,ucr.color6_ratio,ucr.color7,ucr.color7_ratio,ucr.color8,ucr.color8_ratio,ucr.color9,ucr.color9_ratio, ucr.color10,ucr.color10_ratio,ucr.color11,ucr.color11_ratio,ucr.color12,ucr.color12_ratio,ucr.color13,ucr.color13_ratio,ucr.color14,ucr.color14_ratio, ucr.color15,ucr.color15_ratio FROM scpomgr.udt_country_ratio ucr WHERE ucr.market = c_mds1_item_null_row.market AND ucr.itemgroup = c_mds1_item_null_row.itemgroup ) WHERE ROWNUM=1; EXCEPTION WHEN no_data_found THEN pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_null_row.market || '和' || c_mds1_item_null_row.itemgroup || '没有在scpomgr.udt_country_ratio查询到对应国家和颜色及占比信息'); prc_udt_error_log(c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.market, '', '没有在scpomgr.udt_country_ratio查询到对应国家和颜色及占比信息', 1, 'PRC_MDS1_TO_MDS2'); END; -- 4.2对于country_ratio、color_ratio不为NULL的记录 更新W0-W27的数据 FOR i in 1 .. 15 LOOP -- 先判断有没有颜色，如果没有则不处理 IF(v_color(i) IS NOT NULL AND TRIM(v_color(i)) IS NOT NULL) THEN -- 4.2.1 获取item，descr v_item := ''; v_descr := ''; -- 先设置为默认值 BEGIN SELECT item,descr INTO v_item,v_descr FROM ( SELECT ubi.item,ubi.descr FROM scpomgr.udt_basic_info ubi WHERE ubi.country = v_country AND ubi.itemgroup = c_mds1_item_null_row.itemgroup AND ubi.package = c_mds1_item_null_row.package AND ubi.color = v_color(i) AND ubi.config = c_mds1_item_null_row.config ) WHERE ROWNUM=1; EXCEPTION WHEN no_data_found THEN pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_null_row.itemgroup || ',' || c_mds1_item_null_row.package || ',' || v_color(i) || ',' || c_mds1_item_null_row.config || '没有在scpomgr.udt_basic_info查询到对应的item和descr信息'); prc_udt_error_log(c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.package|| '+' || c_mds1_item_null_row.config, '', '没有在scpomgr.udt_basic_info查询到对应的item和descr信息', 1, 'PRC_MDS1_TO_MDS2'); END; -- 4.2.2 如果不为空，则要计算v_w的数据 IF(v_country_ratio IS NOT NULL AND v_color_ratio(i) IS NOT NULL) THEN --循环处理v_w0...v_w27的数据 FOR j in 1 .. 28 LOOP v_w(j) := v_w(j) * v_country_ratio * v_color_ratio(i); END LOOP; -- 将处理完的数据插入到中间表中 INSERT INTO scpomgr.mid_mds1_mds2(market,itemgroup,package,config,country,country_ratio,color,color_ratio,item, descr, w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27) VALUES (c_mds1_item_null_row.market, c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.package, c_mds1_item_null_row.config, v_country, v_country_ratio, v_color(i),v_color_ratio(i), v_item, v_descr, v_w(1), v_w(2), v_w(3), v_w(4), v_w(5), v_w(6), v_w(7), v_w(8), v_w(9), v_w(10), v_w(11), v_w(12), v_w(13), v_w(14), v_w(15), v_w(16),v_w(17), v_w(18), v_w(19), v_w(20), v_w(21), v_w(22), v_w(23), v_w(24), v_w(25), v_w(26), v_w(27), v_w(28)); END IF; END IF; END LOOP; end; END LOOP; -- 5. 对于若UDT_MDS1_INPUT.ITEM为NULL，MARKET、ITEMGROUP、PACKAGE、CONFIG有为NULL的 pkg_log.info(v_logic_proc_name || ':' || '步骤5,循环处理udt_mds1_input中item为null,并且MARKET/itemgroup/package/config有为NULL字段的数据'); FOR c_mds1_item_null_other_row IN c_udt_mds1_item_null_other LOOP -- 使用嵌套内部块 declare -- 定义一个变量保存数据 v_null_filed varchar2(500); begin if(c_mds1_item_null_other_row.market is null or trim(c_mds1_item_null_other_row.market) is null) then v_null_filed := v_null_filed || 'market,'; elsif(c_mds1_item_null_other_row.itemgroup is null or trim(c_mds1_item_null_other_row.itemgroup) is null) then v_null_filed := v_null_filed || 'itemgroup,'; elsif(c_mds1_item_null_other_row.package is null or trim(c_mds1_item_null_other_row.package) is null) then v_null_filed := v_null_filed || 'package,'; elsif(c_mds1_item_null_other_row.config is null or trim(c_mds1_item_null_other_row.config) is null) then v_null_filed := v_null_filed || 'config'; end if; -- 找出哪个字段为空，并记录下来 pkg_log.error(v_logic_proc_name || ':' || 'scpomgr.UDT_MDS1_INPUT表中下面字段为空: ' || v_null_filed); prc_udt_error_log('', '', '', 'scpomgr.UDT_MDS1_INPUT表中下面字段为空: ' || v_null_filed, 1, 'PRC_MDS1_TO_MDS2'); END; END LOOP; -- 6. 将中间表的数据保存到UDT_MDS2_REVIEW表中 pkg_log.info(v_logic_proc_name || ':' || '步骤6,将中间表mid_mds1_mds2的数据保存到udt_mds2_review表中'); INSERT INTO scpomgr.udt_mds2_review(market,brand,itemgroup,item,descr,status,loc,package,color,config,dmdtype,version_no, w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27) SELECT market,brand,itemgroup,item,descr,status,loc,package,color,config,dmdtype,version_no, w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27 FROM scpomgr.mid_mds1_mds2; -- 说明没有错误, 执行成功 exitcode := 0; -- 记录完成 logger.info (v_logic_proc_name || ':' || parameter || ' ,Success'); -- 成功的话, 提交 COMMIT; EXCEPTION WHEN OTHERS THEN exitcode := SQLCODE; -- 返回错误代码 pkg_log.error('Failure:' || v_logic_proc_name || ':' || SQLCODE || ':' || SQLERRM || ':' || substr(dbms_utility.format_error_backtrace, 1, 1024)); COMMIT; END PRC_MDS1_TO_MDS2;