oracle10g英文原厂培训les

1、Manipulating Large Data SetsObjectivesAfter completing this lesson, you should be able to do the following:Manipulate data using subqueriesDescribe the features of multitable insertsUse the following types of multitable insertsUnconditional INSERTPivoting INSERTConditional ALL INSERTConditional FIRS

2、T INSERTMerge rows in a tableTrack the changes to data over a period of timeUsing Subqueries to Manipulate DataYou can use subqueries in DML statements to:Copy data from one table to anotherRetrieve data from an inline viewUpdate data in one table based on the values of another tableDelete rows from

3、 one table based on rows in a another tableCopying Rows from Another TableWrite your INSERT statement with a subquery.Do not use the VALUES clause.Match the number of columns in the INSERT clause with that in the subquery.INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, l

4、ast_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%;33 rows created.Inserting Using a Subquery as a TargetINSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM empl3 WHERE department_id = 50) VALUES (99999, Taylor, DTAYLOR, TO_DATE(07-JU

5、N-99, DD-MON-RR), ST_CLERK, 5000, 50);1 row created.Inserting Using a Subquery as a TargetVerify the results.SELECT employee_id, last_name, email, hire_date, job_id, salary, department_idFROM employeesWHERE department_id = 50;SELECT a.last_name, a.salary, a.department_id, b.salavgFROM employees a, (

6、SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) bWHERE a.department_id = b.department_idAND a.salary b.salavg;Retrieving Data with a Subquery as SourceUPDATE empl3SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employe

7、es WHERE employee_id = 168)WHERE employee_id = 114;1 row updated.Updating Two Columns with a SubqueryUpdate the job and salary of employee 114 to match the job of employee 205 and the salary of employee 168.UPDATE empl3SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)

8、WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);1 row updated.Updating Rows Based on Another TableUse subqueries in UPDATE statements to update rows in a table based on values from another table.DELETE FROM empl3WHERE department_id = (SELECT department_id FROM departments WHERE

9、 department_name LIKE %Public%);1 row deleted.Deleting Rows Based on Another TableUse subqueries in DELETE statements to remove rows from a table based on values from another table.Using the WITH CHECK OPTION Keyword on DML StatementsA subquery is used to identify the table and columns of the DML st

10、atement.The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM empl3 WHERE department_id = 50 WITH CHECK OPTION)VALUES (99998, Smith, JSMITH, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK

11、, 5000);INSERT INTO *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violationOverview of the Explicit Default FeatureWith the explicit default feature, you can use the DEFAULT keyword as a column value where the column default is desired.The addition of this feature is for compliance

12、 with the SQL:1999 standard.This allows the user to control where and when the default value should be applied to data.Explicit defaults can be used in INSERT and UPDATE statements.Using Explicit Default ValuesDEFAULT with INSERT:DEFAULT with UPDATE:INSERT INTO deptm3 (department_id, department_name

13、, manager_id) VALUES (300, Engineering, DEFAULT);UPDATE deptm3SET manager_id = DEFAULT WHERE department_id = 10;Overview of Multitable INSERT StatementsINSERT ALL INTO table_a VALUES(,) INTO table_b VALUES(,) INTO table_c VALUES(,) SELECT FROM sourcetab WHERE ;Table_aTable_bTable_cOverview of Multit

14、able INSERT StatementsThe INSERTSELECT statement can be used to insert rows into multiple tables as part of a single DML statement.Multitable INSERT statements can be used in data warehousing systems to transfer data from one or more operational sources to a set of target tables.They provide signifi

15、cant performance improvement over:Single DML versus multiple INSERTSELECT statementsSingle DML versus a procedure to do multiple inserts using IF.THEN syntaxTypes of Multitable INSERT StatementsThe different types of multitable INSERT statements are:Unconditional INSERTConditional ALL INSERTConditio

16、nal FIRST INSERTPivoting INSERTMultitable INSERT StatementsSyntaxconditional_insert_clauseINSERT ALL conditional_insert_clauseinsert_into_clause values_clause (subquery)ALL FIRSTWHEN condition THEN insert_into_clause values_clauseELSE insert_into_clause values_clause Unconditional INSERT ALLSelect t

17、he EMPLOYEE_ID, HIRE_DATE, SALARY, and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200.Insert these values into the SAL_HISTORY and MGR_HISTORY tables using a multitable INSERT.INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_histor

18、y VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;12 rows created. Conditional INSERT ALLSelect the EMPLOYEE_ID, HIRE_DATE, SALARY, and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID

19、 is greater than 200.If the SALARY is greater than $10,000, insert these values into the SAL_HISTORY table using a conditional multitable INSERT statement.If the MANAGER_ID is greater than 200, insert these values into the MGR_HISTORY table using a conditional multitable INSERT statement.Conditional

20、 INSERT ALLINSERT ALL WHEN SAL 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;4 rows created. Conditional INSERT FIRSTSelect t

21、he DEPARTMENT_ID, SUM(SALARY), and MAX(HIRE_DATE) from the EMPLOYEES table.If the SUM(SALARY) is greater than $25,000, then insert these values into the SPECIAL_SAL, using a conditional FIRST multitable INSERT.If the first WHEN clause evaluates to true, then the subsequent WHEN clauses for this row

22、should be skipped.For the rows that do not satisfy the first WHEN condition, insert into the HIREDATE_HISTORY_00, HIREDATE_HISTORY_99, or HIREDATE_HISTORY tables, based on the value in the HIRE_DATE column using a conditional multitable INSERT.Conditional INSERT FIRST INSERT FIRST WHEN SAL 25000 THE

23、N INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like (%00%) THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like (%99%) THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MA

24、X(hire_date) HIREDATE FROM employees GROUP BY department_id;12 rows created.Pivoting INSERTSuppose you receive a set of sales records from a nonrelational database table, SALES_SOURCE_DATA, in the following format:EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THUR, SALES_FRIYou want t

25、o store these records in the SALES_INFO table in a more typical relational format:EMPLOYEE_ID, WEEK, SALESUsing a pivoting INSERT, convert the set of sales records from the nonrelational database table to relational format.Pivoting INSERT INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_

26、MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,

27、sales_FRI FROM sales_source_data;5 rows created. The MERGE StatementProvides the ability to conditionally update or insert data into a database tablePerforms an UPDATE if the row exists, and an INSERT if it is a new row:Avoids separate updatesIncreases performance and ease of useIs useful in data wa

28、rehousing applicationsThe MERGE Statement SyntaxYou can conditionally insert or update rows in a table by using the MERGE statement.MERGE INTO table_name table_alias USING (table|view|sub_query) alias ON (join condition) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED

29、THEN INSERT (column_list) VALUES (column_values);MERGE INTO empl3 c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, . c.department_id = e.department_idWHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_n

30、ame, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, mission_pct, e.manager_id, e.department_id);Merging RowsInsert or update rows in the EMPL3 table to match the EMPLOYEES table.Merging RowsMERGE INTO empl3 c USING employees e ON (c.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET .WHEN NOT MATCHED THEN INSERT VALUES.;TRUNCATE TABLE empl3;SELECT * FROM empl3;no rows selectedSELECT * FROM empl3;107 rows selected.Track