oracle双引号拼接,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再执行效率高。