備忘録

Ask Tom "how to dynamically generate cursors with..."

質問した人は、テーブル名をパラメータにして動的にupdate文を生成したいらしい。

やり方は、

CREATE OR REPLACE PROCEDURE populate_email(p_in_table_name VARCHAR2)

AS
    
    TYPE cur_type IS REF CURSOR;
    tmp_cur cur_type;
    
    TYPE email_rec IS RECORD (
        intid internetaddress.intid%TYPE,
        intaddress internetaddress.intaddress%TYPE);    
    query varchar2(100);
    
    tmp_rec email_rec;
BEGIN
    
    query := 'select i.intid, i.intaddress
    from internetaddress i, '||p_in_table_name ||' b 
    where i.intid = b.id';
    
    OPEN tmp_cur  FOR query;
    LOOP
        FETCH tmp_cur INTO tmp_rec;
        EXIT WHEN tmp_cur%NOTFOUND;
    
        EXECUTE IMMEDIATE
        'update '||p_in_table_name ||' a
        set a.ademal = substr(trim('||tmp_rec.intaddress||'),1,50)
        where a.id = '||tmp_rec.intid;

    END LOOP;
    
    EXECUTE IMMEDIATE
    'COMMIT';

    CLOSE tmp_cur;

END;

一方Ｔｏｍの回答は、

execute immediate '
update (select a.ademal, substr( trim( i.intaddress ) ) intaddress
       from ' || dbms_assert.sql_object_name( p_in_table_name ) || ' a,
          internetaddress i
      where i.intid = a.id )
  set ademal = intaddress';