sql*plus与存储过程举例

一、表结构

-- Create table
create table CARD_BIN_CTRL
(
  CARD_BIN     CHAR(12) not null,
  CARD_LEN     INTEGER not null,
  CHANNEL_TYPE CHAR(2) not null,
  CARD_BIN_LEN INTEGER,
  CARD_TYPE    CHAR(1),
  ISSUER_CODE  CHAR(11),
  CARD_NAME    CHAR(60),
  AMT_LIMIT    CHAR(12),
  TRAN_ENABLE  CHAR(1),
  REVERSAL1    CHAR(100),
  REVERSAL2    CHAR(100)
)
tablespace ELINK
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table CARD_BIN_CTRL
  add primary key (CARD_BIN, CARD_LEN, CHANNEL_TYPE)
  using index
  tablespace ELINK
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 

二、投产操作步骤如下
1、登录sql*plus
2、依次输入以下命令：
   a)show serveroutput
   b)set serveroutput on
   c)show serveroutput
   d)exec insert_03_channel;

三、存储过程如下
create or replace procedure insert_03_channel is
  tmp_card_bin          CARD_BIN_CTRL.Card_Bin%TYPE; --暂存单个card_bin的变量
  tmp_card_bin_ctrl_rcd CARD_BIN_CTRL%ROWTYPE; --暂存CARD_BIN_CTRL表中单条记录的变量

  --定义并初始化变量
  channel_07 CARD_BIN_CTRL.CHANNEL_TYPE%TYPE := '07';
  channel_16 CARD_BIN_CTRL.CHANNEL_TYPE%TYPE := '16';
  channel_03 CARD_BIN_CTRL.CHANNEL_TYPE%TYPE := '03';

  CURSOR cur_card_bin IS --指向card_bin_ctrl表中所有卡BIN的游标
    SELECT DISTINCT CARD_BIN FROM CARD_BIN_CTRL;
  TYPE RC IS REF CURSOR;
  cur_card_bin_ctrl_rcd RC; --指向包含特定卡BIN的记录集合的游标

begin
  dbms_output.enable(9999999999);
  OPEN cur_card_bin;
  LOOP
    EXIT WHEN(cur_card_bin%NOTFOUND);
    FETCH cur_card_bin
      INTO tmp_card_bin;
 
    OPEN cur_card_bin_ctrl_rcd FOR
      SELECT * FROM CARD_BIN_CTRL WHERE CARD_BIN = tmp_card_bin;
    LOOP
      EXIT WHEN(cur_card_bin_ctrl_rcd%NOTFOUND);
      FETCH cur_card_bin_ctrl_rcd
        INTO tmp_card_bin_ctrl_rcd;
      BEGIN
        IF (TRIM(tmp_card_bin_ctrl_rcd.channel_type) = channel_07 OR
           TRIM(tmp_card_bin_ctrl_rcd.channel_type) = channel_16) THEN
       
          INSERT INTO CARD_BIN_CTRL
            (CARD_BIN,
             CARD_LEN,
             CHANNEL_TYPE,
             CARD_BIN_LEN,
             CARD_TYPE,
             ISSUER_CODE,
             CARD_NAME,
             AMT_LIMIT,
             TRAN_ENABLE,
             REVERSAL1,
             REVERSAL2)
          VALUES
            (tmp_card_bin_ctrl_rcd.Card_Bin,
             tmp_card_bin_ctrl_rcd.Card_Len,
             channel_03,
             tmp_card_bin_ctrl_rcd.Card_Bin_Len,
             tmp_card_bin_ctrl_rcd.Card_Type,
             tmp_card_bin_ctrl_rcd.Issuer_Code,
             tmp_card_bin_ctrl_rcd.Card_Name,
             tmp_card_bin_ctrl_rcd.Amt_Limit,
             tmp_card_bin_ctrl_rcd.Tran_Enable,
             tmp_card_bin_ctrl_rcd.Reversal1,
             tmp_card_bin_ctrl_rcd.Reversal2);
          COMMIT;
        END IF;
     
      exception
        WHEN DUP_VAL_ON_INDEX THEN
          dbms_output.put_line('重复插入，联合主键冲突!');
        WHEN OTHERS THEN
          ROLLBACK;
      END;
    END LOOP;
    CLOSE cur_card_bin_ctrl_rcd;
  END LOOP;
  CLOSE cur_card_bin;
end insert_03_channel;