Oracle

Oracle Exception

The Oracle database contains the following types of exceptions:

Internally defined are automatically raises by the runtime system. An internally defined exception always has an error code, but does not have a name.

Predefined are internally defined exceptions that PL/SQL has given a name. For example, ORA-01403 (PL/SQL: NO_DATA_FOUND).

The most used predefined exceptions are:

NO_DATA_FOUND
TOO_MANY_ROWS
OTHERS
DUP_VAL_ON_INDEX
INVALID_NUMBER
VALUE_ERROR

User-defined are exceptions declared by user in the declarative part of any PL/SQL anonymous block, subprogram, or package.

Oracle Predefined Exception

declare
   v_name varchar2(255);
begin
   select name into v_name 
   from customers
   where id in (3,4);
exception
  when no_data_found then
  dbms_output.put_line('error: '||sqlerrm);
  when too_many_rows then
  dbms_output.put_line('error: '||sqlerrm);  
end;

Oracle User-defined Exception

declare
	v_count number;
	exception_user_defined exception;
begin

	select count(*) 
	into v_count 
	from customers
	where id = 341;
   
	if v_count=0 then
	raise exception_user_defined;
	end if;
	
	dbms_output.put_line('The count > 0');
	
exception
	when exception_user_defined then
		dbms_output.put_line('The count = 0');
end;

Oracle Cursors

The Oracle database contains two types of cursors:

Implicit cursors are automatically constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement.

The implicit cursor attributes are:

SQL%ISOPEN
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%BULK_ROWCOUNT
SQL%BULK_EXCEPTIONS

Explicit cursors are created by the developer user. The explicit cursor consists of the following parts:

Declare a Cursor – declare and define the explicit cursor
Open cursor – open cursor with the OPEN statement
FETCH statement – fetch rows from the result set
CLOSE statement – close the explicit cursor

Implicit cursor example

DECLARE
   v_implicit_cursor number;
BEGIN
   UPDATE customers 
   SET address = 'Address 2Bcd' 
   WHERE id=2;
   
   IF SQL%NOTFOUND THEN
      dbms_output.put_line('No customer found');
   ELSIF SQL%FOUND THEN
      v_implicit_cursor := SQL%ROWCOUNT;
      dbms_output.put_line('Rows = ' || v_implicit_cursor);
   END IF; 
END;

Explicit cursor example

DECLARE
	CURSOR c_cursor IS 
		SELECT name, address 
		FROM customers
		WHERE id in (3,4);
	v_name customers.name%type;
	v_address customers.address%type;
BEGIN
	OPEN c_cursor;
	LOOP
		FETCH c_cursor into v_name, v_address;
		dbms_output.put_line(v_name||' / '||v_address);      
		EXIT WHEN c_cursor%NOTFOUND;
	END LOOP;
	CLOSE c_cursor;
END;

Oracle Tutorial – Learn how to create and drop objects on Oracle database.
Learn how to use DML Statements, DDL Statements, Conditional Statements, SQL queries, joins, data types.
The main objects in Oracle database are tables, procedures, functions, triggers, cursors, views, packages, constraints, collections, records, indexes, exceptions.

Oracle tutorial

Oracle Create Trigger

To create a trigger uses the CREATE statement.
A Trigger is a stored PL/SQL block associated with a table, a schema, or the database.
The events that fire a trigger are:
DML statements
DDL statements
System events such as startup, shutdown, and error messages
User events such as logon and logoff

Create Trigger syntax

CREATE OR REPLACE TRIGGER trg_name 
  {BEFORE | AFTER | INSTEAD OF} 
  {INSERT | UPDATE | DELETE} 
    [OF column_name] 
ON table_name 
 [REFERENCING OLD AS old NEW AS new] 
[FOR EACH ROW] 
WHEN condition
DECLARE
	-- variables 
BEGIN
	-- DML statements 
	-- DDL statements 
EXCEPTION
   -- raise message
END;

Create Trigger example

CREATE TRIGGER cust_log
      BEFORE INSERT OR UPDATE OR DELETE ON customers
      FOR EACH ROW
BEGIN
INSERT INTO customers_log(ID, NAME, ADDRESS) 
VALUES (:NEW.id,:NEW.name,:NEW.address);
END;
/

Oracle Create View

To create a view uses the CREATE VIEW statement.
Oracle view is a logical table based on one or more tables or views. A view contains no data itself.

Create View syntax

CREATE OR REPLACE VIEW view_name AS
	select * from table_name;

CREATE OR REPLACE VIEW view_name AS
	select * from sql_query;

Create View example

CREATE OR REPLACE VIEW cust_view AS
	select * from customers;
	
CREATE OR REPLACE VIEW cust_view2 AS
	select c.* 
	from customers c, old_customers oc
	where c.id=oc.id;