ORACLE：在存储过程使用WITH...AS与CURSOR

本以为ORACLE创建存储过程就是把能执行的SQL语句加上输入/输出变量，放入BEGIN... END块中，结果却不尽人意。

总结了几点：

1. 变量声明必须有；分号结尾，否则会报错PLS-00103
2. 存储过程不能只有SELECT语句 ，否者会报错PLS-00428
3. SELECT "1" AS id ，语句中如果为字符串只能使用单引号，双引号报错。
   （这个现象在正常SELECT测试语句中未报错）
4. WITH…AS 必须配合 CURSOR 使用，否则会报错ORA-00928
   可以OPEN OUT_RESULT for WITH...AS 把游标OUT_RESULT作为存储过程的out参数
   或者CURSOR OUT_RESULT IS WITH...AS定义游标变量，再在存储过程的 BEGIN END 中 使用FOR row LOOP OUT_RESULT 操作游标进行后续操作。

例子：

整个存储过程大体分为三个模块

• 头部

CREATE OR REPLACE PROCEDURE PRO_BUS_FAL_H01_TEMP (GATHERDATE in varchar) IS

TX_DT_DATE DATE := TO_DATA(GATHERDATE ,'YYYYMMDD') ;        --采集日期（本月最后一天）
LAST_DAY_OF_LAST_MONTH VARCHAR(10) := to_char(last_day(add_months(to_date(GATHERDATE ,'yyyymmdd'), -1)),'yyyymmdd');    --上月最后一天
REPORT_DT VARCHAR(6) := substr(GATHERDATE ,1,6);             --报告期（取日期前六位）
--BEGIN_MONTH VARCHAR(10) := trunc(DATE , 'mm');             --获取当月第一天     

• 游标 

CURSOR OUT_RESULT IS 
WITH      
  cf AS (   
    ... 
  ),    
  id AS (   
    ...
  ),  
  nation AS (   
    ...
  ),    
  cf_info AS (    
    ...
  ),    
  cfm AS (    
    ...
  ),    
  tbs AS (    
    ...
  ),    
   tbp AS (    
    ...
  ),    
  bal AS (    
    ...
  ),    
  trans AS (    
    ...
  )
SELECT 
    ...
FROM cf_info      
    left join ... on ... 
    left join ... on ... and ...    
WHERE ... 
   OR ...
   OR ... ;

• 内容

BEGIN
	FOR row IN OUT_RESULT LOOP
		BEGIN
			INSERT INTO(
				插入表名
			)VALUES(
				row.游标结果集字段名
			)
		END;
	END LOOP;
COMMIT;
END PRO_BUS_FAL_H01_TEMP;