Oracle PL/SQL Tutorial/SQL PLUS Session Environment/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.
Spool result to a text file
SQL>
SQL> set heading off
SQL> set feedback off
SQL>
SQL> spool c:\ALIASES.TXT
SQL>
SQL> select object_name || "=" || substr(object_name,1,5) from user_objects wher
e object_type = "TABLE";
MYSTATS=MYSTA
WORKING_CUSTOMERS=WORKI
CUST_WITH_VARRAY_TEMP_TABLE=CUST_
P1=P1
IT=IT
CUST_NO_KEY_TABLE=CUST_
DEMO=DEMO
P=P
CUSTLOG=CUSTL
IX=IX
EMP_DELTAS=EMP_D
PARMS=PARMS
PASSWORD_AUDIT=PASSW
DEPT$AUDIT=DEPT$
INVENTORY_TBL=INVEN
TMP=TMP
EMPLOYEETEMPTABLE=EMPLO
SYSTEM_STATS=SYSTE
TEMP_TABLE=TEMP_
MYTABLE1=MYTAB
C1=C1
WORKING_EMPLOYEES=WORKI
SQL>
SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL>
Spool to a file with compute
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL>
SQL> --clear screen
SQL> set pause off
SQL> break on deptno skip page -
> on job
SQL>
SQL> compute sum label total of msal 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, msal, comm
2 from employees
3 order by deptno, job;
DEPTNO JOB EMPNO last_name MSAL
------ -------- ------ -------------------- ------
10 SALESREP 2 Jerry 1600
TRAINER 1 Jason 800
****** ******** ------
count
number
total 2400
DEPTNO JOB EMPNO last_name MSAL
------ -------- ------ -------------------- ------
20 DIRECTOR 9 Yellow 2300
MANAGER 4 Mary 1800
SALESREP 3 Jord 1700
****** ******** ------
count
number
total 5800
DEPTNO JOB EMPNO last_name MSAL
------ -------- ------ -------------------- ------
30 MANAGER 6 Black 2000
SALESREP 10 Pink 2400
5 Joe 1900
****** ******** ------
count
number
total 6300
DEPTNO JOB EMPNO last_name MSAL
------ -------- ------ -------------------- ------
40 MANAGER 7 Red 2100
TRAINER 8 White 2200
****** ******** ------
count
number
total 4300
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> drop table employees;
Table dropped.
SQL>
SQL> set pause off
Use Spoll to save query to a file
SQL>
SQL>
SQL> set linesize 500
SQL> set trimspool on
SQL> set embedded on
SQL> spool logmnr.opt
SQL> select
2 "colmap = " || user || " " || table_name || " (" ||
3 max( decode( column_id, 1, column_id , null ) ) ||
4 max( decode( column_id, 1, ", "||column_name, null ) ) ||
5 max( decode( column_id, 2, ", "||column_id , null ) ) ||
6 max( decode( column_id, 2, ", "||column_name, null ) ) ||
7 max( decode( column_id, 3, ", "||column_id , null ) ) ||
8 max( decode( column_id, 3, ", "||column_name, null ) ) ||
9 max( decode( column_id, 4, ", "||column_id , null ) ) ||
10 max( decode( column_id, 4, ", "||column_name, null ) ) ||
11 max( decode( column_id, 5, ", "||column_id , null ) ) ||
12 max( decode( column_id, 5, ", "||column_name, null ) ) || ");" colmap
13 from user_tab_columns
14 group by user, table_name
15 /
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle AVAIL_TRACE_FILES (1, USERNAME, 2, FILENAME, 3, DT);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle INTEREST (1, YR, 2, RATE);
colmap = sqle AUTHOR_PUBLICATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle DEPT_OR (1, DEPTNO, 2, DNAME, 3, LOC, 4, EMPS);
colmap = sqle BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle HOME_ADDRESS_LIST (1, LIST_ID, 2, HOME_ADDRESSES);
colmap = sqle MYCLOB (1, ID, 2, CLOB_DATA);
colmap = sqle CRS_OFFERINGS (1, COURSE_CODE, 2, DESCRIPTION, 3, BEGINDATE);
colmap = sqle BIN$2AYTRnCPRfG80DYlHN4sJg==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle HRC_ORG_SITE (1, HRC_CODE, 2, HRC_DESCR, 3, ORG_ID, 4, ORG_SHORT_NAME, 5, ORG_LONG_NAME);
colmap = sqle EMPLOYEE_EMPLOYEE_EVALUATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle EMPLOYEE_EVALUATION (1, ID, 2, TITLE, 3, WRITTEN_DATE);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle I2 (1, N, 2, V);
colmap = sqle DEPT20_V (1, EMPNO, 2, ENAME, 3, INIT, 4, JOB, 5, MGR);
colmap = sqle USER_AVAIL_TRACE_FILES (1, USERNAME, 2, FILENAME, 3, DT);
colmap = sqle T1_TIMES (1, XSTART, 2, XSTOP);
colmap = sqle WORKING_CUSTOMERS (1, CUST_NO, 2, LASTNAME, 3, FIRSTNAME, 4, MIDINIT, 5, STREET);
colmap = sqle STUDENT (1, STUDENT_ID, 2, PERFORMANCE, 3, GENDER, 4, ETHNICITY, 5, AGE);
colmap = sqle WORKING_EMPLOYEES (1, EMPL_NO, 2, LASTNAME, 3, FIRSTNAME, 4, MIDINIT, 5, STREET);
colmap = sqle EMP_V (1, EMPNO, 2, ENAME, 3, JOB, 4, MGR, 5, SAL);
colmap = sqle BIN$BIALfQCESHa/SipQl+oqGg==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle COURSE_DAYS (1, EMPNO, 2, ENAME, 3, DAYS);
colmap = sqle HAZARD_LEVEL_T (1, ID, 2, MAGNITUDE, 3, CODE, 4, DESCRIPTION, 5, ACTIVE_DATE);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle EMP_VIEW (1, ENAME, 2, EMPNO);
colmap = sqle DDL_AUDIT (1, OBJECT_NAME, 2, OBJECT_TYPE, 3, WHEN_CREATED, 4, WHO_CREATED, 5, WHEN_UPDATED);
colmap = sqle MYTABLE1_TIMES (1, XSTART, 2, XSTOP);
colmap = sqle I1 (1, N, 2, V);
colmap = sqle CRS_COURSE_SCHEDULE (1, COURSE_CODE, 2, DESCRIPTION, 3, BEGINDATE);
colmap = sqle EMPLOYEE_PUBLICATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle SALES_BY_ATLAS_V (1, PRODUCT_NAME, 2, SALESPERSON, 3, ORDER_DATE, 4, QUANTITY);
colmap = sqle SALES_PER_PERSON_V (1, SALESPERSON, 2, PRODUCT_NAME, 3, ORDER_DATE, 4, QUANTITY);
colmap = sqle AVG_EVALUATIONS (1, COURSE, 2, AVG_EVAL);
colmap = sqle APPLICATION_USERS (1, UNAME, 2, PW, 3, ROLE_TO_GRANT);
colmap = sqle SITE_TAB (1, SITE_NO, 2, SITE_DESCR);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle OUTRAIN (1, DY, 2, RAINFALL);
colmap = sqle DUMMY (1, COL1);
colmap = sqle BIN$uR9LzoavTbyjK4b4t8kgXg==$0 (1, ID, 2, WORKER_ID, 3, PHYSICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle BIN$ukEn3nqRT+u/paBe8fJpxQ==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle MAP (1, N, 2, I1, 3, I2);
colmap = sqle EMP_DEPT_CLUSTER (1, DEPTNO);
colmap = sqle HASH_CLUSTER (1, HASH_KEY);
41 rows selected.
SQL> spool off
SQL>