Anexa

Acestia sunt pasii ce trebuie urmati :


  1. Generati prima parte a comenzii CREATE TABLE. Retineti ca de asemenea noi selectam numele tabelei si o constanta. Aceste doua coloane vor fi folosite numai pentru a realiza sortarea. Ele nu vor mai fi tiparite la ultimul pas.

    
    EX.
    
    
    
            set feedback off
    
            set echo off
    
            set head off
    
    
    
            select distinct table_name sort1,
    
                  0             sort2,
    
                  'CREATE TABLE '|| table_name ||' ('
    
            from use_tab_columns
    
            /
    
    
    
    DEPT     0      CREATE TABLE DEPT (
    
    EMP      0      CREATE TABLE EMP  (
    
    SALGRADE 0      CREATE TABLE SALGRADE (
    
    
    
    

  2. Acum obtinem definitia completa a fiecarei coloane pentru fiecare tabla.

    
    EX.
    
    
    
    break  on sort1 skip 1
    
    select table_name sort1,
    
            column_id sort2,
    
            ' ' ||
    
            column_name ||
    
            decode(data_type, 'DATE',' DATE',
    
                              'LONG',' LONG'
    
                              'CHAR',' CHAR('||data_length||')',
    
                              'VARCHAR','VARCHAR('||data_length
    
                                                  ||')',
    
                              'NUMBER',
    
                               decode(data_precision,null,' NUMBER',
    
                                    ' NUMBER('||data_precision||','||
    
                                                    data_scale||') '),
    
                            '*********** INVALID DATA TYPE **************')||
    
                      decode(nulltable ,'N',' NOT NULL',null)||','
    
              from user_tab_columns
    
              order by table_name,column_id
    
    /
    
    
    
    

  3. Acum sa urmarim un exemplu complet. El consta din trei SELECT-uri combinate cu operatorul UNION. Primul genereaza comanda "CREATE TABLE" ,al doilea defineste coloanele pentru fiecare tabela mai putin ultima intrare si ultimul selecteaza ultima coloana . Tiparirea primei si celei de-a doua coloane ('sort1' si 'sort2') a fost eliminata folosind optiunea NOPRINT a comenzii COLUMN.

    
    EX.
    
    
    
    set feedback off echo off head off
    
    set pagesize 9999
    
    col sort1 noprint
    
    col sort2 noprint
    
    break on sort1 skip 1
    
    
    
            spool sqlcreates.sql
    
    
    
    select distinct table_name sort1,
    
                    0          sort2,
    
                    'CREATE TABLE '||table_name||' ('
    
    from user_tab_columns
    
    union
    
    select table_name sort1,
    
            column_id sort2,
    
            ' '||
    
            column_name||
    
            decode(data_type,'DATE',' DATE',
    
                             'LONG',' LONG',
    
                             'CHAR',' CHAR('||data_length||')',
    
                             'VARCHAR', 'VARCHAR('||data_length||')',
    
                             'NUMBER',
    
                                    decode(data_precision,null, 'NUMBER',
    
                                            'NUMBER('||data_precision||','||
    
                                                            data_scale||') '),
    
                            '******** INVALID DATA TYPE ********')||
    
                    decode(nullable,'N',' NOT NULL',null)||','
    
    from user_tab_columns c
    
    where column_id <(select max(column_id)
    
                      from user_tab_columns
    
                      where table_name = c.table_name)
    
    
    
    union
    
    select table_nae sort1,
    
            column_id sort2,
    
            ' '||   column_table||
    
            decode(data_type,'DATE',' DATE',
    
                             'LONG',' LONG',
    
                             'CHAR',' CHAR('||data_length||')',
    
                             'VARCHAR', 'VARCHAR('||data_length||')',
    
                             'NUMBER',
    
                                    decode(data_precision,null, 'NUMBER',
    
                                            'NUMBER('||data_precision||','||
    
                                                            data_scale||') '),
    
                            '******** INVALID DATA TYPE ********')||
    
                    decode(nullable,'N',' NOT NULL',null)||','
    
    /'
    
    from user_tab_columns c
    
    where column_id = (select max(column_id)
    
                       from user_tab_columns
    
                        where table_name = c.table_name)
    
    order by 1,2
    
    /
    
    spool off
    
    col sort1 clear
    
    col sort2 clear
    
    set head on feedback on echo on
    
    set pagesize 24
    
    
    
    Acest exemplu, relativ complicat produce urmatorul fisier :
    
    
    
    CREATE TABLE DEPT (
    
            DEPTNO NUMBER (2,0) NOT NULL,
    
            DNAME CHAR (14) ,
    
            LOC CHAR (13) )
    
    /
    
    
    
    CREATE TABLE EMP (
    
            EMPNO NUMBER (4,0) NOT NULL,
    
            ENAME CHAR (10) ,
    
            JOB   CHAR (9),
    
            MGR NUMBER (4,0) ,
    
            HIREDATE DAATE,
    
            SAL NUMBER (7,2),
    
            COMM NUMBER (7,2),
    
            DEPTNO NUMBER (2,0) NOT NULL)
    
    /
    
    
    
    CREATE TABLE SALGRADE (
    
            GRADE NUMBER (1,0) NOT NULL,
    
            LOSAL NUMBER (5,0),
    
            HISAL NUMBER (5,0) )
    
    /
    
    
    
    



GENERARE de Predicate "dinamice"



Este posibil sa capturam clauza "WHERE" intr-o substituire de variabila utilizind optiunea "NEW_VALUE" a comenzii "COLUMN". Exemplul permite utilizatorului sa genereze un raport al angajatilor din cadrul unui departament si/sau incepind cu o anumita data.


EX.



Contens of dynamic.SQL

accept deptno char prompt 'Enter deptno; for all'

accept hiredate char prompt 'Enter date ;  for all'



set termout off

col c1 new_value where_clause

select decode('&hiredate',

                null,' ',

                'where hiredate = '''||'&hiredate'||''''

                ),

       decode('&hiredate',

                numm,'where deptno = '||'||

                        ' and hiredate = '''||'&hiredate'||''''

                )

        ) c1

from sys.dual

/

set verify on

set termout on

select ename from emp &where_clause

/







start dynamic.sql

Enter deptno;  for all

Enter hiredate;  for all 4-jun-94

SQL> select ename

   2  from emp &where_clause

   3

SQL> /

old  2: from emp &where_clause

new  2: from emp where hiredate = '4-jun-94'


ENAME

----------

TURNER

ADAMS






start dynamic.sql

Enter deptno;  for all

Enter hiredate;  for all 4-jun-94

SQL> select ename

   2  from emp &where_clause

   3

SQL> /

old  2: from emp &where_clause

new  2: from emp where deptno = 20



ENAME ------------------ SMITH JONES SCOTT ADAMS

Aceasta tehnica poate fi utilizata de asemenea pentru a permite unui utilizator in timpul rularii sa specifice coloana dupa care sa se ordoneze fisierul de raport.

Retineti ca pentru a selecta un apostrof trebuie sa folositi doua.