桂林网站制作公司,做网站用的什么编程语言,公司装修哪家比较好,青岛网络推广服务原文地址#xff1a;SQL Plus 一些使用技巧作者#xff1a;☆水『若寒Sql*plus的使用 Sql*plus介绍 Sql*plus是oracle提供的一个工具程序#xff0c;既可以在oracle服务器使用#xff0c;也可以在oracle客户端使用。在windows下分两种#xff0c;sqlplus.exe是命令行程序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 2115*3 from dual; --计算表达式的值 说明不要对dual表进行ddl与dml操作只进行查询操作。 3 sql*plus使用 3.1 启动sql*plus 1. 不带参数启动 启动sqlplusw.exe程序会弹出登陆框让输入用户名、密码和连接字符串在用户名中输入“/nolog”表示先进入sql提示符先不连接数据库下面可以利用connect命令连接数据库。启动sqlplus.exe程序会提示输入用户名与密码。如果用户名输入 2. 带参数启动 下面列举一些最常见的方式还有很多可选参数。 1) 不连接数据库 sqlplus /nolog 2) 连接数据库 sqlplus username/password 3) 使用net8连接字符串连接数据库 sqlplus username/passwordconnstr 4) 连接后执行filename指定的sql脚本,sql脚本中是sql命令和sql*plus的设置命令 sqlplus username/password[connstr] filename 3.2 sql*plus的命令 3.2.1 帮助命令 1. help命令 格式help 命令名 用于知道某个具体命令的帮助信息。 如help connect 2. describe命令 用户查看表的结构获取函数存储过程和包的描述。这是非常有用和常用的一个命令。 如desc user_tables 说明在sql*plus中所有命令都可以用前面的四个字母作为整个命令。 3.2.2 编辑命令 sql*plus会将上一次执行过的sql命令包括sql语句和pl/sql语句包括一行或多行保存到缓存区中可以对缓存区中信息进行编辑。 编辑后可以通过 “/ ”命令执行修改后的缓存区中的命令如果不休改则是执行原有命令。 1. list [n] 命令 显示上一条命令中的第n行如果不指定n则显示上一命令的所有行这样当前行就是最后一行。其它操作会对当前行进行操作所以其它操作需要先执行list命令。 如 begin insert into test values(1); end; / list 2. change命令 编辑当前行的内容先用list命令指定当前行。语法为 change /被修改字串/修改后的串 如 list 2 change /(1)/(20) 3. 增加新行 在第一行插入一行方法为输入0在0后输入文本。如 0 insert into test values(2); 在当前行后插入一行方法为输入input或i回车输入新行再回车会提示再输入新行如不想输入输入点号回车。如 4. 删除行 del --删除缓存区当前行执行前先用list命令指定当前行 del n 删除缓存区指定的行 5. 使用操作系统编辑器编辑命令 在 sql*plus中输入edit命令会自动打开系统的缺省的文本编辑器windows下为notepad缓存区中内容被装到文本编辑器中这时可以对其中的内容进行编辑这时sql*plus处于等待状态修改完毕后保存文件后。被修改的内容就会被写入缓存区。这对于修改错误命令很方便。 6. save命令 格式save 文件名 [replace | append] save命令的作用是将缓存区中内容保存到指定文件中。如果指定的文件不存在将会创建但如果文件目录不存在将会失败。如果指定文件名的文件已存在不指定replace或append参数将会失败。指定replace表示将覆盖原文件内容指定append表示将缓存区内容加到文件后。 7. get命令 格式get 文件名 get命令的作用是将指定文件的内容加载到缓存区中以供编辑或执行。 3.2.3 spool命令 sql spool 文件名 执行该命令后如果指定的文件不存在则会按指定的文件名创建一个空文本文件如果目录不存在会失败。如果指定的文件已存在则文件内容将会被清空。 执行上述命令后此命令后的所有输出包括命令、输出提示信息、错误信息等都会被写入指定的文件。需要说明的时并不是每输出一行信息就会立即写入文件有个缓存过程。 Sqlspool off 上述命令就是停止存储将前面所有输出立即写入文件。 Sqlspool out 除完成spool off命令的功能外还打印输出的信息。 说明spool off/out命令必须与spool命令一一对应。 Spool命令的用途主要有如下 1 导出数据 2 记录脚本的执行日志 3 生成新的sql脚本 3.2.4 start/ 命令 格式start/ 脚本文件名 这样可以将相关的sql/plsql语句sql*plus的命令写在脚本中从而执行。最常用的就是安装脚本升级脚本。 如 sql start e:test.sql sql e:test.sql 3.2.5 设置sql*plus环境set命令 可以通过设置参数来改变sql*plus的一些属性如显示等。如果运行的是sqlplusw.exe程序通过菜单“选项|环境”可以通过界面改变这些参数的默认值。也可在sql提示符下输入set命令来改变参数的值。 查看参数的当前设置值的命令是show 参数名 。 常见的设置参数命令有 1. set pagesize [n] 用于设置每页的行数范围为1~ 50000如果为0则表示不分页不带n表示为0。否则缺省情况下当查询结果的行数超过一页的行数时就会分页显示每页的开头会显示列标题信息。 2. set newpage [n] 该命令与pagesize结合使用用来设置每一页的顶行的空行数范围为0~ 999不带n表示为0。缺省值为1。 3. set linesize n 设置每行能容纳的字符数范围为1~32767 。在查询时通常一条记录会显示一行如果一行显示不下则会自动换行。用户输入数据时当一行输入的值超过一行的最大值时也会自动换行。 4. set heading off|on 设置打开(on)或关闭(off)查询结果页的头信息如列标题。比如想输出sql语句存储到文件中时就需要把这关闭。如 select insert into test1 values(||id||); from test; 5. set feedback off|on 设置为on当执行insert,updatepl/sql等操作时会提示执行的结果。如果设置为off则不显示。 6. set termout off|on 设置为off执行的信息就不会在屏幕上显示。需要说明的是该选项只有在脚本中设置执行脚本时才有效。 7. set trimspool off|on 设置为on查询结果输入到文件中时对于查询结果的后面的空格将被截掉。 8. set serveroutput off|on 设置on在pl/sql中使用dbms_output包输出调试信息时sql*plus中可以显示出来否则不显示。缺省为off。关于dbms_output包的详细信息在以后介绍。需要说明的是该设置只是在当前会话有效一旦重新连接后又恢复为默认值。可以在未连接数据库的情况下设置设置后连接后有效但一旦重新连接就恢复默认值了。 如 begin dbms_output.put_line(hello); end; 9. set timing off|on 设置为on每执行一sql或pl/sql都会显示该执行所需要的时间通过这可以查看sql语句的执行效率。 10. set autocommit on|off|n 在sql*plus中执行dml语句后需要commit后或者执行了dcl或ddl语句后才会被提交。本命令可以设置让sql*plus自动提交。 其中on表示每执行一sql/plsql都自动提交一下。而off只是当sql*plus退出时才自动提交一下。n表示执行n条sql/plsql语句后就自动提交一下。 11. set echo on|off 设置为onsql*plus执行脚本时都会将每一条执行的sql语句输出来这样如果执行出错便于定位。缺省为off 。 3.2.6 show命令 通过show 参数名 可以看到当前sql*plus的一些环境参数的设置。还可以查看其它信息如 show user 查看当前登陆的用户 show error 查看sql执行出错的详细信息因为创建pl/sql对象时即使出错sql*plus不会报error只会报warning而且无法看到详细错务信息通过show error就可以看到。 3.2.7 column(col)命令 该命令可用于设置列的显示属性常见格式如 1. col 列名 format an [truncate] 上面命令用于设置列的显示宽度n为宽度。Truncate表示如果列值宽度超过n时就截去超长的部分。不加Truncate如果列值宽度超过n时换行显示。 如col s format a20 比如一个字段定义了varchar2类型长度很大但实际字段值宽度很少如果不加设置在sql*plus查询时会占用定义的宽度这样看起来不方面。 2. 设置数字的显示宽度 create table test(id number); insert into test values(888888812345678); SQL select * from test; ID ---------- 8.8889E14 SQL select to_char(id) from test; TO_CHAR(ID) ---------------------------------------- 888888812345678 col id format 999999999999999999999999 --设置数字的显示宽度 SQL select * from test; ID ------------------------- 888888812345678 3. 清除列的设置 col 列名 clear --清除指定列的格式设置 clear columns --清除所有列的格式设置 3.2.8 host命令 通过host命令可以在sql*plus中执行操作系统命令。如 sqlhost mkdir e:temp 3.2.9 退出sql*plus 语法exit [n] 说明通过exit命令退出sql*plus退出可以带一个错务码。主要是供父进程使用。 3.3 执行失败的处理 当一个脚本中存在大量的sql/plsql语句时执行该脚本时如果其中有一条sql语句执行失败可能很难发现。如果能让碰到错误时让sql*plus退出再结合spool命令查看日志就很容易知道脚本的执行情况。这可以通过whenever命令来实现。 格式一whenever sqlerror exit [success | failure | warning | n] [commit | rollback | none] 说明一旦脚本中该语句之后的某sql语句执行出错就会停止出错sql*plus就会自动退出。其中success | failure | warning | n 为设定sql*plus出错后退出带出的错务码success | failure | warning为固定的常量n为指定任意数值。如果加了commit选项则出错后sql*plus在退出前会自动执行commit一下如果加了rollback选项则出错后sql*plus在退出前会自动执行rollback一下如果加了none选项或什么都不加则退出不会做任何事但实际上默认设置下sql*plus在退出前会commit一下所以这种情况与加了commit选项效果一样。 例whenever sqlerror exit sql.sqlcode --保证了出现错误立即退出并返回错误码 例***test.sql*** spool e:test.txt whenever sqlerror exit select * from test1233; select * from dual; spool off 格式二whenever sqlerror continue [commit | rollback | none] 说明一旦脚本中该语句之后的某sql语句执行出错会继续往下执行sql*plus不会自动退出默认就是这样的设置。如果加了commit选项则出错后执行下一条语句前sql*plus会自动执行commit一下如果加了rollback选项则出错后执行下一条语句前sql*plus会自动执行rollback一下如果什么都没加或加了none选项则出错后对前面执行过的语句不会自动添加执行任何commit或rollback语句是否commit或rollback看后面的语句。 例***test.sql*** spool e:test.txt whenever sqlerror continue select * from test12; select * from dual; spool off 说明当创建一个pl/sql对象时如存储过程函数触发器包等。即使代码写的有问题在sql*plus执行时不会报error只会报warning。这样通过whenever sqlerror是无法获取到的。而且sql*plus不直接给出出错的详细信息这可以通过show error命令看到出错的详细信息。 3.4 固化对sql*plus的环境设置 通过上面的介绍我们可以知道可以通过命令设置sql*plus运行环境参数。但sql*plus一旦关闭重新打开这些参数又恢复成默认值又需要重新设置这非常不方便。好的是oracle提供一种方法可以使这些设置固定下来。 如果%ORACLE_HOME%sqlplusadmin目录下有glogin.sql文件不同的系统目录和文件名可能不确定则启动sql*plus后如果有初始连接则连接后sql*plus会自动执行该脚本这样就可以在该脚本对sql*plus的参数进行设置还可以加上特定的sql/plsql语句。 可以看出每次启动sql*plus不管是谁启动都会执行glogin.sql脚本。Oracle同时提供一种机制让不同的操作系统用户启动sql*plus执行自己特定的脚本。方法是在当前目录下即运行sql*plus程序时的当前目录编写一个文件名为login.sql的脚本将自己特定的设定写入文件这样启动sql*plus时首先会执行login.sql然后再执行公共的glogin.sql。这在windows系统下显的不是很方面因为只有在命令行下启动sql*plus时才有效且必须先设置当前目录。而在unix下就非常有用因为unix下每个登陆用户都有自己的主目录而登陆后当前目录就是主目录不同的用户可以将login.sql文件放在自己的主目录下。 4 日期类型的显示 在sql*plus中执行select sysdate from dual; 发现查询结果是以字符串显示的但格式不好看。原因是oracle对日期类型它会自动的按照缺省格式隐式的转换为字符串类型。 可以通过t_char函数和to_date函数来实现日期与字符串之间的显示转转。如 select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual; create table test(s date); insert into test values(to_date(‘2005-01-25 10:20:22’,’ yyyy-mm-dd hh24:mi:ss’)); 也可以通过执行sql命令来改变当前会话的日期格式。如 alter session set nls_date_format’ yyyy-mm-dd hh24:mi:ss’; select sysdate from dual; insert into test values(‘2005-01-25 10:20:22’); 可以直接将字符串插入到日期类型中原因是oracle对数据类型会做自动隐式的转换。 说明该设置只在当前会话有效重新登陆后又恢复为默认值。 5 脚本中变量 5.1 替换变量 变量前加一个符号sql*plus在命令中遇到替换变量时用真实值去代替相当于c语言中的宏定义。真实值来源于三个地方 1 脚本参数带入 2 脚本中直接定义 3 用户动态输入 如 select num from dual; sql*plus中有几个环境参数将影响替换变量为 set define off | c off表示关闭替换变量功能。c为定义替换字符缺省为同时启用替换变量功能。 set escape off | c 定义转义字符。即前面如果有指定的c字符则作为普通字符处理。默认是off即没有定义转义字符。如 set escape select ‘hello’ from dual; set ver off|on 如果为on在替换前后会列出命令文件的每一行缺省为on。 set concat c 设置替换变量与其后的其它字符的分隔符缺省为句点.。如 select ‘hello.good’ from dual; -- 句点不作为输出的一部分 等价于select ‘hello’||’good’ from dual; select ‘hello good’ from dual; --空格也起到分隔作用单空格作为输出的一部分 select ‘hello’||’.good’ from dual; 5.1.1 脚本带参数 脚本可以带参数在脚本中通过n来引用参数n为1表示为第一个参数2表示第二个参数依次类推。如 set ver off; connect omc/1 insert into test values(2); commit; 执行该脚本的方法是sqle:test.sql omc self 45 如果参数是字符串且字符串有空格应该用双引号或单引号扩起如果字符串中有双引号则只能用单引号扩起如果字符串中要输入单引号则只能用双引号扩起且输入两个单引号才代表一个单引号。如 set ver off; connect omc/12 create table test(s varchar2(20)); insert into test values(3); commit; 执行 sqle:test.sql omc self hello sqle:test.sql omc self “hello world” sqle:test.sql omc self hello world’ sqle:test.sql omc self “hello ‘’zte’’world” sqle:test.sql omc self ‘hello “zte” world’ 正常情况下slq*plus执行时碰到符号就会作为参数来处理如果符号后跟的是数字就会从命令行中取相应的值替换n如果找不到如n为3但执行时只带了2个参数sql*plus就会在屏幕上提示输入参数。如果后跟的不是数字而是其它字符则sql*plus会把及其后的字符串截止到空格为止当作一个参数提示输入。 如 select ‘hellogood china’ from dual; 那么如何将作为普通字符处理呢除了前面介绍的定义escape环境参数外另一方法是符号后紧跟单引号之间可以跟空格。如 select ‘hello’||’good china’ from dual; select ‘hello ’||’good china’ from dual; 所以一般情况下在sql*plus下执行sql语句时或脚本时对字符需要特殊处理一下因为它是sql*plus中的特殊字符。 5.1.2 脚本中定义 格式define 标识符 值 如 define n12 define s’hello’ --是否加引号没有关系 通过加引用,如 select n from dual; select ‘s’ from dual; --注意必须要加引号 begin dbms_output.put_line(s); end; 取消定义的方法是undefine标识符 一旦取消定义后如果在通过引用则sql*plus会提示输入。 5.1.3 接收用户交互式输入 很多时候在执行脚本时我们希望有些信息根据脚本的提示让用户动态输入从而在下面的sql语句中使用。 语法为accept var_name typename prompt ‘hint’ [hide] 说明hide表示以密文方式让用户输入。typename只能是char,number,date三种。 如 accept user_name char prompt ‘please input username:’ accept passwd char prompt ‘please input password:’ hide 通过变量前加引用connect user_name/passwd 5.2 捆绑变量 一般情况下我们都是在pl/slq中定义变量如 delclare s varchar2(10); begin s:’hello’; insert into test values(s); end; 上面的程序段中定义了变量s但它只在该程序段有效。那如何定义在整个sql*plus中都有效的变量这里称为捆绑变量呢方法如 var g_str varchar2(10) begin --赋值只能在pl/sql中进行 :g_str:hello; --注意前面要加冒号 end; select :g_str from dual; --引用捆绑变量注意前面要加冒号 print g_str --显示捆绑变量注意前面不要加冒号 declare --在pl/sql中引用捆绑变量 s varchar2(10); begin dbms_output.put_line(:g_str); s::g_str; dbms_output.put_line(s); insert into test values(:g_str); end; 6 脚本例子 6.1 生成表的备份脚本 编写存储过程该存储过程生成一个select语句执行结果为输出指定表的所有记录的insert语句。 create or replace function BuildSelSql(tablename in varchar2) return varchar2 is cursor cur(tabname varchar2) is select column_name,data_type from user_tab_columns where table_nameupper(tabname) order by COLUMN_ID; tmp varchar2(4000); msg varchar2(4000); len number; num number; begin tmp:select insert into ||tablename|| values(; for re in cur(tablename) loop if re.data_typeCHAR or re.data_typeDATE or re.data_typeVARCHAR2 or re.data_typeRAW then tmp:tmp||||||; tmp:tmp||||||||re.column_name||||||||,; else --tmp:tmp||||||||decode(||re.column_name||,null,null,||re.column_name||)||||||||,; tmp:tmp||||||||nvl(||re.column_name||,0)||||||||,; end if; end loop; len:length(tmp); msg:substr(tmp,1,len-3); msg:msg||); from ||tablename ||;; return msg; end; 执行函数例子 select BuildSelSql(test) from dual; --test为表名 set trimout on set trimspool on set heading off set feedback off set termout off set ver off set linesize 4000 set pagesize 0 whenever sqlerror exit set serveroutput on spool e:test1.sql select BuildSelSql(test) from dual; spool off set pagesize 6 btitle left commit; --分批提交 spool e:test.txt start e:test1.sql spool off 执行 sqle:test.sql 执行后查看e:test.txt文件的内容。 转载于:https://www.cnblogs.com/songzhenhua/archive/2012/08/13/9312846.html
