Basic SQL*Plus edit and execute commands

1
Basic SQLPlus edit and execute commands

• SQLPlus buffer and built-in editor
• holds the last SQL statement
• Statements are created in free-flow style and
  are numbered
• Example select Custnumb, Balance from
  CUSTOMER
• The semicolon indicates the end of the SQL
  statement and causes execution
• A builtin editor is used to change the
  statements in the buffer after it has been
• created
• Append Credlim to the current listing

• Change the SELECT statement to return Custname
  rather than Custnumb

2
Basic SQLPlus edit and execute commands contd.

• Insert a new line ORDER BY SLSRNUMB after the
  current line

• List the current statement in the buffer

• Formatting columns

3
Basic SQLPlus edit and execute commands contd.

• The SQL statement in the buffer can be saved to
  a file
• A file may contain any number of SQL statements

• Comments
• --for a single line of text
• / for multi-line texts /
• Editing and running a file

4
Database tables

• Table columns, rows
• Valid Oracle object names
• must be unique
• lt 30 characters
• not a reserved word
• must start with a letter
• can contain letters, digits, some special
  characters (_,,)
• Data types for columns
• VARCAHR2
• variable length character string up to 2000
  characters
• must specify length-- Example slname VARCHAR2
  (30)
• CHAR
• fixed length character string up to 255
  characters
• should not be used if exact length is not known
• NUMBER integer, fixed point, floating point
  numbers
• INTEGER whole numbers
• Example sid number(5)
• fixed-point numbers price NUMBER (5,2)
• floating point numbers gpa NUMBER

5
Database tables contd

• Data types contd
• DATE no length specification needed. Various
  format masks can be used
• LONG used for very large character data. Do
  not use
• RAW/LONG RAW used for binary data--digitized
  image.
• Integrity constraints
• Primary key sid NUMBER (5) CONSTRAINT
  student_pk PRIMARY KEY
• Foreign key locid NUMBER (5) CONSTRAINT
  faculty_fk
• REFERENCES location(locid)
• Value constraints
• check that gpa is between 0 and 4
• gpa NUMBER (5,2) CONSTRAINT gpa_cc
• CHECK gpa between 0 and 4
• Creating/modifying/dropping tables
• CREATE TABLE lttablenamegt
• (ltfieldnamegt ltdata typegt CONSTRAINT
  ltconstraint definitiongt,....)
• Example CREATE TABLE location
• (locid number (5) CONSTRAINT
  location_pk PRIMARY KEY
• ......)
• DROP TABLE lttablenamegt

6
Database tables contd

• Viewing table structure
• field definition describe lttablenamegt
• Example

• Constraint definition
• user_constraints table

• Modifying tables
• changing a columns size, data type, default
  value
• adding/deleting PK, FK constraints
• deleting column/changing column name--not
  allowed
• adding a new column

7
Database tables contd

• Modifying table definitions
• Adding column/constraints
• ALTER TABLE lttablenamegt ADD (ltcolumn
  definitiongt/
• 
  ltconstraint definitiongt)
• Modifying existing table
• ALTER TABLE lttablenamegt MODIFY (ltnew column
  defgt/
• 
  ltnew constraint defgt)

• Dropping constraints
• ALTER TABLE lttablenamegt DROP CONSTRINT
  ltconstraint namegt