第5章SQL-Plus使用

第5章SQL*Plus使用1第5章SQL*Plus使用Oracle11g中提供了OEM和SQL*PLUS两种方式对数据库进行管理。OEM（OracleEnterpriseManager，Oracle企业管理器）提供一种基于Web界面的图形化的管理手段，特点是直观简单，但灵活性比较差。SQL*PLUS则是通过执行命令的方式管理数据库，非常灵活且功能全面，同时能加深用户对复杂命令选项的理解。因此SQL*PLUS是数据库管理人员通常采用的一种管理方式。通过SQL*Plus可运行SQL语句和PL/SQL块来处理数据和生成报表，同时可执行数据库管理操作。本章将主要介绍SQL*Plus一些常用功能和命令的使用，为后边的章节奠定一个基础。5.1用SQL*PLUS与数据库交互5.1.1SQL*Plus的主要功能SQL*Plus工具主要用来做数据查询和数据处理。利用SQL*Plus可将SQL和Oracle专有的PL/SQL结合起来进行数据查询和处理。利用SQL*Plus工具可实现以下操作：对数据库进行管理。启动/停止数据库实例连接数据库，定义变量。对SQL语句、PL/SQL块执行插入、修改、删除、查询、运行和调试操作。对查询结果的格式化、运算处理、保存、打印以及输出Web格式。显示任何一个表的字段定义与终端用户进行交互。5.1.2启动SQL*Plus连接数据库1．用SQLPLUS命令连接数据库在使用SQL*Plus之前必须先启动SQL*Plus。基本步骤如下：（1）依次选择【开始菜单】|【程序】|【Oracle-OraDb11g_home1】|【应用程序开发】|【SQL*Plus】命令，打开SQL*Plus的主界面。（2）输入相应的用户名和登录密码，这是在安装Oracle时指定的。输入正确的用户名和密码后，按Enter键，SQL*Plus将连接到相应的数据库。例如以System用户登录，登录密码是安装时确定的密码，如图5.1所示。第5章SQL*Plus使用2图5.1SQL*Plus登录界面也可以通过运行命令窗口启动SQL*Plus。打开【开始菜单】|【运行】，出现运行对话框。此时有两种选择：一是在运行对话框里输入cmd命令，打开命令提示窗口，在窗口中输入SQLPLUS命令启动SQL*Plus工具，如图5.2所示；图5.2通过cmd命令启动sqlplusC:\DocumentsandSettings\Ownersqlplussystem/passwordSQL*Plus:Release11.1.0.6.0-Productionon星期四4月2209:35:192010Copyright(c)1982,2007,Oracle.Allrightsreserved.连接到:OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-ProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL二是在运行对话框里直接输入SQLPLUS命令来启动SQL*Plus工具，如图5.3所示。第5章SQL*Plus使用3图5.3通过运行对话框直接启动SQL*PlusSQLPLUS命令的基本语法如下：sqlplus[username]/[password][@server]命令中方括号里的参数为可选项。其中username表示要登录的数据库用户名，password为相应用户密码，@server用于连接到远程数据库时指定网络服务名。当连接到本地数据库时不需要指定网络服务名。如果sqlplus命令中省略username或password，那么命令对话框会提示输入相应的用户名和密码，如下所示。C:\DocumentsandSettings\OwnersqlplusSQL*Plus:Release11.1.0.6.0-Productionon星期四4月2209:39:182010Copyright(c)1982,2007,Oracle.Allrightsreserved.请输入用户名:system输入口令:连接到:OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-ProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQL2．连接命令(1)、CONNCET命令CONNECT把当前事务提交到数据库，断开当前用户连接并用指定的用户名重新连接到Oracle。CONNECT命令的基本语法为：CONN[ECT][{username[/password][@connect_identifier]}[AS{SYSOPER|SYSDBA}]]第5章SQL*Plus使用4其中CONN[ECT]表示CONNECT命令可以简写为CONN。SQL*PLUS里命令的都可以采用简写的形式，但对初学者来说，完整的命令可以帮助我们理解和记忆。username和password是可选参数，如果省略的话，SQL*PLUS会提示输入，如下：SQLconnect请输入用户名:system输入口令:已连接。SQL@connect_identifier用于指定连接到一个远程数据库，例如使用用户名HR和口令HR连接到OracleNet别名为FLEETDB的数据库。HR这个用户是Oracle自带安装的一个示例模式，默认的HR用户是被锁定的，在使用该用户之前需要对它进行解锁操作，具体步骤如下：①先以system身份连接到数据库SQLconnectsystem/password已连接。②解锁用户帐号SQLalteruserhraccountunlock;用户已更改。注：命令里的user表示HR属于user这个表空间③修改其登录密码SQLalteruserhridentifiedbyhr;用户已更改。对于一个刚解锁的用户来说，其密码都是过期的，必须要修改其密码以保证密码有效。如果此处不修改密码，在用该用户登录时会提示密码过期和修改密码。④以HR用户连接到FLEETDB数据库CONNECTHR/HR@FLEETDBAS{SYSOPER|SYSDBA}是可选项，表示用户以SYSOPER或SYSDBA的身份登录数据库。SYSOPER和SYSDBA是两个特权身份，具有最高的系统权限。SYSDBA身份默认是连接到SYS模式，而SYSOPER身份默认连接到PUBLIC模式。例如HR以SYSDBA连接数据库：SQLconnecthr/hrassysdba;已连接。第5章SQL*Plus使用5(2)、DISCONNECT命令DISCONNECT命令用于短开已经存在的数据库连接，但并不会退出SQL*PLUS。基本语法为：PASSW[ORD][username]示例如下：SQLdisconnect从OracleDatabase11gEnterpriseEditionRelease11.1.0.6.0-ProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions断开SQL(3)、PASSWORD命令PASSWORD命令用于修改用户密码。任何用户都可以修改自身密码，但如果要修改其他用户的密码，则必须以SYS或SYSTEM身份登录。基本语法为：PASSW[ORD][username]例如将HR用户的密码改为123，操作如下：SQLconnecthr/hr已连接。SQLpasswordhr更改hr的口令旧口令:新口令:重新键入新口令:口令已更改SQLconnecthr/123已连接。SQL5.2使用SQL*Plus的编辑功能SQL*PLUS缓冲区用来临时存放用户最近运行的SQL语句或PL/SQL块。通过在缓冲区中存储这些命令，用户能够重新调用、编辑和运行那些最近输入的SQL语句。5.2.1SQL语句、PL/SQL块与SQL*Plus命令的区别在SQL*Plus中可以运行三种类型的命令：SQL*Plus命令、SQL语句和PL/SQL块。但三者之间是有区别的。SQL*Plus命令主要用来格式化查询结果、设置选择、编辑及存储SQL命令、以设置查询结果的显示格式，并且可以设置环境选项。第5章SQL*Plus使用6SQL语句是以数据库为操作对象的语言，主要包括数据定义语言DDL、数据操纵语言DML和数据控制语言DCL以及数据存储语言DSL。当输入SQL语句后，SQL*Plus将其保存在内部缓冲区中。SQL*PLUS命令输入完毕后可直接执行，，而当SQL命令输入完毕时，需要在末尾加上分号（；），否则SQL*PLUS不识别。也可以采用换行并输入斜线（/）来表示SQL命令完毕。例如：SQLselecttable_namefromuser_tables;或者SQLselecttable_namefromuser_tables2/由于SQL不具备过程控制功能，所以，为了能够与其他语言一样具备面向过程的处理功能，在SQL中加入了诸如循环、选择等面向过程的处理功能，由此形成了PL/SQL。所有PL/SQL语句的解释均由PL/SQL引擎来完成。PL/SQL块同样以数数据为操作对象，使用PL/SQL块可编写过程、触发器和包等数据库永久对象。5.2.2编辑命令在SQL*PLUS中执行的SQL语句或PL/SQL块会临时存放在SQL缓冲区中，直到新的语句将其覆盖。通过SQL*PLUS的编辑命令，可以显示、修改和运行这些存放在缓冲区中的语句。1．LIST命令LIST命令用于列出SQL*PLUS缓冲区中指定的一行或若干行内容。基本语法：L[IST][n|nm|n*|nLAST|*|*n|*LAST|LAST]n和m表示行的序号，*表示当前行，LAST表示最末行。省略所有参数的LIST命令表示显示所有行内容。举例：假定当前在SQL*PLUS中输入了如下查询内容：SQLselect*fromjobs2wherejob_id='ST_MAN'3andjob_title='StockManager';例1：显示缓冲区所有内容，用不带参数的LIST命令SQLlist1select*fromjobs2wherejob_id='ST_MAN'3*andjob_title='StockManager'例2：列出缓冲区中的某一行的内容，需要在LIST命令后指定行号SQLlist22*wherejob_id=''例3：列出缓冲区中的某几行的内容，需要在LIST命令后指定起始行号和结束行号。SQLlist23第5章SQL*Plus使用72wherejob_id='ST_MAN'3*andjob_title='StockManager'例4：列出缓冲区中当前行到最末行的内容，需要引用*和LAST参数SQLlist*last2wherejob_id='ST_MAN'3*andjob_title='StockManager'2．n命令n命令用于指定缓冲区的当前行。在SQL*PLUS中执行SQL语句时，默认情况下是将最后一行命令作为当前行。如果要指定其它行为当前行，就需要用到n命令，其中n代表行号。例如：SQLlist1select*fromjobs2wherejob_id='ST_MAN'3*andjob_title='StockManager'SQL22*wherejob_id='ST_MAN'SQL11*select*fromjobs3．CHANGE命令CHANGE命令用于修改缓冲区当前行中第一次出现的指定文本。基本语法：C[HANGE]/old[/[new]]old表示当前行需要替换的部分，new是用来替换的内容举例：假定当前在SQL*PLUS中输入了如下查询内容SQLselect*fromjobswherejob_id='ST