分享几条实用SQL

SELECT 'INSERT INTO owner.tableName (' || WMSYS.WM_CONCAT(t1.COLUMN_NAME)||') VALUES ('|| WMSYS.WM_CONCAT(':'||t1.COLUMN_NAME)||')' FROM user_col_comments t1 WHERE t1.TABLE_NAME=UPPER('tablename');--insert

 

结果：

INSERT INTO owner.tableName (ID,NPPNAME,MACID,DETECTOR_NAME,VARNAME,PHYVALUNIT,MAXPHYVAL,MINPHYVAL,VARORDER,CNDESC,UKVARNAME,ISNUMERIC,EQUATION,ISSESAME,ISSVGLABLE) VALUES (:ID,:NPPNAME,:MACID,:DETECTOR_NAME,:VARNAME,:PHYVALUNIT,:MAXPHYVAL,:MINPHYVAL,:VARORDER,:CNDESC,:UKVARNAME,:ISNUMERIC,:EQUATION,:ISSESAME,:ISSVGLABLE)

SELECT 'SELECT '|| WMSYS.WM_CONCAT('t1.'||t1.COLUMN_NAME)||' from owner.tableName  t1 ' FROM user_col_comments t1 WHERE t1.TABLE_NAME=UPPER('tablename');--select

 

结果：

SELECT t1.ID,t1.NPPNAME,t1.MACID,t1.DETECTOR_NAME,t1.VARNAME,t1.PHYVALUNIT,t1.MAXPHYVAL,t1.MINPHYVAL,t1.VARORDER,t1.CNDESC,t1.UKVARNAME,t1.ISNUMERIC,t1.EQUATION,t1.ISSESAME,t1.ISSVGLABLE from owner.tablename t1