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