SQLPLUS命令.doc

Lesson 1 (LAB)
Oracle-Lab1

说明：上机课—SQL*Plus基本操作
课前准备：Oracle安装、C/S结构的Oracle安装访问模型
课堂演示：Oracle配置、使用方法

本章知识要点：
1、 启动SQL*Plus
2、 使用Select语句查看数据
3、 环境设置
4、 数据词典的使用

上机前的操作：
1、 每人创建一个用户：用户名为E5301~E5330/oracle(其中E53表示班级， 01-30表示你所在的上机痤位号，oracle为密码，注意每个人都按此规则创建自己的帐号)，创建方法为
a) 用系统管理员system/manager登录，注意不能更改system的密码
b) CREATE USER 用户名 IDENTIFIED BY 密码;
c) 执行语句：grant connect,resource to E5301
d) 退出重新用新建立的帐号登录即可
2、 运行C:\oracle\ora90\sqlplus\demo\DemoBld.sql文件，方法为：@C:\oracle\ora90\sqlplus\demo\DemoBld.sql
操作步骤：
1． 用system/manager@accp3登录到SQL＊PLUS
2． 创建用户：CREATE USER 用户名 IDENTIFIED BY 密码;
3． 给新建帐号授权：GRANT CONNECT TO 用户名;GRANT RESOURCE TO 用户名
4． 重新用新建的帐号登录到SQL＊PLUS
5． 创建示例数据表
A． 执行命令：ALTER SESSION SET NLS_LANGUAGE=’American’;
B． 执行脚本：@ C:\oracle\ora90\sqlplus\demo\DemoBld.sql
6． 重新用新建的帐号登录到SQL＊PLUS

一、 Net9配置步骤
1、 启动Net9 Configuation Assistant
2、 配置Listener：本地
3、 命名方法配置：选择本地即可
4、 配置本地网络服务名：计算机名输入lab3；数据服务名输入nsaccp

二、 SQL*PLUS启动步骤
1、 启动SQL*PLUS 或者在命令行输入：SQLPLUSW.EXE
2、 登录：scott/tiger；system/manager；sys/change_on_install
3、 命令操作
4、 若不能用scott/tiger登录，考虑用其他用户登录，然后在提示符下执行文件：C:\Oracle\Oracle81\sqlplus\demo\demobld.sql

三、 使用的特殊命令及语句
1、 column命令：//设置特定列的显示格式
select * from emp;
column sal format a12; //column sal format $99,999
select * from emp;column sal;
column sal off(屏蔽设置的格式)
2、 日期格式的设置及语言环境的设置
select sysdate from dual; //dual为一个虚表(空表)
select sys_context('userenv','language') from dual; //查看当前语言格式
alter session set nls_language='American';
alter session set nls_language='Simplified Chinese';
alter session set nls_calendar='ROC Official'; //设置日期格式为"中华民国"
alter session set nls_calendar='GREGORIAN'; //将日期格式设置为：16-DEC-02
select sys_context('USERENV','NLS_CALENDAR') FROM DUAL; //查看当前日历格式
alter session set nls_date_format='"罗瑞明"YY"年"MM"月"DD"日"'; //自定义日期格式
alter session set nls_date_format='YYYY-MM-DD';
3、 查询时，对于常量值一定要注意区分大小写，这不同于SQL Server

四、 注意事项
1、 使用EDIT命令进入编程器之后：最后的“/”同命令行的分号“；”意义一样，但是不能既使用“；”又使用“/”
2、 使用SQL*PLUS查看表结构：SELECT table_name FROM user_tables;其中user_tables是一个数据库系统表（数据字典），用于存储数据库信息如表和用户。
3、 查看表的结构定义：DESC/DESCRIBLE tablename；DESC是一个命令，后可不跟分号
1、 对于SQL中的关键字，字段、表名等大小写不区分，但对于字符串内的内容大小写是严格区分的
4、 COUNT(*)与COUNT(字段名)的区分，前者可以包含NULL的记录，后者是不包含NULL的记录
5、 查看完整性约束信息：SELECT constraint_name FROM user_constraints WHERE table_name=’EMP’;
6、 查看表、视图、同义词等对象的信息：SELECT * FROM TAB；其中TAB也是一个系统表
7、 查询系统对象（表、视图、索引等等）的语句：select object_name from user_objects
8、 列出系统名称：help index;或者help <名称名>
9、 将表改为新名：RENAME TO ;
10、 退出SQL*PLUS的方法：输入命令EXIT/QUIT均可
11、 表名可以包含：$_#，但是必须以字母开头，也可以是中文字
12、 从系统表中提取系统日期：select sysdate from dual;
13、 指定数据库的默认语言, 默认值:根据操作系统而定.修改系统语言环境：

Alter session set nls_language=‘American/ Simplified Chinese’;
14、 建议页宽和页高：set linesize 120;和set pagesize 24;
15、 显示所有系统参数的状态：show all;
或者查看文件：C:\Oracle\iSuites\BIN\login.sql，当对该文件修改后，会在启动SQL*PLUS自动执行该login.sql文件
16、 显示系统输出的内容参数设置：输出的内容放到缓冲区中
set serveroutput on size 1000000(最大数)；或者DBMS_OUTPUTENABLE
17、 DUAL［'dju:el] 是一个伪表，有人称之为空表，因为在Oracle里，任何SELECT 语句都必须是SELECT …FROM tablename的格式，所以当查询一些常量值或一些函数值时通常用DUAL当作伪表处理，如：select sysdate from dual;查看当前系统日期
18、 常用系统函数：
1) ABS(n)：返回n绝对值，select abs(-1) from dual;
2) POWER(n,power)：返回n的power次幂，select power(3,2) from dual;
3) ROUND(n,precision)：返回按精度四舍五入的n值，select round(123.476,1) from dual;
4) TRUNC(n,precision)：按特定精度截断n值，select trunc(123.476,1) from dual;
5) FLOOR(num_expr)：返回小于或等于数值表达式的最大整数
6) CEIL(num_expr)：返回大于或等于数值表达式的最小整数
7) MOD(num1,num2)：求两个数的余数, select MOD(1600,300) from dual
8) UPPER(char_expr)：将字符表达式全部转换为大写
9) LOWER(char_expr)：将字符表达式全部转换为小写
10) LTRIM(char_expr)：截取字符串最左端的空格，如：select ltrim(' abc') from dual
11) TRIM(char_expr)：截取字符串两端的空格
12) RTRIM(char_expr)：截取字符串最右端的空格
13) SQRT(num_expr)：取得某数的平方根，如select sqrt(25) from dual
14) LENGTH(char_expr)：求字符串的长度，如：
SELECT * FROM EMP WHERE LENGTH(ENAME)=15;
15) INSTRchar_expr1,char_expr2)：返回一个整数值，判断一个字符串2是否在另一个字符串1里，若值为0则不存在，否则返回它存在的位置，如：select * from emp where INSTR(ENAME,'R')=0
16) SUBSTR(char_expr,n1,n2)：从一个字符串里截取指定长度的字符串，如：select substr(ename,1,3) AS NAME from emp;
17) REPLACE(newstring,oldstring)： 用newstring替换oldstring，如：
select ename,replace('goldens'ename) from emp;
REPLACE(char_expr,char1,char2)：在字符串char_expr中，找到char1,然后用char2替换，如：select ename,replace(ename,'A','a') from emp;
18) INITCAP(char_expr)：将字符串首字母大写，其余小写，如：SELECT * FROM EMP WHERE ENAME=INITCAP(ENAME);
19) SYSDATE：是一个求系统时间的函数，select sysdate from dual;
20) TO_DATE(char_expr,format)将字符串转换成指定的日期格式，如：
Select To_date(‘2002-11-22’,’YYYY-MM-DD’) FROM dual;
21) TO_CHAR(date,format)：将日期转换成指定的格式，如：
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY')AS YEAR,TO_CHAR(HIREDATE,'MM') AS MONTH,
TO_CHAR(HIREDATE,'DD') AS DAY FROM EMP;
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW"
FROM DUAL;
22) TO_NUMBER(num_char_expr)：把字符转换为NUMBER，如：
select TO_NUMBER('34.55')+50 FROM DUAL;
23) ADD_MONTHS(date,number)：将日期加上整数所表示的月份所获取的新的日期，如：SELECT * FROM EMP WHERE ADD_MONTHS(HIREDATE,120)<=SYSDATE;
24) MONTHS_BETWEEN(date1,date2)：返回两个日期所相差的月份数
SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DDYYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;
25) CONCAT(char1,char2)：字符串连接函数，如：
SELECT CONCAT(CONCAT(ename, ' is a '), job) "Job"
FROM emp WHERE empno = 7900;
”||”符号：也是字符串连接符,如：
select 'abc' || 'def' from dual；
SELECT 'Name is ' || ename FROM emp;
select TO_NUMBER('2') || 3 +10 FROM DUAL;结果为：33
26) NVL(Field,expr)函数：若某个值为空值，则用expr代替显示，如select nvl(null,0) from dual;
27) NVL2(Field,expr1,expr2)：若Field不为空，用expr1显示，若为空，用expr2显示，如：
SELECT ename, NVL2(TO_CHAR(COMM), 'SAL ’||‘COMM', 'SAL')
income  FROM emp WHERE deptno = 30;
28）DECODE函数：相当于一条件语句(IF)，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及对应结果值组成的若干序偶形式。如果匹配不成功，则函数也有默认的返回值
DECODE(input_value,
Value,result[,
Value,result…][,
Default_result]);
举例：SELECT checkup_type,
DECODE(blood_test_flag,
’Y’,’Yes’,
‘N’,’No’,
NULL,’None’,
‘Invalid’)
FROM checkup;
如果列blood_test_flag的值是’Y’，函数返回’Yes’；如果列的值是NULL，那么返回值为’None’,如果列的值是’N’则返回的值为’No’，否则表示当前值无效显示为’Invalid’。它的一个重要的技术就是为了将一个表的行换成列。
19、 SQL执行UNION查询：
select empno,ename from emp union select deptno,dname from dept
注意区别：select emp.empno,emp.ename,dept.deptno,dept.dname from emp,dept
后者表示联接，前者表示联合
20、 更新或插入记录时，可以给字段赋空值，如：
update emp set job=null where ename='Goldens';
insert into emp(empno,ename) values(11,null)
21、 提交所有的命令及语句的执行，并写到数据库：commit;
22、 若不能用scott/tiger登录，考虑用其他用户登录，然后在提示符下执行文件： C:\Oracle\Oracle81\sqlplus\demo\demobld.sql
23、 下面列出一些重要数据词典表
28) USER_TABLES //当前用户所拥有的关系表
29) USER_VIEWS //当前用户所拥有的视图
30) USER_COL_PRIVS //对可访问表或视图列的授权，用户是其所有者、授权 人或被授权人。
31) USER_CONSTRAINTS //当前用户表的约束
32) USER_DEPENDENCIES //
33) USER_ERRORS //当前用户的视图、包、过程、对象类型等的编译错误
34) USER_LOBS //包含在当前用户所拥有表中的LOB
35) USER_TAB_COLUMNS //当前用户所拥有的表的列
36) USER_TRIGGERS //当前用户所拥有的数据库触发器
37) USER_TYPES //当前用户所拥有的对象类型
24: connect SYSTEM/SYSTEM@myoracle as sysdba
grant sysdba to SYSTEM;
shutdown immediate;     
SQL> startup mount   
SQL> alter database archivelog;     
SQL> alter database open;     
SQL> alter system archive log start;     
SQL> exit     

      SQL*PLUS命令的使用大全

       Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。
   我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。
   除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
   下面就介绍一下一些常用的sql*plus命令：

1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
@与@@的区别是什么？
@等于start命令，用来运行一个sql脚本文件。
@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件。该命令使用是一般要指定要执行的文件的全路径，否则从缺省路径(可用SQLPATH变量指定)下读取指定的文件。
@@用在sql脚本文件中，用来说明用@@执行的sql脚本文件与@@所在的文件在同一目录下，而不用指定要执行sql脚本文件的全路径，也不是从SQLPATH环境变量指定的路径中寻找sql脚本文件，该命令一般用在脚本文件中。
如：在c:\temp目录下有文件start.sql和nest_start.sql，start.sql脚本文件的内容为：
@@nest_start.sql     - - 相当于@ c:\temp\nest_start.sql
则我们在sql*plus中，这样执行：
SQL> @ c:\temp\start.sql

2. 对当前的输入进行编辑
SQL>edit

3. 重新运行上一次运行的sql语句
SQL>/

4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
   在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。

5. 关闭spool输出
SQL> SPOOL OFF
   只有关闭spool输出，才会在输出文件中看到输出的内容。

6．显示一个表的结构
SQL> desc table_name

7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
     DEPTNO DNAME                        LOC
---------- ---------------- ---------
         10 ACCOUNTING                   NEW YORK
sql>col  LOC heading location
sql>select * from dept;
    DEPTNO DNAME                        location
--------- ---------------------------- -----------
        10 ACCOUNTING                   NEW YORK

2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department  name           Salary
---------- ---------- ----------
         10 aaa                11        
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
            Employee
Department  name           Salary
---------- ---------- ---------- 
         10 aaa                11
note: the col heading turn into two lines from one line.

3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
      EMPNO ENAME      JOB       
---------- ----------     ---------
       7369 SMITH      CLERK     
       7499 ALLEN      SALESMAN  
7521 WARD       SALESMAN  
Sql> col ename format a40
      EMPNO ENAME                                    JOB
----------   ----------------------------------------         ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
       7521 WARD                                    SALESMAN

4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
      EMPNO           ENAME                   JOB
---   ----------------------------------------       ---------
       7369 SMITH                                    CLERK
       7499 ALLEN                                    SALESMAN
7521 WARD                                     SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边

5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
      EMPNO           ENAME
----------     ----------------------------------------
       7369 SMITH
       7499 ALLEN
7521 WARD

6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name        Salary    Commission
---------- ---------- --------- ----------
30          ALLEN        $1,600    300

7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text

8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
        COL1
--------------------
HOW ARE YOU?

SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A

9). 显示列的当前的显示属性值
SQL> COLUMN column_name

10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS

8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
  WHERE SAL < 2500
  ORDER BY DEPTNO;
DEPTNO      ENAME         SAL
---------- ----------- ---------
10           CLARK        2450
MILLER      1300
20            SMITH       800
ADAMS       1100

9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n

SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300

20 SMITH 800
ADAMS 1100

10. 显示对BREAK的设置
SQL> BREAK

11. 删除6、7的设置
SQL> CLEAR BREAKS

12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}

1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}

2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}

3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
   默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数

4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替

5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
   如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。

6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。

7).显示时，用text值代替NULL值
SQL> SET NULL text

8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题

9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF} 
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput