Oracle PL/SQL/SQL Plus/SPOOL
Содержание
Extracts data from the emp table and writes it to a text file in a comma-delimited format
SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> --Set the linesize large enough to accommodate the longest possible line.
SQL> SET LINESIZE 80
SQL>
SQL> --Turn off all page headings, column headings, etc.
SQL> SET PAGESIZE 0
SQL>
SQL> --Turn off feedback
SQL> SET FEEDBACK OFF
SQL>
SQL> --Eliminate trailing blanks at the end of a line.
SQL> SET TRIMSPOOL ON
SQL>
SQL> SET TERMOUT OFF
SQL> SPOOL current_emps.csv
SQL> SELECT ""ID","Billing Rate","Hire Date","Name""
2 FROM dual;
"ID","Billing Rate","Hire Date","Name"
SQL>
SQL> SELECT TO_CHAR(emp_id) || ","
2 || TO_CHAR(rate) || ","
3 || TO_CHAR(hire_date,"MM/DD/YYYY") || ","
4 || """ || ename || """
5 FROM emp
6 WHERE end_date IS NULL;
101,169,11/15/1961,"Mary"
105,121,06/15/2004,"Mike"
107,45,01/02/2004,"Less"
111,100,08/23/1976,"Tike"
SQL> SPOOL OFF
SQL>
SQL> SET FEEDBACK ON
SQL>
SQL> drop table emp;
Table dropped.
Spooling files on and off
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> SPOOL c:\test.prn
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL> SPOOL OFF
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
SPOOL query result to a file
SQL>
SQL> BTITLE SKIP 2 CENTER "Page " SQL.PNO
SQL> BREAK ON table_name SKIP 1
SQL> COLUMN table_name FORMAT A20 HEADING "Table"
SQL> COLUMN column_name FORMAT A22 HEADING "Column"
SQL> COLUMN data_type FORMAT A10 HEADING "Data|Type"
SQL> COLUMN data_length FORMAT 999999 HEADING "Length"
SQL> COLUMN data_scale FORMAT 99999 HEADING "Scale"
SQL> COLUMN nullable FORMAT A5 HEADING "Null"
SQL>
SQL> SPOOL 4_1.lis
SQL> SELECT table_name, column_name, data_type,
2 DECODE(data_length, 22, data_precision,
3 data_length) data_length,
4 data_scale, nullable
5 FROM user_tab_columns
6 where rownum < 50
7 ORDER BY table_name, column_id;
no rows selected
SQL> SPOOL OFF
SQL>
SQL> SPOOL OFF
not spooling currently
SQL>
spool to a file with text string concatenation
SQL>
SQL>
SQL> set heading off
SQL> set feedback off
SQL> set echo off
SQL>
SQL> spool tabsyns.sql
SQL>
SQL> select "create public synonym " || table_name ||
2 " for " || table_name || ";"
3 from user_tables
4 where rownum < 50;
create public synonym MYTABLE_SESSION for MYTABLE_SESSION;
create public synonym DONE for DONE;
create public synonym EMP_CHANGES for EMP_CHANGES;
create public synonym AQ$_EMP_CHANGES_S for AQ$_EMP_CHANGES_S;
create public synonym AQ$_EMP_CHANGES_T for AQ$_EMP_CHANGES_T;
create public synonym AQ$_EMP_CHANGES_H for AQ$_EMP_CHANGES_H;
create public synonym COMPILE_SCHEMA_TMP for COMPILE_SCHEMA_TMP;
create public synonym TEMP_EMP for TEMP_EMP;
create public synonym UPPER_ENAME for UPPER_ENAME;
create public synonym SYS_IOT_OVER_16251 for SYS_IOT_OVER_16251;
create public synonym AQ$_EMP_CHANGES_G for AQ$_EMP_CHANGES_G;
create public synonym AQ$_EMP_CHANGES_I for AQ$_EMP_CHANGES_I;
create public synonym DEPT_AND_EMP for DEPT_AND_EMP;
create public synonym EMP_REG for EMP_REG;
create public synonym SESS_EVENT for SESS_EVENT;
create public synonym PAGES for PAGES;
SQL>
SQL> spool off
SQL> set echo on
SQL> set heading on
SQL> set feedback on
SQL>
SQL>
SQL>
spool to report.txt replace
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> set pause off
SQL> break on deptno skip page -
> on job
SQL>
SQL> compute sum label total of sal on deptno
SQL> compute count number of comm on deptno
SQL>
SQL> set trimspool on
SQL> set feedback off
SQL> spool report.txt replace
SQL>
SQL> select deptno, job, empno, ename, sal, comm
2 from emp
3 order by deptno, job;
DEPTNO JOB EMPNO ENAME SAL COMM
------ -------- ------ -------- ------ ------
10 Designer 7 Chris 2450 [N/A]
9 Peter 5000 [N/A]
Manager 14 Mike 1300 [N/A]
****** ******** ------ ------
count 0
number 3
total 8750
DEPTNO JOB EMPNO ENAME SAL COMM
------ -------- ------ -------- ------ ------
20 Designer 4 Jane 2975 [N/A]
TRAINER 8 Smart 3000 [N/A]
11 Ana 1100 [N/A]
13 Fake 3000 [N/A]
1 Tom 800 [N/A]
****** ******** ------ ------
count 0
number 5
total 10875
DEPTNO JOB EMPNO ENAME SAL COMM
------ -------- ------ -------- ------ ------
30 Designer 6 Black 2850 [N/A]
Manager 12 Jane 800 [N/A]
Tester 10 Take 1500 0
2 Jack 1600 300
3 Wil 1250 500
5 Mary 1250 1400
****** ******** ------ ------
count 4
number 6
total 9250
SQL>
SQL> spool off
SQL>
SQL> clear computes
computes cleared
SQL> clear breaks
breaks cleared
SQL> set pause on
SQL> set feedback on
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>