Anexa

Acestia sunt pasii ce trebuie urmati :

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 (

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

/

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.

Anexa

GENERARE de Predicate "dinamice"