动态SQL求教

[mmtyes]

静态SQL如下：
其中J表的表名是由dcustmsgadd拼接A表的ID_NO的末两位得来，如：dcustmsgadd48，我按照如下方式不行，有人说得用动态SQL，不懂动态SQL，求高手帮忙，谢谢
SELECT A.PHONE_NO,
C.REGION_NAME,
D.DISTRICT_NAME,
A.OPEN_TIME,
E.ID_NAME,
G.OFFER_ID,
F.TYPE,
I.DEV_NAME,
B.OWNER_TYPE
FROM DCUSTMSG A,
DCUSTDOC B,
SREGIONCODE C,
SDISCODE D,
SIDTYPE E,
DCUSTDOCAUTMSG F,
PRODUCT_OFFER G,
PRODUCT_OFFER_INSTANCE H,
DCHNDEVMSG I,
DCUSTMSGADD || substr(A.ID_NO, -2) J
WHERE A.CUST_ID = B.CUST_ID
AND B.ID_TYPE = E.ID_TYPE
AND B.ID_ICCID = F.CARDNO(+)
AND A.BELONG_CODE LIKE '0101%'
AND B.ID_TYPE = '2'
AND C.REGION_CODE = '01'
AND D.REGION_CODE = '01'
AND D.DISTRICT_CODE = '01'
AND H.EXP_DATE > SYSDATE
AND G.OFFER_ID = H.OFFER_ID
AND H.SERV_ID = A.ID_NO
AND G.OFFER_TYPE = '10'
AND J.ID_NO = A.ID_NO
AND I.DEV_CODE = J.ELEMENT_VALUE
AND I.ELEMENT_ID = '4'
AND J.ARRAY_SEQ = '19';

[CT_LXL]

引用 楼主 aslima 的回复:

静态SQL如下： 其中J表的表名是由dcustmsgadd拼接A表的ID_NO的末两位得来，如：dcustmsgadd48，我按照如下方式不行，有人说得用动态SQL，不懂动态SQL，求高手帮忙，谢谢 SELECT A.PHONE_NO, C.REGION_NAME, D.DISTRICT_NAME, A.OPEN_TIME, E.ID_NAME, G.OFFER_ID, F.TYPE, I.DEV_NAME, B.OWNER_TYPE FROM DCUSTMSG A, DCUSTDOC B, SREGIONCODE C, SDISCODE D, SIDTYPE E, DCUSTDOCAUTMSG F, PRODUCT_OFFER G, PRODUCT_OFFER_INSTANCE H, DCHNDEVMSG I, DCUSTMSGADD || substr(A.ID_NO, -2) J WHERE A.CUST_ID = B.CUST_ID AND B.ID_TYPE = E.ID_TYPE AND B.ID_ICCID = F.CARDNO(+) AND A.BELONG_CODE LIKE '0101%' AND B.ID_TYPE = '2' AND C.REGION_CODE = '01' AND D.REGION_CODE = '01' AND D.DISTRICT_CODE = '01' AND H.EXP_DATE > SYSDATE AND G.OFFER_ID = H.OFFER_ID AND H.SERV_ID = A.ID_NO AND G.OFFER_TYPE = '10' AND J.ID_NO = A.ID_NO AND I.DEV_CODE = J.ELEMENT_VALUE AND I.ELEMENT_ID = '4' AND J.ARRAY_SEQ = '19';

动态SQL是将SQL语句先存为一个字符串，然后再执行： example:

declare 
  -- Local variables here
  STR VARCHAR2(100);
  HIT TGP_FUNDS%ROWTYPE;
begin
    STR := 'select * from TGP_FUNDS'||' where '||'fnd_id=''MPGF''';
    EXECUTE IMMEDIATE STR into hit;
    DBMS_OUTPUT.put_line(HIT.FND_ID);
  
end;