Oracle 10g SQL Fundamentals II--App_c

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Writing Advanced Scripts : 

Writing Advanced Scripts

Objectives : 

Objectives After completing this appendix, you should be able to do the following: Describe the type of problems that are solved by using SQL to generate SQL Write a script that generates a script of DROP TABLE statements Write a script that generates a script of INSERT INTO statements

Using SQL to Generate SQL : 

Using SQL to Generate SQL SQL can be used to generate scripts in SQL The data dictionary: Is a collection of tables and views that contain database information Is created and maintained by the Oracle server SQL script SQL Data dictionary

Creating a Basic Script : 

Creating a Basic Script SELECT 'CREATE TABLE ' || table_name || '_test ' || 'AS SELECT * FROM ' || table_name ||' WHERE 1=2;' AS "Create Table Script" FROM user_tables;

Controlling the Environment : 

SPOOL dropem.sql SPOOL OFF Controlling the Environment Set system variables to appropriate values. Set system variables back to the default value. SQL STATEMENT

The Complete Picture : 

The Complete Picture SET ECHO OFF SET FEEDBACK OFF SET PAGESIZE 0 SELECT 'DROP TABLE ' || object_name || ';' FROM user_objects WHERE object_type = 'TABLE' / SET FEEDBACK ON SET PAGESIZE 24 SET ECHO ON

Dumping the Contents of a Table to a File : 

Dumping the Contents of a Table to a File SET HEADING OFF ECHO OFF FEEDBACK OFF SET PAGESIZE 0 SELECT 'INSERT INTO departments_test VALUES (' || department_id || ', ''' || department_name || ''', ''' || location_id || ''');' AS "Insert Statements Script" FROM departments / SET PAGESIZE 24 SET HEADING ON ECHO ON FEEDBACK ON

Dumping the Contents of a Table to a File : 

Dumping the Contents of a Table to a File

Generating a Dynamic Predicate : 

Generating a Dynamic Predicate COLUMN my_col NEW_VALUE dyn_where_clause SELECT DECODE('&&deptno', 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;

Summary : 

Summary In this appendix, you should have learned the following: You can write a SQL script to generate another SQL script. Script files often use the data dictionary. You can capture the output in a file.

authorStream Live Help