How to use SQL*PLUS

boobooke

How to use  SQL*PLUS

scripts

report

format

ORACLE SQL*PLUS 

the definitive guide

SQL

PL/SQL

SQL*PLUS

OS Command

SQL:ANSI && ISO  relational database

PL/SQL:增加了分支等等语句，如do,while,for,ORACLE公司专有，运行在数据库的引擎里

SQL*PLUS：可以在里面运行SQL和PL/SQL

SQL*PLUS Environment Variables:

ORACLE_HOME

ORACLE_SID

PATH

TNS_ADMIN

LD_LIBRARY_PATH:Specify the path used to search libraries on unix and linux

SQLPATH :specify the location of sql scripts

$sqlplus [ [options] [logon] [start] ]

-H[ELP] | -V[ERSION]|...|[-S[ILENT]]

{username[/password][@connect_identifier] | /}

[AS {SYSOPER|SYSDBA|SYSASM} /NOLOG

@｛url | file_name [.ext]} [arg ....]

including password in sqlplus  is a risk !  

Task Manager  可以看到密码

ps -ef |grep sql  也可以看到用户名和密码

The SQL Buffer

run :会把命令显示出来

/

SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql

SQL> @?/sqlplus/admin/help/helpdrop.sql

exit

quit

desc  table_or_view_name

desc plsql_function

How to End a SQL Command?

;

/

with a blank line

begin

end;

/

declare

end;

/

CTRL+C

Run OS Commands

SQL> host ls -l /tmp

SQL>!(unix)    or     $(windows)

SQL>! ps -ef|grep sh

SQL>! ps -ef|grep sql

Pause the Display:

SET PAU[SE] {ON | OFF | text }

set pau '^^^^^^^^^^^^^^^^^^^^^^^^^^'

Saving changes to the db automatically :

set autocommit on

set autocommit immediate

set autocommit 10

show auto

set autocommit off

Editing Scripts:

@

@@

start

SQL>define

SQL>define _editor=vi

Listing the Buffer Contents

l 3

l 5

l

CHANGE   or C

5

c/25/15

vi 

%s/select/SELECT/g

Appending Text to a Line

SQL> l 4

SQL> append desc

SQL>a  and name like '%boo'

Adding a new line:

0 /* this is a demo sql statement */

4

i hahahahahahh*****

 

Deleting lines:

del 4

del 2 6

只有LIST && DEL 可以对多行进行操作。其他 的只能对单行进行操作。

Placing Comments in Scripts:

REMARK

/*   ...   */

--  ANSI/ISO

NOTES:

不要把注释放在命令行开头的地方

;

& 

Running Scripts;

@

start

@@

Use SQLPATH Environment Variables

$ sqlplus hr@tsspks @sales.sql

$ sqlplus hr@tsspks @

直接放在文件中

Nesting Scripts:

SPOOL:

巧妙使用

Using Substitution Variables:

绑定变量