Oracle NULL column and PL./SQL Execution Experiment

Oracle NULL column and PL./SQL Execution Experiment

Today, we have some experiments on Oracle NULL values. We will discuss how to insert NULL values.

1. You can insert ''or NULL in PL/SQL, provided that the column is allowed to be empty.

2. You can perform a series of database operations on NULL. For example:

Select round (TO_NUMBER (MAX (A. IN_STATION_TIME)-SYSDATE) * 24 FROM SFISM4.R _ WIP_TRACKING_T A WHERE

A. IN_STATION_TIME IS NULL

Conclusion: The corresponding TO_NUMBER (NULL) is also feasible, and MAX is also feasible, no problem.

3. Return to the topic. The original format of SQL is as follows:

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 'co111111111111111111 ',

'Niubi1 ',

'123 ',

NVL (NULL, MAX (A. IN_STATION_TIME )),

SYSDATE,

NVL (ROUND (TO_NUMBER (MAX (A. IN_STATION_TIME)-SYSDATE) * 24, '0 '),

'1 ',

'1'

FROM SFISM4.R _ WIP_LOG_T E

Where e. SERIAL_NUMBER = 'co111111111') and e. GROUP_NAME = 'smts ')

Result: After partial execution, no Insert. Insert operation is performed for one entry. According to the subquery, if the subquery is empty, nothing is inserted.

4. the climax -- after removing the WHERE clause in the subquery, that is, the SELECT condition must have a value, and insert all records in Table E. ah, thanks to the test database, the data volume is small, but it is also executed for 5 s. okay, exception. delete all! We can see that we still need to be careful when inserting the result set. Otherwise, we will put the amount of junk data into reality.

5. Regression intention. The final determination of NULL values will prevent unexpected large amounts of data.

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------