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
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.

Inapoi la cuprins.

BogSoft 1999