#Exceptions: An error condition during a program execution is called an exception in PL/SQL. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition.
There are two types of exceptions:
Example : for System-defined exceptions.
Let us write some simple code to illustrate the concept.
DECLARE
c_id customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
OutPut: No such customer!
Raising Exceptions:
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax of raising an exception:
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
You can use above syntax in raising Oracle standard exception or any user-defined exception.
User-defined Exceptions:PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is:
DECLARE
my-exception EXCEPTION;
Pre-defined Exceptions:PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows.
ACCESS_INTO_NULL-It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND - It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL- It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program
attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX - It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR - It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER - It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED - It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND - It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON - It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR - It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH - It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL - It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR - It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS - It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR - It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE - It is raised when an attempt is made to divide a number by zero.
#Triggers: Triggersare stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers could be defined on the table, view, schema, or database with which the event is associated.
The syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Example:
The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table.
This trigger will display the salary difference between the old values and new values:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
#Packages:
PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.
A package will have two mandatory parts:
The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.
All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.
Ex:
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
Package Body:The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the package body.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed with the following syntax:
package_name.element_name;
Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
#Collections:A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.
PL/SQL provides three collection types:
We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.
Both types of PL/SQL tables, i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.
Index-By Table
An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.
An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_type and associated values will be of element_type.
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Example:
Following example shows how to create a table to store integer values along with names and later it prints the same list of names.
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;
-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000
PL/SQL procedure successfully completed.
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field.
Example:
DECLARE
CURSOR c_customers is
select name from customers;
TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
name_list c_list;
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
#Nested Tables:
A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:
An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.
A nested table is created using the following syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.
A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.
Example:
The following examples illustrate the use of nested table:
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92
Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field.
Collection Exceptions:
The following table provides the collection exceptions and when they are raised:
Collection Exception Raised in Situations
COLLECTION_IS_NULL You try to operate on an atomically null collection.
NO_DATA_FOUND A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT A subscript is outside the allowed range.
VALUE_ERROR A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.
There are two types of exceptions:
- System-defined exceptions
- User-defined exceptions
Example : for System-defined exceptions.
Let us write some simple code to illustrate the concept.
DECLARE
c_id customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
OutPut: No such customer!
Raising Exceptions:
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax of raising an exception:
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
You can use above syntax in raising Oracle standard exception or any user-defined exception.
User-defined Exceptions:PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is:
DECLARE
my-exception EXCEPTION;
Pre-defined Exceptions:PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows.
ACCESS_INTO_NULL-It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND - It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL- It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program
attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX - It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR - It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER - It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED - It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND - It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON - It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR - It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH - It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL - It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR - It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS - It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR - It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE - It is raised when an attempt is made to divide a number by zero.
#Triggers: Triggersare stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers could be defined on the table, view, schema, or database with which the event is associated.
The syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Example:
The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table.
This trigger will display the salary difference between the old values and new values:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
#Packages:
PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.
A package will have two mandatory parts:
- Package specification
- Package body or definition
The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.
All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.
Ex:
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
Package Body:The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the package body.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed with the following syntax:
package_name.element_name;
Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
#Collections:A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.
PL/SQL provides three collection types:
- Index-by tables or Associative array
- Nested table
- Variable-size array or Varray.
We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.
Both types of PL/SQL tables, i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.
Index-By Table
An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.
An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_type and associated values will be of element_type.
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
Example:
Following example shows how to create a table to store integer values along with names and later it prints the same list of names.
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;
-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000
PL/SQL procedure successfully completed.
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field.
Example:
DECLARE
CURSOR c_customers is
select name from customers;
TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
name_list c_list;
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
#Nested Tables:
A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:
An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.
A nested table is created using the following syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.
A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.
Example:
The following examples illustrate the use of nested table:
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92
Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field.
Collection Exceptions:
The following table provides the collection exceptions and when they are raised:
Collection Exception Raised in Situations
COLLECTION_IS_NULL You try to operate on an atomically null collection.
NO_DATA_FOUND A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT A subscript is outside the allowed range.
VALUE_ERROR A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.
No comments:
Post a Comment