oracle 调用动态存储过程，动态sql

1、动态sql返回一条记录

            v_sql := ' select p.name,p.party_id,pi.identity_num,co.staff_id,co.channel_id,to_char(co.complete_date,''yyyymmddhh24miss''),co.so_area_id,pi.identidies_type_cd default_id_type,co.prod_id,co.co_date ' ||
                   ' from crm.party' || v_dblink || ' p,crm.party_identity' ||
                   v_dblink || ' pi,crm.cust_order' || v_dblink || ' co ' ||
                   ' where p.party_id = pi.party_id and co.owner_id = pi.party_id ' ||
                   ' and pi.identidies_type_cd <> 13 ' ||
                   ' and co.co_id = :1 and rownum < 2';
                 execute immediate v_sql
          into vPartyName, vPartyId, vIdentityNum, vStaffId, vChannelId, vComplete, vCustArea, vdefaultIdType, v_prodId, v_coDate
          using v_coId;

 

2、动态sql返回结果集

       v_sql := ' select ti.item_spec_id,ti.value,ct.terminal_code,ct.state,ct.terminal_dev_spec_id,ct.device_model_id ' ||
                     ' from crm.tianyi_mobile_terminal_type tt,crm.co_2_td' || v_dblink ||
                     ' ct ,rm.terminal_dev_item' || v_dblink || ' ti ' ||
                     ' where ct.co_id = ''' || v_coId || '''' ||
                     ' and ct.terminal_dev_id = ti.terminal_dev_id ' ||
                     ' and ct.terminal_dev_spec_id= 10302057 ' ||
                     ' and ct.device_model_id = tt.device_model_id ' ||--1 2927
                     ' and ti.item_spec_id in (816800031,816800032) ';

            open cur for v_sql;
            Loop
              fetch cur
                Into tdInfo;
              exit when cur%NOTFOUND;

 

3、动态存储过程

     new_V_TER_DEV_ID := '';
        --获取旧IMSI  只传旧ter_dev_id,新值传空
        L_STRSQL := 'BEGIN itf_crm.PG_CRM_GETRESINFO.getImis' ||
                    v_ossdblink || '(:1,:2,:3,:4,:5); END; ';
        EXECUTE IMMEDIATE L_STRSQL
            USING IN new_V_TER_DEV_ID, IN V_TER_DEV_ID, OUT outXml, OUT outcode, OUT outMsg;