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再执行效率高。
