Oracle存储过程使用知识点合集（不间断更新）

1 存储过程基本代码

create or replace PROCEDURE  P_FUN_NAME(
P_USERID in NUMBER,
p_MONTH IN Date DEFAULT NULL --可为空参数
)
/*------------------------------------------------------------*
*功能：oracle存储过程基本代码    		
*作者：llp                                                     
*日期：2021/05/19			  					  
--------------------------------------------------------------*/
AS
M_DATE  			varchar2(12);
M_SQL			   varchar2(520);
BEGIN
--此处添加功能代码
COMMIT;
END P_DHFK_COMPANY_VINFO_CONCERN;

--执行过程
begin
   P_FUN_NAME(123,ADD_MONTHS(SYSDATE,-1));
end;

2.使用select into 赋值时 如果查询结果为空，会出现异常中断执行，解决方案是单独对异常进行处理
（必须要使用begin end包裹）

--根据公司名称获取公司ID，如果为空赋值0
BEGIN
DBMS_OUTPUT.put_line(p_CORPNAME);
select CORPID  into M_CORPID from CORPINFO where CORPNAME=p_corpname;
DBMS_OUTPUT.put_line('公司ID：'||M_CORPID);
--如果为空 赋值为0
EXCEPTION WHEN NO_DATA_FOUND THEN 
M_CORPID:=0;
DBMS_OUTPUT.put_line('为空赋值0');
END;

3存储过程中 拼接sql 后输出拼接的sql和执行sql

DBMS_OUTPUT.put_line(M_SQL);---输出sql到dbms 输出
execute immediate M_SQL;--执行sql

4存储过程中字符串连接 使用“||”符号连接，单引号转义单引号

M_SQL:='INSERT INTO corpinfo(corpid, corpname, corptypeid, remarks, isdelete,userid,cfusc,createtime)
            VALUES (SEQ_GIS_DHFK_CORPINFO.Nextval,'''||p_CORPNAME||''',1,null,0,'||P_USERID||',null,'''||M_NOWDATE||''')';

5存储过程可以使用循环

for item in (select id as i_id,name as i_name from UserInfo) loop
 M_SQL:= 'update TESE_UserInfo set oldname='||item.i_name||' where id='||item.i_id||';
execute immediate M_SQL;
DBMS_OUTPUT.put_line(M_SQL);
end loop;

6时间类型的变量初始化

M_NOWDATE:=TO_DATE(SYSDATE);

7默认参数是否为空的处理

IF p_month IS NOT NULL
   THEN M_DATE:=TO_CHAR(p_month,'YYYYMM');
   ELSE M_DATE:=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM');
END IF;

8.存储过程传入参数是“1,2,3”，通过指定分隔符分隔字符串

//pn_LABLEID:='1,2,3'
(SELECT REGEXP_SUBSTR (pn_LABLEID, '[^,]+', 1,rownum) as AAA from dual connect by rownum<=LENGTH (pn_LABLEID) - LENGTH (regexp_replace(pn_LABLEID, ',', ''))+1)

结果为：

9.非自表更新的时候推荐使用Megre替换Update ,会极大提升效率