SQLPLUS使用介绍

来源：互联网发布：空中英语教室知乎编辑：程序博客网时间：2024/06/07 06:01

1、连接(connect可简写为CONN)
CONN[ECT] {username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}] [AS {SYSOPER | SYSDBA}]

SQLPLUS下操作系统验证登录DBA角色:
connect / AS sysdba
用户名/密码登录格式:
connect AS a USER CONN <logon> / <password> @ <instance>
@后为配置好的监听连接标识符,如:
connect joesmith/password@orcl
注意:密码中含有@、/等特殊字符的需要用双引号把密码引起来

直接使用IP/SID登录方式:

sqlplus 用户名/密码@IP:端口/SID

2、查看对象

--查看函数 describe可简写为DESC16:23:22 SCOTT@orcl> describe fn_getnameFUNCTION fn_getname RETURNS VARCHAR2--查看存储过程16:36:48 SCOTT@orcl> DESC TEST_001PROCEDURE TEST_001--查看表16:36:59 SCOTT@orcl> DESC emp 名称                                                  是否为空? 类型 ----------------------------------------------------- -------- --------------- EMPNO                                                 NOT NULL NUMBER(4) ENAME                                                          VARCHAR2(10) JOB                                                            VARCHAR2(9) MGR                                                            NUMBER(4) HIREDATE                                                       DATE SAL                                                            NUMBER(7,2) COMM                                                           NUMBER(7,2) DEPTNO                                                         NUMBER(2)--查看同义词16:40:15 SCOTT@orcl> CREATE SYNONYM syn_emp for emp;同义词已创建。已用时间:  00: 00: 00.0316:40:53 SCOTT@orcl> desc syn_emp 名称                                                  是否为空? 类型 ----------------------------------------------------- -------- ------------ EMPNO                                                 NOT NULL NUMBER(4) ENAME                                                          VARCHAR2(10) JOB                                                            VARCHAR2(9) MGR                                                            NUMBER(4) HIREDATE                                                       DATE SAL                                                            NUMBER(7,2) COMM                                                           NUMBER(7,2) DEPTNO                                                         NUMBER(2)

3、格式设置命令

--设置列分隔符 (SET colsep 字符或字符串)SET colsep ','16:45:19 SCOTT@orcl> SELECT * FROM EMP WHERE ROWNUM<2;     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE      ,       SAL,      COMM,    DEPTNO----------,----------,---------,----------,--------------,----------,----------,----------      1214,<null>    ,<null>   ,<null>    ,<null>        ,  1.21E+03,<null>    ,<null>已选择 1 行。--设置表头是否显示(set head ON/OFF)16:48:31 SCOTT@orcl> show headheading ON16:48:36 SCOTT@orcl> SELECT * FROM EMP WHERE ROWNUM<2;     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ----------      1214 <null>     <null>    <null>     <null>           1.21E+03 <null>     <null>已选择 1 行。已用时间:  00: 00: 00.0016:49:06 SCOTT@orcl> set head off16:49:13 SCOTT@orcl> SELECT * FROM EMP WHERE ROWNUM<2;      1214 <null>     <null>    <null>     <null>           1.21E+03 <null>     <null>已选择 1 行。--设置行的大小(SET linesize <integer>)16:52:49 SCOTT@orcl> SET linesize 2016:53:18 SCOTT@orcl> select text from user_source where rownum<2;TEXT--------------------package csharp_interface is已选择 1 行。已用时间:  00: 00: 00.0016:53:19 SCOTT@orcl> SET linesize 10016:53:26 SCOTT@orcl> /TEXT------------------------------------------package csharp_interface is已选择 1 行。已用时间:  00: 00: 00.00--设置执行时间显示 (SET timing ON)16:54:41 SCOTT@orcl> SET timing off16:54:44 SCOTT@orcl> select 1 from dual;         1----------         1已选择 1 行。16:54:54 SCOTT@orcl> SET timing on16:54:59 SCOTT@orcl> select 1 from dual;         1----------         1已选择 1 行。已用时间:  00: 00: 00.01

数字、字符等输出格式控制:

--设置显示列宽(字符类型的)COL object_name FORMAT a30 --设置显示列宽及格式(数字类型的)COL sal FORMAT $99,99916:06:29 SCOTT@orcl> select sal from emp;     SAL--------  $1,212  $4,500--负数显示格式 -COL sal FORMAT 9999MI16:10:34 SCOTT@orcl> select -4321.66 sal from dual;  SAL-----4322---负数显示格式 <>COL sal FORMAT 9999PR16:12:01 SCOTT@orcl> select -4321.66 sal from dual;   SAL------<4322> --数字精度控制COL sal FORMAT 9999D99 16:12:50 SCOTT@orcl> select 654321.66666 sal from dual;     SAL--------########已选择 1 行。已用时间:  00: 00: 00.0016:13:00 SCOTT@orcl> select 6521.6666 sal from dual;     SAL-------- 6521.67已选择 1 行。  --科学计数法显示数字COL sal FORMAT 9999.99EEEE 16:13:46 SCOTT@orcl> select 6521.6666 sal from dual;       SAL----------  6.52E+03已选择 1 行。 --数字显示为日期格式COL test FORMAT DATE 16:19:32 SCOTT@orcl> select  1 test from dual;    TEST--------01/01/12

4、查看参数值 (show parameter)

--查看以UNDO开头的初始化参数的值17:27:08 SYS@orcl> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ---------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS1

5、SPOOL

SPOOL 为SQLPLUS下使用的输出工具,类似如CMD下的ECHO >>文件名,不过SPOOL要强大的多.
1.输出 SPOOL 文件路径
如:
spool c:\temp\test1.txt
...
--各种操作命令后
SPOOL OFF
打开文件TEST1.TXT,发现各种操作命令及显示结果都已经保存到此文件.

6、其他命令

--显示当前用户17:32:30 SCOTT@orcl> show userUSER 为 "SCOTT"--显示oracle服务器版本号 (show release)17:28:52 SYS@orcl> show releaserelease 1102000100--显示缺省实例的连接标识符 (show instance)17:23:26 SCOTT@orcl> show instanceinstance "local"--显示归档日志路径 (show logsource)17:26:36 SYS@orcl> show logsourcelogsource ""--显示错误信息 (show errors)17:15:51 SCOTT@orcl> CREATE PROCEDURE test123 IS17:16:03   2  BEGIN17:16:07   3    ifasdfn;17:16:08   4  END;17:16:08   5  /警告: 创建的过程带有编译错误。已用时间:  00: 00: 00.0417:16:09 SCOTT@orcl> show errorsPROCEDURE TEST123 出现错误:LINE/COL ERROR-------- ----------------------------------------3/3      PL/SQL: Statement ignored3/3      PLS-00201: 必须声明标识符 'IFASDFN'17:16:14 SCOTT@orcl>--显示所有配置参数 (show all)17:14:24 SCOTT@orcl> show allappinfo 为 OFF 并且已设置为 "SQL*Plus"arraysize 15autocommit OFFautoprint OFFautorecovery OFFautotrace OFFblockterminator "." (hex 2e)btitle OFF 为下一条 SELECT 语句的前几个字符cmdsep OFFcolsep " "compatibility version NATIVEconcat "." (hex 2e)copycommit 0COPYTYPECHECK 为 ONdefine "&" (hex 26)describe DEPTH 1 LINENUM OFF INDENT ONecho OFFeditfile "afiedt.buf"embedded OFFescape OFFescchar OFFexitcommit ON用于 1 或更多行的 FEEDBACK ONflagger OFFflush ONheading ONheadsep "|" (hex 7c)instance "local"linesize 100lno 7loboffset 1logsource ""long 100000longchunksize 80markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFFnewpage 1null "<null>"numformat ""numwidth 10pagesize 1000PAUSE 为 OFFpno 1recsep WRAPrecsepchar " " (hex 20)release 1102000100repfooter OFF  为 NULLrepheader OFF  为 NULLsecuredcol is OFFserveroutput ON SIZE 1000000 FORMAT WORD_WRAPPEDshiftinout INVISIBLEshowmode OFFspool ONsqlblanklines OFFsqlcase MIXEDsqlcode 0sqlcontinue "> "sqlnumber ONsqlpluscompatibility 11.2.0sqlprefix "#" (hex 23)sqlprompt "_user@orcl> "sqlterminator ";" (hex 3b)suffix "sql"tab ONtermout OFFtiming ONtrimout ONtrimspool ONttitle OFF 为下一条 SELECT 语句的前几个字符underline "-" (hex 2d)USER 为 "SCOTT"verify OFFwrap : 将换至下一行errorlogging is OFF--设置SQL提示符 (SET sqlprompt 字符串)16:58:31 SCOTT@orcl> SET sqlprompt sqlpro_test>17:06:04 sqlpro_test>--清空屏幕显示 可简写为CL SCRclear screen--调用系统编辑器 自动打开前一个命令进行编辑 简写为EDedit