火龙果软件Oracle公司内部数据库培训资料.pptVIP

Conditional FIRST INSERT (continued) The example in the slide inserts rows into more than one table, using one single INSERT statement. The SELECT statement retrieves the details of department ID, total salary, and maximum hire date for every department in the EMPLOYEES table. This INSERT statement is referred to as a conditional FIRST INSERT, as an exception is made for the departments whose total salary is more than $25,000. The condition WHEN ALL 25000 is evaluated first. If the total salary for a department is more than $25,000, then the record is inserted into the SPECIAL_SAL table irrespective of the hire date. If this first WHEN clause evaluates to true, the Oracle Server executes the corresponding INTO clause and skips subsequent WHEN clauses for this row. For the rows that do not satisfy the first WHEN condition (WHEN SAL 25000), the rest of the conditions are evaluated just as a conditional INSERT statement, and the records retrieved by the SELECT statement are inserted into the HIREDATE_HISTORY_00, or HIREDATE_HISTORY_99, or HIREDATE_HISTORY tables, based on the value in the HIREDATE column. The feedback 8 rows created can be interpreted to mean that a total of eight INSERT statements were performed on the base tables SPECIAL_SAL ,HIREDATE_HISTORY_00, HIREDATE_HISTORY_99, and HIREDATE_HISTORY. Instructor Note In order to demonstrate the code example in the slide, you must first run the script files lab\cre_special_sal.sql, lab\cre_hiredate_history_99.sql lab\cre_hiredate_history_00.sql and lab\cre_hiredate_history.sql Pivoting INSERT (continued) In the example in the slide, the sales data is received from the nonrelational database table SALES_SOURCE_DATA, which is the details of the sales performed by a sales representative on each day of a week, for a week with a particular week ID. DESC SALES_SOURCE_DATA Instructor Note In order to demonstrate the code example in the slide, you must first run the script files lab\cre_sales_source_data