下载文档原格式
Sql*plus的使用Sql*plus的使用Sql*plus介绍Sql*plus是oracle提供的一个工具程序，既可以在oracle服务器使用，也可以在oracle 客户端使用。
在windows下分两种，sqlplus.exe是命令行程序，sqlplusw.exe是窗体程序，通常我们在开始菜单中启动的是后者，两者的功能是一致的。
Sql*plus是一个最常用的工具，具有很强的功能，主要有：1. 数据库的维护，如启动，关闭等，这一般在服务器上操作。
2. 执行sql语句执行pl/sql。
3. 执行sql脚本。
4. 数据的导出，报表。
5. 应用程序开发、测试sql/plsql。
6. 生成新的sql脚本。
7. 供应用程序调用，如安装程序中进行脚本的安装。
2 dual表dual是一张系统表，同时也被定义成了public同义词。
它只有一个字段和一条记录。
该表本身的结构和数据没有什么意义，主要是借助该表进行其它操作。
如：select sysdate from dual; --获取函数值select 21+15*3 from dual; --计算表达式的值说明：不要对dual表进行ddl与dml操作，只进行查询操作。
3 sql*plus使用3.1 启动sql*plus1. 不带参数启动启动sqlplusw.exe程序，会弹出登陆框，让输入用户名、密码和连接字符串，在用户名中输入“/nolog”，表示先进入sql>提示符，先不连接数据库，下面可以利用connect命令连接数据库。
启动sqlplus.exe程序，会提示输入用户名与密码。
如果用户名输入2. 带参数启动下面列举一些最常见的方式，还有很多可选参数。
1) 不连接数据库sqlplus /nolog2) 连接数据库sqlplus username/password3) 使用net8连接字符串连接数据库sqlplus username/password@connstr4) 连接后执行filename指定的sql脚本,sql脚本中是sql命令和sql*plus的设置命令sqlplus username/password[@connstr] @filename3.2 sql*plus的命令3.2.1 帮助命令1. help命令格式：help 命令名，用于知道某个具体命令的帮助信息。
PL/SQL Developer导入导出数据库方法及说明2011-07-26 13:05 leeli1987 CSDN博客字号：T| T本文主要介绍了利用PL/SQL Developer导入或导出数据库的步骤以及导入导出过程中的一些说明，希望能对您有所帮助。
AD：51CTO学院：IT精品课程在线看！PL/SQL Developer是Oracle数据库中用于导入或导出数据库的主要工具，本文主要介绍了利用PL/SQL Developer导入和导出数据库的过程，并对导入或导出时的一些注意事项进行了说明，接下来我们就一一介绍。
导出步骤：1 tools ->export user object 选择选项，导出.sql文件。
2 tools ->export tables-> Oracle Export 选择选项导出.dmp文件。
导入步骤：注：导入之前最好把以前的表删除，当然导入另外数据库除外。
1 tools->import tables->SQL Inserts 导入.sql文件。
2 tools->import talbes->Oracle Import然后再导入dmp文件。
一些说明：Tools->Export User Objects导出的是建表语句（包括存储结构）。
Tools->Export Tables里面包含三种导出方式，三种方式都能导出表结构以及数据，如下：∙Oracle Export∙Sql Insert∙pl/sql developer第一种是导出为.dmp的文件格式，.dmp文件是二进制的，可以跨平台，还能包含权限，效率也很不错，用得最广。
第二种是导出为.sql文件的，可用文本编辑器查看，通用性比较好，但效率不如第一种，适合小数据量导入导出。
尤其注意的是表中不能有大字段（blob,clob,long），如果有，会提示不能导出(提示如下： table contains one or more LONG columns cannot export in sqlformat,user Pl/sql developer format instead)。
使用SQL*PLUS,构建完美excel或html输出通过SQL*PLUS我们可以构建友好的输出，满足多样化用户需求。
本例通过简单示例，介绍通过sql*plus输出xls，html两种格式文件.首先创建两个脚本:1.main.sql用以设置环境，调用具体功能脚本2.功能脚本-get_tables.sql为实现具体功能之脚本通过这样两个脚本可以避免spool中的冗余信息，参考:如何去除SQLPLUS中SPOOL的冗余信息示例如下:1.main.sql脚本:[oracle@jumper utl_file]$ more main.sqlset linesize 200set term off verify off feedback off pagesize 999set markup html on entmap ON spool on preformat offspool tables.xls@get_tables.sqlspool offexit2.get_tables.sql脚本:[oracle@jumper utl_file]$ more get_tables.sqlselect owner,table_name,tablespace_name,blocks,last_analyzed from all_tables order by 1,2;3.执行并获得输出:[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @mainSQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 25 10:30:11 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - ProductionDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - Production[oracle@jumper utl_file]$ ls -l tables.xls-rw-r--r-- 1 oracle dba 69539 Apr 25 10:30 tables.xls [oracle@jumper utl_file]$此处输出为xls文件，通过下图我们可以看到输出效果:把main.sql脚本中的，spool tables.xls更改为spool tables.htm,我们可以获得htm格式输出，效果如下图:历史上的今天...>> 2010-04-25文章:环境：windows 2000 server + oracle 8.1.71.读文件举例：-- 环境 windows 2000 server + oracle 8.1.7-- 先在 init.ora中的参数utl_file_dir-- 例: utl_file_dir=(d:\test,e:\\,e:\share)--set serveroutput on size 1000000 format wrappedcreate or replace procedure read_txtfile( -- 读一个文本文件，并在SQLplus 中显示其内容path in varchar2,name in varchar2)asl_output utl_file.file_type;str varchar2(1000);beginl_output:=utl_file.fopen(path,name,'r',2000); -- 每行最大字节数最多为32K bytes--l_output:=utl_file.fopen(path,name,'r'); -- 每行最大字节数最多为1023 byteslooputl_file.get_line(l_output,str);dbms_output.put_line(str);end loop;exceptionwhen no_data_found thenutl_file.fclose(l_output);when utl_file.invalid_path thenraise_application_error(-20001,'INVALID_PATH!');when utl_file.invalid_mode thenraise_application_error(-20002,'INVALID_MODE!');when utl_file.invalid_filehandle thenraise_application_error(-20003,'INVALID_FILEHANDLE!');when utl_file.invalid_operation thenraise_application_error(-20004,'INVALID_OPERATION!');when utl_file.read_error thenraise_application_error(-20005,'READ_ERROR!');when utl_file.write_error thenraise_application_error(-20006,'WRITE_ERROR!');when utl_file.internal_error thenraise_application_error(-20007,'INTERNAL_ERROR!');when others thenstr:=SQLerrm(SQLcode);dbms_output.put_line(str);end;/--SQL> execute read_txtfile('d:\test','test.txt');--日本SONY(索尼) 1万/月,仅要研究生--韩国三星电子中国总部 25万/年--法国索姆软件,年薪20万/年,赴欧工作--美国Cisco(思科)15000/月,仅要研究生--美国INTEL(英特尔) 13000/月--美国IBM 5000左右/月--德国西门子 8000 /月--PL/SQL 过程已成功完成。
OraclePLSQL调试的输出方法我们在PL/SQL过程中需要对过程进行调试，查看变量的内容。
可以使用下面方法。
这里假设我们需要查看的变量是output，类型为varchar2。
先打开oracle 输出： set serveroutput on size 10000;1.最基本的DBMS_OUTPUT.PUT_LINE()方法。
随便在什么地方，只要是BEGIN和END之间，就可以使用DBMS_OUTPUT.PUT_LINE(output);然而这会有一个问题，就是使用该函数一次最多只可以显示255个字符，否则缓冲区会溢出。
此外，函数DBMS_OUTPUT.ENABLE(20000)这种函数，仅仅是设置整个过程的全部输出缓冲区大小，而非DBMS_OUTPUT.PUT_LINE()的缓冲区大小。
对于超过255个字符的变量，使用DBMS_OUTPUT.PUT_LINE()方法是没有效的。
据说在Oracle10中，解除了这个限制，可以输出任意大小的字符串。
declareoutput varchar2(200);beginoutput:='...'; //赋值DBMS_OUTPUT.PUT_LINE(output);end;2.使用表的方法。
首先建立一张表，然后在PL/SQL中将要输出的内容插到表中。
然后再从表中查看内容。
对于这种方法一次可以输出几千个字符。
(1) create table my_output{id number(4),txt varchar2(4000));(2) declareoutput varchar2(4000);strSql varchar2(4500);count number(4):=0;beginstrSql:='delete * from my_output';EXECUTE IMMEDIATE strSql;output:='...'; //赋值count:=count+1;strSql:='Insert into my_output value (count,'''||output||''')';--''在单引号中相当于字符'EXECUTE IMMEDIATE strSql;end;3.使用输出文件的方法。
.SQLPLUS命令的使用大全1. 执行一个SQL脚本文件SQL>start file_nameSQL>@ file_name我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
2. 对当前的输入进行编辑SQL>edit3. 重新运行上一次运行的sql语句SQL>/4. 将显示的内容输出到指定文件SQL> SPOOL file_name在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5. 关闭spool输出SQL> SPOOL OFF只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构SQL> desc table_name7. COL命令：主要格式化列的显示形式。
该命令有许多选项，具体如下：COL[UMN] [{ column|expr} [ option ...]]Option选项可以是如下的子句:ALI[AS] aliasCLE[AR]FOLD_A[FTER]FOLD_B[EFORE]FOR[MAT] formatHEA[DING] textJUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}LIKE { expr|alias}NEWL[INE]NEW_V[ALUE] variableNOPRI[NT]|PRI[NT]NUL[L] text.OLD_V[ALUE] variableON|OFFWRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]1). 改变缺省的列标题COLUMN column_name HEADING column_headingFor example:Sql>select * from dept;DEPTNO DNAME LOC---------- ---------------------------- ---------10 ACCOUNTING NEW YORKsql>col LOC heading locationsql>select * from dept;DEPTNO DNAME location--------- ---------------------------- -----------10 ACCOUNTING NEW YORK2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：Sql>select * from empDepartment name Salary---------- ---------- ----------10 aaa 11SQL> COLUMN ENAME HEADING ’Employee|Name’Sql>select * from empEmployeeDepartment name Salary---------- ---------- ----------10 aaa 11note: the col heading turn into two lines from one line.3). 改变列的显示长度：FOR[MAT] formatSql>select empno,ename,job from emp;EMPNO ENAME JOB---------- ---------- ---------7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMANSql> col ename format a40EMPNO ENAME JOB---------- ---------------------------------------- ---------7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMAN.4). 设置列标题的对齐方式JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}SQL> col ename justify centerSQL> /EMPNO ENAME JOB---------- ---------------------------------------- ---------7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMAN对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边5). 不让一个列显示在屏幕上NOPRI[NT]|PRI[NT]SQL> col job noprintSQL> /EMPNO ENAME---------- ----------------------------------------7369 SMITH7499 ALLEN7521 WARD6). 格式化NUMBER类型列的显示：SQL> COLUMN SAL FORMAT $99,990SQL> /EmployeeDepartment Name Salary Commission---------- ---------- --------- ----------30 ALLEN $1,600 3007). 显示列值时，如果列值为NULL值，用text值代替NULL值COMM NUL[L] textSQL>COL COMM NUL[L] text8). 设置一个列的回绕方式WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]COL1--------------------HOW ARE YOU?SQL>COL COL1 FORMAT A5SQL>COL COL1 WRAPPEDCOL1-----.HOW ARE YOU?SQL> COL COL1 WORD_WRAPPEDCOL1-----HOWAREYOU?SQL> COL COL1 WORD_WRAPPEDCOL1-----HOW A9). 显示列的当前的显示属性值SQL> COLUMN column_name10). 将所有列的显示属性设为缺省值SQL> CLEAR COLUMNS8. 屏蔽掉一个列中显示的相同的值BREAK ON break_columnSQL> BREAK ON DEPTNOSQL> SELECT DEPTNO, ENAME, SALFROM EMPWHERE SAL < 2500ORDER BY DEPTNO;DEPTNO ENAME SAL---------- ----------- ---------10 CLARK 2450MILLER 130020 SMITH 800ADAMS 11009. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n 个空行。
利用SQL*PLUS导出成EXCEL和html的功能实现报表统计:也就是生成HTML格式，但是同样的格式输出到EXCEL中也能正常显示。
关键就是这些参数的设定set markup html on entmap ON spool on preformat off参数注解如下:===================================================================== ===TABLE text设置<TABLE>标签的属性，如BORDER, CELLPADDING, CELLSPACING和WIDTH。
默认情况下，<TABLE> 的WIDTH属性设置为90%，BORDER属性设置为1。
ENTMAP {ON|OFF}指定在SQL * Plus中是否用HTML字符实体如<, >, " and &等替换特殊字符<, >, " and & 。
默认设置是ON。
SPOOL {ON|OFF}指定是否在SQL*Plus生成HTML标签<HTML> 和<BODY>, </BODY> 和</HTML>。
默认是OFF。
注：这是一个后台打印操作，只有在生成SPOOL文件生效，在屏幕上并不生效。
PRE[FORMAT] {ON|OFF}指定SQL*Plus生成HTML时输出<PRE>标签还是HTML表格，默认是OFF，因此默认输出是写HTML表格。
===================================================================== ====通过SQL*PLUS我们可以构建友好的输出，满足多样化用户需求。
本例通过简单示例，介绍通过sql*plus输出xls，html两种格式文件.首先创建两个脚本:1.get_d_stat.sh 用以设置环境，主要调用具体脚本2.get_d_stat.sql 为获取具体数据之脚本创建实验表t_grade如下：1.create table t_grade(id int,name varchar2(10),subjectvarchar2(20),grade number);2.insert into t_grade values(1,'ZORRO','语文',70);3.insert into t_grade values(2,'ZORRO','数学',80);4.insert into t_grade values(3,'ZORRO','英语',75);5.insert into t_grade values(4,'SEKER','语文',65);6.insert into t_grade values(5,'SEKER','数学',75);7.insert into t_grade values(6,'SEKER','英语',60);8.insert into t_grade values(7,'BLUES','语文',60);9.insert into t_grade values(8,'BLUES','数学',90);10.insert into t_grade values(9,'PG','数学',80);11.insert into t_grade values(10,'PG','英语',90);12.insert into t_grade values(11,'TOM','化学',90);mit;脚本get_d_stat.sh内容如下：1.sqlplus -s dba_user/dbapasswd<<EOF2.set linesize 2003.set term off verify off feedback off pagesize 9994.set markup html on entmap ON spool on preformat off5.spool/apps/dba_tool/get_data/get_d_stat_`date--date "1 daysago" +%F`.xls6.7.--spool get_d_stat_`date +%F`.xls8.9.--spool tables.html10.11.@/apps/dba_tool/get_data/get_d_stat.sql;12.spool off13.exit;14.EOF脚本get_d_stat.sql 内容如下：1.select name,sum(case when SUBJECT='语文'then GRADE else 0 end)"语文",sum(case when SUBJECT='数学'then GRADE else 0 end)"数学",2.sum(case when SUBJECT='英语'then GRADE else 0 end)"英语",sum(case when SUBJECT='化学'then GRADE else0 end)"化学"from t_grade group by name;运行脚本get_d_stat.sh后，会在/apps/dba_tool/get_data/目录下生成到此为止，利用SQL*PLUS导出成EXCEL和html的功能实现报表统计已经成功。
第5章SQL*Plus使用Oracle11g中提供了OEM和SQL*PLUS两种方式对数据库进行管理。
OEM（Oracle Enterprise Manager，Oracle企业管理器）提供一种基于Web界面的图形化的管理手段，特点是直观简单，但灵活性比较差。
SQL*PLUS则是通过执行命令的方式管理数据库，非常灵活且功能全面，同时能加深用户对复杂命令选项的理解。
因此SQL*PLUS是数据库管理人员通常采用的一种管理方式。
通过SQL*Plus可运行SQL语句和PL/SQL块来处理数据和生成报表，同时可执行数据库管理操作。
本章将主要介绍SQL*Plus一些常用功能和命令的使用，为后边的章节奠定一个基础。
5.1 用SQL*PLUS与数据库交互5.1.1 SQL*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所示。
sqlplus格式化输出；SQL> set linesize 100SQL> col parameter for a40SQL> col value for a40作为⼀名oracle dba,sqlplus是我们必须熟练掌握的⼯具。
但是，命令⾏下的sqlplus有时表现的并不是那么友好，本⼈搜集了部分关于格式化sqlplus结果的⽂档，总结整理如下：1）、修改⽤户配置⽂件login.sql，添加如下内容DEFINE _editor=vi -- windows下应改为define _eidtor=notepadSET serveroutput on size 1000000SET trimspool onSET long 5000SET linesize 120SET PAGESIZE 16---- Used by Trusted OracleCOLUMN ROWLABEL FORMAT A15----设置SHOW ERRORS 格式COLUMN LINE/COL FORMAT A8COLUMN ERROR FORMAT A65 WORD_WRAPPED---- 设置 SHOW SGA 格式COLUMN name_col_plus_show_sga FORMAT a24---- 设置SHOW PARAMETERS 的格式COLUMN name_col_plus_show_param FORMAT a36 HEADING NAMECOLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE----设置autotrace的格式COLUMN id_plus_exp FORMAT 990 HEADING iCOLUMN parent_id_plus_exp FORMAT 990 HEADING pCOLUMN plan_plus_exp FORMAT a60COLUMN object_node_plus_exp FORMAT a8COLUMN other_tag_plus_exp FORMAT a29COLUMN other_plus_exp FORMAT a44----设置sqlpromptCOLUMN gname new_value gnameSET termout offselect lower(user)||'@'||global_name gname from global_name;SET sqlprompt '&gname>'SET termout on2）、由于表中记录过长，⼀条记录换⾏显⽰，显得⽐较错乱，借鉴tom⼤师转列为⾏的思想，创建如下过程：CREATE OR REPLACE PROCEDURE print_table( p_query in varchar2 )AUTHID CURRENT_USERISl_theCursor integer default dbms_sql.open_cursor;l_columnValue varchar2(4000);l_status integer;l_descTbl dbms_sql.desc_tab;l_colCnt number;l_rowCnt number:=1;BEGINdbms_sql.parse( l_theCursor, p_query, dbms_sql.native );dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);for i in 1 .. l_colCnt loopdbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);end loop;l_status := dbms_sql.execute(l_theCursor);while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loopfor i in 1 .. l_colCnt loopdbms_sql.column_value( l_theCursor, i, l_columnValue );dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )|| ': ' || l_columnValue );end loop;dbms_output.put_line();dbms_output.put_line( '[ROW:'||l_rowCnt||']-----------------------------' );l_rowCnt := l_rowCnt + 1;end loop;dbms_sql.close_cursor( l_theCursor );EXCEPTIONwhen others thendbms_sql.close_cursor( l_theCursor );RAISE;END print_table;以下的⽂章主要是对化的相关实际的介绍，我们⼤家都知道在实际的应⽤中是很好的，但是其在格式化的相关数据上，对与set是不⼤熟悉的，于是⽹上找了下，在此备注下：1. SQL>set colsep ; 域输出分隔符1. SQL>set newp none 查询出来的数据分多少页，如果需要连续的数据，中间不要出现空⾏就把newp设置为none，这样输出的都是连续的，中间没有空⾏之类的1. SQL>set echo off; 显⽰start启动的中的每个sql命令，缺省为on1. SQL> set echo on 设置运⾏命令是是否显⽰1. SQL> set feedback on; 设置显⽰“已选择XX⾏”1. SQL>set feedback off; 回显本次sql命令的条数，缺省为on即去掉最后的 "已经选择00⾏"1. SQL>set heading off; 输出域标题，缺省为on 设置为off就去掉了结果的名，只显⽰Oracle sqlplus格式化数据 1. SQL>set pagesize 0; 输出每页⾏数，缺省为24,为了避免，可设定为0 。
