esProc Codes Column Shuffling

In database table REF_VALUES, ID column acts as the primary key and ORIGINAL_VALUE is used as the original column, whose values are to be shuffled and updated to the empty SHUFFLED_VALUE column. Below is a selection from the table: 

SQL approach:

Databases differ in their solution to the problem. Here Oracle is used as the example:

create or replace procedure shuffle as

TYPE My_CurType IS REF CURSOR; 

CUR_1 My_CurType;

V_id NUMBER(10,0);

V_result varchar2(20);

v_sql varchar2(2000);

begin

  OPEN CUR_1 FOR  select t1.id,t2.result from (

        select rownum rn,id,ORIGINAL_VALUE from REF_VALUES order by ORIGINAL_VALUE) t1

    join(

        select rownum rn,result from(

            select ORIGINAL_VALUE result from REF_VALUES order by dbms_random.value()))t2

    on t2.rn=t1.rn;

  LOOP

    fetch CUR_1 INTO V_id,V_result ; 

       EXIT WHEN CUR_1%NOTFOUND; 

    v_sql:=’update REF_VALUES set SHUFFLED_VALUE=”’||V_result||”’ where id=’||V_id;

    EXECUTE immediate v_sql;  

  END LOOP;

  CLOSE  CUR_1;

end shuffle;

You need to write multilayered nested subqueries to get the shuffling result (as shown by the first half of the above code), then you need cursors (or temporary tables) and the stored procedure to insert it to the target table. The code will be rather tedious. 

The esProc approach allows us to dispense with the complex nested subqueries and applies to various types of databases. The code is as follows:

A1: Execute the SQL statement to get data from ID column and ORIGINAL_VALUE column.

A2: Shuffle the values in ORIGINAL_VALUE column.

A3: Join A1’s ID column with A2’s ORIGINAL_VALUE column to create a two-dimensional table, as shown below:

A4：Update REF_VALUES table with A3’s table. @u option means only generating the UPDATE statement. The updated REF_VALUES table is as follows:

esProc Codes Dynamic MERGE statement

Databases, such as MSSQL and ORACLE, support updating tables using MERGE statement. But they lack functions for performing set operations. If data structure of the target table is unknown, it is very complicated to use the stored procedure to get its data structure and then compose the dynamic SQL statement. This may need scores of lines of code. For the same reason, it is also not easy to perform the operation in Java and other high-level languages. On the other hand, you must write the code into the database or the application when using stored procedures or the Java language, which is inconvenient for modification and management. In contrast, if esProc is used to help with the operation, the code can be database/application-independent and the architecture of the database or the application will be unaffected and easy to maintain.

Parameters source and target represent two tables with the same structure but different data. The source table will be used to update the target table based on their primary keys. For example, both Table 1 and Table 2 (as shown below) have a primary key consisting of column A and column B:

Below is the MERGE statement for merging Table 1 with Table 2.

MERGE INTO table1 as t

USING table2 as s

ON t.A=s.A and t.B=s.B

WHEN MATCHED

THEN UPDATE SET t.C=s.C,t.D=s.D

WHEN NOT MATCHED

THEN INSERT VALUES(s.A,s.B,s.C,s.D)

The modified Table 1 will be as follows: 

esProc code：

A1,A2: Get the source table’s primary key from the system tables and store it in variable pks; the result is a set - [“A”,“B”]. Databases vary in how to get the primary key, here we’ll take MSSQL as an example.

A3,A4：Retrieve all columns from source, the result is [“A”,“B”,“C”,“D”].

A5：Compose the MERGE statement dynamically. pks.(…) is a loop function for computing members of a set (including the result set) in order. You can use ~ to reference the loop variable and # to reference the loop number in the computation.

A6：Execute the MERGE statement.