代码之家 › 专栏 › 技术社区 › repzero

MYSQL-Set子句不更新变量值

mysql

repzero · 技术社区 · 6 年前

flexcube_data

下表

+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| item_no | CIF_NO | CIF_NAME     | PRIMARY_KEY | Collateral_Type | Collateral_Code | Stamped_to_Cover |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
|       1 | 801125 | John Doe     | YES         | 06              | J1              |                0 |
|       2 | 801125 | John Doe     | YES         | 08              | J5              |                0 |
|       3 | 801125 | John Doe     | YES         | 26              | J25             |                0 |
|       4 | 801125 | John Doe     | YES         | 26              | J25             |                0 |
|       5 | 801125 | John Doe     | YES         | 89              | J53             |                0 |
|       6 | 801125 | John Doe     | YES         | 98              | J57             |                0 |
|       7 | 801125 | John Doe     | YES         | 58              | J88             |                0 |
|       8 | 800102 | John Doe     |             | 19              | J03             |                0 |
|       9 | 800102 | Mary Jane    | YES         | 22              | J1              |                0 |
|      10 | 800102 | Mary Jane    | YES         | 90              | J5              |                0 |
|      11 | 800102 | Mary Jane    | YES         | 01              | J25             |                0 |
|      12 | 800102 | Mary Jane    | YES         | 77              | J25             |                0 |
|      13 | 800102 | Mary Jane    | YES         | 42              | J53             |                0 |
|      14 | 800102 | Mary Jane    | YES         | 25              | J57             |                0 |
|      15 | 800102 | Mary Jane    | YES         | 36              | J88             |                0 |
|      16 | 800102 | Mary Jane    |             | 32              | J03             |                0 |
|      17 | 563021 | Jack Daniels | YES         | 58              | ab22            |                0 |
|      18 | 563021 | Jack Daniels | YES         | 51              | ca55            |                0 |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+

删除重复项 到岸价 flexcube_表
tmp_表 (临时表)并只插入一个重复值

我对tmp_表的预期结果应该是

+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| item_no | CIF_NO | CIF_NAME     | PRIMARY_KEY | Collateral_Type | Collateral_Code | Stamped_to_Cover |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
|       1 | 801125 | John Doe     | YES         | 06              | J1              |                0 |
|       9 | 800102 | Mary Jane    |             | 19              | J03             |                0 |
|      17 | 563021 | Jack Daniels | YES         | 58              | ab22            |                0 |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+

我的示例代码如下:

DELIMITER //
DROP PROCEDURE if exists FLEX //
use members_db;
Drop table if exists tmp_table;
CREATE TABLE tmp_table (
    item_no INT AUTO_INCREMENT,
    CIF_NO VARCHAR(255),
    CIF_NAME VARCHAR(255),
    Collateral_Type_1 VARCHAR(255),
    Collateral_Code_1 VARCHAR(255),
    Stamped_to_Cover_1 BIGINT,
    Collateral_Type_2 VARCHAR(255),
    Collateral_Code_2 VARCHAR(255),
    Stamped_to_Cover_2 BIGINT,
    Collateral_Type_3 VARCHAR(255),
    Collateral_Code_3 VARCHAR(255),
    Stamped_to_Cover_3 BIGINT,
    Collateral_Type_4 VARCHAR(255),
    Collateral_Code_4 VARCHAR(255),
    Stamped_to_Cover_4 BIGINT,
    Collateral_Type_5 VARCHAR(255),
    Collateral_Code_5 VARCHAR(255),
    Stamped_to_Cover_5 BIGINT,
    PRIMARY KEY (item_no)
);
Drop table if exists flexcube_table;
CREATE TABLE flexcube_table (
    item_no INT AUTO_INCREMENT,
    CIF_NO VARCHAR(255),
    CIF_NAME VARCHAR(255),
    PRIMARY_KEY VARCHAR(255),
    Collateral_Type VARCHAR(255),
    Collateral_Code VARCHAR(255),
    Stamped_to_Cover BIGINT,
    PRIMARY KEY (item_no)
);
/* I INSERTED A COUPLE OF VALUES IN THE FLEXCUBE TABLE AS A SAMPLE */ 
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","06","J1","$20,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","08","J5","$22,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","26","J25","$5,100");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","26","J25","$5,100");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","89","J53","$111,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","98","J57","$118,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","58","J88","$103,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","","John Doe","19","J03","$114,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","22","J1","$20,125");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","90","J5","$88,135");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","01","J25","$3,401");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","77","J25","$8,301");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","42","J53","$25,501");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","25","J57","$82,101");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","36","J88","$156,222");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","","Mary Jane","32","J03","$187,256");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("563021","YES","Jack Daniels","58","ab22","$10,110");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("563021","YES","Jack Daniels","51","ca55","$26,725");
CREATE PROCEDURE FLEX()
BEGIN
DECLARE total_flex_rows INT DEFAULT 0; 
DECLARE tmp_table_rows INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE tmp_table_counter INT DEFAULT 0;
DECLARE FLEXCUBE_CIF_NO VARCHAR(255);
DECLARE TEMPORARY_CIF_NO VARCHAR(255);
DECLARE MATCH_FOUND INT DEFAULT 0;
DECLARE COLLATERAL_EXIST INT DEFAULT 0;
/* GET TOTAL ROWS IN ORIGINAL TABLE TO LOOP THROUGH  AND STORE IT INTO VARIABLE*/
SELECT COUNT(*) FROM flexcube_table INTO total_flex_rows;
SET counter=0;
/* GET TOTAL ROWS IN TMP TABLE AND STORE IT IN VARIABLE */
  SET tmp_table_rows=(SELECT COUNT(*) FROM tmp_table);
  IF tmp_table_rows=0 THEN
      INSERT INTO tmp_table (CIF_NO) VALUES("1");
  END IF;
/* LOOP THROUGH EVERY ROW IN ORIGINAL TABLE TO DETERMINE IF CIF_NO VALUE EXIST IN TEMPORARY TABLE */
WHILE counter<=total_flex_rows DO
/* SET MATCH FOUND TO 0 - NO MATCH IS FOUND AS YET */
  SET MATCH_FOUND=0; 
  SET FLEXCUBE_CIF_NO=""; 
  SET FLEXCUBE_CIF_NO=(SELECT CIF_NO FROM flexcube_table WHERE PRIMARY_KEY="YES" LIMIT counter,1);
/* FOR EACH CIF_NO VALUE IN ORIGINAL TABLE, LOOP THROUGH EVERY ROW IN TEMPORARY TABLE TO CHECK IF VALUE ALREADY EXISTS */
  SELECT COUNT(*) FROM tmp_table INTO tmp_table_rows;
    sub_loop:WHILE tmp_table_counter<=tmp_table_rows DO
        /* THE PROBLEM IS TEMPORARY_CIF_NO IS NOT NEW VALUES */ 
    SET TEMPORARY_CIF_NO=(SELECT CIF_NO FROM tmp_table LIMIT tmp_table_counter,1); 
    IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO THEN
        /* WE CIF_NO IN ORIGINAL TABLE IS EQUAL TO CIF_NO VALUE IN TEMPORARY TABLE MATCH_FOUND=1 (TRUE) */
      SET MATCH_FOUND=1;
          SELECT "I FOUND A MATCH!!!!!!!!!!!!";
          SET COLLATERAL_EXIST=0;
        /* LEAVE WHILE LOOP SINCE THERE IS NO NEED TO CONTINUE FINDING CIFS */
      LEAVE sub_loop;
    END IF;
  /* CONTINUE TEMPORARY TABLE ROW COUNTER INCREMENTING COUNTER */
    SET tmp_table_counter=tmp_table_counter+1;
    END WHILE sub_loop;
/* IF NOT MATCH IS FOUND THEN ADD NEW CIF_VALUE TO TEMPORARY TABLE */
  IF MATCH_FOUND=0 THEN
    /*IF NO MATCH IS FOUND INSERT THE NEW CIF_NO VALUE IN THE TEMPORARY TABLE */
    INSERT INTO tmp_table (CIF_NO) VALUES((SELECT (CIF_NO) FROM flexcube_table LIMIT counter,1));
  END IF;
  SET counter = counter + 1;
  SET MATCH_FOUND=0;
END WHILE;
End//
DELIMITER //
CALL FLEX();
select * from tmp_table;

发行

当循环遍历临时表中的行时,我将把每一行的CIF-NO值存储在一个名为 TEMPORARY_CIF_NO . 线路 SELECT TEMPORARY_CIF_NO; 正在返回 NULL 尽管我设定了 临时到岸价 值是临时表中当前行的CIF-NO值。

为什么变量是 临时到岸价 返回 ?

1 回复 | 直到 6 年前

repzero 6 年前

第一期

Set子句按预期工作。 tmp_table_counter 变量在每个完整循环后从最后一个计数开始计时。

也就是说,我没有重新设置在遍历tmp_表中的所有行之后,变量为0。

更正的代码是:

 SET tmp_table_counter=0; /* I NEEDED THIS LINETO RESET THE COUNTER BEFORE LOOPING AGAIN
  SET MATCH_FOUND=0;
    sub_loop:WHILE tmp_table_counter<=tmp_table_rows DO
    ..................
    LEAVE sub_loop;

第二期

下一行

`SET FLEXCUBE_CIF_NO=(SELECT CIF_NO FROM flexcube_table WHERE PRIMARY_KEY="YES" LIMIT` counter,1);

flexcube_data )表和不检索 CIF_NO 没有任何条件?

然后改变

IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO THEN

到

IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO AND PRIMARY_KEY="YES" THEN