Transcript OracleSQL1Plus

1
ORACLE
SQL
iSQLPlus & SQLPLUS
Statements
iSQLPlus Environment
iSQLPlus is a application software layer
that allows programmers to utilize SQL to
make changes to the database
• Log in to iSQL*Plus.
• Describe the table structure.
• Edit your SQL statement.
• Execute SQL from SQL*Plus.
• Save SQL statements to files and append SQL
statements to files.
• Execute saved files.
• Load commands from file to buffer
to edit.
1-2
SQL and iSQL*Plus Interaction
SQL Statements
Buffer
SQL Statements
Server
SQL*Plus
SQL*Plus
Commands
Formatted Report
1-3
Query Results
SQL Statements Versus
iSQL*Plus Commands
SQL
• A language
• ANSI standard
• Keyword cannot be
abbreviated
• Statements manipulate
data and table
definitions in the
database
SQL
statements
1-4
SQL
buffer
iSQL*Plus
• An online environment
• Oracle proprietary
• Keywords can be
abbreviated
• Commands do not
allow manipulation of
values in the database
SQL*Plus
commands
SQL*Plus
buffer
Things you can do in iSQL*Plus
• Log in to SQL*Plus.
• Describe the table structure.
• Edit your SQL statement.
• Execute SQL from SQL*Plus.
• Save SQL statements to files and
append SQL statements to files.
• Execute saved files.
• Load commands from file to buffer
to edit.
1-5
Logging In to SQL*Plus
• From Windows environment (Using the
client software):
• From command line:
sqlplus [username[/password
[@database]]]
1-6
Logging In to iSQL*Plus
• From Browser:
http://lovelace.cs.missouriwestern.edu:5560/isqlplus/
1-7
SQL Commands
Creating a table
Create Table Dept (
DEPT_NO
NUMBER(2) Not Null,
DNAME
VARCHAR2(14),
LOC
VARCHAR2(13),
PRIMARY KEY (DEPT_NO));
1-8
Displaying Table Structure
Use the SQL*Plus DESCRIBE command to
display the structure of a table.
DESC[RIBE] tablename
1-9
Displaying Table Structure
SQL> DESCRIBE dept
Name
Null?
----------------- -------DEPTNO
NOT NULL
DNAME
LOC
1-10
Type
-----------NUMBER(2)
VARCHAR2(14)
VARCHAR2(13)
Running a Script File from iSQLPlus
A. Select ‘Load Scrip
1. make sure your script
is a text file (.txt or .sql)
B. Browse to find your
script, then select it
C. Hit Execute
D. Review results at
the bottom of page
OR
Copy/Paste script into
window, then
Execute
1-11
iSQLPlus
Environment
Go to preferences
to set environment
Go to History to see and access
previously ran scripts
1-12
Set Display Commands
Set display Commands to
ON - this will allow the
command to display before the
result. This is the required
format for turning in homework.
1-13
Summary
Use iSQL*Plus as an environment to:
• Execute SQL statements
• Edit SQL statements
1-14