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 [email protected]
注意:[email protected]/等特殊字符的需要用双引号把密码引起来
2、查看对象
--查看函数 describe可简写为DESC16:23:22 [email protected]> describe fn_getnameFUNCTION fn_getname RETURNS VARCHAR2--查看存储过程16:36:48 [email protected]> DESC TEST_001PROCEDURE TEST_001--查看表16:36:59 [email protected]> 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 [email protected]> CREATE SYNONYM syn_emp for emp;同义词已创建。已用时间: 00: 00: 00.0316:40:53 [email protected]> 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 [email protected]> 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 [email protected]> show headheading ON16:48:36 [email protected]> 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 [email protected]> set head off16:49:13 [email protected]> SELECT * FROM EMP WHERE ROWNUM<2; 1214 <null> <null> <null> <null> 1.21E+03 <null> <null>已选择 1 行。--设置行的大小(SET linesize <integer>)16:52:49 [email protected]> SET linesize 2016:53:18 [email protected]> select text from user_source where rownum<2;TEXT--------------------package csharp_interface is已选择 1 行。已用时间: 00: 00: 00.0016:53:19 [email protected]> SET linesize 10016:53:26 [email protected]> /TEXT------------------------------------------package csharp_interface is已选择 1 行。已用时间: 00: 00: 00.00--设置执行时间显示 (SET timing ON)16:54:41 [email protected]> SET timing off16:54:44 [email protected]> select 1 from dual; 1---------- 1已选择 1 行。16:54:54 [email protected]> SET timing on16:54:59 [email protected]> select 1 from dual; 1---------- 1已选择 1 行。已用时间: 00: 00: 00.01
数字、字符等输出格式控制:
--设置显示列宽(字符类型的)COL object_name FORMAT a30 --设置显示列宽及格式(数字类型的)COL sal FORMAT $99,99916:06:29 [email protected]> select sal from emp; SAL-------- $1,212 $4,500--负数显示格式 -COL sal FORMAT 9999MI16:10:34 [email protected]> select -4321.66 sal from dual; SAL-----4322---负数显示格式 <>COL sal FORMAT 9999PR16:12:01 [email protected]> select -4321.66 sal from dual; SAL------<4322> --数字精度控制COL sal FORMAT 9999D99 16:12:50 [email protected]> select 654321.66666 sal from dual; SAL--------########已选择 1 行。已用时间: 00: 00: 00.0016:13:00 [email protected]> select 6521.6666 sal from dual; SAL-------- 6521.67已选择 1 行。 --科学计数法显示数字COL sal FORMAT 9999.99EEEE 16:13:46 [email protected]> select 6521.6666 sal from dual; SAL---------- 6.52E+03已选择 1 行。 --数字显示为日期格式COL test FORMAT DATE 16:19:32 [email protected]> select 1 test from dual; TEST--------01/01/12
4、查看参数值 (show parameter)
--查看以UNDO开头的初始化参数的值17:27:08 [email protected]> 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 [email protected]> show userUSER 为 "SCOTT"--显示oracle服务器版本号 (show release)17:28:52 [email protected]> show releaserelease 1102000100--显示缺省实例的连接标识符 (show instance)17:23:26 [email protected]> show instanceinstance "local"--显示归档日志路径 (show logsource)17:26:36 [email protected]> show logsourcelogsource ""--显示错误信息 (show errors)17:15:51 [email protected]> 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 [email protected]> show errorsPROCEDURE TEST123 出现错误:LINE/COL ERROR-------- ----------------------------------------3/3 PL/SQL: Statement ignored3/3 PLS-00201: 必须声明标识符 'IFASDFN'17:16:14 [email protected]>--显示所有配置参数 (show all)17:14:24 [email protected]> 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 "[email protected]> "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 [email protected]> SET sqlprompt sqlpro_test>17:06:04 sqlpro_test>--清空屏幕显示 可简写为CL SCRclear screen--调用系统编辑器 自动打开前一个命令进行编辑 简写为EDedit