Oracle数据库创建存储过程

创建存储过程通用语法：

CREATE OR REPLACE PROCEDURE 存储过程名称  (参数1名称 参数类型(长度), 参数2名称 参数类型(长度),·········) IS(AS也可以) 

-- 创建变量
变量1名称 类型(长度);
变量2名称 类型(长度);
···················;

-- 开始标识
BEGIN

  --业务处理逻辑

END 存储过程名称;

下面是一个迁移数据库数据的存储过程：

create or replace procedure addTableData is

beginDateStr varchar2(200);
endDateStr varchar2(200);

begin
  -- 获取开始时间
  select substr(begindate, 0, 8) INTO beginDateStr from PROCEDURE_TEST;
    
  WHILE beginDateStr <= '20230628' LOOP

    -- 根据时间插入数据
    insert into CUS_TAB_PROCEDURE (ID, CZR, CZDW, CZLX, CJSJ, GXSJ, SX_ID, SX_SEND_DATE, SX_LEIBIE, SX_SOURCE, SX_CHECK_STATUS, SX_TRANSFER_FLAG, RECEIVEUNIT, REPORTUNIT, SX_DESC, SX_FEEDBACK, CASE_LIAN, CU_GJC, CUS_GRADE, CUS_CASEID, CRIMINALER_LOCATION, QUESTION, PNUM, PROLE, INSIDE_INTERMEDIARY, OUT_CORPERATION, WORK_TRACERT, MUTI_FLAG, TIKTOK, XS_TYPE, SX_XSLXR, SX_XSLXFS, XS_GRADE, XS_DISTRIBUTE, LINKMAN, PHONENUMBER, AAA, JUDGMENTGROUP_CIRCULATION, JUDGMENTGROUP_CIRCULATION_SYS)
    select ID, CZR, CZDW, CZLX, CJSJ, GXSJ, SX_ID, SX_SEND_DATE, SX_LEIBIE, SX_SOURCE, SX_CHECK_STATUS, SX_TRANSFER_FLAG, RECEIVEUNIT, REPORTUNIT, SX_DESC, SX_FEEDBACK, CASE_LIAN, CU_GJC, CUS_GRADE, CUS_CASEID, CRIMINALER_LOCATION, QUESTION, PNUM, PROLE, INSIDE_INTERMEDIARY, OUT_CORPERATION, WORK_TRACERT, MUTI_FLAG, TIKTOK, XS_TYPE, SX_XSLXR, SX_XSLXFS, XS_GRADE, XS_DISTRIBUTE, LINKMAN, PHONENUMBER, AAA, JUDGMENTGROUP_CIRCULATION, JUDGMENTGROUP_CIRCULATION_SYS from CUS_TAB
    where cjsj like beginDateStr || '%';

    -- 修改已经操作的数据时间
    update procedure_TEST set begindate = to_char((to_date(begindate, 'yyyyMMdd') + 1), 'yyyyMMdd');

    -- 提交数据
    commit;
    select substr(begindate, 0, 8) INTO beginDateStr from PROCEDURE_TEST;
  END LOOP;

end addTableData;

存储过程中用到的 while 循环逻辑：

-- while循环 语法解析 
-- LOOP:循环内容开始标志，代表着循环执行内容从此开始
-- END LOOP：循环内容结束标识，代表着循环内容从此结束（注：END LOOP后面一定要有“;”否则回报错）
WHILE 条件（满足就执行否则退出）LOOP
   -- 循环执行的业务逻辑
END LOOP;

执行存储过程

-- 执行存储过程
call 存储过程名()

删除存储过程 

-- 删除存储过程
drop procedure 存储过程名;