There is a correlation experiment for null values today. A discussion of NULL value insertions. 1, you can perform an insert ' or null operation in PL/SQL, provided the column is allowed to be empty. 2, you can perform a series of database operations on NULL. Such as:SELECT ROUND(To_number (MAX(a.in_station_time)-sysdate) ) * fromSFISM4. r_wip_tracking_t AWHERE
a.in_station_time is NULL
Summary: The corresponding to_number (NULL) is also feasible, MAX is also feasible, no problem.
3, return to the theme, SQL originally look like to make this kind of:
INSERT into SFISM4. r_dt_pcb_overtime_t F(F.serial_number,F.model_name,F.line_name,F.production_time,F.intercept_time,F.expriatuion_hour,F.state_flag,f.send_user)(SELECT ' COCO111111111 ', ' NIUBI1 ', ' 15555 ', NVL(NULL,MAX(a.in_station_time)), sysdate, NVL(ROUND(To_number (MAX(a.in_station_time)-sysdate) ) * , ' 0 '), ' 1 ', ' 1 ' From SFISM4. r_wip_log_t E WHERE e.serial_number = ' COCO111111111 ') and E.group_name = ' Smts ')
As a result, a piece is not inserted after the partial execution. Insert operation, according to the subquery, if the subquery is empty, then nothing will be inserted.
4, the climax is coming--after removing the where in the subquery, that is, the select condition is definitely a value, it actually inserts all the records in Table E. Ah, fortunately the test library, the amount of data is small, but also executed 5s. Well, that's unusual. Delete all! Depending on the result set insert or need to be cautious ah, otherwise how much garbage data in practice.
5, return to the original intention. The result is a separate determination of null values to prevent accidental mass data generation.
From for notes (Wiz)
6 performing experiments on Oracle null fields and Pl./sql
