存储过程 --- 9 --- 给用户数据库中发奖品

「爱情、让人受尽委屈。」 2022-05-08 12:24 80阅读 0赞

领导提供一个宝贝调查问卷答题的EXCEL约6万条，将这些问卷的旅客数据判断为有效用户且未发奖的表中发奖品（或其它操作）；

--答题记录表
    create table BB_BABYQUE_ANSWER_T
    (
      VIP_CARD      VARCHAR2(32),--会员号
      QUESTIONID    VARCHAR2(8),--题库哪套题
      OPTIONCONTENT VARCHAR2(200),--答题情况描述
    --  CREATE_DATE   DATE,--创建时间
      UPDATE_DATE   DATE,--修改时间
      IF_VALID      NUMBER(2) DEFAULT '1',--是否有效，1有效，0无效，若已做过处理则该条修改为无效
      REMARK        VARCHAR2(150)
    );
    --旅客信息表
    create table BB_USER_T
    (
      VIP_CARD      VARCHAR2(32),--会员号
      FIRST_NAME    VARCHAR2(200),
      LAST_NAME     VARCHAR2(200),
      IF_VALID      NUMBER(2) DEFAULT '1'--是否是有效用户，1有效，0无效
    );
    --旅客答题发奖品表（原奖品表中有N条发奖记录）
    create table BB_USER_PRIZE_T
    (
      VIP_CARD      VARCHAR2(32),--会员号
      QUESTIONID    VARCHAR2(8),--题库哪套题
      ANSWER_FLAG   NUMBER(2),--答题发奖品标识，0未答题，1答题发奖品，2答题用户信息异常未发奖品，3已发过奖品不重复发奖品，4答题用户信息异常已发奖品，5其他情况
      CREATE_DATE   DATE,--创建时间
      IF_VALID      NUMBER(2),--是否有效，修改过
      REMARK        VARCHAR2(150)
    );

PL/SQL导入Excel到数据库

Excel文件地址-》链接：https://pan.baidu.com/s/1qWC3ACuzzBq7ozNY18Ur4g 密码：gwwt  
1.打开PL/SQL之后，在工具栏点击【tools】-》【ODBC Imoprter】。

2.选择导入文件的类型，这里是Excel文件，所以选择Excel Files  
输入连接数据库的用户名和密码  
点击Connect。

![70][]

3.选择excel文件所在的磁盘，双击磁盘或者文件夹名，然后选中需要导入的excel文件名。

![70 1][]  
4.点击文件页的名称，下面的数据就是在excel中存储的数据。

![70 2][]

5.点击菜单【Data to Oracle】选择导入这些数据的数据库"用户名","数据库表名"  
在这里设置excel中表头的名称与数据库中表字段对应关系。注意在数据库中的数据类型  
设置完之后，点击Import进行导入。

![70 3][]

PL/SQL不显示可查看Procedures左侧任务栏窗口，进行如下操作：  
1.打开PL/SQL之后，在工具栏点击【tools】-》选中【Browser】。  
PL/SQL不显示window list窗口，进行如下操作：  
1.在菜单项的Tools下的Preference选项中的User Interface中选择Option，在右边对于的Autosave desktop中把前面的复选框勾选上。  
2.在菜单项的Tools下的Window list选项勾上。

创建存储过程：

CREATE OR REPLACE PROCEDURE BABY_QUESTION_SEND_PRICE_P
    /* BABY_QUESTION_SEND_PRICE_P
      **********************************************************
      *  存储过程名  :BABY_QUESTION_SEND_PRICE_P 宝贝调查问卷优惠券发放
    
      *  建立日期  :
      *  作者    :cheng_xy
      *  模块    :营销活动
      *  描述    :为指定用户发放奖品
      *------------------------------------------------------------
      *  修改历史
      *  序号      日期               修改人      修改原因
      *  1
      *************************************************************/
    (
     ON_CODE          OUT NUMBER,
    
     OS_MSG OUT VARCHAR2) AS
      VN_COUNT               NUMBER(4);
      VN_COUNT_TOTAL         NUMBER(10);
      USER_INFO_NUM         NUMBER(4);
      SEND_PRIZE_NUM         NUMBER(4);
      PRIZE_ANSWER_FLAG      VARCHAR2(32);
      PRIZE_REMARK           BB_USER_PRIZE_T.REMARK%TYPE;
    
      CURSOR CUR_CPN_CARD IS
        SELECT U.VIP_CARD,U.QUESTIONID,U.OPTIONCONTENT
          FROM BB_BABYQUE_ANSWER_T U WHERE U.IF_VALID='1';--IF_VALID='1'为未处理过数据
    
    BEGIN
      VN_COUNT := 0;
      VN_COUNT_TOTAL := 0;
      ON_CODE  := 9999;
    
      FOR REC IN CUR_CPN_CARD LOOP
        
        SELECT COUNT(1) INTO USER_INFO_NUM FROM BB_USER_T WHERE VIP_CARD = REC.VIP_CARD AND IF_VALID='1';
        SELECT COUNT(1) INTO SEND_PRIZE_NUM FROM BB_USER_PRIZE_T WHERE VIP_CARD = REC.VIP_CARD AND QUESTIONID = REC.QUESTIONID AND IF_VALID='1';
        IF USER_INFO_NUM > 0 AND SEND_PRIZE_NUM = 0 THEN
           /*
           旅客信息正常，未发过奖品
           需要发奖品--0未答题，1答题发奖品，2答题用户信息异常未发奖品，3已发过奖品不重复发奖品，4答题用户信息异常已发奖品，5其他情况
           */
           PRIZE_ANSWER_FLAG := '1';
           PRIZE_REMARK := '旅客信息正常，未发过奖品';
           
        ELSIF USER_INFO_NUM > 0 AND SEND_PRIZE_NUM > 0 THEN
           /*
           旅客信息正常，发过奖品
           不发奖品--0未答题，1答题发奖品，2答题用户信息异常未发奖品，3已发过奖品不重复发奖品，4答题用户信息异常已发奖品，5其他情况
           */
           PRIZE_ANSWER_FLAG := '3';
           PRIZE_REMARK := '不发奖品：用户信息正常，发过奖品';
        
        ELSIF USER_INFO_NUM = 0 AND SEND_PRIZE_NUM = 0 THEN
           /*
           用户信息异常，未发过奖品
           不发奖品--0未答题，1答题发奖品，2答题用户信息异常未发奖品，3已发过奖品不重复发奖品，4答题用户信息异常已发奖品，5其他情况
           */
           PRIZE_ANSWER_FLAG := '2';
           PRIZE_REMARK := '可能需要需要发奖品，核查用户信息：用户信息异常，未发过奖品';
        
        ELSIF USER_INFO_NUM = 0 AND SEND_PRIZE_NUM > 0 THEN
           /*
           用户信息异常，发过奖品
           不发奖品--0未答题，1答题发奖品，2答题用户信息异常未发奖品，3已发过奖品不重复发奖品，4答题用户信息异常已发奖品，5其他情况
           */
           PRIZE_ANSWER_FLAG := '4';
           PRIZE_REMARK := '不发奖品，核查会员信息：旅客信息异常，发过奖品';
        
        ELSE
           PRIZE_ANSWER_FLAG := '5';
           PRIZE_REMARK := '其他情况';
    
        END IF;
        INSERT INTO BB_USER_PRIZE_T(VIP_CARD, QUESTIONID, ANSWER_FLAG, CREATE_DATE, IF_VALID, REMARK) 
                  VALUES(REC.VIP_CARD, REC.QUESTIONID, PRIZE_ANSWER_FLAG, SYSDATE, '1', PRIZE_REMARK);
        UPDATE BB_BABYQUE_ANSWER_T SET IF_VALID = '0', UPDATE_DATE = SYSDATE WHERE VIP_CARD = REC.VIP_CARD AND QUESTIONID = REC.QUESTIONID;
        VN_COUNT := VN_COUNT + 1;
        VN_COUNT_TOTAL := VN_COUNT_TOTAL + 1;
        IF VN_COUNT = 100 THEN
          COMMIT;
          VN_COUNT := 0;
        END IF;
    
      END LOOP;
    
      COMMIT;
    
      OS_MSG := '给用户批量处理完成！,处理总数为: ' || VN_COUNT_TOTAL;
    
    EXCEPTION
      WHEN OTHERS THEN
        ON_CODE := -20001;
        OS_MSG  := '处理批量发奖品失败！' || SUBSTR(SQLERRM, 1, 200);
        ROLLBACK;
    
    END BABY_QUESTION_SEND_PRICE_P;

在左侧任务栏窗口中，点开“Procedures”-》右击存储过程“BABY\_QUESTION\_SEND\_PRICE\_P”-》选择“test”-》选中Window list窗口刚打开的窗口，右击点“Execute”执行存储过程

![70 4][]

**希望对你有帮助，祝你有一个好心情，加油！**

**若有错误、不全、可优化的点，欢迎纠正与补充！（转载请标明出处）**

[70]: /images/20220508/5cca68199ff44893975aea309e549710.png
[70 1]: /images/20220508/6e4dc73a46cf43b1ac1294ce5cf9ba60.png
[70 2]: /images/20220508/15ede859042a4ca48b86d8eeadbf88cd.png
[70 3]: /images/20220508/ac2ef2ee1f714d2ebc9e7a7a7ef76d14.png
[70 4]: /images/20220508/b1fea2fa04b64519b07c6ded810b5fa5.png