ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (SQL*PLUS 命令操作)

oracle实验记录 (SQL*PLUS 命令操作)

原创 Linux操作系统 作者:fufuh2o 时间:2009-08-06 15:34:01 0 删除 编辑

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> ? Change~~~~~~~~~~~~~查看具体使用

 CHANGE
 ------

 Changes the first occurrence of the specified text on the current
 line of the SQL buffer. The buffer has no command history list and
 does not record SQL*Plus commands.

 C[HANGE] sepchar old [sepchar [new[sepchar]]]

 Not available in iSQL*Plus


常用的

SQL> select
  2  *
  3
~~不支持空格
SQL> set sqlblanklines on~~~~~~~~~~~~~~用这个支持了
SQL> select
  2  *
  3
  4
  5  from test;

         A
----------
         1
LIST:查看已写命令 可以简写成L

SQL> l
  1  select
  2  *
  3
  4
  5* from test

change :修改
SQL> select * fro test;
select * fro test
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> l
  1* select * fro test
SQL> c/fro/from
  1* select * from test
SQL> l
  1* select * from test
SQL> /

         A
----------
         1


del:删除

SQL> l
  1* select * from test
SQL> del
SQL> l
SP2-0223: No lines in SQL buffer.
SQL> set sqlblanklines on
SQL> select
  2  *
  3
  4  from test;

         A
----------
         1

SQL> ;
  1  select
  2  *
  3
  4* from test
SQL> del 3~~~~~~~~~~~~~~~~~~~~删除第几行
SQL> l
  1  select
  2  *
  3* from test
SQL> /

         A
----------
         1
APPEND 追加
SQL> select * from tes;
select * from tes
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> l
  1* select * from tes
SQL> append t
  1* select * from test
SQL> l
  1* select * from test
SQL> /

         A
----------
         1

SAVE:保存buffer               geT:显示不执行
edit:编辑

col对列修改
SQL> ? col

 COLUMN
 ------

 Specifies display attributes for a given column, such as:
     - text for the column heading
     - alignment for the column heading
     - format for NUMBER data
     - wrapping of column data
 Also lists the current display attributes for a single column
 or all columns.

 COL[UMN] [{column | expr} [option ...] ]

 where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     ENTMAP {ON|OFF}
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
SQL> col  a heading "b"
SQL> select * from test;

         b
----------
         1
SQL> col a format a5 heading "c"
SQL> select * from test;

         c
----------
##########

报表的一些命令


SQL> select * from book;

        ID BOOKNAME   PUB
---------- ---------- ----------
         1 a          a
         2 b          b
         3 c          c
         4 c          c
SQL> break on pub(该列重复的只显示1条)分类
SQL> select * from book;

        ID BOOKNAME   PUB
---------- ---------- ----------
         1 a          a
         2 b          b
         3 c          c
         4 c

compute count of bookname on pub 计算bookname数 按pub分类
SQL> compute count of bookname on pub
SQL> select * from book;

        ID BOOKNAME   PUB
---------- ---------- ----------
         1 a          a
           ---------- **********
                    1 count
         2 b          b
           ---------- **********
                    1 count
         3 c          c
         4 c
           ---------- **********
                    2 count

 

*********

SQL> passw xh~~~~~~~~~~~~~~~~~~SQLPLUS中改user密码 
Changing password for xh
New password:
Retype new password:
Password changed
SQL>

SQL> passw zz
Changing password for zz
New password:
Retype new password:
Password changed

SQL> show user
USER is "XH"~~~~~~~~~~~改自己的
SQL> passw
Changing password for XH
Old password:

~~~~~~~~~~~~~~~~~~
SQL> define a=1~~~~~~~~~定义变量

SQL> select * from t1 where a=&a;
old   1: select * from t1 where a=&a
new   1: select * from t1 where a=1

no rows selected

SQL> define a
DEFINE A               = "1" (CHAR)~查看定义的
SQL>

SQL> accept b prompt 'input'~~~~定义变量提示输入变量value,另外还可以有format 和hide隐藏输出
input2~~~~~~~~~~~~属于变量value
SQL>
SQL> select * from t1 where a=&b;
old   1: select * from t1 where a=&b
new   1: select * from t1 where a=2

no rows selected
SQL> define b
DEFINE B               = "2" (CHAR)
SQL> undefine b~~~~~~~~~~~~~~~~~~~~~~~~~~清除变量
SQL> define b
SP2-0135: symbol b is UNDEFINED

~~~~~~~~~~~~~~~~

prompt ,pause
脚本里用

prompt '这是TEST,按回车'
pause
select count(*) from t1 ;         script中内容


SQL> @d:\1.txt
'这是TEST,按按回车'


  COUNT(*)
----------
        15


****************
variable 绑定变量

SQL> variable test number;~~~~定义
SQL> exec :test:=2;~~~~~~~~赋值

PL/SQL procedure successfully completed.

SQL> select * from t1 where a=:test;~使用

no rows selected

SQL> print test~~~~~~~~~~输出变量结果

      TEST
----------
         2
********************

set arraysize N 指定数目提取尺寸 DEFALUT 15,这个跟优化有关系,后面关于优化实验中单独介绍


SQL> set autocommit on;~~~~~~~~自动提交
SQL> update t1 set a=2;

15 rows updated.

Commit complete.
SQL> set autocommit off;


SQL> set colsep |~~~~~~~~~~设置分隔符号default 空格
SQL> select object_name,object_id from user_objects where rownum<2;

OBJECT_NAME
--------------------------------------------------------------------------------

 OBJECT_ID
----------
TEST_SQ
     53795


SQL> col object_name format a20
SQL> select object_name,object_id from user_objects where rownum<2;

OBJECT_NAME         | OBJECT_ID
--------------------|----------
TEST_SQ             |     53795


SQL> set heading off~~~~~~~~不显示标题 列名 default on
SQL> select object_name,object_id from user_objects where rownum<2;

TEST_SQ             |     53795

set linesize行长  default 80
     pagsize每页显示的行数 default 14

SQL> select * from t1;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

         A
----------
        12
        13
        14
        15
        16
        17
        18
        19
        20

20 rows selected.

SQL> select * from t1;

         A~~~~~~~~~~~~
----------~~~~~~~~~~~~~~~~
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17

         A
----------~~~~~~~~~~~~~~这都算1行
        18
        19
        20

SQL> set pagesize 23
SQL> select * from t1;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20

20 rows selected.

20 rows selected.

SQL>

**********serveroutput
针对dbms_output的 default off 不输出

  1  declare
  2  begin
  3  dbms_output.put_line('test');
  4* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
test***************************

PL/SQL procedure successfully completed.


*********
termout 控制script. 输出default on ,off只运行不输出

SQL> set termout off
SQL> @d:\1.txt;

SQL>
SQL> set termout on
SQL> @d:\1.txt;
'这是TEST,按按回车'


  COUNT(*)
----------
        20

SQL>
**************
SQL> set time on~显示时间系统时间
15:25:41 SQL>
15:25:41 SQL> set time off
SQL>
SQL> set timing on~~~~~~~~~~~~~~~显示SQL语句运行时间
SQL> select count(*) from t1;

  COUNT(*)
----------
        20

Elapsed: 00:00:00.00


SQL> set define '@'~~~~~~改变量定义字符
SQL> define a=1
SQL> select count(*) from t1 where a=@a;
old   1: select count(*) from t1 where a=@a
new   1: select count(*) from t1 where a=1

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

SQL> select count(*) from t1 where a=&a;
SP2-0552: Bind variable "A" not declared.

SQL> set verify off~~~~~~~~~~~~~~不显示old,new行 (不显示验证消息)
SQL> define a=1
SQL> select count(*) from t1 where a=@a;

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

SQL> set verify on~~~~~~~~~~~~~~~~~~~~~
SQL> select count(*) from t1 where a=@a;
old   1: select count(*) from t1 where a=@a
new   1: select count(*) from t1 where a=1

  COUNT(*)
----------
         1
****************
clear colunm_name clear 清除对列设置
clear buffer 清除buffer
clear compute清除报表

spool d: 位置     append追加,replace覆盖,create新建
spool off

 

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426861