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行目を現在行とする
二、ファイルの保存、取得、実行
三、列の書式設定
説明: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終了結果出力に保存
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終了結果出力に保存