หลังจากที่ ติดตั้ง 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