3902 Chapter 1

1

• Outline SQL in Oracle
• Oracle database system architecture
• - Oracle server
• - Oracle client
• SQLPlus
• PL/SQL

2

• Oracle system architecture
• Oracle server and Oracle client

data management transaction control recovery secur
ity
Oracle server
Oracle client
Oracle client
Oracle client
interface to manipulate data tools to support
development of application
3

• SQLPlus
• Interface to manipulate Oracle databases
• Tool to support the development of application
• - SQLPlus as an interface
• To start SQLPlus, enter Oracle username and
  password
• gt sqlplus jason/athena
• (from a command line operating system such UNIX)
• or
• click Start ? Program ? Oracle
• (for Windows - SQLPlus)

4
- SQLPlus as an interface Create a table with
integrity constraints CREATE TABLE
bank_account (bank_acct_no VARCHAR2(40), empid
NUMBER(10), BANK_ROUTE_NO VARCHAR2(40), BANK_
NAME VARCHAR2(50), CONSTRAINT pk_bank_acct_01,
PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_
acct_01 FOREIGN KEY (empid) REFERENCE employee
(empid))
5
- SQLPlus as an interface Create a table with
integrity constraints CREATE TABLE
bank_account (bank_acct_no VARCHAR2(40), empid
NUMBER(10), BANK_ROUTE_NO VARCHAR2(40), BANK_
NAME VARCHAR2(50), CONSTRAINT pk_bank_acct_01,
PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_
acct_01 FOREIGN KEY (empid) REFERENCE employee
(empid) ON DELETE CASCADE)
6
CREATE TABLE employee (empid NUMBER(10), las
tname VARCHAR2(25), firstname VARCHAR2(25), sa
lary NUMBER(10, 4), home_phone
NUMBER(15), CONSTRAINT pk_employee_01 PRIMARY
KEY (empid), CONSTRAINT uk_employee_01 UNIQUE
(home_phone))
The difference between PRIMARY KEY and UNIQUE
is that for a UNIQUE attribute NULL value is
allowed.
7
- SQLPlus as an interface NOT NULL constraints
and check constraints CREATE TABLE
employee (empid NUMBER(10), lastname VARCHAR2(
25), NOT NULL firstname VARCHAR2(25), NOT
NULL salary NUMBER(10, 4), CHECK(salary lt
50000 home_phone NUMBER(15), CONSTRAINT pk_em
ployee_01 PRIMARY KEY (empid), CONSTRAINT uk_e
mployee_01 UNIQUE (home_phone))
8
- SQLPlus as an interface Adding and modifying
columns ALTER TABLE products ADD (color
VARCHAR2(10)) ALTER TABLE products MODIFY
(SERIAL VARCHAR2(25)) Assume that SERIAL is
an attribute in PRODUCTS with type VARCHAR2(10).
9
- SQLPlus as an interface Modifying integrity
constraints ALTER TABLE products MODIFY (color
NOT NULL) ALTER TABLE products ADD
(CONSTRAINT pk_products_01 PRIMARY
KEY (product)) ALTER TABLE products ADD
(CONSTRAINT fk_products_01 FOREIGN
KEY REFERENCES (AVAIL_COLOR.color))
10
- SQLPlus as an interface Modifying integrity
constraints ALTER TABLE products ADD (UNIQUE
(serial)) ALTER TABLE products ADD (size
CHECK (size in P, S, M, L, XL,
XXL, XXXL))
11
- SQLPlus as an interface Enabling or disabling
constraints ALTER TABLE products ENABLE
CONSTRAINT pk_products_01 ALTER TABLE
products ENABLE CONSTRAINT uk_products_03
12
- SQLPlus as an interface Enabling or disabling
constraints ALTER TABLE products DISABLE
PRIMARY KEY ALTER TABLE products DISABLE
UNIQUE (serial) ALTER TABLE
products DISABLE PRIMARY KEY CASCADE
13
- SQLPlus as an interface Dropping constraints
ALTER TABLE products DROP CONSTRAINT
uk_products_01 ALTER TABLE products DROP
PRIMARY KEY CASCADE
14
- SQLPlus as an interface Dropping Tables
ALTER TABLE products ALTER TABLE
products DROP CONSTRAINT Truncating
Tables TRUNCATE TABLE products
15
- SQLPlus as an interface Changing Names of
Objects RENAME products TO objects CREATE
SYNONYM objects FOR products CREATE PUBLIC
SYNONYM objects FOR products
16
- SQLPlus as an interface Sequences A
sequence is a special database object that
generates integers according to specified rules
at the time the sequence was created. - In some
cases, the primary key is not important to use
for accessing data to store stored in a
table. Example A doctors office may have a
client tracking system that assigns each new
patient a unique integer ID to identify their
records. - Using a sequence to generate primary
keys automatically.
17
- SQLPlus as an interface Creating sequences
CREATE SEQUENCE countdown_20 START WITH
20 INCREMENT BY 1 NOMAXVALUE CYCLE ORDER CRE
ATE SEQUENCE SOME_NUM MINVALUE 0 MAXVALUE
1000 NOCYCLE
20 19
...
1 20 19
...
0 1
...
1000
18
- SQLPlus as an interface Using sequences
SELECT some_num.currval CURRENT some_num.ne
xtval NEXT some_num.currval
CURRENT FROM dual CURRENT NEXT CURRENT 1
2 2
19
- SQLPlus as an interface Using sequences
INSERT INTO expense(expense_no, empid, amt,
submit_date) VALUE(some_num.nextval, 59495,
456.34, 21-nov-99) UPDATE product SET
product_num some_num.currval WHERE serial_num
3498583945
20
- SQLPlus as an interface Modifying a sequence
definition ALTER SEQUENCE countdown_20 INCREM
ENT BY 4 ALTER SEQUENCE countdown_20 NOCYCL
E ALTER SEQUENCE some_num MAXVALUE 10000
21
- SQLPlus as an interface Removing sequence
DROP SEQUENCE some_num
22
- SQLPlus as an interface Views Creating
simple views CREATE VIEW employee_view AS
(SELECT empid, lastname, firstname, salary FROM
employee WHERE empid 59495) UPDATE
employee_view SET salary 99000 WHERE empid
59495
23
- SQLPlus as an interface Creating complex
views CREATE VIEW employee_view AS (SELECT
e.empid empid, e.lastname lastname, e.firstname,
firstname, e.salary salary, a.address, a.city,
a.state, a.zipcode FROM employee e,
employee_address a WHERE e.empid
a.empid) CREATE VIEW employee_view AS (SELECT
empid, lastname, firstname, salary FROM
employee WHERE empid 59495) WITH CHECK
OPTION
24
- SQLPlus as an interface Modifying
views CREATE OR REPLACE VIEW employee_view AS
(SELECT empid, lastname, firstname, salary FROM
employee WHERE empid user) WITH CHECK
OPTION Removing views DROP VIEW
employee_view
25
- SQLPlus as an interface Creating indexes
manually CREATE UNIQUE INDEX employee_lastname_i
ndex_01 ON employee (lastname) CREATE
INDEX employee_lastname_index_01 ON employee
(lastname)
26
- SQLPlus as an interface Creating indexes
manually CREATE UNIQUE INDEX employee_last_first
_index_01 ON employee (lastname, firstname)
27
- SQLPlus as an interface Automatic
indexes Oracle will create a B-tree for an
attrubute with primary key constraint or
unique constraint.
28

• PL/SQL
• PL/SQL is a special language available for
  developers to code stored procedures that
  seamlessly integrate with database objects access
  via the language of database objects, SQL.
• PL/SQL procedure a series of statements
  accepting and/or returning zero or more
  variables.
• PL/SQL function a series of statements accepting
  zero or more variables and returning one value.
• A PL/SQL procedure or a PL/SQL function is
  called a PL/SQL block.

29

• - PL/SQL
• A PL/SQL block normally contains three
  components
• variable declaration section,
• executable section, and
• exception section.

30
- PL/SQL There are two kinds of blocks in
Oracle named and unnamed or anonymous
blocks. Named block CREATE FUNCTION
convert_money ( AMOUNT IN NUMBER, convert_cu
rrency IN VARCHAR2, old_currency IN VARCHAR2)
IS my_new_amt number(10) 0 bad_data exce
ption BEGIN IF my_new_amt gt 3 THEN
... ELSE ... END IF
Decralation section
Executable section
31
- PL/SQL ... EXCEPTION WHEN bad_data
THEN DBMS_OUTPUT.PUT_LINE(Error
condition) END
Exception handler
32
- PL/SQL Unnamed block DECLARE my_new_amt numb
er(10) 0 bad_data exception BEGIN IF
my_new_amt gt 3 THEN ... ELSE ... END
IF EXCEPTION WHEN bad_data
THEN DBMS_OUTPUT.PUT_LINE(Error
condition) END
33

• Datatypes used in PL/SQL
• There are two kinds of datatypes database
  datatypes and nondatabase types.
• Database datatypes
• There are several datatypes that can be used in
  PL/SQL that correspond to the datatypes used on
  the database, i.e., the datatypes used for
  defining a table.
• 1. NUMBER(size,precision) - used to any
  number.
• NUMBER(10), NUMBER(10, 5)
• 2. CHAR(size), VARCHAR(size) - used to store
  alphanumeric text strings. The CHAR datatype pads
  the value stored to the full length of the
  variable with blanks.

34
3. DATE - Used to store dates. 4. LONG - Stores
large blocks of text, up to 2 gigabytes in
length. 5. LONG RAW - Stores large blocks of
data stored in binary format. RAW - Stores
smaller blocks of data stored in binary
format. 6. BLOB, CLOB, NCLOB BFILE - Large
object datatype.
35
Nondatabase datatypes 1. DEC, DECIMAL, REAL,
DOUBLE_PRECISION - These numeric datatypes are a
subset of the NUMBER datatype that is used for
variable declaration in PL/SQL. 2. INTEGER, INT,
SMALLINT, NATURAL, POSITIVE, NUMERIC - These
numeric datatypes are a subset of the NUMBER
datatype that is used for variable declaration in
PL/SQL. 3. BINARY_INTEGER, PLS_INTEGER - These
datatypes store integers. A variable in either
format cannot be stored in the database without
conversion first.
36
4. CHARACTER - Another name for the CHAR
datatype. 5. VARCHAR - Another name for the
VARCHAR2 datatype. 6. BOOLEAN - Stores a
TRUE/FALSE value. 7. TABLE/RECORD - Tables can
be used to store the equivalent of an array,
while records store variables with composite
datatypes.
37

• TYPE
• Using the TYPE keyword, you can declare a
  variable to be of the type same as an attribute.
• DECLARE
• my_employee_id employee.empidTYPE
• BEGIN ...
• DECLARE
• my_salary employee.salaryTYPE 0
• my_lastname employee.lastnameTYPE SMITH
• BEGIN ...

38

• ROWTYPE
• Using the ROWTYPE keyword, you can declare a
  variable to be of the type same as a table.
• DECLARE
• my_employee employeeROWTYPE
• BEGIN ...

39
DECLARE TYPE t_employee IS RECORD
( my_empid employee.empidTYPE, my_lastname em
ployee.lastnameTYPE, my_firstname employee.fir
stnameTYPE, my_salary employee.firstnameTYPE)
my_employee t_employee BEGIN ...
40
Constant declaration CREATE FUNCTION
find_circle_area ( p_radius IN
circle.radiusTYPE RETURN NUMBER
IS my_area number(10) 0 pi constant
number(15, 14) 3.14159265358 BEGIN my_area
(p_radiusp_radius)pi Return
(my_area) END
41
Using SQL Statements in PL/SQL DECLARE my_empl
oyee employeeROWTYPE my_lastname VARCHAR(30)
SAMSON my_firstname VARCHAR(30)
DELILAN my_salary NUMBER(10)
49500 BEGIN SELECT INTO
my_employee FROM employee WHERE empid
49594 UPDATE employee SET salary
my_employee.salary 10000 WHERE empid
my_employee.empid
42
INSERT INTO employee (empid, lastname,
firstname, salary) VALUE (emp_sequence.nextval,
my_lastname, my_firstname, my_salary) my_employ
ee.empid 59495 DELETE FROM employee WHERE
empid my_empid END
43

• CURSOR concept
• A cursor is an address in memory where a SQL
  statement is processed.
• There are two kinds of cursors explicit and
  implicit
• An explicit cursor is named address (via a
  variable)
• An implicit cursor is unnamed address.

44

• CURSOR concept
• Explicit cursor - an named address where an SQL
  statement is processed.
• DECLARE
• high_pctinc constant number(10, 5) 1.20
• med_pctinc constant number(10, 5) 1.10
• low_pctinc constant number(10, 5) 1.05
• my_salary employee.salaryTYPE
• my_empid employee.empidTYPE
• CURSOR employee_crsr IS
• SELECT empid, salary
• FROM employee

45
BEGIN OPEN employee_crsr LOOP FETCH
employee_crsr INTO my_empid, my_salary EXIT
WHEN employee_crsrNOTFOUND IF my_empid
59697 OR my_empid 76095 THEN UPDATE employee
SET salary my_salaryhigh_pctinc WHERE empid
my_empid
46
ELSEIF my_empid 39294 OR my_empid 94329
THEN UPDATE employee SET salary
my_salarylow_pctinc WHERE empid
my_empid ELSE UPDATE employee SET salary
my_salary mid_pctinc WHERE empid
my_empid END IF END LOOP END
47

• CURSOR concept
• Implicit cursor - an unnamed address where an SQL
  statement is processed. Therefore, there is no
  declaration for an implicit cursor variable.
• Whenever an SQL is evaluated, an implicit cursor
  is automatically associated with it.
• Such an implicit cursor can be manipulated using
  the cursor attributes
• notfound
• found
• rowcount
• isopen

48

• Implicit cursor
• DECLARE
• my_empid employee.empidTYPE 59694
• my_salary employee.salaryTYPE 99000
• my_lastname employee.lastnameTYPE
  RIDDINGS
• BEGIN
• UPDATE employee
• SET salary my_salary
• WHERE my_empid
• IF sqlNOTFOUND THEN
• INSERT INTO EMPLOYEE (empid, lastname, salary)
• VALUE(my_empid, my_lastname, my_salary)
• END IF
• END

49

• Parameters and explicit cursors
• DECLARE
• high_pctinc constant number(10, 5) 1.20
• med_pctinc constant number(10, 5) 1.10
• low_pctinc constant number(10, 5) 1.05
• my_salary employee.salaryTYPE
• my_empid employee.empidTYPE
• CURSOR employee_crsr (low_end in VARCHAR2,
  high_end in VARCHAR2) IS
• SELECT empid, salary
• FROM employee
• WHERE UPPER(substr(lastname, 1, 1) BETWEEN
  UPPER(low_end)
• AND UPPER(high_end)

50
BEGIN OPEN employee_crsr(A, M)
LOOP FETCH employee_crsr INTO my_empid,
my_salary EXIT WHEN employee_crsrNOTFOUND I
F my_empid 59697 OR my_empid 76095
THEN UPDATE employee SET salary
my_salaryhigh_pctinc WHERE empid
my_empid ELSEIF my_empid 39294 OR my_empid
94329 THEN UPDATE employee SET salary
my_salarylow_pctinc WHERE empid
my_empid ELSE UPDATE employee SET salary
my_salary mid_pctinc WHERE empid
my_empid END IF END LOOP END
51

• - Error handling
• In Oracle PL/SQL, there are three types of
  exceptions
• predefined exceptions,
• user-defined exceptions, and
• internal exceptions.
• Predefined exceptions
• Oracle has designed several built-in
  exceptions used to handle common situations
  that may occur on the database.
• Example statement returns no data.
• When a statement expecting one piece of data
  receives more than one piece of data.

52
- Error handling Predefined exceptions Some of
the predefined cursors are represented using the
following key words invalid_cursor - Occurs
when an attempt is made to close a nonopend
cursor. cursor_already_open - Occurs when an
attempt is made to open a nonclosed
cursor. dup_val_on_index - Unique or
primary-key constraint violation. No_data_foun
d - No rows were selected or changed by the SQL
operation.
53
too_many_rows - More than one row was obtained
by a single-row subsequently, or in another SQL
statement operation where Oracle was expecting
one row. zero_divide - An attempt was made to
divide by zero. rowtype_mismatch - The
datatypes of the record to which data from the
cursor is assigned are incompatible. Invalid_num
ber - An alphanumeric string was referenced as a
number. EXCEPTION WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE(No data is
found) END
54
- Error handling User-defined exceptions In
addition to predefined exceptions, there can be
created a whole host of user-defined exceptions
that handle situations that may arise in the
code. To handle exceptions, a PL/SQL code should
contain exception declaration, exception
testing, and exception handling.
55
- Error handling User-defined
exceptions DECLARE my_empid employee.empidTYP
E 59694 my_emp_record employeeROWTYPE
99000 my_salary_null EXCEPTION BEGIN SELEC
T FROM employee INTO my_emp_record WHERE
empid my_empid IF my_emp_record.salary IS
NULL THEN RAISE my_salary_null END IF
56
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(No data is found) WHEN
my_salary_null THEN DBMS_OUTPUT.PUT_LINE(Salar
y column was null for employees) END
57

• - Error handling
• Internal exceptions
• All the Oracle errors are represented by
  alphanumeric string of the form ORA-xxxx.
• Example ORA-1400.
• A predefined exception is an association of a
  name with an Oracle alphanumeric string
  representing an error.
• One can extend the list of predefined exceptions
  by associating a new name with an Oracle
  alphanumeric string representing an error, using
  pragam exception_init keywords.

58
DECLARE my_emp_record employeeROWTYPE salary
_null exception pragam exception_init
(salary_null, -1400) BEGIN my_emp_record.empid
59485 my_emp_record.lastname
RICHARD my_emp_record.firstname
JEAN-MARIE my_emp_record.salary
65000 INSERT INTO employee(empid, lastname,
firstname, salary) VALUE(my_emp_record.empid,
my_emp_record.lastname, my_emp_record.firstname
, my_emp_record.salary)
59
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT
.PUT_LINE(No data is found) WHEN salary_null
THEN DBMS_OUTPUT.PUT_LINE(Salary column was
null for employees) END