動的SQL: 備忘録

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;