PL/SQL Cursor

• A cursor is a pointer that points to a result of a query.

• PL/SQL has two types of cursors:
• implicit cursors and
• explicit cursors.
Implicit cursors
• Whenever Oracle executes an SQL statement such as SELECT INTO,
INSERT, UPDATE, and DELETE, it automatically creates an implicit
cursor.
Explicit cursors
• An explicit cursor is a SELECT statement declared explicitly in the
declaration section of the current block or a package specification.
The following illustration shows the
execution cycle of an explicit cursor:
Declare a cursor
• Before using an explicit cursor, you must declare it in the declaration
section of a block or package as follows:
CURSOR cursor_name IS query;
Open a cursor
• Before start fetching rows from the cursor, you must open it. To open
a cursor, you use the following syntax:
OPEN cursor_name;
Fetch from a cursor
• The FETCH statement places the contents of the current row into
variables. The syntax of FETCH statement is as follows:
FETCH cursor_name INTO variable_list;
Closing a cursor
After fetching all rows, you need to close the cursor with the CLOSE
statement:
CLOSE cursor_name;

Explicit Cursor Attributes

A cursor has four attributes which you can reference in the following
format:
cursor_name%attribute
Where cursor_name is the name of the explicit cursor.
1) %ISOPEN
This attribute is TRUE if the cursor is open or FALSE if it is not.
2) %FOUND
This attribute has four values:
•NULL before the first fetch
•TRUE if a record was fetched successfully
•FALSE if no row is returned
•INVALID_CURSOR if the cursor is not opened
3) %NOTFOUND
This attribute has four values:
•NULL before the first fetch
•FALSE if a record was fetched successfully
•TRUE if no row is returned
•INVALID_CURSOR if the cursor is not opened
3) %ROWCOUNT
The %ROWCOUNT attribute returns the number of rows fetched from the cursor.
If the cursor is not opened, this attribute returns INVALID_CURSOR.
CREATE VIEW sales AS
SELECT customer_id,
SUM(unit_price * quantity) total,
ROUND(SUM(unit_price * quantity) * 0.05)
credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;
DECLARE FETCH c_sales INTO r_sales;
l_budget NUMBER := 1000000; EXIT WHEN c_sales -- reduce the budget for credit limit
-- cursor %NOTFOUND; l_budget := l_budget -
CURSOR c_sales IS r_sales.credit;
SELECT * FROM sales -- update credit for the current customer
ORDER BY total DESC; UPDATE
-- record customers DBMS_OUTPUT.PUT_LINE( 'Custo
SET mer id: ' ||r_sales.customer_id ||
r_sales c_sales%ROWTYPE;
credit_limit = ' Credit: ' || r_sales.credit || '
BEGIN Remaining Budget: ' || l_budget );
CASE WHEN l_budget >
r_sales.credit
-- reset credit limit of all customers -- check the budget
THEN r_sales.credit
UPDATE customers SET EXIT WHEN l_budget <= 0;
credit_limit = 0; ELSE l_budget
END END LOOP;

OPEN c_sales; WHERE

customer_id = CLOSE c_sales;
r_sales.customer_id; END;
LOOP
Creating a trigger in Oracle
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception_handling statements
END;
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception_handling statements
END;
CREATE TABLE audits (
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY
PRIMARY KEY,
table_name VARCHAR2(255),
transaction_name VARCHAR2(10),
by_user VARCHAR2(30),
transaction_date DATE
);
CREATE OR REPLACE TRIGGER customers_audit_trg
AFTER
UPDATE OR DELETE
ON customers
FOR EACH ROW
DECLARE
l_transaction VARCHAR2(10);
BEGIN
-- determine the transaction type
l_transaction := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;

-- insert a row into the audit table

INSERT INTO audits (table_name, transaction_name, by_user, transaction_date)
VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
END;
/