SQL*PLus 帮助手册(SP2-0171)

本文主要是介绍SQL*PLus 帮助手册(SP2-0171)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

对于经常在SQL*Plus 下工作的大师们而言，总是时不时查询SQL*Plus的帮助命令。着实太多了，记不住。SQL*Plus下直接提供了help命令来帮助描述所有命令的用法。嘎嘎，再也不用查SQL*Plus reference了。对于Oracle 10g缺省情况下，SQL*Plus的help手册并没有被安装，需要手动安装。Oracle 10/11g下采用相同的安装方式，下面来描述一下help的安装与使用。

1、安装sqlplus help

system@CNMMBO> help 
SP2-0171: HELP system not available.
robin@SZDB:~> cd $ORACLE_HOME/sqlplus/admin/help    --脚本路径位于ORACLE_HOME,sqlplus/admin/hlep目录下
robin@SZDB:/users/oracle/OraHome10g/sqlplus/admin/help> ls
helpbld.sql  helpdrop.sql  helpus.sql  hlpbld.sql
robin@SZDB:/users/oracle/OraHome10g/sqlplus/admin/help> head -40 hlpbld.sql
--
-- Copyright (c) Oracle Corporation 1983, 2003.  All Rights Reserved.
--
-- NAME
--   hlpbld.sql
--
-- DESCRIPTION
--   Builds the SQL*Plus HELP table and loads the HELP data from a
--   data file.  The data file must exist before this script is run.
--
-- USAGE
--   To run this script, connect as SYSTEM and pass the datafile to be
--   loaded as a parameter e.g.
--
--       sqlplus system/<system_password> @hlpbld.sql helpus.sql
--
--
DEFINE DATAFILE = &1
--
-- Create the HELP table
--
DROP TABLE HELP;
CREATE TABLE HELP
(
TOPIC VARCHAR2 (50) NOT NULL,
SEQ   NUMBER        NOT NULL,
INFO  VARCHAR2 (80)
) PCTFREE 0 STORAGE (INITIAL 48K PCTINCREASE 0);
GRANT SELECT ON HELP TO PUBLIC;
--
-- Insert the data into HELP.
--
@@&DATAFILE
system@CNMMBO> @$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql
DROP TABLE HELP
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
Grant succeeded.
...
Commit complete.
View dropped.

2、help手册的用法

--查看刚刚创建的表已经添加了978行
system@CNMMBO> select count(*) from help;
COUNT(*)
----------
978
--直接在命令提示符下输入help，则给出SQL*plus help的用法:HELP|? [topic]
--help index or ? index则列出了当前sqlplus下所有可用命令
system@CNMMBO> help
HELP
----
Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics. In iSQL*Plus, click the Help button to display
iSQL*Plus online help.
You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/
and the Oracle Database Library at http://otn.oracle.com/documentation/
HELP|? [topic]
--切换别的帐户来测试help是否可用
system@CNMMBO> conn goex_admin/xxxxx
Connected.       
--下面的测试其他帐户也可以使用，因为在创建时创建了视图其授予了其他用户select 权限
goex_admin@CNMMBO> help index
Enter Help [topic] for help.
@             COPY         PAUSE                    SHUTDOWN
@@            DEFINE       PRINT                    SPOOL
/             DEL          PROMPT                   SQLPLUS
ACCEPT        DESCRIBE     QUIT                     START
APPEND        DISCONNECT   RECOVER                  STARTUP
ARCHIVE LOG   EDIT         REMARK                   STORE
ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
BREAK         EXIT         REPHEADER                TTITLE
BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
CLEAR         HOST         RUN                      WHENEVER OSERROR
COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
COMPUTE       LIST         SET
CONNECT       PASSWORD     SHOW
--查看每一个命令的具体用法
--直接在提示符下help <command> 或者 ？ <command>
goex_admin@CNMMBO> help define
DEFINE
------
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
-- Author : Robinson
-- Blog   : http://blog.csdn.net/robinson_0612
goex_admin@CNMMBO> help accept
ACCEPT
------
Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
--有了它，再也不必为记不住的命令犯愁了。

更多参考

DML Error Logging 特性

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点