Oracle Sqlplus Common Settings

Oracle Sqlplus Common Settings§2.5 sqlplus Common Settings

Most of the necessary environment settings are needed to complete the output we need.

The settings for all environments are completed by the SET command plus the corresponding environment variable. The following are the common environment settings:

§2.5.1 ARRAYSIZE (number of rows fetched)
SET Array[size]{integer}

The number of rows that can fetch (fetch) at a time, 1->5000, should be smaller when there are longer fields.


§2.5.2 autocommit (Auto-submit)
SET AUTO [COMMIT] {[OFF | On | IMM | N]}
Used to automatically commit or partially commit or not automatically commit in an operation.
1) or IMM causes uncommitted changes to be submitted to the database system immediately upon completion of each SQL statement. On
2) allows the number of commands that can be executed after the user has committed a commit (commits the changes made by n SQL statements). N
3) Stop auto-commit, the user must use the commit command to be committed. OFF

§2.5.3 linesize (line display width)
You can set the LINESIZE environment variable to control the display width of a row, which is 80 characters by default.
SET Lin[esize]{80|integer}
Integer = Sets the line width (number of characters), the maximum value of 999, such as:
Sql>set linesize 160

§2.5.4 Long (length-type display bytes)
In the default sql> state, the,sql> buffer is used to display a long byte count of only 80 characters. If we need to query columns that contain a long field, we need to set the long buffer to a larger size.
SET Long{80|integer}
Integer is the maximum width for displaying or copying a Long value, n=1->32767 (but must be less than Maxdata value)
Sql>show Maxdata (max line width)
Sql>set Long 2000

§2.5.5 PAGESIZE (page lines)
In the default sql> state, the,sql> buffer shows the number of rows in the page as 24 rows, with 22 rows showing the data and 2 rows showing the headings and dashes. We'll set the pagesize larger to reduce the hint header and dash.
SET pag[esize] {24|integer}
Sql>set PageSize 66

§2.5.6 Pause (pause)
You can set PAUSE to on or off to control the screen display. When set to ON, pressing the ENTER key after the SELECT statement is issued to display a screen.
SET PAUSE [on | OFF]
Sql> Setpause on

Tip: After you issue the SELECT statement and press ENTER, press ENTER again to display the results.

§2.5.7 Space (space between columns)
Use set space to set the number of spaces between columns, with the following syntax:
SET Spa[ce] {1|n}
n Sets the number of spaces between the output columns, up to a maximum of 10.
Sql>set Space 2

Recommendation: In general, do not set the space parameter.

§2.5.8 termout (Start/STOP screen display)
The termout is used to set the display or not display the output information on the screen.
SET Termout {on | OFF}

Set Termout off
Set Termout on

Set Termout off common spool xxx, that is, turn off the display of the report on the screen (save time)
Set termout on common spool off, that is, restore the display of the report on the screen

§2.5.9 ECHO (Start/stop command Display)
You can use the echo command to display or not display the SQL commands that are executed. Syntax such as:
SET echo{off| on}
Show commands in execution (when start is used)
Set ECHO is affected by set Termout

Set PageSize 100
Set echo on
Select table_name from dict where rownum<20;
SELECT * FROM Cat where rownum<30;
Set echo off
--The following shows only the results do not show commands:
Select table_name from dict where rownum<20;
SELECT * FROM Cat where rownum<30;

§2.5.10 TRANSACTION (Start transaction)
A very important transaction environment setting is transaction. It includes two sections of content:
SET TRANSACTION {READ only | Use ROLLBACK SEGMENT segment_name}
Read only is used to guarantee the consistency of reads. That is, changes to other users do not affect the current query results.
The use ROLLBACK SEGMENT segment_name specifies a dedicated rollback segment for the transaction currently being processed. This is mainly when a large number of INSERT or delete or update is required, and a large rollback segment is needed to ensure that the transaction is completed properly. See the database administrator for details.

§2.5.11 SHOW All (list all parameters)
You can use Show all to display all current parameter conditions. It's very simple to use. Like what:

Sql> Show All
AppInfo is on and set to "Sql*plus"
ArraySize 15
Autocommit OFF
Autoprint OFF
AutoRecovery OFF
Autotrace OFF
Blockterminator "." (Hex 2e)
Btitle OFF and the first few characters of the next SELECT statement
Cmdsep OFF
Colsep ""
Compatibility version NATIVE
Concat "." (Hex 2e)
Copycommit 0
Copytypecheck is on
Define "&" (Hex 26)
Describe DEPTH 1 linenum OFF INDENT on
Markup HTML off SPOOL off Entmap on Preformat off
Echo OFF
Editfile "Afiedt.buf"
Embedded OFF
Escape OFF
Flagger OFF
Flush on
Heading on
Headsep "|" (Hex 7c)
Instance "Local"
Linesize 80
Lno 14
Loboffset 1
Logsource ""
Long 80
Longchunksize 80
NewPage 1
Null ""
Numformat ""
Numwidth 10
PageSize 14
Pause is off
Pno 0
Recsep WRAP
Recsepchar "" (Hex 20)
Release 801070000
Repfooter OFF and is null
Repheader OFF and is null
Serveroutput OFF
Shiftinout INVISIBLE
Showmode OFF
Spool OFF
Sqlblanklines OFF
Sqlcase MIXED
Sqlcode 0
Sqlcontinue ">"
Sqlnumber on
Sqlprefix "#" (Hex 23)
Sqlprompt "Sql>"
Sqlterminator ";" (Hex 3b)
suffix "SQL"
tab on
Termout on
Time OFF
Timing OFF
Trimout on
Trimspool OFF
Ttitle OFF and the first few characters of the next SELECT statement
Underline "-" (Hex 2d)
User is "SYS"
Verify on
Wrap: Row is wrapped
Sql>
You can see its current value from the parameters above, or you can modify the values of some parameters.
Formatted output
§2.6.1 formatted output for general data

Under Oracle's sql>, columns are often formatted with the column command, which is displayed in a certain format. The Colmun command syntax is as follows:

Col[umn] [{column | expr} [option_1 ... option_n]

Column: List name
Expr: A valid SQL expression
Option_1...option_n: Can be one of the following:
Ali[as] Alias
Cle[ar]
Fold_a[fter]
Fold_b[efore]
For[mat] Format
Hea[ding] Text
Jus[tify] {l[eft]| c[enter]| c[entre]| R[ight]}
Like {Expr|alias}
Newl[ine]
New_v[alue] Variable
nopri[nt]| PRI[NT]
Nul[l] Text
Old_v[alue] Variable
on| OFF
wra[pped]| wor[d_wrapped]| Tru[ncated]

Here is an explanation of commonly used keywords:
Alias gives the aliases of the dequeue, break and Coumn can refer to the defined aliases.
Clear cancels the definition of the column.
The format column displays formats as:
9999990 9 or 0 determines the maximum number of digits to display
9,999,999.99 displays the data according to commas and decimal points, if 0 is displayed as a space
099999 Show Front fill 0
$999,999.99 number Front plus dollar number
B99999 if 0, the result is blank
99999Mi If the number is negative, the minus sign is placed after the number (to the right), left by default
The 99999PR minus sign is enclosed in parentheses
9.999EEEE in scientific notation (must have 4 e)
The 999v99 number is multiplied by 10n, as 1234 becomes 123400.
Date format (MM/DD/YY)


Heading the display title of the column, such as:

Sql> col ename heading name format A10
Sql> select Ename,sal from emp;


Example:
Sql
COLUMN SALARY for $9,999,999.99
COLUMN last_name for A35.

§2.6.2 formatted output of date
The Oracle system provides a NLS_DATE_FORMAT environment variable to set the display format of the date. It can be used to complete the display according to the requirements of different formats, for example, according to the Chinese habit of yyyy mm month DD day and so on.

1. Display of system date Sysdate
Use Sysdate to display the date and time of the machine on which the Oracle RDBMS resides, such as:
Sql> alter session Set Nls_date_format = ' "A.D." yyyy "MM" month "DD" Day ";

The session has changed.

Sql> select Sysdate from dual;

Sysdate
------------------
May 30, 2001 CE


2. Display of date types

Select Sysdate,to_char (sysdate, ' yyyy.mm.dd hh24:mi;ss ') from dual;

Sql> Connect Scott/tiger
is connected.
Sql> alter session Set Nls_date_format = ' yyyy ' year "MM" month "DD" "Day";

The session has changed.
Sql> Col hiredate heading Birthday
Sql> Col sal heading Wages
sql> Col Sal ename Name
Sql> select Ename,sal,hiredate from emp;

Name Salary Birthday
---------- ---------- -----------------
SMITH 800 born December 17, 1980
ALLEN 1600 born February 20, 1981
WARD 1250 born February 22, 1981
JONES 2975 born April 02, 1981
MARTIN 1250 September 28, 1981-born
BLAKE 2850 born May 01, 1981
CLARK 2450 born June 09, 1981
SCOTT 3000 born April 19, 1987
KING 5000 born November 17, 1981
TURNER 1500 September 08, 1981-born
ADAMS 1100 born May 23, 1987
JAMES 950 born December 03, 1981
FORD 3000 born December 03, 1981
MILLER 1300 born January 23, 1982

14 rows have been selected.
§2.7 Plus title
Sometimes when you output some results, you may need to add some headings, such as the top header above the table, the inscription, and so on. Such requirements can be done by Ttitle and Btitle.
Ttitle and Btitle

Ttitle [center|left|right]string Top title
Btitle [Center|left|right]string Bottom title
Ttitle Center ' XX Company personnel situation table '
Btitle left ' watchmakers: xxxx ' right ' date: xxxx year XX month '
Clear Ttitle

§2.8 Creating a simple report
We can set the display format of query results with Ttitle, Btitle, COLUMN, break on, COMPUTE sum and set linesize, set PAGESIZE, set NewPage ; In the spool command to output the display results to an operating system file, the general output file type is. Lst.

Creating a simple report is accomplished using the following command:

1. Spool command

SPOOL FileName Writes the contents of the buffer to a file
SPOOL off Write command

2. COLUMN command
Column Col_name[,heading] Format Format_spe
Specify the result of a field as an output format
COL name heading ' name ' for A10
COL sal heading ' Wages ' for 9,999.99

3.ttitle, Btitle

Ttitle [center|left|right]string Top title
Btitle [Center|left|right]string Bottom title
Ttitle Center ' XX Company personnel situation table '
Btitle left ' watchmaker: Zhao Yuanjie ' right ' date: 1998.11 months '
Clear Ttitle

4.break, COMPUTE

Clear Breaks,clear computes
Break on column breaks on this column
Break on row breaks on each line
Break on Page
Break on the report
Skip N skips N rows
Skip page skips pages that are not exhausted
COMPUTE AVG
Compute count
Compute Max
Compute min
COMPUTE STD
Compute sum
COMPUTE var
Compute NUM Calculates all rows
Compute sum of Sal on Deptno

5.set settings in a report
L set Termout off, set termout on command
L set Termout off common spool xxx before, that is, to turn off the display of the report on the screen (save time)
L set termout on common spool off, that is, restore the display of the report on the screen
L Set echo{off| On} displays commands in execution (i.e. when start is used), set ECHO is affected by set Termout
L Set Lin[esize]{80|integer} sets the line width, the maximum value 999
L Set Pag[esize] {24|integer} setting the size of the page

Example:
Sql>col ename heading ' name ' for A12
Sql>col sal heading ' Wages ' for a999,999.99
Sql>col hiredate Heading ' Born '
Sql>set Linesize 200
Sql>set PAGESIZE 60
Sql>spool c:\all_emp
Sql>select ename,sal,deptno,hiredate from emp order by DEPTNO;
Sql>spool OFF

§2.9 Input variables
Oracle provides a technique that can treat parameters as variables when working with SQL statements, that is, a variable rather than a specific value in a conditional sentence, which is the input variable. The purpose of this is to reuse the same statement each time, just enter the corresponding value. To implement a parameter as a variable, simply add a & number before the variable. Look at the following statement:

Select SID, Serial#,username, command from V$session
Where USERNAME = Upper (' &usr ');

When such a statement is run, the system automatically prompts you to answer the variable's specific values, and the information displayed when the statement is run-time prompts and answers is as follows:

sql> Select SID, Serial#,username, command from V$session
2 Where USERNAME = Upper (' &usr ');
Enter the value of USR: sys
Original value 2:where USERNAME = Upper (' &usr ')
New value 2:where USERNAME = upper (' sys ')

SID serial# USERNAME COMMAND
---------- ---------- ------------------------------ ----------
7 SYS 3


In the variable description, you can use multiple variables, such as:

Alter system kill session ' &sid,&ser ';
Or
Alter system kill session ' & session number,& serial number ';

It works as follows:

sql> Select SID, Serial#,username, command from V$session;

SID serial# USERNAME COMMAND
---------- ---------- ------------------------------ ----------
1 1 0
2 1 0
3 1 0
4 1 0
5 1 0
6 1 0
7 SYS 3
8 ZHAO 0

8 rows have been selected.

Sql> Alter System kill session ' &sid,&ser ';
Enter the value of the SID: 8
Enter the value of Ser: 16
Original value 1:alter system kill session ' &sid,&ser '
New value 1:alter system kill session ' 8,16 '

The system has changed.

General system lack of saving is to use the "&" symbol to define variables, you can also use other symbols instead, such as do not like to use & to use? , there are:

Sql> set define?
Sql> Select Sid,serial#,username from v$session where username= '? usr ';
Enter the value of USR: SYS
Original value 1:select sid,serial#,username from v$session where username= '? usr '
New value 1:select sid,serial#,username from v$session where username= ' SYS '

SID serial# USERNAME
---------- ---------- ------------------------------
7 SYS

Oracle Sqlplus Common Settings