sqlplus常用操作コマンド2

32754 ワード

共通編集コマンド:A[ppend]text現在行にtextをアタッチした後C[hange]/old/new現在行のoldをnewCLear buff[er]パージバッファ内のすべての行DEL現在行DEL x削除x行L[ist]リストバッファ内のすべての行L[ist]xリストバッファ内のx行R[un]または/実行バッファ内に保存されている文xを削除x行目を現在行とする
SQL> select product_id,name

  2  from

  3  products;



PRODUCT_ID NAME

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

         1 Modern Science

         2 Chemistry

         3 Supernova

         4 Tank War

         5 Z Files

         6 2412: The Return

         7 Space Force 9

         8 From Another Planet

         9 Classical Music

        10 Pop 3

        11 Creative Yell

        12 My Front Line



12 rows selected.



SQL> 1    --  1   1 

  1* select product_id,name

SQL> append ,price  --  1    ,price

  1* select product_id,name,price

SQL> list  --         

  1  select product_id,name,price

  2  from

  3* products

SQL> 1  --                    

  1* select product_id,name,price

SQL> change /price / description  --  price   description

SP2-0023: String not found.

SQL> change /price /description

  1* select product_id,name,description

SQL> list

  1  select product_id,name,description

  2  from

  3* products

SQL> /    --           



PRODUCT_ID NAME                           DESCRIPTION

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

         1 Modern Science                 A description of modern science

         2 Chemistry                      Introduction to Chemistry

         3 Supernova                      A star explodes

         4 Tank War                       Action movie about a future war

         5 Z Files                        Series on mysterious activities

         6 2412: The Return               Aliens return

         7 Space Force 9                  Adventures of heroes

         8 From Another Planet            Alien from another planet lands on Earth

         9 Classical Music                The best classical music

        10 Pop 3                          The best popular music

        11 Creative Yell                  Debut album

        12 My Front Line                  Their greatest hits



12 rows selected.





SQL> run   --  ,           

  1  select product_id,name,description

  2  from

  3* products



PRODUCT_ID NAME                           DESCRIPTION

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

         1 Modern Science                 A description of modern science

         2 Chemistry                      Introduction to Chemistry

         3 Supernova                      A star explodes

         4 Tank War                       Action movie about a future war

         5 Z Files                        Series on mysterious activities

         6 2412: The Return               Aliens return

         7 Space Force 9                  Adventures of heroes

         8 From Another Planet            Alien from another planet lands on Earth

         9 Classical Music                The best classical music

        10 Pop 3                          The best popular music

        11 Creative Yell                  Debut album

        12 My Front Line                  Their greatest hits



12 rows selected.



SQL> 

二、ファイルの保存、取得、実行
SQL> list  --         

  1  select product_id,name,description

  2  from

  3* products



SQL> save /home/oracle/test_save.sql  --                    

Created file /home/oracle/test_save.sql

SQL> get test_save.sql

  1  select product_id,name,description

  2  from

  3* products

SQL> start test_save.sql     --                



PRODUCT_ID NAME                           DESCRIPTION

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

         1 Modern Science                 A description of modern science

         2 Chemistry                      Introduction to Chemistry

         3 Supernova                      A star explodes

         4 Tank War                       Action movie about a future war

         5 Z Files                        Series on mysterious activities

         6 2412: The Return               Aliens return

         7 Space Force 9                  Adventures of heroes

         8 From Another Planet            Alien from another planet lands on Earth

         9 Classical Music                The best classical music

        10 Pop 3                          The best popular music

        11 Creative Yell                  Debut album

        12 My Front Line                  Their greatest hits



12 rows selected.



SQL> @test_save.sql    -- start



PRODUCT_ID NAME                           DESCRIPTION

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

         1 Modern Science                 A description of modern science

         2 Chemistry                      Introduction to Chemistry

         3 Supernova                      A star explodes

         4 Tank War                       Action movie about a future war

         5 Z Files                        Series on mysterious activities

         6 2412: The Return               Aliens return

         7 Space Force 9                  Adventures of heroes

         8 From Another Planet            Alien from another planet lands on Earth

         9 Classical Music                The best classical music

        10 Pop 3                          The best popular music

        11 Creative Yell                  Debut album

        12 My Front Line                  Their greatest hits



12 rows selected.

 SQL> spool /home/oracle/select.txt   --                

SQL> /



PRODUCT_ID NAME                           DESCRIPTION

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

         1 Modern Science                 A description of modern science

         2 Chemistry                      Introduction to Chemistry

         3 Supernova                      A star explodes

         4 Tank War                       Action movie about a future war

         5 Z Files                        Series on mysterious activities

         6 2412: The Return               Aliens return

         7 Space Force 9                  Adventures of heroes

         8 From Another Planet            Alien from another planet lands on Earth

         9 Classical Music                The best classical music

        10 Pop 3                          The best popular music

        11 Creative Yell                  Debut album

        12 My Front Line                  Their greatest hits



12 rows selected.



SQL> spool off     --      ,      ,   spoo off,                  select.txt  

SQL> 

[root@rhel201 oracle]# ls

afiedt.buf  oradiag_oracle  select.txt  testbak  test_save.sql  test.sql

[root@rhel201 oracle]# cat test_save.sql 

select product_id,name,description

from

products

/

[root@rhel201 oracle]# cat select.txt 

SQL> /



PRODUCT_ID NAME                           DESCRIPTION                                                                                                                                                   

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

         1 Modern Science                 A description of modern science                                                                                                                               

         2 Chemistry                      Introduction to Chemistry                                                                                                                                     

         3 Supernova                      A star explodes                                                                                                                                               

         4 Tank War                       Action movie about a future war                                                                                                                               

         5 Z Files                        Series on mysterious activities                                                                                                                               

         6 2412: The Return               Aliens return                                                                                                                                                 

         7 Space Force 9                  Adventures of heroes                                                                                                                                          

         8 From Another Planet            Alien from another planet lands on Earth                                                                                                                      

         9 Classical Music                The best classical music                                                                                                                                      

        10 Pop 3                          The best popular music                                                                                                                                        

        11 Creative Yell                  Debut album                                                                                                                                                   

        12 My Front Line                  Their greatest hits                                                                                                                                           



12 rows selected.



SQL> spool off

[root@rhel201 oracle]# 

三、列の書式設定
SQL> /



PRODUCT_ID PRODUCT_TYPE_ID NAME                           DESCRIPTION                                             PRICE

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

         1               1 Modern Science                 A description of modern science                         19.95

         2               1 Chemistry                      Introduction to Chemistry                                  30

         3               2 Supernova                      A star explodes                                         25.99

         4               2 Tank War                       Action movie about a future war                         13.95

         5               2 Z Files                        Series on mysterious activities                         49.99

         6               2 2412: The Return               Aliens return                                           14.95

         7               3 Space Force 9                  Adventures of heroes                                    13.49

         8               3 From Another Planet            Alien from another planet lands on Earth                12.99

         9               4 Classical Music                The best classical music                                10.99

        10               4 Pop 3                          The best popular music                                  15.99

        11               4 Creative Yell                  Debut album                                             14.99

        12                 My Front Line                  Their greatest hits                                     13.49



12 rows selected.





SQL> col product_id format 99  --    

SQL> col name heading product_name format a13 word_wrapped   --  name   product_name,13    ,    

SQL> col descripton format a20 word_wrapped   --20    ,    

SQL> col proce format $99.99   --      price   

SQL> /



PRODUCT_ID PRODUCT_TYPE_ID product_name  DESCRIPTION                                             PRICE

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

         1               1 Modern        A description of modern science                         19.95

                           Science



         2               1 Chemistry     Introduction to Chemistry                                  30

         3               2 Supernova     A star explodes                                         25.99

         4               2 Tank War      Action movie about a future war                         13.95

         5               2 Z Files       Series on mysterious activities                         49.99

         6               2 2412: The     Aliens return                                           14.95

                           Return



         7               3 Space Force 9 Adventures of heroes                                    13.49

         8               3 From Another  Alien from another planet lands on Earth                12.99

                           Planet



         9               4 Classical     The best classical music                                10.99

                           Music



        10               4 Pop 3         The best popular music                                  15.99

        11               4 Creative Yell Debut album                                             14.99

        12                 My Front Line Their greatest hits                                     13.49



12 rows selected.



SQL> 



-----------

SQL> show linesize 

linesize 200

SQL> show pagesize

pagesize 50

SQL> set linesize 150

SQL> set pagesize 80

SQL> 



SQL> set verify on --  old  new    

SQL> /  

Enter value for test: 1

old   1: select * from products where product_type_id=&test

new   1: select * from products where product_type_id=1



PRODUCT_ID PRODUCT_TYPE_ID product_name  DESCRIPTION                                             PRICE

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

         1               1 Modern        A description of modern science                         19.95

                           Science



         2               1 Chemistry     Introduction to Chemistry                                  30



SQL> set verify off   --  old new

SQL> /

Enter value for test: 2



PRODUCT_ID PRODUCT_TYPE_ID product_name  DESCRIPTION                                             PRICE

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

         3               2 Supernova     A star explodes                                         25.99

         4               2 Tank War      Action movie about a future war                         13.95

         5               2 Z Files       Series on mysterious activities                         49.99

         6               2 2412: The     Aliens return                                           14.95

                           Return





SQL> 

説明:save filenameコンテンツを保存し、保存時にパスsave filename replace/apendを追加get filenameファイルstart filenameファイルを開いてfilenameファイルを開いて@filename同上edit filenameを実行するメモ帳またはviを使用してfilenameファイルを開いて編集し、filenameを指定しない場合は現在のバッファ内のコンテンツdefine_を開くedit='editor'またはdefine_edit='vi'定義デフォルトエディタspool filename出力結果を指定ファイルspool off終了結果出力に保存