การใช้ Oracle SQL Plus เบื้องต้น เพื่อคิวรี่ข้อมูล

หลังจากที่ ติดตั้ง Oracle Database 11g Release 2 บน CentOS 6 และรู้ วิธีเปิด ปิด Oracle Database 11g  ไปแล้ว ขั้นต่อไปขอแนะนำการใช้คำสั่ง SQL Plus (sqlplus) เบื้องต้นเพื่อคิวรี่ข้อมูล โดยทดสอบกับฐานข้อมูลตัวอย่าง HR ที่ติดตั้งมาโดยดีฟอลต์

sqlplus เป็นโปรแกรมในรูปแบบ command-line ที่ใช้ในการเชื่อมต่อเข้ากับ Oracle Database

การใช้คำสั่ง ต้องล็อกอินด้วย oracle แล้วใช้ . เพื่อโหลดค่าคอนฟิกที่จำเป็นด้วยคำสั่ง oraenv

[oracle@cent6-oracle11g2 ~]$ . /usr/local/bin/oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base for ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1 is /home/oracle/app/oracle

 

ปลดล็อกฐานข้อมูลตัวอย่าง HR

โดยดีฟอลต์การติดตั้ง Oracle Database จะมีฐานข้อมูลตัวอย่าง HR มาด้วย แต่จะถูกล็อค (lock) ไว้ ไม่สามารถใช้งานได้

ต้องปลดล็อก (unlock) ก่อน

รัน sqlplus เป็น sysdba แล้วใช้คำสั่ง ALTER USER เพื่อปลดล็อก HR (ระบุรหัสผ่านเป็น your_password)

[oracle@cent6-oracle11g2 ~]$ sqlplus / as sysdba

SQL> ALTER USER HR IDENTIFIED BY your_password ACCOUNT UNLOCK;

User altered.

SQL> exit

 

พิมพ์คำสั่ง exit เพื่อออกจากคำสั่ง sqlplus

เปลี่ยนเข้า sqlplus ด้วยผู้ใช้ HR

หมายเหตุ ใน SQL Plus คำสั่ง ชื่อผู้ใช้ ชื่อตาราง ชื่อคอลัมน์ จะพิมพ์ด้วยตัวอักษรใหญ่หรือตัวเล็กก็ได้ (case-insensitive)

[oracle@cent6-oracle11g2 ~]$ sqlplus HR
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 20 11:50:07 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter password: your_password
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

แสดงตารางที่มีอยู่ (MySQL : SHOW TABLES)

user_tables จะเก็บรายชื่อตาราง (table) ที่ผู้ใช้ (ในที่นี้คือ HR) เป็นเจ้าของ (owner) อยู่

เราสามารถใช้คำสั่ง SELECT เพื่อแสดงรายชื่อตารางได้

SQL> SELECT table_name FROM user_tables;
TABLE_NAME
------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
COUNTRIES
7 rows selected.
SQL>

 

ดูโครงสร้างตาราง (DESCRIBE)

ใช้คำสั่ง DESC หรือ DESCRIBE เพื่อแสดงโครงสร้างตารางที่ต้องการ

ตัวอย่างการใช้คำสั่ง DESC เพื่อดูโครงสร้างตาราง countries

SQL> DESC countries;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COUNTRY_ID                                NOT NULL CHAR(2)
COUNTRY_NAME                                       VARCHAR2(40)
REGION_ID                                          NUMBER

ผลลัพธ์จากคำสั่ง DESC แสดงชื่อคอลัมน์ ชนิดตัวแปร

 

กำหนดจำนวนบรรทัดที่แสดงผลในแต่ละหน้า

ตัวอย่างการ SELECT เพื่อดูข้อมูลในตาราง countries

SQL> SELECT * FROM countries;
CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CH Switzerland                                       1
CN China                                             3
DE Germany                                           1
DK Denmark                                           1
EG Egypt                                             4
FR France                                            1
CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
IL Israel                                            4
IN India                                             3
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
ML Malaysia                                          3
MX Mexico                                            2
NG Nigeria                                           4
NL Netherlands                                       1
SG Singapore                                         3
UK United Kingdom                                    1
CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          4
25 rows selected.
SQL>

 

ใน sqlplus ผลลัพธ์จะแสดงทีละชุด (page) ตามจำนวนบรรทัด (รวมส่วนหัว ชื่อคอลัมน์ด้วย) ที่คอนฟิกไว้ในค่า PAGESIZE

ใช้คำสั่ง SHOW PAGESIZE เพื่อดูค่าที่กำหนดไว้

SQL> SHOW PAGESIZE;
pagesize 14

หากต้องการเพิ่มค่านี้ ให้ใช้คำสั่ง SET PAGESIZE เช่นแก้ไขให้แสดงผลลัพธ์ทีละ 100 บรรทัดเลย

SQL> SET PAGESIZE 100;
SQL> SHOW PAGESIZE;
pagesize 100

ลอง SELECT ดูอีกครั้ง

SQL> SELECT * FROM countries;
CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CH Switzerland                                       1
CN China                                             3
DE Germany                                           1
DK Denmark                                           1
EG Egypt                                             4
FR France                                            1
IL Israel                                            4
IN India                                             3
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
ML Malaysia                                          3
MX Mexico                                            2
NG Nigeria                                           4
NL Netherlands                                       1
SG Singapore                                         3
UK United Kingdom                                    1
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          4
25 rows selected.
SQL>

 

กำหนดจำนวนตัวอักษรที่แสดงผลในแต่ละบรรทัด

ตัวอย่างการ SELECT เพื่อดูข้อมูลในตาราง locations

SQL> SELECT * FROM locations;
LOCATION_ID STREET_ADDRESS                           POSTAL_CODE
----------- ---------------------------------------- ------------
CITY                           STATE_PROVINCE            CO
------------------------------ ------------------------- --
       1000 1297 Via Cola di Rie                     00989
Roma                                                     IT
       1100 93091 Calle della Testa                  10934
Venice                                                   IT
       1200 2017 Shinjuku-ku                         1689
Tokyo                          Tokyo Prefecture          JP
       1300 9450 Kamiya-cho                          6823
Hiroshima                                                JP
       1400 2014 Jabberwocky Rd                      26192
Southlake                      Texas                     US
       1500 2011 Interiors Blvd                      99236
South San Francisco            California                US
       1600 2007 Zagora St                           50090
South Brunswick                New Jersey                US
       1700 2004 Charade Rd                          98199
Seattle                        Washington                US
       1800 147 Spadina Ave                          M5V 2L7
Toronto                        Ontario                   CA
       1900 6092 Boxwood St                          YSW 9T2
Whitehorse                     Yukon                     CA
       2000 40-5-12 Laogianggen                      190518
Beijing                                                  CN
       2100 1298 Vileparle (E)                       490231
Bombay                         Maharashtra               IN
       2200 12-98 Victoria Street                    2901
Sydney                         New South Wales           AU
       2300 198 Clementi North                       540198
Singapore                                                SG
       2400 8204 Arthur St
London                                                   UK
       2500 Magdalen Centre, The Oxford Science Park OX9 9ZB
Oxford                         Oxford                    UK
       2600 9702 Chester Road                        09629850293
Stretford                      Manchester                UK
       2700 Schwanthalerstr. 7031                    80925
Munich                         Bavaria                   DE
       2800 Rua Frei Caneca 1360                     01307-002
Sao Paulo                      Sao Paulo                 BR
       2900 20 Rue des Corps-Saints                  1730
Geneva                         Geneve                    CH
       3000 Murtenstrasse 921                        3095
Bern                           BE                        CH
       3100 Pieter Breughelstraat 837                3029SK
Utrecht                        Utrecht                   NL
       3200 Mariano Escobedo 9991                    11932
Mexico City                    Distrito Federal,         MX
23 rows selected.
SQL>

 

จำนวนตัวอักษรที่แสดงในแต่ละบรรทัด จะถูกกำหนดโดยค่าคอนฟิก LINESIZE

ใช้คำสั่ง SHOW LINESIZE เพื่อดูค่า

SQL> SHOW LINESIZE;
linesize 80

หากต้องการเพิ่มจำนวนตัวอักษร ก็แก้ไขค่าคอนฟิกนี้ เช่นต้องการเพิ่มเป็น 128

SQL> SET LINESIZE 128;
SQL> SHOW LINESIZE;
linesize 128

 

ลอง SELECT อีกครั้ง ผลลัพธ์จะดูง่ายขึ้นมาก

SQL> SELECT * FROM locations;
LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
       1000 1297 Via Cola di Rie                     00989        Roma                                                     IT
       1100 93091 Calle della Testa                  10934        Venice                                                   IT
       1200 2017 Shinjuku-ku                         1689         Tokyo                          Tokyo Prefecture          JP
       1300 9450 Kamiya-cho                          6823         Hiroshima                                                JP
       1400 2014 Jabberwocky Rd                      26192        Southlake                      Texas                     US
       1500 2011 Interiors Blvd                      99236        South San Francisco            California                US
       1600 2007 Zagora St                           50090        South Brunswick                New Jersey                US
       1700 2004 Charade Rd                          98199        Seattle                        Washington                US
       1800 147 Spadina Ave                          M5V 2L7      Toronto                        Ontario                   CA
       1900 6092 Boxwood St                          YSW 9T2      Whitehorse                     Yukon                     CA
       2000 40-5-12 Laogianggen                      190518       Beijing                                                  CN
       2100 1298 Vileparle (E)                       490231       Bombay                         Maharashtra               IN
       2200 12-98 Victoria Street                    2901         Sydney                         New South Wales           AU
       2300 198 Clementi North                       540198       Singapore                                                SG
       2400 8204 Arthur St                                        London                                                   UK
       2500 Magdalen Centre, The Oxford Science Park OX9 9ZB      Oxford                         Oxford                    UK
       2600 9702 Chester Road                        09629850293  Stretford                      Manchester                UK
       2700 Schwanthalerstr. 7031                    80925        Munich                         Bavaria                   DE
       2800 Rua Frei Caneca 1360                     01307-002    Sao Paulo                      Sao Paulo                 BR
       2900 20 Rue des Corps-Saints                  1730         Geneva                         Geneve                    CH
       3000 Murtenstrasse 921                        3095         Bern                           BE                        CH
       3100 Pieter Breughelstraat 837                3029SK       Utrecht                        Utrecht                   NL
       3200 Mariano Escobedo 9991                    11932        Mexico City                    Distrito Federal,         MX
23 rows selected.
SQL>

 

เมื่อออกจากคำสั่ง sqlplus ค่าที่ตั้งไว้ด้วย SET เหล่านี้จะหายไป

หากต้องการให้มีการตั้งค่าเหล่านี้โดยอัตโนมัติ เมื่อรันคำสั่ง sqlplus เราสามารถสร้างไฟล์ login.sql ไว้ในไดเร็คทอรีที่จะรันคำสั่ง เช่น HOME ของ oracle เอง

[oracle@cent6-oracle11g2 ~]$ pwd
/home/oracle
[oracle@cent6-oracle11g2 ~]$ vi login.sql
SET PAGESIZE 100
SET LINESIZE 128

ลองรันคำสั่ง sqlplus อีกครั้ง

[oracle@cent6-oracle11g2 ~]$ sqlplus hr
SQL> SHOW PAGESIZE
pagesize 100
SQL>
SQL> SHOW LINESIZE
linesize 128
SQL>

ข้อแม้ของการแก้ไขไฟล์คอนฟิก login.sql แบบนี้ ต้องรันคำสั่ง sqlplus ในไดเร็คทอรีที่ไฟล์ login.sql นี้อยู่

หากต้องการให้รันคำสั่ง sqlplus ในไดเร็คทอรีไหนก็ได้ แล้วมีการตั้งค่าคอนฟิกเหล่านี้โดยอัตโมมัติ ต้องแก้ไขไฟล์ glogin.sql

[oracle@cent6-oracle11g2 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
SET PAGESIZE 99
SET LINESIZE 127

ข้อมูลอ้างอิง

Leave a Reply