SQL series (basic)-preface supplementary knowledge before class

Preface supplement knowledge before class


Number (p, s)
Number (p, s): Number type. p indicates the effective length of the Number (from the left 1st digits of the Number not 0
Until the rightmost length. value range: 0 ~ 38 digits), and s indicates the precision of the number (that is, the number of digits on the right of the decimal point. The value range is-84 ~ 127 bits );
Varchar2 (s)
Varchar2 (s): variable-length character type. value range: 1 ~ 4000 bits;
Char (s)
Char (s): Specifies the length of a string. value range: 1 ~ 2000 bits;
Date
Date: Time type, indicating the year, month, and day of the time. It has no length or precision. The value range is from January 1, to January 1 ~ December 31, 9999


1. Use the sav command
1.1 run the sav command to save the file content.
TYGER @ ORCL> create table t as select * from user_tables;


Table created.


TYGER @ ORCL> select count (*) from t;


COUNT (*)
----------
11


TYGER @ ORCL> sav/home/oracle/sel_t. SQL
Created file/home/oracle/sel_t. SQL
TYGER @ ORCL>!
[Oracle @ tyger ~] $ Ls
Sel_t. SQL


[Oracle @ tyger ~] $ Cat sel_t. SQL
Select count (*) from t
/
[Oracle @ tyger ~] $


1.2 overwrite content sav filename rep
TYGER @ ORCL> l
1 * select * from t order by 1
TYGER @ ORCL> sav/home/oracle/sel_t. SQL rep
Wrote file/home/oracle/sel_t. SQL
TYGER @ ORCL>!
[Oracle @ tyger ~] $ Cat sel_t. SQL
Select * from t order by 1
/
[Oracle @ tyger ~] $


1.3 Append content sav filename app
[Oracle @ tyger ~] $ Cat sel_t. SQL
Select * from t order by 1
/
[Oracle @ tyger ~] $ Exit
Exit


TYGER @ ORCL> select count (*) from t;


COUNT (*)
----------
11


TYGER @ ORCL> sav/home/oracle/sel_t. SQL app
Appended file to/home/oracle/sel_t. SQL
TYGER @ ORCL>!
[Oracle @ tyger ~] $
[Oracle @ tyger ~] $ Cat sel_t. SQL
Select * from t order by 1
/
Select count (*) from t
/
[Oracle @ tyger ~] $


2. Use the spool command
Spool on indicates that the cache is opened. Otherwise, only one recently executed command can be saved;
Spool filename indicates to output the commands and results in the cache to a file;
Spool off indicates that the cache is disabled and files are automatically saved;


[Oracle @ tyger ~] $ Sqlplus/as sysdba


SQL * Plus: Release 10.2.0.1.0-Production on Wed Jun 18 13:52:25 2014


Copyright (c) 1982,200 5, Oracle. All rights reserved.




Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options


SYS @ ORCL> spool/home/oracle/sel_emp. SQL
SYS @ ORCL> conn scott/tiger;
Connected.
SCOTT @ ORCL> select empno, ename from emp;


EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS


EMPNO ENAME
--------------------
7900 JAMES
7902 FORD
7934 MILLER


14 rows selected.


SCOTT @ ORCL> spool off;
SCOTT @ ORCL> exit


[Oracle @ tyger ~] $ Cat sel_emp. SQL
SYS @ ORCL> conn scott/tiger;
Connected.
SCOTT @ ORCL> select empno, ename from emp;


EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS


EMPNO ENAME
--------------------
7900 JAMES
7902 FORD
7934 MILLER


14 rows selected.


SCOTT @ ORCL> spool off;
[Oracle @ tyger ~] $