SQLPlus

oracle_db發表於2012-05-24

SQL

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

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

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;

SQL> list;

1* select * from dual

SQL> select count(*) from dual;

COUNT(*)

----------

SQL> list;

1* select count(*) from dual

SQL>

執行當前SQL BUFFER中的命令？

SQL> run

1* select count(*) from dual

COUNT(*)

----------

SQL> /

COUNT(*)

----------

如何儲存緩衝區中的命令？

[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;

----------

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

--------

$10

10 rows selected.

SQL> col id format $99,999 -

> heading "yourid"

SQL> select * from testpl;

yourid

--------

$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

----------

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 ~]$

指令碼編寫注意事項？

如果是SQL語句後面以分號結束，如果是PLSQL塊要執行的話另起一行打上/

SQL> show user;

USER is "SCOTT"

SQL> select * from testpl;

----------

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

SQL>

讓指定行變成當前行--LIST 行號

SQL> list 4

4* where id>10

SQL>

如何改變當前行內容？change [查詢內容] 【更換內容】

SQL> l

1 select

2 id

3 from testpl

4 where id>10

SQL> list 4

4* where id>10

SQL> 4

4* where id>10

SQL> c/10/998

4* where id>998

SQL> /

----------

999

1000

SQL>

如何增加一行？APPEND 後面一般加兩個空格

SQL> l

1 select

2 id

3 from testpl

4 where id>998

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

如何增加新行？

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

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 /****?****/

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 /****?****/

清空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

------

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

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

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;

----------

SQL> /

----------

SQL> spool off

[oracle@oraclelinux ~]$ cat lab5.lst

SQL> select * from testpl;

----------

SQL> /

----------

SQL> spool off

此選項一般用來生成日誌。

替換變數是什麼？類似字串替換，它是SQLPLUS的功能，不是資料庫的功能

SQL> show user;

USER is "SCOTT"

SQL> select * from testpl;

----------

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

----------

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-730926/，如需轉載，請註明出處，否則將追究法律責任。

sqlplus spool
2016-10-14
SQL
sqlplus sqlprompt
2017-01-11
SQL
sqlplus用法
2014-07-19
SQL
sqlplus -premli
2011-08-26
SQLREM
sqlplus提示
2010-07-06
SQL
sqlplus(一)
2013-06-04
SQL
如何在solaris的oracle sqlplus中使用sqlplus
2009-01-03
OracleSQL
【SQLPLUS】sqlplus 客戶端所需的檔案列表
2018-04-04
SQL客戶端
SQLPLUS 操作大全
2008-11-07
SQL
sqlplus的使用
2011-07-14
SQL
讓sqlplus 飛
2012-07-19
SQL
sqlplus set命令
2010-12-02
SQL
sqlplus 基礎
2010-09-13
SQL
SQLPLUS COPY 功能。
2010-07-10
SQL
sqlplus 命令大全
2012-06-14
SQL
sqlplus-help
2006-03-26
SQL
sqlplus小記
2005-07-30
SQL
windows sqlplus亂碼
2019-07-30
WindowsSQL
sqlplus專用命令
2014-02-12
SQL
sqlplus 使用總結
2013-12-07
SQL
sqlplus column命令用法
2013-06-24
SQL
sqlplus登陸方式
2014-09-22
SQL
sqlplus 內部命令
2012-11-15
SQL
sqlplus失去響應
2008-06-07
SQL
sqlplus -prelim/ as sysdba用法
2009-02-03
SQL
隨筆 sqlplus / as sysdba
2011-03-10
SQL
rlwrap sqlplus in linux
2010-11-30
SQLLinux
修改sqlplus的SQLPROMPT
2010-10-25
SQL
SQLPLUS的ACCEPT命令
2010-10-05
SQL
常用sqlplus設定
2010-08-17
SQL
sqlplus安裝help
2006-05-20
SQL
sqlplus help install
2006-03-26
SQL
sqlplus set命令使用
2013-06-06
SQL
sqlplus 顯示控制
2013-05-17
SQL
SQLPLUS 的COPY命令
2007-01-25
SQL
sqlplus與空行.txt
2011-12-08
SQL
sqlplus 命令列格式
2009-09-09
SQL命令列
sqlplus下的命令
2007-10-18
SQL

SQLPlus

相關文章