Common Instructions and skills for SQL plus in Oracle

Source: Internet
Author: User
Tags dname trims

 

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 username/password @ Sid 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}

Ver [ify] {on | off}

Wra [p] {on | off}

 

1) set whether the current session will automatically submit the modified data

SQL> set auto [Commit] {on | off | Imm [ediate] | n}

 

2) Whether to display the SQL statements being executed in the script when you run an SQL script using the start command

SQL> set echo {on | off}

 

3). Whether to display the number of rows queried or modified by the current SQL statement

SQL> Set feed [Back] {6 | n | on | off}

By default, only rows with more than 6 results are displayed. If set feedback 1 is returned, no matter how many rows are queried. When it is off, the number of queried rows is not displayed.

 

4). Whether to display the column title

SQL> set hea [Ding] {on | off}

When set heading off, Column Titles are not displayed on each page, instead of blank rows.

 

5) set the number of characters that a row can hold

SQL> set LiN [esize] {80 | n}

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

 

6). Set the separation between pages

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

When set Newpage 0, a small black box is displayed at the beginning of each page.

When set Newpage N is set, there are n blank lines between the page and the page.

When set Newpage none is set, there is no interval between the page and the page.

 

7). Replace the null value with the text value during display.

SQL> set null text

 

8). Set the number of rows on a page.

SQL> set pages [ize] {24 | n}

If it is set to 0, all output content is one page and the column title is not displayed.

 

9). Whether to display the output information using 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 necessary information to debug the stored procedure. The information can be displayed on the screen only after the serveroutput variable is set to on.

 

10) Whether to intercept the SQL statement when the length of the SQL statement is greater than linesize.

SQL> set wra [p] {on | off}

When the length of the output line is greater than the length of the Set line (set with the set linesize n command), when set wrap on, more than the characters of the output line will be displayed in another line, otherwise, the output line is removed from the output line and will not be displayed.

 

11). Whether to display the output content on the screen is mainly used in combination with spool.

SQL> set term [out] {on | off}

When you use the spool command to output the content in a large table to a file, it takes a lot of time to output the content on the screen. After setting set termspool off, the output content is saved only in the output file and not displayed on the screen, greatly improving the spool speed.

 

12) Remove unnecessary spaces behind each line in the spool output.

SQL> set trims [out] {on | off}

 

13) display the execution time of each SQL statement

Set timing {on | off}

 

◆ 14. Modify the first string in the current row of SQL Buffer

C [hange]/old_value/new_value

SQL> L

1 * select * from Dept

SQL> C/DEPT/EMP

1 * select * from EMP

 

◆ 15. Edit the SQL statement in SQL Buffer

EDI [T]

 

◆ 16. Display SQL statements in SQL Buffer. List n shows the nth row in SQL Buffer and makes the nth row the current row.

L [ist] [N]

 

◆ 17. Add one or more rows under the current row of SQL Buffer

I [nput]

 

◆ 18. Add the specified text to the current row of the 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 SQL statements in a file into SQL Buffer

Get file_name

 

◆ 21. Execute the SQL statement that has just been executed again

Run

Or

/

 

◆ 22. Execute a stored procedure

Execute procedure_name

 

◆ 23. Connect to the specified database in SQL * Plus

Connect user_name/passwd @ db_alias

 

◆ 24. Set the top title of each report

Ttitle

 

◆ 25. Set the end title of each report

Btitle

 

◆ 26. Write a comment

Remark [text]

 

◆ 27. output the specified information or an empty row to the screen.

Prompt [text]

 

◆ 28. Pause the execution process and wait for the user to respond.

Pause [text]

 

SQL> pause adjust paper and press return to continue.

 

◆ 29. copy some data from a database to another database (for example, copy data from one table to another)

Copy {from database | to database | from database to database}

{Append | create | insert | replace} destination_table

[(Column,...)] using query

 

SQL> copy from Scott/tiger @ hq to John/chrome @ West

Create emp_temp

Using select * from EMP

 

◆ 30. If you do not exit SQL * Plus, execute an operating system command in SQL * Plus:

Host

 

SQL> host hostname

This command may be supported in windows.

 

◆ 31. In SQL * Plus, switch to the operating system command prompt and run the operating system command. You can switch back to SQL * Plus again:

!

 

SQL>!

$ Hostname

$ Exit

SQL>

 

This command is not supported in windows.

 

◆ 32. display the help of the SQL * Plus command

Help

How to install the Help file:

SQL> @? Sqlplusadminhelphlpbld. SQL? Sqlplusadminhelphelpus. SQL

SQL> Help Index

 

◆ 33. display the SQL * Plus System variable value or SQL * Plus environment variable value

Syntax

Sho [w] Option

Where option represents one of the following terms or clses:

System_variable

All

BTI [TLE]

Err [ors] [{function | procedure | package body |

Trigger | View | type body} [schema.] Name]

Lno

Parameters [parameter_name]

PNO

Rel [statement]

Repf [Ooter]

Reph [eader]

SGA

Spoo [l]

Sqlcode

TTI [TLE]

User

 

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

Show all

 

2). displays the error information of the currently created function, stored procedure, trigger, package, and other objects.

Show ERROR

When an error occurs in creating a function or stored procedure, you can use this command to view the error and related error information at that place, modify the information, and compile the code again.

 

3). display the value of the initialization parameter:

Show parameters [parameter_name]

 

4). display the database version:

Show rel [Rules]

 

5). display the SGA size

Show SGA

 

6). display the current user name

Show user

 

SQL> set define on

It is often used to run SQL scripts and replace macro variables (&)

SQL> define tstemp = tempts

Replace & tstemp in the running script with tempts

 

SQL> set define off

Disable macro replacement, which is often used to run a package or stored procedure

 

SQL * Plus contains a lot of skills. If you master these skills, it is helpful for quick development and effective database maintenance in Oracle databases.

◆ 1. Use SQL * Plus to dynamically generate Batch scripts

When combined with the SELECT command, Spool can generate a script containing statements that can execute a task in batches.

Example 1:

Generate a script to delete all tables under the Scott User:

A. Create the gen_drop_table. SQL file, which contains the following statements:

Spool C: drop_table. SQL

Select 'drop table' | table_name | ';' from user_tables;

Spool off

B. log on to the database as a Scott user

Sqlplus> @..... Gen_dorp_table. SQL

C. The drop_table. SQL file will be generated under the C root directory, containing the statement for deleting all tables, as shown below:

SQL> select 'drop table' | table_name | ';' from user_tables;

 

'Droptable' | table_name | ';'

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

Drop table dept;

Drop table EMP;

Drop table parent;

Drop table stat_vender_temp;

Drop table table_forum;

 

5 rows selected.

 

SQL> spool off

D. Edit the generated drop_table. SQL file to remove unnecessary parts, leaving only drop table ..... Statement

E. Run the dorp_table. SQL file under the Scott user to delete all tables under the Scott user.

Sqlplus> @ C: dorp_table. SQL

 

In the above operation, there are extra characters in the generated script file, such as the running SQL statement, title, or number of returned rows. We need to edit the script and run it again, it brings a lot of inconvenience to the actual operation. Laziness is a human nature, which prompts us to implement the above tasks in a simpler way.

 

A. Create the gen_drop_table. SQL file, which contains the following statements:

Set echo off

Set feedback off

Set Newpage none

Set pagesize 5000

Set linesize 500

Set verify off

Set pagesize 0

Set term off

Set trims on

Set linesize 600

Set heading off

Set timing off

Set verify off

Set numwidth 38

Spool C: drop_table. SQL

Select 'drop table' | table_name | ';' from user_tables;

Spool off

B. log on to the database as a Scott user

Sqlplus> @..... Gen_dorp_table. SQL

C. The drop_table. SQL file will be generated under the C root directory, containing the statement for deleting all tables, as shown below:

Drop table dept;

Drop table EMP;

Drop table parent;

Drop table stat_vender_temp;

Drop table table_forum;

D. Run the dorp_table. SQL file under the Scott user to delete all the tables under the Scott user.

Sqlplus> @ C: dorp_table. SQL

 

 

◆ 2. export data from a table to generate a text file separated ","

Set echo off

Set feedback off

Set Newpage none

Set pagesize 5000

Set linesize 500

Set verify off

Set pagesize 0

Set term off

Set trims on

Set linesize 600

Set heading off

Set timing off

Set verify off

Set numwidth 38

Spool C: drop_table. SQL

Select deptno | ',' | dname from Dept;

Spool off

After saving the preceding content as a text file, log on to Scott and execute the file. The result is displayed:

10, accounting

20, Research

30, sales

40, Operations

 

 

Through the two examples above, we can:

Set echo off

Set feedback off

Set Newpage none

Set pagesize 5000

Set linesize 500

Set verify off

Set pagesize 0

Set term off

Set trims on

Set linesize 600

Set heading off

Set timing off

Set verify off

Set numwidth 38

Spool C: Specific file name

The SQL statement you want to run

Spool off

As a template, you only need to take the necessary statements as the template.

 

In a newer version of Oracle, you can also use the set colsep command to implement the above functions:

SQL> set colsep,

SQL> select * from Dept;

10, accounting, New York

20, research, Dallas

30, sales, Chicago

40, operations, Boston

35, AA, BB

 

◆ 3. dynamically generate the file name required by the spool command

In the above example, the file names required by the spool command are fixed. Sometimes we need to spool once a day, and each spool file name is different, such as the file name contains the date of the day, how to implement it?

Column dat1 new_value filename;

Select to_char (sysdate, 'yyyymmddhh24mi ') dat1 from dual;

Spool C: & filename..txt

Select * from Dept;

Spool off;

 

◆ 4. How to obtain the Windows environment variable value from the script file:

In ipvs:

Spool C: temp1_oracle_sid=.txt

Select * from Dept;

...

Spool off

 

In the preceding example, the value of the environment variable oracle_sid is referenced by % oracle_sid %. If the value of oracle_sid is orcl, the generated spool file name is orcl.txt.

 

In Unix:

Spool C: temp1_oracle_sid.txt

Select * from Dept;

...

Spool off

 

In the preceding example, the value of the environment variable oracle_sid is referenced by $ oracle_sid. If the value of oracle_sid is orcl, the generated spool file name is orcl.txt.

 

◆ 5. How to specify the default script editing directory

In SQL * Plus, you can use the Save command to save the last executed SQL statement to a file. But how to set the default directory of the file?

You can use the SQL> set editfile C: tempfile. SQL command to set the default directory to C: tmpe and the default file name to file. SQL.

 

◆ 6. How to remove the same rows in the table

Find the same row:

Select * From dept

Where rowid <> (select max (rowid)

From dept B

Where a. deptno = B. deptno

And a. dname = B. dname -- make sure all columns are compared

And a. Loc = B. LOC );

 

Note:

If you only want to find the rows with the same deptno column, the preceding query can be changed:

Select * From dept

Where rowid <> (select max (rowid)

From dept B

Where a. deptno = B. deptno)

 

Delete the same row:

Delete from dept

Where rowid <> (select max (rowid

From dept B

Where a. deptno = B. deptno

And a. dname = B. dname -- make sure all columns are compared

And a. Loc = B. LOC );

 

Note: The row whose column value is null is not deleted.

 

◆ 7. How to insert two single quotes ('') into the database ('')

Insert inot dept values (35, 'aa' '''bb ', 'A' B ');

 

During the insert operation, two ''are used to represent one '.

 

◆ 8. How to set the search path for SQL * Plus so that the full path of the file is not required when @ command is used.

Set the sqlpath environment variable.

For example:

Sqlpath = C: orantdbs; C: custom scripts; C: myscripts

 

◆ 9. What is the difference between @ and?

@ Equals to the start command, used to run an SQL script file.

@ Command to call the script file in the current directory, or specify the full path, or you can use the sqlpath environment variable to search for the script file.

@ Is used in the script file to specify that the file executed with @ is in the same directory as the file where @ is located, instead of specifying the full path, this command does not search for files from the path specified by the sqlpath environment variable. It is generally used in nested script files.

 

◆ 10. & Differences

& Used to create a temporary variable. Whenever this temporary variable is encountered, you will be prompted to enter a value.

& Used to create a persistent variable, just like a persistent variable created using the define command or the column command with the new_vlaue words. When you use the & command to reference this variable, you will not be prompted to enter a value every time you encounter this variable, but will be prompted only once when you first encounter this variable.

 

For example, if you save the following three statements as a script file and run the script file, you will be prompted three times to enter the deptnoval value:

Select count (*) from EMP where deptno = & deptnoval;

Select count (*) from EMP where deptno = & deptnoval;

Select count (*) from EMP where deptno = & deptnoval;

 

Save the following three statements as a script file. If you run the script file, you will only be prompted once to enter the value of deptnoval:

Select count (*) from EMP where deptno = & deptnoval;

Select count (*) from EMP where deptno = & deptnoval;

Select count (*) from EMP where deptno = & deptnoval;

 

◆ 11. Purpose of introducing copy

The Copy command is particularly useful when copying data between two databases. In particular, this command can transmit data of long fields between two databases.

Disadvantages:

When data is transmitted between two databases, the precision (lose precision) may be lost ).

 

◆ 12. What will slow my script when a large number of rows are modified?

When you modify many rows in a table through PL/SQL blocks, you will create a cursor in the table, but the rollback segment will be released only when you disable cursor, when the cursor is enabled, the modification process slows down because the database has to search for a large number of rollback segments to maintain read consistency. To avoid this problem, add a flag field to the table to describe whether the row has been modified, close the cursor, and then open the cursor. You can modify 5000 rows each time.
This post comes from the group music community-http://q.yesky.com/group/review-17556869.html

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.