Oraclede的 SQL*PLus提供了一个命令行方式的解释器,同时支持SQL和PL/SQL。SQL*Plus可以接受用户输入的语句,然后把语句发送给Oracle服务器,并显示处理结果。
1.启动SQL*Plus
为了启动SQL*Plus需要跟随以下步骤:
1.1执行以下的其中一项
- 确认PATH环境变量包含了ORACLE_HOME/bin。
- 改变目录为ORACLE_HOME/bin。
1.2输入命令
sqlplus /nolg
(注意:LINUX和UNIX操作系统对大小写敏感)
2.SQL*Plus连接语句
提交SQL*Plus连接语句开始连接到Oracle实例,或在任何时刻重新连接到一个不同的用户。连接语句的语法如下:
CONN[ECT] [username | /][@connect_identifier] [AS {SYSOPER | SYSDBA}]
当你输入用户名后,SQL*Plus会提示输入密码,你输入的密码是不会被显示的。
下表描述了在CONNECT语句的语法成分。
语法成分 | 描述 |
/ | 调用连接请求的外部验证,这种验证类型不需要数据库密码。最常见的外部验证结构是操作系统验证,数据库用户身份验证通过使用主机账户登录到主机操作系统。也可以通过Oracle钱包或由网络服务进行外部认证 |
AS {SYSOPER | SYSDBA | 指明数据库用户正在以SYSOPER或SYSDBA系统权限连接,只有某些预定义的用户或者已经添加到密码文件的用户才能使用这些权限。 |
username | 一个有效的数据库用户名。数据库通过在数据字典中查找匹配的用户名验证请求,并提示数据用户密码。 |
connect_identifier(1) | 一个Oracle Net连接标识符,用来远程连接,确切语法取决于Oracle Net配置。如果省略,SQL*Plus尝试连接到本地实例。 Net连接描述符(网络地址和数据库服务名)的别名,网络服务名称。网络服务名称通常是定义在本地tnsname.ora文件中,但也可以使用其他方法定义。 |
connect_identifier(2) | 作为另外一个连接标识符,它可以使用简单的连接语法。简易连接提供了即装即用TCP/IP连接到远程数据库,而无需在本地电脑上配置Oracle网络服务。 连接的连接标识符的语法如下: host[:port][/service_name] 其中: host:远程数据库计算机的主机名或IP地址。 port:主机上Oracle网络监听器监听数据库连接的TCP端口。如果省略,默认是1521。 service_name:数据库服务名,如果远程主机上的Oracle网络监听器配置默认服务,则可以省略。如果没有配置值默认服务,则必须要提供。每个数据库通常提供的服务名称等于全局数据库名
|
3.EXAMPLES
3.1使用SYSDBA系统权限以操作系统验证方式连接到数据库
connect / as sysdba
3.2使用简易连接标识符连接到远程数据库
connect salesadmin@db1.mycompany.com:1522/sales.mycompany.com
4.SQL*PLUS命令汇总
Command | Description |
@ (at sign) | Runs SQL*Plus statements in the specified script. The script can be called from the local file system or from a web server. |
@@ (double at sign) | Runs a script. This command is similar to the @ (at sign) command It is useful for running nested scripts because it looks for the specified script in the same path as the calling script. |
/ (slash) | Executes the SQL command or PL/SQL block. |
ACCEPT | Reads a line of input and stores it in a given substitution variable. |
APPEND | Adds specified text to the end of the current line in the buffer. |
ARCHIVE LOG | Starts or stops the automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files. |
ATTRIBUTE | Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes. |
BREAK | Specifies where and how formatting will change in a report, or lists the current break definition. |
BTITLE | Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition. |
CHANGE | Changes text on the current line in the buffer. |
CLEAR | Resets or erases the current clause or setting for the specified option, such as BREAKS or COLUMNS. |
COLUMN | Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns. |
COMPUTE | Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. |
CONNECT | Connects a given user to Oracle Database. |
COPY | Copies results from a query to a table in the same or another database. |
DEFINE | Specifies a substitution variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables. |
DEL | Deletes one more lines of the buffer. |
DESCRIBE | Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function procedure. |
DISCONNECT | Commits pending changes to the database and logs the current user off Oracle Database, but does not exit SQL*Plus. |
EDIT | Invokes an operating system text editor on the contents of the specified file or on the contents of the buffer. |
EXECUTE | Executes a single PL/SQL statement. |
EXIT | Terminates SQL*Plus and returns control to the operating system. |
GET | Loads an operating system file into the buffer. |
HELP | Accesses the SQL*Plus command-line help system. |
HOST | Executes an operating system command without leaving SQL*Plus. |
INPUT | Adds one or more new lines after the current line in the buffer. |
LIST | Lists one or more lines of the buffer. |
PASSWORD | Enables a password to be changed without echoing the password on an input device. |
PAUSE | Displays the specified text, then waits for the user to press Return. |
Displays the current value of a bind variable. | |
PROMPT | Sends the specified message to the user's screen. |
EXIT | Terminates SQL*Plus and returns control to the operating system QUIT is identical to EXIT. |
RECOVER | Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. |
REMARK | Begins a comment in a script. |
REPFOOTER | Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition. |
REPHEADER | Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition. |
RUN | Lists and runs the SQL command or PL/SQL block currently stored in the SQL buffer. |
SAVE | Saves the contents of the buffer in an operating system file (a script). |
SET | Sets a system variable to alter the SQL*Plus environment for your current session. |
SHOW | Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. |
SHUTDOWN | Shuts down a currently running Oracle Database instance. |
SPOOL | Stores query results in an operating system file and, optionally, sends the file to a printer. |
START | Runs the SQL statements in the specified script. The script can be called from a local file system or a web server in SQL*Plus command-line. |
STARTUP | Starts an Oracle Database instance and optionally mounts and opens a database. |
STORE | Saves attributes of the current SQL*Plus environment in an operating system script. |
TIMING | Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers. |
TTITLE | Places and formats a specified title at the top of each report page, or lists the current TTITLE definition. |
UNDEFINE | Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command). |
VARIABLE | Declares a bind variable that can be referenced in PL/SQL. |
WHENEVER OSERROR | Exits SQL*Plus if an operating system command generates an error. |
WHENEVER SQLERROR | Exits SQL*Plus if a SQL command or PL/SQL block generates an error. |
XQUERY | Runs an XQuery 1.0 statement. |