Today’s Lecture Contents
Cursors
Triggers
Assertions
RDBMS
CURSORS
• A cursor is a private set of records
• An Oracle Cursor = VB recordset = JDBC
ResultSet
• Implicit cursors are created for every
query made in Oracle
• Explicit cursors can be declared by a
programmer within PL/SQL.
Cursor
Attributes
• cursorname%ROWCOUNT Rows returned so far
• cursorname%FOUND One or more rows retrieved
• cursorname%NOTFOUND No rows found
• Cursorname%ISOPEN Is the cursor open
Explicit Cursor
Control
• Declare the cursor
• Open the cursor
• Fetch a row
• Test for end of cursor
• Close the cursor
Note: there is a FOR LOOP available with an implicit fetch
Sample Cursor
Program
DECLARE
CURSOR students_cursor IS
SELECT * from
students;
v_student students_cursor
%rowtype;
/* instead we could do v_student students%rowtype */
BEGIN
DBMS_OUTPUT.PUT_LINE
('******************'); OPEN
students_cursor;
FETCH students_cursor into
v_student; WHILE students_cursor
%found LOOP
DBMS_OUTPUT.PUT_LINE (v_student.last);
DBMS_OUTPUT.PUT_LINE (v_student.major);
DBMS_OUTPUT.PUT_LINE ('******************');
FETCH students_cursor into v_student;
Triggers
1. PL/SQL code executed automatically in response to a database
event, typically DML.
2. Like other stored procedures, triggers are stored
in the database. Often used to:
– enforce complex constraints, especially multi-table constraints. Financial posting
is an example of this.
– Trigger related actions
– implement auditing “logs”
– pop a sequence when creating token keys
3. Triggers do not issue transaction control statements (such as
commit). Triggers are part of the SQL transaction that
invoked them.
4. USER_TRIGGERS provides a data dictionary view of triggers.
Triggers Syntax
CREATE OR REPLACE TRIGGER <trigger_name>
[BEFORE/AFTER][DELETE/INSERT/UPDATE of <column_name |, column_name… |>
ON <table_name>
|FOR EACH ROW|
|WHEN <triggering condition>|
|DECLARE|
BEGIN
trigger
statem
ents
…………
END;
To delete a trigger use:
DROP TRIGGER <trigger_name>;
Relational Database
What’s a database?
A collection of logically-related
information stored in a consistent fashion
Phone book
Bank records (checking statements, etc)
Library card catalog
Soccer team roster
The storage format typically appears to
users as some kind of tabular list
(table, spreadsheet)
What Does a Database Do?
Stores information in a highly organized manner
Manipulates information in various ways, some of
which are not available in other applications or
are easier to accomplish with a database
Models some real world process or activity through
electronic means
Often called modeling a business process
Often replicates the process only in appearance or end
result
Databases and the Systems which
manage them
Modern electronic databases are created and managed
through means of RDBMS: Relational DataBase
Management Systems
An individual data storage structure created with an
RDBMS is typically called a “database”
A database and its attendant views, reports, and
procedures is called an “application”
Relational Database Management Systems
Low-end, proprietary, specific purpose
Email: Outlook, Eudora, Mulberry
Bibliographic: Ref. Mgr., EndNote, ProCite
Mid-level
Microsoft Access, Lotus Approach, Borland’s
Paradox
More or less total control of design allows
custom builds
High-end
Oracle, Microsoft SQL Server, Sybase, IBM DB2
Professional level DBs: Banks, e-commerce,
secure
Amazon.com, Ebay.com, Yahoo.com
QUESTIONS…..?
