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>998
and 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-730926/
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
