ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sqlplus

sqlplus

原创 Linux操作系统 作者:oracle_db 时间:2012-05-24 23:36:58 0 删除 编辑
SQLPLUS是什么?
SQLPLUS是一个交互式的和命令行的工具,它有自己的命令集和环境,它可以执行SQL,PLSQL,SQLPLUS和操作系统命令。
SQLPLUS有那些作用?
查询打印数据库内容,检查数据库表结构,对象定义,开发运行批处理脚本,生成报告,支持TXT,HTML格式,也可以打到屏幕上。
SQLPLUS可以调用那些东西?

SQLPLUS----------SQL
                           |
                           |---PLSQL
SQLPLUS相关环境变量?
ORACLE_HOME--ORACLE安装在那个目录下
,ORACLE_SID--它是SERVER必须的环境变量,
,PATH--指定可执行文件目录,命令是在PATH中去找的
TNS_ADMIN--必须配置TNSNAMES.ORA这个文件
LD_LIBRARY_PATH--指定运行所需要的动态库命令,WINDOWS不需要设置这个,因为包含在PATH中了
SQLPATH--这是SQLPLUS专有的,指定脚本路径

如果CLIENT与SERVER在同一台机器上怎么用SQLPLUS?
[oracle@oraclelinux ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 24 23:50:31 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: scott
Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 
如果CLIENT与SERVER不在同一台服务器上怎么用SQLPLUS连服务器?
sqlplus user/pass@server
查看SQLPLUS版本?
oracle@oraclelinux ~]$ sqlplus -v

SQL*Plus: Release 10.2.0.1.0 - Production
与服务器连接需要注意,CLIENT的版本通常要高于或者等于服务器版本
对于登录格式的说明?
后面的@部分就是TNSNAMES文件中配置的名字
[oracle@oraclelinux ~]$ sqlplus scott/scott@dbtest

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 24 23:59:58 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 
,SQL BUFFER是什么?
是一个内在缓冲区,它存放最近一次使用的SQL和PLSQL,可以使用LIST查看缓冲区的
SQL> conn /as sysdba
Connected.
SQL> select * from dual;

D
-
X

SQL> list;
  1* select * from dual
SQL> select count(*) from dual;

  COUNT(*)
----------
         1

SQL> list;
  1* select count(*) from dual
SQL> 
执行当前SQL BUFFER中的命令?
SQL> run
  1* select count(*) from dual

  COUNT(*)
----------
         1

SQL> /

  COUNT(*)
----------
         1
如何保存缓冲区中的命令?
[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ exit
exit

SQL> list;
  1* select count(*) from dual
SQL> save /u01/oracle/dbtest.sql
Created file /u01/oracle/dbtest.sql
SQL> ! 
[oracle@oraclelinux ~]$ cat dbtest.sql
select count(*) from dual
/
[oracle@oraclelinux ~]$ 

SQL> 
SQLPLUS都有那些命令?
QL> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics. In iSQL*Plus, click the Help button to display
 iSQL*Plus online help.
 You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/
 and the Oracle Database Library at http://otn.oracle.com/documentation/

 HELP|? [topic]


SQL> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET
 CONNECT       PASSWORD     SHOW


SQL> 
SQL> help save

 SAVE
 ----

 Saves the contents of the SQL buffer in a script. In iSQL*Plus, click
 the Save Script. button to save the Workspace contents to a script. The
 buffer has no command history list and does not record SQL*Plus commands.

 SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

 Not available in iSQL*Plus


SQL> 
SQLPLUS中如何写PLSQL块?
把你的代码写在BEGIN....END当中,SQLPLUS看见BEGIN就知道是PLSQL块开始了,在块中分号不表示结束,点号表示结束但不执行,/表示执行
SQL> begin
  2  for i in 1..10
  3  loop
  4    insert into testpl values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from testpl;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.
SQL> set serveroutput on;
SQL> set echo on;
SQL> declare
  2    x varchar2(12) := 'hello plsql';
  3    begin
  4      dbms_output.put_line(x);
  5    exception
  6    when others then
  7      dbms_output.put_line('error');
  8    end;
  9  /
hello plsql

PL/SQL procedure successfully completed.

SQL> 
SQLPLUS命令语法?-是连字符,COL可以对指定列指定格式,
SQL> col id format $99,999 heading "MYID"
SQL> select * from testpl;

    MYID
--------
      $1
      $2
      $3
      $4
      $5
      $6
      $7
      $8
      $9
     $10

10 rows selected.

SQL> col id format $99,999 -
> heading "yourid"
SQL> select * from testpl;

  yourid
--------
      $1
      $2
      $3
      $4
      $5
      $6
      $7
      $8
      $9
     $10

10 rows selected.

SQL> 
如何在SQLPLUS中执行操作系统命令?
QL> host ls
assistants              dept_load10.log   ldap                owm
awr01.html              dept_load11.bad   lib                 perl
awrrpt02.lst            dept_load11.log   log                 plsql
backupbashprofile       dept_load12.log   log1.log            precomp
bash_profilebakDBTEST2  dept_load13.log   log2.log            racg
bash_profileenv01       dept_load14.log   log3.log            rdbms
bin                     dept_load8.log    md                  relnotes
cdata                   dept_load9.log    mesg                root.sh
cfgtoollogs             desc              mgw                 root.sh.old
clone                   Desktop           network             show
config                  diagnostics       nls                 slax
crs                     has               oc4j                sqlj
css                     hs                odbc                sqlldr
ctx                     install           olap                sqlnet.log
dbcreate.log            install.platform.  OPatch              sqlplus
dbs                     inventory         opmn                srvm
dbtest.sql              javavm            oraclelinux_dbtest  sysman
demo                    jdbc              oracore             uix
demo1.ctl               jdk               oraInst.loc         wwg
demo1.log               jlib              ord                 xdk
demo1.log_xt            jre               oui

SQL> !ls
assistants              dept_load10.log   ldap                owm
awr01.html              dept_load11.bad   lib                 perl
awrrpt02.lst            dept_load11.log   log                 plsql
backupbashprofile       dept_load12.log   log1.log            precomp
bash_profilebakDBTEST2  dept_load13.log   log2.log            racg
bash_profileenv01       dept_load14.log   log3.log            rdbms
bin                     dept_load8.log    md                  relnotes
cdata                   dept_load9.log    mesg                root.sh
cfgtoollogs             desc              mgw                 root.sh.old
clone                   Desktop           network             show
config                  diagnostics       nls                 slax
crs                     has               oc4j                sqlj
css                     hs                odbc                sqlldr
ctx                     install           olap                sqlnet.log
dbcreate.log            install.platform.  OPatch              sqlplus
dbs                     inventory         opmn                srvm
dbtest.sql              javavm            oraclelinux_dbtest  sysman
demo                    jdbc              oracore             uix
demo1.ctl               jdk               oraInst.loc         wwg
demo1.log               jlib              ord                 xdk
demo1.log_xt            jre               oui

SQL> 
在WINDOWS下用$,在LINUX下!然后加上操作系统命令
PAUSE用法?
SQL> show pause
PAUSE is OFF
SQL> show pagesize
pagesize 14
SQL> set pause on
SQL> select * from testpl;


  yourid
--------
    $651
    $652
    $653
    $654
    $655
    $656
    $657
    $658
    $659
    $660
    $661


  yourid
--------
    $662
    $663
    $664
    $665
    $666
    $667
    $668
    $669
    $670
    $671
    $672


    $673

24 rows selected.


SQL> 
以CTRL+C 然后回车结束当前查看内容

设定指定PAUSE?
SQL> show pau
PAUSE is ON and set to ""
SQL> set pause 'xxxxxx'
SQLPLUS如何设置自动提交?
set autocommit on  set autocommit immediate     set autocommit 10[执行10条语句才提交DML语句]
SQL> show autoco
autocommit OFF
SQL> set autocommit on
SQL> show autoco
autocommit IMMEDIATE
SQL> 
一般情况最好不要设置自动提交

SQLPLUS如何编辑脚本?
SQL> !
[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ vi lab1.sql

select * from testpl order by id desc;
~

SQL> @/u01/oracle/lab1.sql

        ID
----------
         1
         1

SQL> 


QL> define
DEFINE _DATE           = "25-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TESTENV01" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
SQL> define_editor=vi
SQL> define
DEFINE _DATE           = "25-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TESTENV01" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
SQL> edit sales

select * from dual;
~

~
"sales.sql" [New] 1L, 20C written

SQL> !
[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ ls -ltr lab1.sql
-rw-r--r--  1 oracle oinstall 39 May 25 11:04 lab1.sql
[oracle@oraclelinux ~]$ 
[oracle@oraclelinux ~]$ 


脚本编写注意事项?
如果是SQL语句后面以分号结束,如果是PLSQL块要执行的话另起一行打上/

SQL> show user;
USER is "SCOTT"
SQL> select * from testpl;

        ID
----------
         1
         1

SQL> truncate table testpl;

Table truncated.

SQL> select 8 from testpl;

no rows selected

SQL> define
DEFINE _DATE           = "25-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TESTENV01" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
SQL> !
[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ vi lab2.sql

select * from testpl;
truncate table testpl;

begin
for i in 1..1000
loop
  insert into testpl values (i);
end loop;
commit;
end;
/

select count(*) from testpl;


~
"lab2.sql" [New] 14L, 163C written                            
[oracle@oraclelinux ~]$ 

[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ exit
exit

SQL> @/u01/oracle/lab2.sql

no rows selected


Table truncated.


PL/SQL procedure successfully completed.

Commit complete.

  COUNT(*)
----------
      1000

SQL> 

如何修改BUFFER中的SQL语句?

编辑当前行,LIST中前面有*号的是当前行
SQL> l
  1  select
  2  id
  3  from testpl
  4  where id>10
  5*
SQL> 
让指定行变成当前行--LIST 行号
SQL> list 4
  4* where id>10
SQL> 
如何改变当前行内容?change [查找内容] 【更换内容】
SQL> l
  1  select
  2  id
  3  from testpl
  4  where id>10
  5*
SQL> list 4
  4* where id>10
SQL> 4
  4* where id>10
SQL> c/10/998
  4* where id>998
SQL> /

        ID
----------
       999
      1000

SQL> 
如何增加一行?APPEND 后面一般加两个空格

SQL> l
  1  select
  2  id
  3  from testpl
  4  where id>998
  5*
SQL> 4
  4* where id>998
SQL> a and id<1000
  4* where id>998and id<1000
SQL> c/998and/998 and
  4* where id>998 and id<1000
SQL> l
  1  select
  2  id
  3  from testpl
  4  where id>998 and id<1000
  5*

如何增加新行?
SQL> 0 /
SQL> 0 /* this is a demo sql statement */
SQL> l
  1  /* this is a demo sql statement */
  2  /
  3  select
  4  id
  5  from testpl
  6  where id>998 and id<1000
  7*
SQL> 
如何在指定行之间增加行?i
在第6行和第7行间插入数据
SQL> 6
  6* where id>998 and id<1000
SQL> i /****?****/
SQL> l
  1  /* this is a demo sql statement */
  2  /
  3  select
  4  id
  5  from testpl
  6  where id>998 and id<1000
  7  /****?****/
  8*
SQL> 
怎么删除行?DEL
del 删除当前行
del n删除第n行
del * N删除当前行到N行
del n m删除n,m间的行
del n last删除第n行到最后一行记录
del n *删除第n行到当前行
delete last删除最后一行
delete * last删除当前行到最后一行

LIST也有类同用法
SQL> list 3 5
  3  select
  4  id
  5* from testpl
SQL> list * last
  5  from testpl
  6  where id>998 and id<1000
  7  /****?****/
  8*

清空BUFFER?
CLEAR BUFFER
怎么在脚本加注解?
REM   用来把但单行注释,这是SQLPLUS的命令不是SQL的
/*..*/    多行注释  SQL语句的注释方法
--        注解单行   SQL语句的注释方法
查看相关HELP
QL> help input

 INPUT
 -----

 Adds one or more new lines of text after the current line in the
 SQL buffer. The buffer has no command history list and does not
 record SQL*Plus commands.

 I[NPUT] [text]

 Not available in iSQL*Plus


SQL> help del

 DEL
 ---

 Deletes one or more lines of the SQL buffer. The buffer has no
 command history list and does not record SQL*Plus commands.

 DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]

 Not available in iSQL*Plus


SQL> help a

 ACCEPT
 ------

 Reads a line of input and stores it in a given substitution variable.
 In iSQL*Plus, displays the Input Required screen for you to enter a
 value for the substitution variable.

 ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
 [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]


 APPEND
 ------

 Adds text to the end of the current line in the SQL buffer.

 A[PPEND] text

 Not available in iSQL*Plus


 ARCHIVE LOG
 -----------

 Starts or stops automatic archiving of online redo log files,
 manually (explicitly) archives specified redo log files, or
 displays information about redo log files.

 ARCHIVE LOG {LIST|STOP} | {START|NEXT|ALL|integer} [TO destination]


 ATTRIBUTE
 ---------

 Specifies display characteristics for a given attribute of an Object Type
 column, such as the format of NUMBER data. Columns and attributes should
 not have the same names as they share a common namespace. Lists the
 current display characteristics for a single attribute or all attributes.

 ATTRIBUTE [type_name.attribute_name [option ... ]]

 where option represents one of the following terms or clauses:
     ALI[AS] alias
     CLE[AR]
     FOR[MAT] format
     LIKE {type_name.attribute_name | alias}
     ON|OFF


SQL> help append

 APPEND
 ------

 Adds text to the end of the current line in the SQL buffer.

 A[PPEND] text

 Not available in iSQL*Plus


SQL> 

怎么运行脚本?
@或者@@这基本没区别
echo on,和echo off用来控制脚本是否在执行过程中显示

如何指定SQLPATH环境变量?
它用来指定SQLPLUS在那去找脚本来执行

当启动SQLPLUS的时候运行指定脚本?

sqlplus user@server  @script.sql


"lab3.sql" [New] 2L, 38C written                              
[oracle@oraclelinux ~]$ cat lab3.sql
insert into testpl values(1);
commit;
[oracle@oraclelinux ~]$ sqlplus scott/scott @lab3.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 25 12:34:36 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


1 row created.


Commit complete.

SQL> 

"lab4.sql" [New] 3L, 45C written                              
[oracle@oraclelinux ~]$ pwd
/u01/oracle
[oracle@oraclelinux ~]$ cat lab4.sql
scott
insert into testpl values (2);
commit;
[oracle@oraclelinux ~]$ sqlplus @lab4.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 25 12:37:55 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


1 row created.


Commit complete.

SQL> 
嵌套脚本执行?

start script1.sql
start scirpt2.sql

....
SPOOL干什么的?

存储结果到文件或者打到屏幕
SQL> help spool

 SPOOL
 -----

 Stores query results in a file, or optionally sends the file to a printer.
 In iSQL*Plus, use the Preferences screen to direct output to a file.

 SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

 Not available in iSQL*Plus


SQL> spool lab5
SQL> select * from testpl;

        ID
----------
         2
         1

SQL> /

        ID
----------
         2
         1

SQL> spool off

[oracle@oraclelinux ~]$ cat lab5.lst
SQL> select * from testpl;

        ID                                                                      
----------                                                                      
         2                                                                      
         1                                                                      

SQL> /

        ID                                                                      
----------                                                                      
         2                                                                      
         1                                                                      

SQL> spool off
 此选项一般用来生成日志。

替换变量是什么?类似字符串替换,它是SQLPLUS的功能,不是数据库的功能
SQL> show user;
USER is "SCOTT"
SQL> select * from testpl;

        ID
----------
         2
         1

SQL> select * from testpl where id=&myid;
Enter value for myid: 2
old   1: select * from testpl where id=&myid
new   1: select * from testpl where id=2

        ID
----------
         2

SQL> 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-730926/,如需转载,请注明出处,否则将追究法律责任。

上一篇: AIX日常维护命令
请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    307974