6 performing experiments on Oracle null fields and Pl./sql

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.