SQL * Plus command usage

xiaoxiao2021-03-06  55

1. Perform a SQL script file

SQL> Start file_name

SQL> @ file_name

We can save multiple SQL statements in a text file so that when all SQL statements in this file are executed, use any of the above commands, which is similar to the batch in DOS.

2. Edit the current input

SQL> Edit

3. Run the last run SQL statement

SQL> /

4. Output 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. Turn off Spool output

SQL> spool off

Only the output content is only seen in the output file only if the Spool output is turned off.

6. Display a structure of a table

SQL> Desc Table_Name

7. COL command:

The display form of the main formatted column.

There are many options that this command is as follows:

Col [umn] [{colorn | evr} [option ...]]

The Option option can be the following clause:

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]

1) Change the default column header

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 ENAME to the new column name Employee Name and put the new column name on both lines:

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 alignment of the column header

Jus [TIFY] {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, column headers are default on the right, other types of columns are default on the left

5) Don't let a column 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 Types:

SQL> Column Sal Format $ 99,990

SQL> /

EMPLOYEE

Department Name Salary Commsion

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

30 Allen $ 1,600 300

7). When the column value is displayed, if the column value is null value, use the text value to replace the null value.

Comm Nul [l] text

SQL> Colone Nul [L] Text

8). Set up a column winding method

WRA [PPED] | Wor [D_Wrapped] | TRU [ncated]

COL1

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

How are you?

SQL> Col ​​Col1 Format A5

SQL> Col ​​col1 wrapped

COL1

-----

How a

Re yo

U?

SQL> col col1 word_wrapped

COL1

-----

HOW

ARE

YOU?

SQL> col col1 word_wrapped

COL1

-----

How a

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

SQL> Column Column_name

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

SQL> CLEAR columns8. Shield off the same value displayed in a column

Break on Break_Column

SQL> BREAK on Deptno

SQL> Select Deptno, ENAME, SAL

From EMP

WHERE SAL <2500

ORDER by deptno;

Deptno ename

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

10 CLARK 2450

Miller 1300

20 Smith 800

ADAMS 1100

9. In the display of the same value displayed in a column, the NS is inserted before the value change is changed.

Break on Break_Column Skip N

SQL> Break on Deptno Skip 1

SQL> /

Deptno ename

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

10 CLARK 2450

Miller 1300

20 Smith 800

ADAMS 1100

10. Show settings to BREAK

SQL> BREAK

11. Delete 6,7 settings

SQL> CLEAR BREAKS

12. SET command:

This command contains many subcommands:

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 [il] file_name [.ext]

EMB [edde] {on | OFF}

ESC [APE] {/ | C | ON | OFF}

Feed [back] {6 | N | on | OFF}

Flagger {OFF | Entry | Intermediate [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 [omat] 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 [ible] | 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}

Ver [IFY] {ON | OFF}

WRA [P] {ON | OFF}

1). Set whether the current session is automatically submitted to the modified data

SQL> SETO [COMMIT] {ON | OFF | IMM [ediate] | n}

2). When performing a SQL script with the start command, whether the SQL statement in the script is displayed in the script

SQL> Set echo {on | OFF}

3) Whether the number of rows that current SQL statements query or modify

SQL> Set feed [back] {6 | N | ON | OFF}

The default only shows the number of lines of the results in the result of greater than 6 rows. If SET feedback 1, no matter how many rows are queried. When it is OFF, the number of lines of the query will not be displayed.

4) Whether to display the column header

SQL> Set Hea [Ding] {ON | OFF}

When SETHEADING OFF, the column header is not displayed on the top of each page, but instead of blank line.

5). Set the number of characters that can be accommodated

SQL> SET Lin [Esize] {80 | N}

If the output content of a row is greater than the number of characters that can be accommodated, the fold is displayed.

6). Settings between page and pages

SQL> Set newp [age] {1 | n | none}

When SET NewPage 0, there is a small black box at the beginning of each page.

When SET NewPage N, there is a n space between the pages and pages.

When Set NewPage None, there is no interval between page and pages.

7). When displaying, use the TEXT value to replace the NULL value.

SQL> Set Null Text

8). How many numbers have been set one page

SQL> SET PAGES [Ize] {24 | N}

If set to 0, all output content is a page and does not display columns

9). Whether to display the information output with the dbms_output.put_line package.

SQL> SET Serverout [PUT] {ON | OFF}

When writing a stored procedure, we sometimes use dbms_output.put_line to output the necessary information to debug the stored procedure, only after setting the serveroutput variable to ON, information can be displayed on the screen.

10). When the length of the SQL statement is greater than the LINSIZE, it is intercepted the SQL statement when displayed.

SQL> SET WRA [P] {ON | OFF}

When the length of the output row is greater than the length of the set row (set with the set lineize n command), when the SET WRAP is ON, the output row will be displayed separately, otherwise, more than one is more than Character resection, not displayed. 11). Whether to display the output on the screen, mainly used in conjunction with SPOOL.

SQL> Set Term [OUT] {ON | OFF}

When using the spool command to output a large table to a file, the content output will take a lot of time on the screen, and then set the set termspool off, the output content will only be saved in the output file. Displayed on the screen, greatly enhances the speed of Spool.

12). Remove the extra spaces after each line in the spool output

SQL> Set Trims [out] {on | OFF}

13) Show the execution time of each SQL statement

Set timing {on | OFF}

14. Modify the first string that appears in the current line in SQL Buffer

C [hange] / old_value / new_value

SQL> L

1 * SELECT * from de PEPT

SQL> C / DEPT / EMP

1 * SELECT * FROM EMP

15. Edit SQL statement in SQL Buffer

EDI [T]

16. Displays the SQL statement in SQL Buffer, List N displays the Nth line in SQL Buffer and makes the nth line become the current line.

L [IST] [N]

17. Add or more lines below the current row of SQL Buffers

I [NPUT]

18. Add the specified text to the current line of SQL Buffer

A [ppend]

SQL> SELECT Deptno,

2 DNAME

3 from dept;

Deptno DNAME

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

10 Accounting

20 Research

30 Sales

40 Operations

SQL> L 2

2 * DNAME

SQL> A, LOC

2 * DNAME, LOC

SQL> L

1 SELECT Deptno,

2 DNAME, LOC

3 * from dept

SQL> /

DEPTNO DNAME LOC

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

10 Accounting New York

20 Research Dallas

30 Sales Chicago

40 Operations Boston

19. Save the SQL statement in SQL BUFFER to a file

Save file_name

20. Import the SQL statement in a file into SQL Buffer

Get file_name

twenty one. Perform the SQL statement that has just been executed again

Run

oral

/

twenty two. Perform a stored procedure

Execute Procedure_name

twenty three. Connect to the specified database in SQL * Plus

Connect

User_name / passwd @ db_alias

twenty four. Set top headings for each report

Ttitle

25. Set the tail title of each report

Btitle

26. Write a comment

Remark [Text]

27. Out the specified information or an empty line to the screen

Prompt [Text]

28. Pause the execution process, wait for the user to continue execution

Pause [text]

SQL> PAUSE Adjust Paper and press Return to Continue.29. Copy some of the data in a database to another database (such as copying data of a table to another)

Copy {from database | To database | From database to database}

{Append | crete | insert | replace} destination_table

(Column, Column, Column, ...)] USING Query

SQL> COPY FROM

Scott / Tiger @ HQ To

John / chrome @ West

CREATE EMP_TEMP

Using SELECT * FROM EMP

30. Do not quit SQL * Plus, execute an operating system command in SQL * Plus:

Host

SQL> Host Hostname

This command may be supported under Windows.

31. In SQL * Plus, switch to the operating system command prompt, after running the operating system command, you can switch back to SQL * Plus again:

!

SQL>!

$ Hostname

$ EXIT

SQL>

This command is not supported under Windows.

32. Show the help of the SQL * Plus command

Help

How to install help files:

SQL> @? /Sqlplus/admin/help/hlpbld.sql? /Sqlplus/admin/help/helpus.sql

SQL> HELP INDEX

33. Display the value of the SQL * PLUS system variable or the value of the SQL * PLUS environment variable

Syntax

SHO [W] Option

WHERE OPTION REPRESENTS One of the Following Terms OR CLAUSES:

System_variable

All

BTI [TLE]

Err [ORS] [{Function | Procedure | Package | Package Body |

Trigger | View | Type | TYPE BODY} [Schema.] Name]

LNO

Parameters [parameter_name]

Pno

REL [EASE]

REPF [OOTER]

REPH [EAder]

SGA

Spoo [l]

Sqlcode

TTI [TLE]

User

1). Display the value of the current environment variable:

SHOW ALL

2). Display error information of the current object, stored procedure, trigger, package and other objects

Show Error

When you create a function, the stored procedure, etc., change can be used to view the error and the corresponding error message in that place, and compile it again.

3). Display the value of the initialization parameter:

Show parameters [parameter_name]

4) Display the version of the database:

Show rel [ease]

5) Display SGA size

Show sga

6) Show current username

SHOW User

转载请注明原文地址:https://www.9cbs.com/read-117544.html

New Post(0)