更新表中每一行的一列 [PL/SQL，unix 脚本]

2021-12-11 00:00:00 sql-update for-loop oracle scripting plsql

我有一个 12 列的表格:

i have a table with 12 columns:

表 1:

<头>
123456789101112
ABC1000aaazzz2234哦哦0000101123214
定义2023bbbyyy4345PPP0000202133224
你好3011抄送xxx6456QQ0000303143234
jkl4112dddwww8567RRR0000404153244

我想在循环中使用第三列数据并从另一个表中获取最佳匹配"数据.

i would like to use 3rd column data in a loop and fetch 'best match' data from another table.

表 2:

<头>
1234
0777676美国
00888878英国
01999989法国
02666656德国

将在循环中修剪第三列数据，直到获取 table2 中的匹配项.

3rd column data will be trimmed in the loop until a match in table2 is fetched.

first row:
iter 1: table1 row1 col3=000 -- no match in table
iter 2: table1 row1 col3=00 -- return england, replace table1 row1 col12=214 with 'england'

updated row: abc,1,000,aaa,zzz,2,234,OOO,00001,01,123,england


second row:
iter 1: table1 row2 col3=023 -- no match in table
iter 2: table1 row2 col3=02 -- return germany, replace table1 row1 col12=224 with 'germany'

updated row: def,2,023,bbb,yyy,4,345,PPP,00002,02,133,germany

推荐答案

您需要做的是创建一个过程，然后在该过程中声明一个 cursor 以及 variable_c_row cursor_name%ROWTYPE.

What you will need to do is create a procedure, then within the procedure declare a cursor as well as a variable_c_row cursor_name%ROWTYPE.

在程序中，这将是内容:

Within the procedure, this will be the contents:

OPEN cursor_name

FETCH cursor_name INTO variable_c_row;

WHILE cursor_name%FOUND LOOP
    -- Declare a number variable (i)
    i := 0;
    -- Declare a varchar variable (match)
    match := variable_c_row.col3
    
    WHILE length(match) > 0 LOOP OR l_countryname IS NULL
      begin
        -- Declare a yourrow%ROWTYPE variable (l_countryname)
        SELECT col4 FROM table2 INTO l_countryname WHERE col1 = match;
        
        UPDATE table1 SET col12 = l_countryname;
      exception when no_data_found then null;
      end;

      i := i+1;
      match := substr(variable_c_row.cow3, 0, length(match)-i);
    END LOOP;
  FETCH cursor_name INTO variable_c_row;
END LOOP;

CLOSE cursor_name;

由于问题没有 DDL 或 DML，我最多只能提供一个广泛的答案，尚未经过测试.

Since the question had no DDL or DML, the most I can provide is a broad answer, which has not been tested.

相关文章