SQLPLUS读书笔记

SQLPLUS读书笔记

1.什么是ＳＱＬＰＬＵＳ：

SQL*Plus has its own commands and environment, and it provides access to the

Oracle RDBMS. It allows you to enter and execute SQL, PL/SQL, SQL*Plus and

operating system commands to perform the following:

n enter SQL*Plus commands to configure the SQL*Plus environment

n enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks

n format, perform calculations on, store, and print from query results

n interact with an end user

n startup and shutdown a database

n connect to a database

n define variables

n capture errors

n list column definitions for any table

n perform database administration

 

２.SQL*PLUS两层结构：

SQL*Plus uses a two-tier model comprising:

n Client Tier (Command-line user interface).

n Database Tier (Oracle9i).

 

3.i SQL*PLUS三层结构：

iSQL*Plus is a fast, browser-based interface which uses the SQL*Plus processing

engine in a three-tier model comprising:

n Client Tier (iSQL*Plus user interface, typically a web browser).

n Middle Tier (iSQL*Plus Server, Oracle Net, and Oracle HTTP Server).

n Database Tier (Oracle9i).

 

4.SQLPLUS登陆后的初始设置：

The site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql.

 

5.定制自己用户登陆的设置：

SQL*Plus also supports a User Profile, executed after the Site Profile. This file is

generally named login.sql. SQL*Plus searches for the user profile in your current

directory, and then the directories you specify with the SQLPATH environment

variable. SQL*Plus searches this colon-separated list of directories in the order they

are listed.

 

6.改变自己登录文件的设置：

SET LINESIZE Followed by a number, sets the number of characters as page

width of the query results.

SET NUMFORMAT Followed by a number format (such as $99,999), sets the

default format for displaying numbers in query results.

SET PAGESIZE Followed by a number, sets the number of lines per page.

SET PAUSE Followed by ON, causes SQL*Plus to pause at the beginning

of each page of output (SQL*Plus continues scrolling after you

enter Return). Followed by text, sets the text to be displayed

each time SQL*Plus pauses (you must also set PAUSE to ON).

SET SQLPROMPT Followed by the connect information variable in the form:

SET SQLPROMPT '&_CONNECT_IDENTIFIER > '

changes the SQL*Plus command-line prompt to display the

SID of the database you are connected to.

SET TIME Followed by ON, displays the current time before each

command prompt.

 

7.保存当前的设置，下次启动时直接执行：

You can store the current SQL*Plus system (“SET”) variables in a host operating

system file (a script) with the STORE command. If you alter any variables, this

script can be run to restore the original values. This is useful if you want to reset

system variables after running a report that alters them.

store set login.sql create/replace

 

8. 登录：

sqlplus –h

获取帮助信息

SQLPLUS [ [Options] [Logon] [Start] ]

 

9.使用MARKUP产生html脚本

Some SQL*Plus commands have different behavior when output is directed to an

HTML table. Commands originally intended to format paper reports may have

different meaning for reports intended for web tables:

n PAGESIZE is the number of rows in an HTML table, not the number of lines.

Each row may contain multiple lines. The TTITLE, BTITLE and column

headings are repeated every PAGESIZE rows.

n LINESIZE may have an effect on data if wrapping is on, or for very long data.

Depending on data size, they may be generated on separate lines, which a

browser may interpret as a space character.

n TTITLE and BTITLE content is output to three line positions: left, center and

right, and the maximum line width is preset to 90% of the browser window.

These elements may not align with the main output as expected due to the way

they are handled for web output. Entity mapping in TTITLE and BTITLE is the

same as the general ENTMAP setting specified in the MARKUP command.

n If you use a title in your output, then SQL*Plus starts a new HTML table for

output rows that appear after the title. Your browser may format column

widths of each table differently, depending on the width of data in each column.

n SET COLSEP and RECSEP only produce output in HTML reports when

PREFORMAT is ON.

 

10.结束一个SQL块：

Ending a SQL Command You can end a SQL command in one of three ways:

n with a semicolon (;)

n with a slash (/) on a line by itself

n with a blank line

 

11.使用连接符”-”输入sqlplus命令：

You can continue a long

SQL*Plus command by typing a hyphen at the end of the line and pressing Return.

If you wish, you can type a space before typing the hyphen. SQL*Plus displays a

right angle-bracket (>) as a prompt for each additional line.

 

12.设置login.sql文件:

--   login.sql

--   SQL*Plus user login startup file.

--

--   This script is automatically run after glogin.sql

--

-- To change the SQL*Plus prompt to display the current user,

-- connection identifier and current time.

-- First set the database date format to show the time.

ALTER SESSION SET nls_date_format = 'HH24:MI:SS';

-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER

-- and _DATE variables.

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

-- To set the number of lines to display in a report page to 24.

SET PAGESIZE 24

-- To set the number of characters to display on each report line to 78.

SET LINESIZE 78

-- To set the number format used in a report to $99,999.

SET NUMFORMAT $99,999

 

13.设置帮助：

设置set oracle_home=E:/oracle/product/ 10.1.0 /Db_1

设置set system_pass=system/system

运行helpins 向数据库中添加帮助信息

1.         %ORACLE_HOME%/BIN/HELPINS

2.     In either case, the HELPINS utility reads the login from SYSTEM_PASS to connect to Oracle Database using SQL*Plus, creates and loads the help tables, and then disconnects. You can use command-line help the next time you start SQL*Plus.

 

14.设置自动提交：

SET AUTOCOMMIT ON

Alternatively, you can enter the following to turn the autocommit feature on:

SET AUTOCOMMIT IMMEDIATE

 

15.执行操作系统命令：

HOST COMMAND

 

16.获取结构的命令：

Lists the column definitions for a table, view, or synonym,

 or the specifications for a function or procedure.

 

17.对于一个长的输出设置暂停：

set pause on|off|

 

18.SQLPlus注释需要注意的地方：

1. Do not put comments within the first few keywords of a statement.

2. Do not put comments after statement terminators (period, semicolon or slash).

3. Do not put statement termination characters at the end of a comment line or

after comments in a SQL statement or a PL/SQL block.

4. Do not use ampersand characters ’&’ in comments in a SQL statement or

PL/SQL block.

 

19.使用SQLPlus执行sql

sqlplus scott/tiger@zianed @hello.sql

 

20.执行嵌套脚本使用@@

 

21.退出执行：

If your script generates a SQL error while running from a batch file on the host

operating system, you may want to abort the script and exit with a return code. Use

the SQL*Plus command WHENEVER SQLERROR to do this; see the WHENEVER

SQLERROR command on page 13-154 for more information.

Similarly, the WHENEVER OSERROR command may be used to exit if an operating

system error occurs. See the WHENEVER OSERROR command on page 13-152 for

more information.

使用help w

获得帮助

22.不能使用 &&定义变量的部分情况：

You cannot use substitution variables in the buffer editing commands, APPEND,

CHANGE, DEL , and INPUT, nor in other commands where substitution would be

meaningless, such as in SQL*Plus comments (REMARK, /*... */ or --).

 

23.set命令

16:14:37 SCOTT@orcl 11-12月-08> help set

 

 SET

 ---

 

 Sets a system variable to alter the SQL*Plus environment settings

 for your current session. For example, to:

     -   set the display width for data

     -   customize HTML formatting

     -   enable or disable printing of column headings

     -   set the number of lines per page

 In iSQL*Plus, you can also use the Preferences screen to set

 system variables.

 

 SET system_variable value

 

 where system_variable and value represent one of the following clauses:

 

   APPI[NFO]{OFF|ON|text}  等价于通过dbms_applocation_info注册信息；

   ARRAY[SIZE] {15|n}      设置每一次sql取回的行数；

   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}     设置自动提交；

   AUTOP[RINT] {OFF|ON}                     自动打印

   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}

   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}

     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED] |

   BLO[CKTERMINATOR] {.|c|OFF|ON}                    EA[CH]|OFF}

   CMDS[EP] {;|c|OFF|ON}                    RECSEPCHAR {_|c}

   COLSEP {_|text}                          SERVEROUT[PUT] {OFF|ON}

   COM[PATIBILITY] {V7|V8|NATIVE}             [SIZE n] [FOR[MAT]

   CON[CAT] {.|c|OFF|ON}                      {WRA[PPED] |

   COPYC[OMMIT] {0|n}                         WOR[D_WRAPPED] |

   COPYTYPECHECK {OFF|ON}                     TRU[NCATED]}]

   DEF[INE] {&|c|OFF|ON}                   设置&是否成为输入变量

   DESCRIBE [DEPTH {1|n|ALL}]                 INV[ISIBLE]}

     [LINENUM {ON|OFF}] [INDENT {ON|OFF}]  *SHOW[MODE] {OFF|ON}

   ECHO {OFF|ON}                           *SQLBL[ANKLINES] {OFF|ON}

  *EDITF[ILE] file_name[.ext]               SQLC[ASE] {MIX[ED] |

   EMB[EDDED] {OFF|ON}                        LO[WER] | UP[PER]}

   ESC[APE] {/|c|OFF|ON}                   *SQLCO[NTINUE] {> | text}

   FEED[BACK] {6|n|OFF|ON}                 *SQLN[UMBER] {OFF|ON}

   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLPLUSCOMPAT[IBILITY] {x.y[.z]}

  *FLU[SH] {OFF|ON}                        *SQLPRE[FIX] {#|c}

   HEA[DING] {OFF|ON}                      *SQLP[ROMPT] {SQL>|text}

   HEADS[EP] {||c|OFF|ON}                   SQLT[ERMINATOR]

   INSTANCE [instance_path|LOCAL]             {;|c|OFF|ON}

   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)   *SUF[FIX] {SQL|text}

   LOBOF[FSET] {n|1}                       *TAB {OFF|ON}

   LOGSOURCE [pathname]                    *TERM[OUT] {OFF|ON}

   LONG {80|n}                             *TI[ME] {OFF|ON}

   LONGC[HUNKSIZE] {80|n}                   TIMI[NG] {OFF|ON}

   MARK[UP] HTML [ON|OFF]                  *TRIM[OUT] {OFF|ON}

     [HEAD text] [BODY text] [TABLE text]  *TRIMS[POOL] {ON|OFF}

     [ENTMAP {ON|OFF}]                      UND[ERLINE] {-|c|ON|OFF}

     [SPOOL {ON|OFF}]                       VER[IFY] {OFF|ON}

     [PRE[FORMAT] {ON|OFF}]                 WRA[P] {OFF|ON}

 

 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.

 

24.能够disabled的命令：

You can disable the following SQL*Plus commands:

COPY HOST SET

EDIT PASSWORD SPOOL

EXECUTE QUIT START

EXIT RUN

GET SAVE

 

You can also disable the following SQL commands:

ALTER GRANT SET CONSTRAINTS

ANALYZE INSERT SET ROLE

AUDIT LOCK SET TRANSACTION

CONNECT NOAUDIT TRUNCATE

CREATE RENAME UPDATE

DELETE REVOKE

DROP SELECT

 

You can also disable the following PL/SQL commands:

BEGIN DECLARE

 

25.角色控制：

Roles are created and used with the SQL CREATE, GRANT, and SET commands:

n To create a role, you use the CREATE command. You can create roles with or

without passwords.

n To grant access to roles, you use the GRANT command. In this way, you can

control who has access to the privileges associated with the role.

n To access roles, you use the SET ROLE command. If you created the role with a

password, the user must know the password in order to access the role.

 

26.不让用户在SQL*Plus中设置角色：

To prevent application users from accessing application roles in SQL*Plus, you can

use the PUP table to disable the SET ROLE command. You also need to disable the

BEGIN and SQL*Plus EXECUTE commands to prevent application users setting

application roles through a PL/SQL block. This allows a SQL*Plus user only those

privileges associated with the roles enabled when they started SQL*Plus.

 

27.禁用用户权限：

To disable a role for a given user, insert a row in the PUP table containing the user’s

username in the Userid column, “ROLES” in the Attribute column, and the role

name in the Char_Value column.

Note: When you enter "PUBLIC" or "%" for the Userid column,

you disable the role for all users. You should only use "%" or

"PUBLIC" for roles which are granted to "PUBLIC". If you try to

disable a role that has not been granted to a user, none of the roles

for that user are disabled.

The Scope, Numeric_Value, and Date_Value columns should contain NULL. For

example:

PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE

VALUE VALUE VALUE

------- ------ --------- ----- -------- ------ -----

SQL*Plus HR ROLES ROLE1

SQL*Plus PUBLIC ROLES ROLE2

During login, these table rows are translated into the command

SET ROLE ALL EXCEPT ROLE1, ROLE2

 

28.限制禁用：

Like the Product User Profile table, the RESTRICT option allows you to disable

certain commands that interact with the operating system. However, commands

disabled with the -RESTRICT option are disabled even when no connection to a

server exists, and remain disabled until SQL*Plus terminates.

The following table shows which commands are disabled in each restriction level.

Command Level 1 Level 2 Level 3

EDIT disabled disabled disabled

GET disabled

HOST disabled disabled disabled

SAVE disabled disabled

SPOOL disabled disabled

START disabled

STORE disabled disabled

29.连接isql*plus权限的种类：

9i：

There are three modes of access to iSQL*Plus:

n Connect as a normal user

Requires an Oracle Database account username and password entered in the

iSQL*Plus Login screen.

n Connect as a SYSDBA or SYSOPER privileged user

Requires an Oracle Database account username and password entered in the

iSQL*Plus DBA Login screen, and an Oracle HTTP Server authentication

username and password entered in a separate dialog.

n Generate the iSQL*Plus Server statistics report

Requires an Oracle HTTP Server authentication username and password

10g：

There are two modes of access to iSQL*Plus:

·         Connect as a normal user.

Requires an Oracle Database account username and password entered in the iSQL*Plus Login screen.

·         Connect as a SYSDBA or SYSOPER privileged user.

Requires an Oracle Database account username and password entered in the iSQL*Plus DBA Login screen, and an Application Server authentication username and password entered in a separate dialog.

 

30.