oracle實驗記錄 （SQL*PLUS 命令操作）

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/，如需轉載，請註明出處，否則將追究法律責任。