1: Import data IMP80 system / manager @ orcl file = d: /temp/dbfile1.dmp full = n fromuser = yh1 Touser = YH2;
2: Multi-table update Update table1 a set a.column1 = (select b.column1 from table2 bwhere a.column2 = b.2column2) Where a.column3 is not null; 3: Date update Update YD_DayTRnHead set jyrq = to_date (' 20041206 '|| to_char (Jyrq,' HH24: MI: SS '),' YYYYMMDD HH24: MI: SS '), JYBH = 20 Jybh WHERE TO_CHAR (Jyrq,' YYYY-MM-DD ') =' 2003-01 -01 '; 4: Control is enhanced in the stored procedure to pay attention to the value of the select statement, to prevent the parameters from setting up S_SM: = SubStr (kmk.kmbm, 1, 3) ||'% '; Begin - acquisition of subjects Funded Subject SELECT KM INTO S_KMBM_TEMP from ZW_KMSZ WHERE FL = 'Ripping Document' And SM = S_SM; Exception When Others Then O_MESSAGE: = S_SM || 'The parameter is not set, please contact the administrator' || SQlerrm; Return ;
5: Delete Record in the data table
Delete repetitive records in the traffic violation data (at the same time [haptime], taxi number plate [Numberplate], penalties [REASON]) 1. Method principles: 1, Oracle, each record has a RowID, RowID is entirely The database is unique, and the RowID determines which data file, block, and rows of each record are in Oracle. 2. In the repeated record, the contents of all columns may be the same, but the ROWID will not be the same, so as long as it is determined that the maximum ROWID has the maximum ROWID, the rest is removed. Second, the realization methods: 1), to query a duplicate record select rowid, haptime, numberplate, reason from peccancy --delete from peccancy6 peccancy6where peccancy.rowid = (select max (rowid) from peccancy b where peccancy.haptime = b.haptime and! peccancy.numberplate = b.numberplate and peccancy.reason = b.reason) 2), delete duplicate records delete from peccancy peccancywhere peccancy.rowid! = (select max (rowid) from peccancy b where peccancy.haptime = b.haptime and peccancy .Numberplate = B.Numberplate and peccancy.reason = b.reason) - Prescription number is output in 10 format, such as less than 10 bits, give left to 补 0, RPAD (right 补 0) SELECT CFBH, LPAD (CFBH 10000, 10 , '0') cfbh1 from mz_jsls; 6. Check the record of the report number in the report construction information
Select * from cnbjxx where cngcbjh in (Select Cngcbjh from cnbjx = (select max (cngcid) from cnbjxx b where a.cngcbjh = b.cngcbjh))
