1、Writing Advanced ScriptsObjectivesAfter completing this appendix, you should be able to do the following:Describe the type of problems that are solved by using SQL to generate SQLWrite a script that generates a script of DROP TABLE statementsWrite a script that generates a script of INSERT INTO stat

2、ementsUsing SQL to Generate SQLSQL can be used to generate scripts in SQLThe data dictionary:Is a collection of tables and views that contain database informationIs created and maintained by the Oracle serverSQL scriptSQLData dictionaryCreating a Basic ScriptSELECT CREATE TABLE | table_name | _test

3、| AS SELECT * FROM | table_name | WHERE 1=2; AS Create Table ScriptFROM user_tables; SPOOL dropem.sqlSPOOL OFF Controlling the EnvironmentSet system variables to appropriate values.Set system variables back to the default value.SQL STATEMENTThe Complete PictureSET ECHO OFF SET FEEDBACK OFFSET PAGESI

4、ZE 0SELECT DROP TABLE | object_name | ;FROM user_objectsWHERE object_type = TABLE/SET FEEDBACK ONSET PAGESIZE 24SET ECHO ONDumping the Contents of a Table to a FileSET HEADING OFF ECHO OFF FEEDBACK OFFSET PAGESIZE 0SELECT INSERT INTO departments_test VALUES ( | department_id | , | department_name |

5、, | location_id | ); AS Insert Statements ScriptFROM departments/SET PAGESIZE 24SET HEADING ON ECHO ON FEEDBACK ONDumping the Contents of a Table to a FileSourceX|department_name|, );ResultXAdministration,);Generating a Dynamic PredicateCOLUMN my_col NEW_VALUE dyn_where_clauseSELECT DECODE(&deptno,

6、null,DECODE (&hiredate, null, ,WHERE hire_date=TO_DATE(|&hiredate,DD-MON-YYYY),DECODE (&hiredate, null,WHERE department_id = | &deptno,WHERE department_id = | &deptno | AND hire_date = TO_DATE(|&hiredate,DD-MON-YYYY) AS my_col FROM dual;SELECT last_name FROM employees &dyn_where_clause;SummaryIn this appendix, you should have learned the following:You can write a SQL script to generate