Common Instructions and skills for SQL plus in Oracle

After oracle9i is installed, enter the username, password, and host identifier to enter sal * plus.

System/manager

Sys/change_on_intall

Scott/tiger

Internal/oracle

The above is the initial password

The host string is null or @ + your service name. The local service name is not required for local login.

The following describes some common SQL * plus commands:

First, view the currently used database instance:

Select name from V $ database;

Switch two database instances

Connect in sqlplus

Or host set ORACLE_SID = sid

Or set ORACLE_SID = sid in cmd

Or modify the ORACLE_SID entry in the registry.

◆ 1. Execute an SQL script file

SQL> start file_name

SQL> @ file_name

We can save multiple SQL statements in a text file, so that when we want to execute all the SQL statements in this file, we can use any of the following commands, which is similar to batch processing in dos.

◆ 2. Edit the current input

SQL> edit

◆ 3. re-run the last running SQL statement

SQL>/

◆ 4. output the displayed content to the specified file

SQL> SPOOL file_name

All content on the screen is included in this file, including the SQL statement you entered.

◆ 5. Disable spool output

SQL> SPOOL OFF

The output content is displayed in the output file only when spool output is disabled.

◆ 6. display the structure of a table

SQL> desc table_name

◆ 7. COL command:

It mainly formats the display of columns.

This command has the following options:

COL [UMN] [{column | expr} [option...]

The Option can be the following clause:

ALI [AS] alias

CLE [AR]

FOLD_A [FTER]

FOLD_ B [EFORE]

FOR [MAT] format

HEA [DING] text

JUS [cipher] {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]

1). Change the default Column Title

COLUMN column_name HEADING column_heading

For example:

SQL> select * from dept;

DEPTNO DNAME LOC

-----------------------------------------------

10 ACCOUNTING NEW YORK

SQL> col LOC heading location

SQL> select * from dept;

Deptno dname location

------------------------------------------------

10 ACCOUNTING NEW YORK

2) Change the column NAME to the new column NAME "employee name" and put the new column NAME on two rows:

SQL> select * from emp

Department name Salary

------------------------------

10 aaa 11

SQL> COLUMN ENAME HEADING 'employee | name'

SQL> select * from emp

Employee

Department name Salary

------------------------------

10 aaa 11

Note: the col heading turn into two lines from one line.

3). Change the display length of the column:

FOR [MAT] format

SQL> select empno, ename, job from emp;

EMPNO ENAME JOB

-----------------------------

7369 SMITH CLERK

7499 ALLEN SALESMAN

7521 WARD SALESMAN

SQL> col ename format a40

EMPNO ENAME JOB

-----------------------------------------------------------

7369 SMITH CLERK

7499 ALLEN SALESMAN

7521 WARD SALESMAN

4). Set the column title alignment

JUS [cipher] {L [EFT] | C [ENTER] | C [ENTRE] | R [IGHT]}

SQL> col ename justify center

SQL>/

EMPNO ENAME JOB

-----------------------------------------------------------

7369 SMITH CLERK

7499 ALLEN SALESMAN

7521 WARD SALESMAN

For NUMBER-type columns, the column title is on the right by default, and for other types of columns, the column title is on the left by default.

5). prevent a column from being displayed on the screen.

NOPRI [NT] | PRI [NT]

SQL> col job noprint

SQL>/

EMPNO ENAME

--------------------------------------------------

7369 SMITH

7499 ALLEN

7521 WARD

6). format the display of NUMBER columns:

SQL> COLUMN SAL FORMAT $99,990

SQL>/

Employee

Department Name Salary Commission

---------------------------------------

30 ALLEN $1,600 300

7). If the column value is NULL when the column value is displayed, replace the NULL value with the text value.

Comm nul [L] text

SQL> COL COMM NUL [L] text

8). Set the rewinding Method for a column.

WRA [PPED] | WOR [D_WRAPPED] | TRU [NCATED]

COL1

--------------------

How are you?

SQL> COL COL1 FORMAT A5

SQL> COL COL1 WRAPPED

COL1

-----

HOW

RE YO

U?

SQL> COL COL1 WORD_WRAPPED

COL1

-----

HOW

ARE

YOU?

SQL> COL COL1 WORD_WRAPPED

COL1

-----

HOW

9). display the current display attribute value of the column

SQL> COLUMN column_name

10). Set the display attribute of all columns to the default value.

SQL> CLEAR COLUMNS

◆ 8. Block the same value displayed in a column

Break on break_column

SQL> BREAK ON DEPTNO

SQL> SELECT DEPTNO, ENAME, SAL

FROM EMP

Where sal <1, 2500

Order by deptno;

DEPTNO ENAME SAL

------------------------------

10 CLARK 2450

MILLER 1300

20 SMITH 800

ADAMS 1100

◆ 9. Shielding the display of the same value displayed in a column from the above, insert n blank rows before the value change whenever the column value changes.

Break on break_column SKIP n

SQL> BREAK ON DEPTNO SKIP 1

SQL>/

DEPTNO ENAME SAL

------------------------------

10 CLARK 2450

MILLER 1300

20 SMITH 800

ADAMS 1100

◆ 10. Display BREAK settings

SQL> BREAK

◆ 11. Delete settings 6 and 7

SQL> CLEAR BREAKS

◆ 12. Set command:

This command contains many sub-commands:

SET system_variable value

System_variable value can be one of the following clauses:

APPI [NFO] {ON | OFF | text}

ARRAY [SIZE] {15 | n}

AUTO [COMMIT] {ON | OFF | IMM [EDIATE] | n}

AUTOP [RINT] {ON | OFF}

AUTORECOVERY [ON | OFF]

AUTOT [RACE] {ON | OFF | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS]

BLO [CKTERMINATOR] {. | c}

CMDS [EP] {; | c | ON | OFF}

COLSEP {_ | text}

COM [PATIBILITY] {V7 | V8 | NATIVE}

CON [CAT] {. | c | ON | OFF}

COPYC [OMMIT] {0 | n}

COPYTYPECHECK {ON | OFF}

DEF [INE] {& | c | ON | OFF}

DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

ECHO {ON | OFF}

EDITF [ILE] file_name [. ext]

EMB [EDDED] {ON | OFF}

ESC [APE] {| c | ON | OFF}

FEED [BACK] {6 | n | ON | OFF}

FLAGGER {OFF | ENTRY | INTERMED [IATE] | FULL}

FLU [SH] {ON | OFF}

HEA [DING] {ON | OFF}

HEADS [EP] {| c | ON | OFF}

INSTANCE [instance_path | LOCAL]

LIN [ESIZE] {80 | n}

LOBOF [FSET] {n | 1}

LOGSOURCE [pathname]

LONG {80 | n}

LONGC [HUNKSIZE] {80 | n}

MARK [UP] HTML [ON | OFF] [HEAD text] [BODY text] [ENTMAP {ON | OFF}] [SPOOL

{ON | OFF}] [PRE [FORMAT] {ON | OFF}]

NEWP [AGE] {1 | n | NONE}

NULL text

NUMF [ORMAT] format

NUM [WIDTH] {10 | n}

PAGES [IZE] {24 | n}

PAU [SE] {ON | OFF | text}

RECSEP {WR [APPED] | EA [CH] | OFF}

RECSEPCHAR {_ | c}

SERVEROUT [PUT] {ON | OFF} [SIZE n] [FOR [MAT] {WRA [PPED] | WOR [D _

WRAPPED] | TRU [NCATED]}]

SHIFT [INOUT] {VIS [visible] | INV [ISIBLE]}

SHOW [MODE] {ON | OFF}

SQLBL [ANKLINES] {ON | OFF}

SQLC [ASE] {MIX [ED] | LO [WER] | UP [PER]}

SQLCO [NTINUE] {> | text}

SQLN [UMBER] {ON | OFF}

SQLPRE [FIX] {# | c}

SQLP [ROMPT] {SQL> | text}

SQLT [ERMINATOR] {; | c | ON | OFF}

SUF [FIX] {SQL | text}

TAB {ON | OFF}

TERM [OUT] {ON | OFF}

TI [ME] {ON | OFF}

TIMI [NG] {ON | OFF}

TRIM [OUT] {ON | OFF}

TRIMS [POOL] {ON | OFF}

UND [ERLINE] {-| c | ON | OFF}