Sebelum seseorang itu mahu mendalami Oracle SQL dan PL/SQL, dia perlu membina asas kemahiran menggunakan SqlPlus. Berikut adalah panduan asas menggunakan SqlPlus.
Notes On Oracle Sql - Getting Started With SqlPlus, SQL Buffer and SQL Script File
Introduction
This notes is based on:
1) Oracle 11g Express Edition Installed On Windows Platform. Refer http://www.oracle.com/pls/db112/homepage for reading materials.
2) Sybex Textbook “OCA Oracle® Database 11g Administrator Certified Associate Study Guide”
Preparation
1) Download Oracle 11g Express Edition from http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
2) Install to Windows (XP or 7, 32-bit)
3) Installation process would have created a new Program Short Cut including an item “Run SQl Command Line”
4) Right-hand click the item and choose Properties.
4a) Look at the target box. It contains “C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog”
4b) Click the “Advanced” button. Tick “Run as Administrator”.
SQL Tools: SQL*Plus
1) Run SqlPlus
Note:
On Unix/Linux platforms, you can invoke SQL*Plus using the sqlplus executable found in
the $ORACLE_HOME/bin directory.
On Windows and Unix/Linux platforms, you can invoke SQL*Plus through command window by typing “sqlplus”.
2) When SqlPlus starts, it requests for username and password. Enter the correct username/password, e.g. SYSTEM/orapass
Entering SQL*Plus Commands
1) type
SELECT 800 -
400 FROM dual;
|
you would get a negative response as follows:
SELECT 800 400 FROM dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL>
|
explanation:
You need to put the hyphen in the next line for the query to succeed
2) type
SELECT 800
- 400 FROM dual;
|
you would get a positive response as follows:
800-400
----------
400
SQL>
|
Extra
You can adjust the paging for your output. This will cause the header line to be repeated in the output to create paging effect. Set the paging size by typing
SET PAGESIZE 20;
|
The output will have the header line repeated on the 20th line of the command window.
Sample HR Schema
A sample HR Schema is installed together with Oracle Database. By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.
1) type the following command to unlock the account.
ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
|
2) Later, you may unlock the account by issuing the command:
ALTER USER HR ACCOUNT LOCK;
|
Getting Information with the DESCRIBE Command
1) type the following command
DESCRIBE tabs;
|
2) the same output may be obtained with the following command (read more)
DESCRIBE user_tables;
|
You would get the description of the tables for the current user.
3) If you’re connected to the HR schema and need to see the tables and views in this schema,
use the following query:
SELECT * FROM tab;
|
Editing the SQL Buffer
1) Login as hr.
2) Type
SELECT * FROM TAB;
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
8 rows selected.
SQL>
|
3) Type
LIST;
|
Output:
1* select * from tab
SQL>
|
4) Type
L;
|
Output:
1* select * from tab
SQL>
|
Note:
The LIST command lists the contents of the buffer. The asterisk indicates the current line.
The abbreviated command for LIST is L.
|
5) Type
APPEND WHERE TABTYPE='TABLE';
|
Note: There is an extra space between APPEND and WHERE
Output:
1* SELECT * FROM tab WHERE TABTYPE='VIEW'
SQL>
|
Lesson:
The APPEND text command adds text to the end of line. The abbreviated command is A.
|
6) Type
CHANGE /'VIEW'/'TABLE'
|
Output:
1* SELECT * FROM tab WHERE TABTYPE='TABLE'
SQL>
|
Lesson:
The CHANGE /old/new command changes an old entry to a new entry. The abbreviated com-
mand is C.
|
7) Type
C /WHERE TABTYPE='TABLE'
|
Output:
1* SELECT * FROM tab
SQL>
|
Lesson:
The abbreviated command for CHANGE is C. If you omit new, old will be deleted.
|
8)
8a) Type
INPUT WHERE TABTYPE='VIEW'
|
8b) Type
LIST
|
Output:
1 SELECT * FROM TAB
2* WHERE TABTYPE='VIEW'
SQL>
|
Lesson:
The INPUT text command adds a line of text. Its abbreviation is I. If text is omitted, you
can add as many lines you want.
|
9)
9a) Type
DEL
|
9b) Type
LIST
|
Output:
1 SELECT * FROM TAB
SQL>
|
Lesson:
The DEL command used alone or with * deletes the current line.
If you continue with DEL command until there is no more line left, SqlPlus will output SP2-0026: No lines to delete.
|
10)
(Continue from Step 9 above)
10a) Type
LIST
|
Output:
SP2-0223: No lines in SQL buffer.
SQL>
|
10b) Type
SELECT * FROM TAB
WHERE TABTYPE='TABLE';
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
LOCATIONS TABLE
JOB_HISTORY TABLE
JOBS TABLE
EMPLOYEES TABLE
DEPARTMENTS TABLE
COUNTRIES TABLE
7 rows selected.
SQL>
|
10c) Type
CLEAR BUFFER
|
Output:
buffer cleared
SQL>
|
Lesson:
The CLEAR BUFFER command (abbreviated CL BUFF) clears the buffer. This deletes all lines from the buffer.
|
11)
(Continue from Step 10 above)
11a) Type
LIST
|
Output:
SP2-0223: No lines in SQL buffer.
SQL>
|
11b) Type
SELECT * FROM TAB;
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
8 rows selected.
SQL>
|
11c) Type
INPUT WHERE TABTYPE='TABLE';
|
11d) Type
RUN
|
Output:
1 SELECT * FROM TAB
2* WHERE TABTYPE='TABLE'
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
LOCATIONS TABLE
JOB_HISTORY TABLE
JOBS TABLE
EMPLOYEES TABLE
DEPARTMENTS TABLE
COUNTRIES TABLE
7 rows selected.
SQL>
|
11e) Type
/
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
LOCATIONS TABLE
JOB_HISTORY TABLE
JOBS TABLE
EMPLOYEES TABLE
DEPARTMENTS TABLE
COUNTRIES TABLE
7 rows selected.
SQL>
|
Lesson:
You can run the command in the buffer by simply typing a slash or using the RUN
command.
|
Creating and Editing SQL Script File
1) Login as hr.
2) Type
SELECT * FROM TAB
WHERE TABTYPE=’TABLE’;
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
7 rows selected.
SQL>
|
3) Type
LIST;
|
Output:
1 SELECT * FROM TAB
2* WHERE TABTYPE='TABLE'
SQL>
|
4) Type
SAVE selectTable;
|
Output:
Created file selectTable.sql
SQL>
|
5) Type
START selectTable.sql;
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
7 rows selected.
SQL>
|
Lesson:
SQL statements saved in a file are called a script file.
SQL Script file can be executed by using the START command.
|
6) Type
EDIT selectTable.sql;
|
Output (By Windows Default, the output is in the Notepad program):
SELECT * FROM TAB
WHERE TABTYPE='TABLE'
/
|
7) Edit in Notepad as follows and save
SELECT * FROM TAB
WHERE TABTYPE='VIEW'
/
|
8) Go back to Command Window and Type
START selectTable.sql;
|
Output:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP_DETAILS_VIEW VIEW
SQL>
|
Lesson:
SQL Script file can be edited by external program such as Notepad.
|
9) Type
GET selectTable.sql;
|
Output:
1 SELECT * FROM TAB
2* WHERE TABTYPE='VIEW'
SQL>
|
Lesson:
GET command loads the SQL Script file to the buffer.
|
10) Type
CHANGE /’VIEW’/’TABLE’
|
Output:
2* WHERE TABTYPE='TABLE'
SQL>
|
11) Type
SAVE selectTable REPLACE;
|
Output:
Wrote file selectTable.sql
SQL>
|
Lesson:
SAVE command with option REPLACE overwrites the content of the existing SQL Script file.
|
No comments:
Post a Comment