感谢小布老师制作分享视频，以下是学习小布老师制作的SQL*Plus用法所做的笔记，记录如下。
播布客地址：http://www.boobooke.com
Installing Command-line Help
Log into SQL*Plus as the SYSTEM user.Running the hlpbld.sql
script to install Command-line help.
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql
Log into SQL*Plus as the SYSTEM user.Running the
helpdrop.sql script to remove Command-line help.
SQL> @?/sqlplus/admin/help/helpdrop.sql
Listing a Table Definition
To see the definitions of each column in a given table or view,
or the definition of a function or procedure,use the SQL*Plus
DESCRIBE command.
SQL>DESCRIBE table_or_view_name
SQL>DESC table_or_view_name
SQL>DESC plsql_function_or_procedure_name
Running Operating System Commands
You can execute an operating system command from the SQL*Plus
command prompt.This is useful when you want to perform a
task such as listing existing operating system files.
To run an operating system command,enter the SQL*Plus
command HOST followed by the operating system command.
You cna use a character in place of HOST command such as
"$" in Window or "!" in Unix. exit返回sql*plus界面
OS commands entered from a SQL*Plus session using the HOST
command do not affect the current SQL*Plus session,
but may affect SQL*Plus sessions started subsequently.
Pausing the Display
You can use the PAUSE system variable to stop and examine
the contents of the screen after each page during the display
of a long report,or during the display of a table definition
with many columns.
The syntax is:SET PAU[SE]{ON | OFF | text}
SET PAUSE ON pauses output at the beginning of each
PAGESIZE number of lines of report output.Press Return to
view more output.
SET PAUSE text specifies the text to be displayed each time
SQL*Plus pauses.Multiple words in text must be enclosed
in single quotes.（指定页码间分隔符，set pause '==============='）
Editing Scripts
The use of an external editor in combination with the @,@@ or START commands
is an effective method of creating and executing generic scripts.
You can use the SQL*Plus DEFINE command to define the variable,_EDITOR,to
hold the name of your preferred text editor.
SQL>define _editor = vi
To create a script with a text editor,enter EDIT followd by the name
of the file to edit.
SQL>edit sales
EDIT adds the filename extension .SQL to the name unless
you specify the file extension.When you save the script
with the text editor,it is saved back into the same file.
EDIT lets you create or modify scripts.
You must include a semicolon at the end of each SQL command
and a slash(/) on a line by itself after each PL/SQL block
in the file.You can include multiple SQL commands and PL/SQL
blocks in a script.
Editing the Current Line
The SQL*Plus CHANGE command enable you to edit the current
line.Various actions determine which line is the current line.
LIST a given line to make it the currnt line.
When you LIST or RUN the command in the buffer,the last line
of the command becomes the current line.
Using the slash(/)command to run the command int the
buffer does not affect the current line.
If you get an error,the error line automatically
becomes the current line.
The CHANGE command changes the first occurrence of the
existing specified text on the current line of the SQL
buffer to the new specified text.
This command has three parts,spearated by slashes or
any other non-alphanumberic character.
- the word CHANGE or the letter c
- the sequence of characters you want to change
- the replacement sequence of character
CHANGE ignores case in searching for the sequences
of characters you want to change.
eg.选择要修改的行后 c/100/15
Appending Text to a Line
To add text to the end of a line in the buffer,use the
APPEND command.
- Use the LIST command(or the line number) to list
the line you want to change.
- Enter APPEND followd by the text you want to add.
If the text you want to add begins with a blank,separate
the word APPEND from the first characte of the text
by two blanks;one to separate APPEND from the text,and one
to go into the buffe with the text.
SQL>list 4
SQL>append desc
Adding a New Line
To insert a new line after the current line,use the
INPUT command.
- To insert a line before line 1,enter a zero("0")and
follow the zero with text.SQL*Plus inserts the line at
the beginning of the buffer and all lines are renumbered
starting at 1.
- INPUT command insert a new line after the current line.
So you should change the current line first and then
use the INPUT command.
Deleting Lines
Use the DEL command to delete lines in the buffer.Enter
DEL specifying the line numbers you want to delete.If
you enter DEL with no clauses,it will delete the current
line of the SQL buffer.
- DEL n ~delete line n.
- DEL n m ~delete lines n through m.
- DEL n * ~delete line n through the current line.
- DEL n LAST ~delete line n through the last line.
- DEL * ~delete the current line.
- DEL * n ~delete the current through line n.
- DEL * LAST ~delete the current line through the last line.
- DEL LAST ~delete the last line.
Placing Comments in Scripts
You can enter comments in a script in three ways:
- using the SQL*Plus REMARK command for single
line comments.
- using the SQL comment delimiters /*...*/ for
single or multi line comments.
- using ANSI/ISO(American National Standards
Institute/International Standard Organization)
comments -- for single line comments.
Notes on Placing Comments
SQL*Plus does not have a SQL or PL/SQL command parser.
It scans the first few keyword of each new statement to
determine the command type,SQL,PL/SQL or SQL*Plus.
Comments in some location can prevent SQL*Plus from
correctly identifying the command type,giving unexpected
results.
- Do not put comments within the first few keyword of
statement.
eg:create or replace
/* this is comments */
procedure hello as
dbmb_output.line('hello');
- Do not put comments after statement terminator(period,
semicolon or slash).
- Do not put statement termination character at the end
of a comment line or after comments in a SQL statement or
a PL/SQL block.
eg:select * from
-- this is comment;
where id = 1;
-Do not use ampersand characters "&"in comments in a SQL
statement or PL/SQL block.
eg:
select id,name
from m
/* this is one & two */
where id = 1;
Running Scripts
The START command retrieves a script and runs
the commands it contains.Use START to run a
script containing SQL commands,PL/SQL blocks,and
SQL*Plus commands.You can have many commands to
run the script.
- To see the commands as SQL*Plus "enter"them,you
can SET ECHO ON.The ECHO system variable controls
the listing of the commands in scripts run with
START,@ and @@ commands.Setting the ECHO variable OFF
suppresses the listing.(控制是否输出运行的脚本)
- START,@ and @@ leave the last SQL command or PL/SQL
block of the script in the SQL buffer.
Use SQLPATH Environment Variables
The SQLPATH is the name of an environment variable you
can set to tell SQL*Plus where to look for scripts.By
using this environment variable,you can put your script
in a single directory,and SQL*Plus will find them for your.
- SQL*Plus will look in the current directory,and then it
will look in the directory specified in the SQLPATH
environment variable.
- This setting works on every platform that support
environment variables(such as Unix,Linux,Macintosh,Windows).
Running a Script You Start SQL*Plus
To run a script as you start SQL*Plus,use one of the following
options:
- Follow the SQL*Plus command with your username,a slash,
a space,@,and the name of the file:
> sqlplus hr@tsspks08 @sales.sql
- Include your username as the first line of the file.
Follow the SQLPLUS command with @ and the filename.SQL*Plus
starts,prompts for your password and runs the file.
eg:
sqlplus @first.sql
first.sql内容如下：
hr/hr@tsspsk08
insert into m values(0,'zmh');
commit;
/
Nesting Scripts
To run a series of script in sequence,first create
a script containing several START commands,each
followed by the name of a script in the sequence.
Then run the script containing the
START commands.
$ cat myscript.sql
START q1sales
START q2sales
START q3sales
START q4sales
Using SPOOL To Save Your Query
To store the results of a query in a file and still
display them on the screen,you can use the SPOOL command.
- Syntax:
SPO[OL] [file_name [.ext] [CRE[ATE] | REP[LACE]
| APP[END] | OFF | OUT]
- SQL*Plus continues to spool information to the
file until you turn spooling off.
Using Substitution Variables(替代变量，是sql*plus有的功能，不是oracle)
A substitution variable is not like a true variable used
in a programming language.Instead,substitution variables
mark places in the text where SQL*Plus does the
equivalent of search and replace at runtime.Substitution
varibales allow you to write generic SQL*Plus script.
- A substitution variable is preceded by one or two
ampersands(&).
- You can use substitution variables anywhere in SQL
and SQL*Plus commands,except at the first word enterd.
When SQL*Plus encounters an undefined substitution
variable in a command,SQL*Plus prompts you for the value.
- When SQL*Plus encounter a variable with a single
leading ampersand,it always prompts you for a value,
even when you use the same variable multiple times in
your script.Double-ampersand variables allow you to
prompt a user only once.
eg: select * from m where id = &myid;