oracle 单引号拼凑和动态sql

a.单引号问题（'）

   在oracle中4个单引号''''代表一个单引号'。在动态sql拼凑中我们经常需要拼凑单引号。比如：

  变量 中带单引号：IV_DATE_FORMAT:=''''||'YYYYMM'||'''';

  动态sql中用单引号：sqlStr := 'select * from tbName where name = ' ||''''||nameValue||'''';

b.动态sql执行和传值（EXECUTE IMMEDIATE ）

  例子：update

  EXECUTE IMMEDIATE 'UPDATE TF_B_DAILYSALES_STAT SET '||IV_COL_NAME||'=:1'||
                                                           ' WHERE RES_KIND_CODE = :2'||
                                                             ' AND BRAND_CODE = :3'||
                                                             ' AND MODEL_CODE = :4'||
                                                             ' AND EPARCHY_CODE = :5'||
                                                             ' AND CITY_CODE = :6'||
                                                             ' AND STOCK_ID = :7'||
                                                             ' AND SALE_TYPE = :8'||
                                                             ' AND SUBSTR(OPER_DATE_STR,1,6) = TO_CHAR(SYSDATE-1, '||IV_DATE_FORMAT||')'
             USING RS.SALE_NUM, RS.RES_KIND_CODE, RS.BRAND_CODE, RS.MODEL_CODE, RS.EPARCHY_CODE, RS.CITY_CODE, RS.STOCK_ID, RS.SALE_TYPE;

 例子：insert

 EXECUTE IMMEDIATE 'INSERT INTO TF_B_DAILYSALES_STAT'||
                                            '(TRADE_ID,'||
                                            ' OPER_DATE_STR,'||
                                            ' OPER_TIME,'||
                                            ' STAT_TYPE,'||
                                            ' RES_KIND_CODE,'||
                                            ' BRAND_CODE,'||
                                            ' MODEL_CODE,'||
                                            ' PROVINCE_CODE,'||
                                            ' EPARCHY_CODE,'||
                                            ' CITY_CODE,'||
                                            ' STOCK_ID,'||
                                            ' SALE_TYPE,'||IV_COL_NAME||
                                            ')VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)'
              USING RS.TRADE_ID, RS.OPER_DATE_STR, RS.OPER_TIME, RS.STAT_TYPE, RS.RES_KIND_CODE, RS.BRAND_CODE,
                    RS.MODEL_CODE, RS.PROVINCE_CODE, RS.EPARCHY_CODE, RS.CITY_CODE, RS.STOCK_ID, RS.SALE_TYPE, RS.SALE_NUM;

  说明:使用动态sql绑定变量形式可以达到sql存储共享，提高执行性能，比动态拼凑好sql再执行效率高。