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
<source lang="sql">
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.</source>
Spool result to a text file
<source lang="sql">
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></source>
Spool to a file with compute
<source lang="sql">
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</source>
Use Spoll to save query to a file
<source lang="sql">
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></source>
