oracle存储过程--名字注意大小写_oracle 存储过程中调用存储过程必须小写-优快云博客

本文链接：https://blog.youkuaiyun.com/kevin_cat/article/details/81110866

本文介绍了一个PLSQL存储过程的实现案例，该过程通过游标遍历CUSTOMER_PERSON_TRANSFER表，并将数据分别插入到错误表QY_CUSTOMER_PERSON_TRA_ERROR和记录表qy_customer_info_remark中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure "QY_CUSTOMER_PERSON" is
r_customer_id VARCHAR2(100);--声明变量
--1自定义筛选条件，自己加条件或者在定义一个游标
cursor CODE_AREA_HIS is SELECT customer_id from CUSTOMER_PERSON_TRANSFER where customer_id in (SELECT CUSTOMERID from CUSTOMER_info) ;--定义游标
begin
open CODE_AREA_HIS;--打开游标
        FETCH CODE_AREA_HIS INTO r_customer_id;--读取吓一条遍历游标
        while CODE_AREA_HIS%found loop --是否有下一条
--2插入错误表
insert into QY_CUSTOMER_PERSON_TRA_ERROR select * from CUSTOMER_PERSON_TRANSFER cpt where cpt.customer_id=r_customer_id;
         FETCH CODE_AREA_HIS INTO r_customer_id;--读取吓一条
        end loop;--跳出循环体
    CLOSE CODE_AREA_HIS;--关闭游标

--3插入记录表
     insert into qy_customer_info_remark
(customerid, customername, nationcode, certtype, certid, custkind, customertype, status, mfcustomerid, belonggroupid, channel, loancardno, customerscale, remark, inputuserid, inputorgid, updateuserid, updateorgid, inputtime, updatetime, channel_name)
select
customer_id, FULLNAME, '','', certid, '', '01', null, null, null, null, null, null, null, 'administrator', '', null, null, sysdate, '', null
from customer_person_transfer   cpt where cpt.customer_id not in (select customer_id from QY_CUSTOMER_PERSON_TRA_ERROR);

COMMIT;--提交
end QY_CUSTOMER_PERSON;