Question: How can I create a table from another table without copying any values from the old table?
Answer: To do this, the basic syntax is:
CREATE TABLE new_table
AS (SELECT * FROM old_table WHERE 1=2);
AS (SELECT * FROM old_table WHERE 1=2);
For example:
CREATE TABLE suppliers
AS (SELECT * FROM companies WHERE 1=2);
AS (SELECT * FROM companies WHERE 1=2);
This would create a new table called suppliersthat included all columns from the companies table, but no data from the companies table.
SQL: ALTER TABLE Statement
The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.
Renaming a table
The basic syntax for renaming a table is:
ALTER TABLE table_name
RENAME TO new_table_name;
RENAME TO new_table_name;
For example:
ALTER TABLE suppliers
RENAME TO vendors;
RENAME TO vendors;
This will rename the suppliers table to vendors.
Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD column_name column-definition;
ADD column_name column-definition;
For example:
ALTER TABLE supplier
ADD supplier_name varchar2(50);
ADD supplier_name varchar2(50);
This will add a column called supplier_name to the supplier table.
Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name | ||
ADD ( | column_1 | column-definition, |
| column_2 | column-definition, |
| ... | |
| column_n | column_definition ); |
For example:
ALTER TABLE supplier | ||
ADD ( | supplier_name | varchar2(50), |
| city | varchar2(45) ); |
This will add two columns (supplier_name and city) to the suppliertable.
Modifying column(s) in a table
Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY column_name column_type;
MODIFY column_name column_type;
For example:
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
MODIFY supplier_name varchar2(100) not null;
This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name | ||
MODIFY ( | column_1 | column_type, |
| column_2 | column_type, |
| ... | |
| column_n | column_type ); |
For example:
ALTER TABLE supplier | ||||
MODIFY ( | supplier_name | varchar2(100) | not null, | |
city | varchar2(75) | | ); |
This will modify both the supplier_name and city columns.
Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
DROP COLUMN column_name;
DROP COLUMN column_name;
For example:
ALTER TABLE supplier
DROP COLUMN supplier_name;
DROP COLUMN supplier_name;
This will drop the column called supplier_name from the table called supplier.
Rename column(s) in a table
(NEW in Oracle 9i Release 2)
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
RENAME COLUMN old_name to new_name;
For example:
ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;
RENAME COLUMN supplier_name to sname;
This will rename the column called supplier_name to sname.
Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!
Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.
CREATE TABLE departments | |||
( | department_id | number(10) | not null, |
| department_name | varchar2(50) | not null, |
| CONSTRAINT departments_pk PRIMARY KEY (department_id) | ||
); |
Solution:
The following ALTER TABLE statement would rename the departments table to depts:
ALTER TABLE departments
RENAME TO depts;
RENAME TO depts;
Practice Exercise #2:
Based on the employees table below, add a column called salary that is a number(6) datatype.
CREATE TABLE employees | |||
( | employee_number | number(10) | not null, |
| employee_name | varchar2(50) | not null, |
| department_id | number(10), | |
| CONSTRAINT employees_pk PRIMARY KEY (employee_number) | ||
); |
Solution:
The following ALTER TABLE statement would add a salary column to the employeestable:
ALTER TABLE employees
ADD salary number(6);
ADD salary number(6);
Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_namethat is a varchar2(50) datatype and one column called last_contactedthat is a date datatype.
CREATE TABLE customers | |||
( | customer_id | number(10) | not null, |
| customer_name | varchar2(50) | not null, |
| address | varchar2(50), | |
| city | varchar2(50), | |
| state | varchar2(25), | |
| zip_code | varchar2(10), | |
| CONSTRAINT customers_pk PRIMARY KEY (customer_id) | ||
); |
Solution:
The following ALTER TABLE statement would add the contact_name and last_contactedcolumns to the customers table:
ALTER TABLE customers | ||
ADD ( | contact_name | varchar2(50), |
| last_contacted | date ); |
Practice Exercise #4:
Based on the employees table below, change the employee_name column to a varchar2(75) datatype.
CREATE TABLE employees | |||
( | employee_number | number(10) | not null, |
| employee_name | varchar2(50) | not null, |
| department_id | number(10), | |
| CONSTRAINT employees_pk PRIMARY KEY (employee_number) | ||
); |
Solution:
The following ALTER TABLE statement would change the datatype for the employee_namecolumn to varchar2(75):
ALTER TABLE employees
MODIFY employee_name varchar2(75);
MODIFY employee_name varchar2(75);
Practice Exercise #5:
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.
CREATE TABLE customers | |||
( | customer_id | number(10) | not null, |
| customer_name | varchar2(50), | |
| address | varchar2(50), | |
| city | varchar2(50), | |
| state | varchar2(25), | |
| zip_code | varchar2(10), | |
| CONSTRAINT customers_pk PRIMARY KEY (customer_id) | ||
); |
Solution:
The following ALTER TABLE statement would modify the customer_name and statecolumns accordingly in the customers table:
ALTER TABLE customers | ||
MODIFY ( | customer_name | varchar2(50) not null, |
| state | varchar2(2) ); |
Practice Exercise #6:
Based on the employees table below, drop the salary column.
CREATE TABLE employees | |||
( | employee_number | number(10) | not null, |
| employee_name | varchar2(50) | not null, |
| department_id | number(10), | |
| salary | number(6), | |
| CONSTRAINT employees_pk PRIMARY KEY (employee_number) | ||
); |
Solution:
The following ALTER TABLE statement would drop the salary column from the employeestable:
ALTER TABLE employees
DROP COLUMN salary;
DROP COLUMN salary;
Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.
CREATE TABLE departments | |||
( | department_id | number(10) | not null, |
| department_name | varchar2(50) | not null, |
| CONSTRAINT departments_pk PRIMARY KEY (department_id) | ||
); |
Solution:
The following ALTER TABLE statement would rename the department_name column to dept_name in the departments table:
ALTER TABLE departments
RENAME COLUMN department_name to dept_name;
RENAME COLUMN department_name to dept_name;
SQL: Global Temporary tables
Global temporary tables are distinct within SQL sessions.
The basic syntax is:
CREATE GLOBAL TEMPORARY TABLE table_name ( ...);
For example:
CREATE GLOBAL TEMPORARY TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50) | ||
) |
This would create a global temporary table called supplier .
SQL: VIEWS
A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.
Creating a VIEW
The syntax for creating a VIEW is:
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
SELECT *
FROM sup_orders;
FROM sup_orders;
Updating a VIEW
You can update a VIEW without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';
Dropping a VIEW
The syntax for dropping a VIEW is:
DROP VIEW view_name;
For example:
DROP VIEW sup_orders;
Frequently Asked Questions
Question: Can you update the data in a view?
Answer: A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.
So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.
Question: Does the view exist if the table is dropped from the database?
Answer: Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.
If you recreate the table (that you had dropped), the view will again be fine.
Oracle/PLSQL: Case Statement
Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)
condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a conditionis found to be true, the case statement will return the result and not evaluate the conditions any further.
result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.
Note:
If no conditionis found to be true, then the case statement will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the casestatement will return NULL.
You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.
Applies To:
· Oracle 9i, Oracle 10g, Oracle 11g
The casestatement will compare each owner value, one by one.
One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as follows:
select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from all_tables;
With the ELSE clause omitted, if no condition was found to be true, the casestatement would return NULL.
For Example:
Here is an example that demonstrates how to use the case statement to compare different conditions:
select
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;
Frequently Asked Questions
Question: Can you create a casestatement that evaluates two different fields? I want to return a value based on the combinations in two different fields.
Answer: Yes, below is an example of a case statement that evaluates two different fields.
select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the case statement will return South office.
Oracle/PLSQL: Sequences (Autonumber)
In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
The syntax for a sequence is:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
For example:
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.
If you omit the MAXVALUE option, your sequence will automatically default to:
MAXVALUE 999999999999999999999999999
So you can simplify your CREATE SEQUENCE command as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.
For example:
supplier_seq.nextval
This would retrieve the next value from supplier_seq. The nextvalstatement needs to be used in an SQL statement. For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');
This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seqsequence. The supplier_name field would be set to Kraft Foods.
Frequently Asked Questions
One common question about sequences is:
Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Or you could create the same sequence with the nocache option:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.
Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.
Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.
Question: How do we set the LASTVALUE value in an Oracle Sequence?
Answer: You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.
For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.
alter sequence seq_name
increment by 124;
increment by 124;
select seq_name.nextval from dual;
alter sequence seq_name
increment by 1;
increment by 1;
Now, the next value to be served by the sequence will be 225.
Oracle/PLSQL: Set Transaction
There are three transaction control functions. These are:
1. SET TRANSACTION READ ONLY;
2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
3. SET TRANSACTION USE ROLLBACK SEGMENT name;
Oracle/PLSQL: Lock Table
The syntax for a Lock table is:
LOCK TABLE tables IN lock_mode MODE [NOWAIT];
Tables is a comma-delimited list of tables.
Lock_mode is one of:
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE.
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE.
NoWait specifies that the database should not wait for a lock to be released.
Oracle/PLSQL Topics: Cursors
A cursoris a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
We've categorized cursors into the following topics:
Cursor Examples:
Oracle/PLSQL: Declare a Cursor A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code. We'll take a look at three different syntaxes for cursors. Cursor without parameters (simplest) The basic syntax for a cursor without parameters is: CURSOR cursor_name IS SELECT_statement; For example, you could define a cursor called c1 as below. CURSOR c1 IS SELECT course_number from courses_tbl where course_name = name_in; The result set of this cursor is all course_numbers whose course_name matches the variable called name_in. Below is a function that uses this cursor. CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; CURSOR c1 IS SELECT course_number from courses_tbl where course_name = name_in; BEGIN open c1; fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; END; Cursor with parameters The basic syntax for a cursor with parameters is: CURSOR cursor_name (parameter_list) IS SELECT_statement; For example, you could define a cursor called c2 as below. CURSOR c2 (subject_id_in IN varchar2) IS SELECT course_number from courses_tbl where subject_id = subject_id_in; The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter. Cursor with return clause The basic syntax for a cursor with a return clause is: CURSOR cursor_name RETURN field%ROWTYPE IS SELECT_statement; For example, you could define a cursor called c3 as below. CURSOR c3 RETURN courses_tbl%ROWTYPE IS SELECT * from courses_tbl where subject = 'Mathematics'; The result set of this cursor is all columns from the course_tbl where the subject is Mathematics. |
Oracle/PLSQL: OPEN Statement
Once you've declared your cursor, the next step is to open the cursor.
The basic syntax to OPEN the cursor is:
OPEN cursor_name;
For example, you could open a cursor called c1 with the following command:
OPEN c1;
Below is a function that demonstrates how to use the OPEN statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;
Oracle/PLSQL: FETCH Statement
The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.
The basic syntax for a FETCH statement is:
FETCH cursor_name INTO <list of variables>;
For example, you could have a cursor defined as:
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
The command that would be used to fetch the data from this cursor is:
FETCH c1 into cnumber;
This would fetch the first course_number into the variable called cnumber;
Below is a function that demonstrates how to use the FETCH statement.
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;
Oracle/PLSQL: Cursor Attributes
While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.
Attribute | Explanation |
%ISOPEN | - Returns TRUE if the cursor is open, FALSE if the cursor is closed. |
%FOUND | - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned. |
%NOTFOUND | - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned. |
%ROWCOUNT | - Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened. |
Below is an example of how you might use the %NOTFOUND attribute.
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;
Oracle/PLSQL: SELECT FOR UPDATE Statement
The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.
The syntax for the Select For Update is:
CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];
For example, you could use the Select For Update statement as follows:
CURSOR c1
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Ofstatement.
Oracle/PLSQL: WHERE CURRENT OF Statement
If you plan on updating or deleting records that have been referenced by a Select For Updatestatement, you can use the Where Current Of statement.
The syntax for the Where Current Of statement is either:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
WHERE CURRENT OF cursor_name;
The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
Updating using the WHERE CURRENT OF Statement
Here is an example where we are updating records using the Where Current OfStatement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
close c1;
RETURN cnumber;
END;
Deleting using the WHERE CURRENT OF Statement
Here is an example where we are deleting records using the Where Current OfStatement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;
DELETE FROM courses_tbl
WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
close c1;
RETURN cnumber;
END;
Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor
Question: In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine" table.
I've created an HTML Form where the user can enter any combination of three values to retrieve results from the "wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user enters.
Example:
parameter_1= "Chianti"
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to initialize this parameter to get all rows for column3?
SELECT * FROM wine
WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;.
WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;.
The output of my stored procedure must be a cursor.
Answer: To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.
Let's take a look at how we can do this. We've divided this process into 3 steps.
Step 1 - Table Definition
First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.
create table wine
( col1 varchar2(40),
col2 varchar2(40),
col3 varchar2(40)
);
( col1 varchar2(40),
col2 varchar2(40),
col3 varchar2(40)
);
We've made this table definition very simple, for demonstration purposes.
Step 2 - Create package
Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we can use a cursor as an outputparameter in our stored procedure.
create or replace PACKAGE winepkg
IS
/* Define the REF CURSOR type. */
TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;
IS
/* Define the REF CURSOR type. */
TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;
This cursor will accept all fields from the "wine" table.
Step 3 - Create stored procedure
Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.
The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).
create or replace procedure find_wine2
(col1_in in varchar2,
col2_in in varchar2,
col3_in in varchar2,
c1 out winepkg.wine_type)
as
(col1_in in varchar2,
col2_in in varchar2,
col3_in in varchar2,
c1 out winepkg.wine_type)
as
BEGIN
/* all columns were entered */
IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col2 = col2_in
and wine.col3 = col3_in;
IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col2 = col2_in
and wine.col3 = col3_in;
/* col1 and col2 were entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col2 = col2_in;
ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col2 = col2_in;
/* col1 and col3 were entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col3 = col3_in;
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in
and wine.col3 = col3_in;
/* col2 and col3 where entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col2 = col2_in
and wine.col3 = col3_in;
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col2 = col2_in
and wine.col3 = col3_in;
/* col1 was entered */
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in;
ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col1 = col1_in;
/* col2 was entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col2 = col2_in;
ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col2 = col2_in;
/* col3 was entered */
ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col3 = col3_in;
ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
THEN
OPEN c1 FOR
select *
from wine
where wine.col3 = col3_in;
END IF;
END find_wine2;
END find_wine2;
Oracle/PLSQL: Cursor within a cursor
Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?
Answer: Below is an example of how to declare a cursor within a cursor.
In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.
create or replace procedure MULTIPLE_CURSORS_PROC is
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);
/* First cursor */
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';
/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;
begin
-- Open first cursor
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;
open get_columns;
loop
fetch get_columns into v_column_name;
loop
fetch get_columns into v_column_name;
end loop;
close get_columns;
close get_columns;
end loop;
close get_tables;
close get_tables;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;
The trick to declaring a cursor within a cursor is that you need to continue to openand close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.
Oracle/PLSQL: Cursor with variable in an "IN CLAUSE"
Question: I'm trying to use a variable in an IN CLAUSE.
Assumptions & declarations:
1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN Clause
4. Execute the Query and Return the Output as REF Cursor
Something similar to the following:
Create or Replace Function func_name (inNumbers in Varchar2)
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Begin
Open out_cursor
For Select * from Table_name
where column_name in (inNumbers);
Open out_cursor
For Select * from Table_name
where column_name in (inNumbers);
Return out_cursor;
End;
End;
I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?
Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:
Option #1
Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:
Create or Replace Function func_name
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
v1 varchar(2);
v2 varchar(2);
v3 varchar(2);
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
v1 varchar(2);
v2 varchar(2);
v3 varchar(2);
Begin
v1 := '1';
v2 := '2';
v3 := '3';
v2 := '2';
v3 := '3';
Open out_cursor
For Select * from Table_name
where column_name in (v1, v2, v3);
For Select * from Table_name
where column_name in (v1, v2, v3);
Return out_cursor;
End;
Option #2
You could try storing your values in a table. Then use a sub-select to retrieve the values.
For example:
For example:
Create or Replace Function func_name
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Return PackageName.ref_cursor
As
out_cursor PackageName.Ref_cursor;
Begin
Open out_cursor
For Select * from Table_name
where column_name in (select values from list_table);
For Select * from Table_name
where column_name in (select values from list_table);
Return out_cursor;
End;
In this example, we've stored our list in a table called list_table.
Oracle/PLSQL: Primary Keys
What is a primary key?
A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.
Note:
In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a primary key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);
For example:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) | |||
); |
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) | |||
); |
Using an ALTER TABLE statement
The syntax for creating a primary key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
For example:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);
Drop a Primary Key
The syntax for dropping a primary key is:
ALTER TABLE table_name
drop CONSTRAINT constraint_name;
drop CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
drop CONSTRAINT supplier_pk;
drop CONSTRAINT supplier_pk;
In this example, we're dropping a primary key on the supplier table called supplier_pk.
Disable a Primary Key
The syntax for disabling a primary key is:
ALTER TABLE table_name
disable CONSTRAINT constraint_name;
disable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
disable CONSTRAINT supplier_pk;
disable CONSTRAINT supplier_pk;
In this example, we're disabling a primary key on the supplier table called supplier_pk.
Enable a Primary Key
The syntax for enabling a primary key is:
ALTER TABLE table_name
enable CONSTRAINT constraint_name;
enable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
enable CONSTRAINT supplier_pk;
enable CONSTRAINT supplier_pk;
In this example, we're enabling a primary key on the supplier table called supplier_pk.
Oracle/PLSQL: Foreign Keys
What is a foreign key?
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary keyin the parent table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a foreign key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
For example:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) | |||
); |
CREATE TABLE products | |||
( | product_id | numeric(10) | not null, |
supplier_id | numeric(10) | not null, | |
CONSTRAINT fk_supplier | |||
FOREIGN KEY (supplier_id) | |||
REFERENCES supplier(supplier_id) | |||
); |
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) | |||
); |
CREATE TABLE products | |||
( | product_id | numeric(10) | not null, |
supplier_id | numeric(10) | not null, | |
supplier_name | varchar2(50) | not null, | |
CONSTRAINT fk_supplier_comp | |||
FOREIGN KEY (supplier_id, supplier_name) | |||
REFERENCES supplier(supplier_id, supplier_name) | |||
); |
In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
For example:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
Oracle/PLSQL: Foreign Keys with cascade delete
What is a foreign key?
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary keyin the parent table.
A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.
A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to be cascade deleted when a record in the supplier table is deleted, based on supplier_id and supplier_name.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;
For example:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;
In this example, we've created a foreign key (with a cascade delete) called fk_supplierthat references the supplier table based on the supplier_id field.
We could also create a foreign key (with a cascade delete) with more than one field as in the example below:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;
Oracle/PLSQL: Foreign Keys with "set null on delete"
What is a foreign key?
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary keyin the parent table.
A foreign key with a "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will notbe deleted.
A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
The delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to have the supplier_id and supplier_name fields set to null when a record in the supplier table is deleted, based on supplier_id and supplier_name.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL;
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL;
For example:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL;
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL;
In this example, we've created a foreign key "with a set null on delete" called fk_supplier that references the supplier table based on the supplier_id field.
We could also create a foreign key "with a set null on delete" with more than one field as in the example below:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE SET NULL;
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE SET NULL;
Oracle/PLSQL: Unique Constraints
What is a unique constraint?
A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.
Note:
In Oracle, a unique constraint can not contain more than 32 columns.
A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
What is the difference between a unique constraint and a primary key?
Primary Key | Unique Constraint |
None of the fields that are part of the primary key can contain a null value. | Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique. |
Oracle does not permit you to create both a primary key and unique constraint with the same columns.
Using a CREATE TABLE statement
The syntax for creating a unique constraint using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);
For example:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_unique UNIQUE (supplier_id) | |||
); |
In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.
We could also create a unique constraint with more than one field as in the example below:
CREATE TABLE supplier | |||
( | supplier_id | numeric(10) | not null, |
supplier_name | varchar2(50) | not null, | |
contact_name | varchar2(50), | ||
CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name) | |||
); |
Using an ALTER TABLE statement
The syntax for creating a unique constraint in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Oracle/PLSQL: Check Constraints
What is a check constraint?
A check constraint allows you to specify a condition on each row in a table.
Note:
· A check constraint can NOT be defined on a VIEW.
· The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
· A check constraint can NOT include a SUBQUERY.
A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a check constraint using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.
For example:
CREATE TABLE suppliers | |||
( | supplier_id | numeric(4), | |
supplier_name | varchar2(50), | ||
CONSTRAINT check_supplier_id | |||
CHECK (supplier_id BETWEEN 100 and 9999) | |||
); |
In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.
CREATE TABLE suppliers | |||
( | supplier_id | numeric(4), | |
supplier_name | varchar2(50), | ||
CONSTRAINT check_supplier_name | |||
CHECK (supplier_name = upper(supplier_name)) | |||
); |
In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.
Using an ALTER TABLE statement
The syntax for creating a check constraint in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.
Oracle/PLSQL: Indexes
What is an Index?
An indexis a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
Create an Index
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.
For example:
CREATE INDEX supplier_idx
ON supplier (supplier_name);
ON supplier (supplier_name);
In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.
We could also create an index with more than one field as in the example below:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city);
ON supplier (supplier_name, city);
We could also choose to collect statistics upon creation of the index as follows:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;
ON supplier (supplier_name, city)
COMPUTE STATISTICS;
Create a Function-Based Index
In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
The syntax for creating a function-based index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
For example:
CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));
ON supplier (UPPER(supplier_name));
In this example, we've created an index based on the uppercase evaluation of the supplier_namefield.
However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:
SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);
Rename an Index
The syntax for renaming an index is:
ALTER INDEX index_name
RENAME TO new_index_name;
RENAME TO new_index_name;
For example:
ALTER INDEX supplier_idx
RENAME TO supplier_index_name;
RENAME TO supplier_index_name;
In this example, we're renaming the index called supplier_idx to supplier_index_name.
Collect Statistics on an Index
If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.
The syntax for collecting statistics on an index is:
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;
REBUILD COMPUTE STATISTICS;
For example:
ALTER INDEX supplier_idx
REBUILD COMPUTE STATISTICS;
REBUILD COMPUTE STATISTICS;
In this example, we're collecting statistics for the index called supplier_idx.
Drop an Index
The syntax for dropping an index is:
DROP INDEX index_name;
For example:
DROP INDEX supplier_idx;
In this example, we're dropping an index called supplier_idx.
Oracle/PLSQL: Creating Functions
In Oracle, you can create your own functions.
The syntax for a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
The following is a simple example of a function:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
cursor c1 is
select course_number
from courses_tbl
where course_name = name_in;
BEGIN
select course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.
You could then reference your new function in an SQL statement as follows:
select course_name, FindCourse(course_name) as course_id
from courses
where subject = 'Mathematics';
from courses
where subject = 'Mathematics';
Oracle/PLSQL: Creating Procedures
In Oracle, you can create your own procedures.
The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
The following is a simple example of a procedure:
CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
IS
cnumber number;
( name_in IN varchar2 )
IS
cnumber number;
cursor c1 is
select course_number
from courses_tbl
where course_name = name_in;
BEGIN
select course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
insert into student_courses
( course_name,
course_number)
values ( name_in,
cnumber );
( course_name,
course_number)
values ( name_in,
cnumber );
commit;
close c1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
This procedure is called UpdateCourse. It has one parameter called name_in. The procedure will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.
Oracle/PLSQL Topics: Creating Triggers
Insert Triggers:
Update Triggers:
Delete Triggers:
Drop Triggers:
Disable/Enable Triggers:
Oracle/PLSQL: BEFORE INSERT Trigger
A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax for an BEFORE INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create a BEFORE trigger on a view.
· You can update the :NEW values.
· You can not update the :OLD values.
Oracle/PLSQL: AFTER INSERT Trigger
An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.
The syntax for an AFTER INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create an AFTER trigger on a view.
· You can not update the :NEW values.
· You can not update the :OLD values.
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2) | |
); |
We could then create an AFTER INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
END;
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2), | |
create_date | date, | |
created_by | varchar2(10) | |
); |
We could then create a BEFORE INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
SELECT user INTO v_username
FROM dual;
-- Update create_date field to current system date
:new.create_date := sysdate;
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
:new.created_by := v_username;
END;
Oracle/PLSQL: BEFORE UPDATE Trigger
A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.
The syntax for an BEFORE UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create a BEFORE trigger on a view.
· You can update the :NEW values.
· You can not update the :OLD values.
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2), | |
updated_date | date, | |
updated_by | varchar2(10) | |
); |
We could then create a BEFORE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE on the table
SELECT user INTO v_username
FROM dual;
SELECT user INTO v_username
FROM dual;
-- Update updated_date field to current system date
:new.updated_date := sysdate;
:new.updated_date := sysdate;
-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;
:new.updated_by := v_username;
END;
Oracle/PLSQL: AFTER UPDATE Trigger
An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create an AFTER trigger on a view.
· You can not update the :NEW values.
· You can not update the :OLD values.
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2) | |
); |
We could then create an AFTER UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;
Oracle/PLSQL: BEFORE DELETE Trigger
A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax for an BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create a BEFORE trigger on a view.
· You can update the :NEW values.
· You can not update the :OLD values.
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2) | |
); |
We could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by )
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by )
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;
Oracle/PLSQL: AFTER DELETE Trigger
An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.
The syntax for an AFTER DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
AFTER DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
· You can not create an AFTER trigger on a view.
· You can not update the :NEW values.
· You can not update the :OLD values.
For example:
If you had a table created as follows:
CREATE TABLE orders | ||
( | order_id | number(5), |
quantity | number(4), | |
cost_per_item | number(6,2), | |
total_cost | number(8,2) | |
); |
We could then create an DELETE UPDATE trigger as follows:
CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
ON orders
FOR EACH ROW
AFTER DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by)
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by)
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;
Oracle/PLSQL: Disable a Trigger
The syntax for a disabling a Trigger is:
ALTER TRIGGER trigger_name DISABLE;
Oracle/PLSQL: Disable all Triggers on a table
The syntax for a disabling all Triggers on a table is:
ALTER TABLE table_name DISABLE ALL TRIGGERS;
Oracle/PLSQL: Named System Exceptions
What is a named system exception?
Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Oracle has a standard set of exceptions already named as follows:
Oracle Exception Name | Oracle Error | Explanation |
DUP_VAL_ON_INDEX | ORA-00001 | You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. |
TIMEOUT_ON_RESOURCE | ORA-00051 | You were waiting for a resource and you timed out. |
TRANSACTION_BACKED_OUT | ORA-00061 | The remote portion of a transaction has rolled back. |
INVALID_CURSOR | ORA-01001 | You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. |
NOT_LOGGED_ON | ORA-01012 | You tried to execute a call to Oracle before logging in. |
LOGIN_DENIED | ORA-01017 | You tried to log into Oracle with an invalid username/password combination. |
NO_DATA_FOUND | ORA-01403 | You tried one of the following:
|
TOO_MANY_ROWS | ORA-01422 | You tried to execute a SELECT INTO statement and more than one row was returned. |
ZERO_DIVIDE | ORA-01476 | You tried to divide a number by zero. |
INVALID_NUMBER | ORA-01722 | You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful. |
STORAGE_ERROR | ORA-06500 | You ran out of memory or memory was corrupted. |
PROGRAM_ERROR | ORA-06501 | This is a generic "Contact Oracle support" message because an internal problem was encountered. |
VALUE_ERROR | ORA-06502 | You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. |
CURSOR_ALREADY_OPEN | ORA-06511 | You tried to open a cursor that is already open. |
The syntax for the Named System Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [procedure_name];
The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [function_name];
Here is an example of a procedure that uses a Named System Exception:
CREATE OR REPLACE PROCEDURE add_new_supplier
(supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS
(supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
IS
BEGIN
INSERT INTO suppliers (supplier_id, supplier_name )
VALUES ( supplier_id_in, supplier_name_in );
INSERT INTO suppliers (supplier_id, supplier_name )
VALUES ( supplier_id_in, supplier_name_in );
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting a supplier.');
raise_application_error (-20002,'An error has occurred inserting a supplier.');
END;
In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clauseto trap all remaining exceptions.
Oracle/PLSQL: Named Programmer-Defined Exceptions
What is a named programmer-defined exception?
Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.
The syntax for the Named Programmer-Defined Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
[ (parameter [,parameter]) ]
IS
[declaration_section]
exception_name EXCEPTION;
BEGIN
executable_section
executable_section
RAISE exception_name ;
EXCEPTION
WHEN exception_name THEN
[statements]
WHEN exception_name THEN
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [procedure_name];
The syntax for the Named Programmer-Defined Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
exception_name EXCEPTION;
BEGIN
executable_section
executable_section
RAISE exception_name ;
EXCEPTION
WHEN exception_name THEN
[statements]
WHEN exception_name THEN
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [function_name];
Here is an example of a procedure that uses a Named Programmer-Defined Exception:
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
raise_application_error (-20002,'An error has occurred inserting an order.');
END;
In this example, we have declared a Named Programmer-Defined Exception called no_salesin our declaration statement with the following code:
no_sales EXCEPTION;
We've then raised the exception in the executable section of the code:
IF sales_in = 0 THEN
RAISE no_sales;
RAISE no_sales;
Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception called no_sales.
Finally, we tell our procedure what to do when the no_sales exception is encountered by including code in the WHEN clause:
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
raise_application_error (-20001,'You must have sales in order to submit the order.');
We are also using the WHEN OTHERS clauseto trap all remaining exceptions:
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
raise_application_error (-20002,'An error has occurred inserting an order.');
Oracle/PLSQL: WHEN OTHERS Clause
What is a WHEN OTHERS clause?
The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.
The syntax for the WHEN OTHERS clause in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [procedure_name];
The syntax for the WHEN OTHERS clause in a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [function_name];
Here is an example of a procedure that uses a WHEN OTHERS clause:
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
raise_application_error (-20001,'You must have sales in order to submit the order.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
raise_application_error (-20002,'An error has occurred inserting an order.');
END;
In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEXor a no_sales, it will be trapped by the WHEN OTHERS clause.
Frequently Asked Questions
Question: Is there any way to get the ORA error number (and/or description) for the errors that will fall into OTHERS?
Something like:
WHEN OTHERS THEN
'Error number ' & Err.Number& ' has happen.'
'Error number ' & Err.Number& ' has happen.'
Answer: Yes, you can use SQLCODE functionto retrieve the error number and SQLERRM functionto retrieve the error message.
For example, you could raise the error as follows:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;
VALUES (err_code, err_msg);
END;
Oracle/PLSQL: SQLCODE Function
What does the SQLCODE Function do?
The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [procedure_name];
You could use the SQLCODE function to raise an error as follows:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;
VALUES (err_code, err_msg);
END;
Learn more about the SQLERRM Function.
Oracle/PLSQL: SQLERRM Function
What does the SQLERRM Function do?
The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
[statements]
WHEN exception_name_n THEN
[statements]
[statements]
WHEN OTHERS THEN
[statements]
[statements]
END [procedure_name];
You could use the SQLERRM function to raise an error as follows:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;
VALUES (err_code, err_msg);
END;
Oracle/PLSQL Topics: Oracle Error Messages
The following is a listing of Oracle Error Messages:
00001-00899
00900-00999
01000-01399
01400-01499
01500-01999
02000-06499
06500-09999
10000 - 12999
Oracle/PLSQL: Grant/Revoke Privileges
Grant Privileges on Tables
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.
Privilege | Description |
Select | Ability to query the table with a select statement. |
Insert | Ability to add new rows to the table with the insert statement. |
Update | Ability to update rows in the table with the update statement. |
Delete | Ability to delete rows from the table with the delete statement. |
References | Ability to create a constraint that refers to the table. |
Alter | Ability to change the table definition with the alter table statement. |
Index | Ability to create an index on the table with the create index statement. |
The syntax for granting privileges on a table is:
grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
grant select, insert, update, delete on suppliers to smithj;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to smithj;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on suppliers from public;
Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege | Description |
Execute | Ability to compile the function/procedure. Ability to execute the function/procedure directly. |
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to user;
For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:
grant execute on Find_Value to smithj;
If you wanted to grant all users the ability to execute this function, you would execute the following:
grant execute on Find_Value to public;
Revoke Privileges on Functions/Procedures
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from user;
If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:
revoke execute on Find_Value from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke execute on Find_Value from public;
Oracle/PLSQL: Roles
A roleis a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.
Creating a Role
To create a role, you must have CREATE ROLE system privileges.
The syntax for creating a role is:
CREATE ROLE role_name
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;
Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.
The role_namephrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.
The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.
The IDENTIFIEDphrase means that a user must be authorized by a specified method before the role is enabled.
The BY password phrase means that a user must supply a password to enable the role.
The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.
The EXTERNALLYphrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.
The GLOBALLYphrase means that a user must be authorized by the enterprise directory service to enable the role.
For example:
CREATE ROLE test_role;
This first example creates a role called test_role.
CREATE ROLE test_role
IDENTIFIED BY test123;
IDENTIFIED BY test123;
This second example creates the same role called test_role, but now it is password protected with the password of test123.
Grant Privileges (on Tables) to Roles
You can grant roles various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.
Privilege | Description |
Select | Ability to query the table with a select statement. |
Insert | Ability to add new rows to the table with the insert statement. |
Update | Ability to update rows in the table with the update statement. |
Delete | Ability to delete rows from the table with the delete statement. |
References | Ability to create a constraint that refers to the table. |
Alter | Ability to change the table definition with the alter table statement. |
Index | Ability to create an index on the table with the create index statement. |
The syntax for granting privileges on a table is:
grant privileges on object to role_name
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:
grant select, insert, update, delete on suppliers to test_role;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on suppliers to test_role;
Revoke Privileges (on Tables) to Roles
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;
For example, if you wanted to revoke delete privileges on a table called suppliers from a role named test_role, you would execute the following statement:
revoke delete on suppliers from test_role;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on suppliers from test_role;
Grant Privileges (on Functions/Procedures) to Roles
When dealing with functions and procedures, you can grant roles the ability to execute these functions and procedures. The Execute privilege is explained below:
Privilege | Description |
Execute | Ability to compile the function/procedure. Ability to execute the function/procedure directly. |
The syntax for granting execute privileges on a function/procedure is:
grant execute on object to role_name;
For example, if you had a function called Find_Value and you wanted to grant execute access to the role named test_role, you would execute the following statement:
grant execute on Find_Value to test_role;
Revoke Privileges (on Functions/Procedures) to Roles
Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a role. To do this, you can execute a revoke command.
The syntax for the revoking privileges on a function or procedure is:
revoke execute on object from role_name;
If you wanted to revoke execute privileges on a function called Find_Value from a role named test_role, you would execute the following statement:
revoke execute on Find_Value from test_role;
Granting the Role to a User
Now, that you've created the role and assigned the privileges to the role, you'll need to grant the role to specific users.
The syntax to grant a role to a user is:
GRANT role_name TO user_name;
For example:
GRANT test_role to smithj;
This example would grant the role called test_role to the user named smithj.
The SET ROLE statement
The SET ROLE statement allows you to enable or disable a role for a current session.
When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.
The syntax for the SET ROLE statement is:
SET ROLE
( role_name [ IDENTIFIED BY password ]
| ALL [EXCEPT role1, role2, ... ]
| NONE );
( role_name [ IDENTIFIED BY password ]
| ALL [EXCEPT role1, role2, ... ]
| NONE );
The role_namephrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALLphrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONEphrase disables all roles for the current session. (including all default roles)
For example:
SET ROLE test_role IDENTIFIED BY test123;
This example would enable the role called test_role with a password of test123.
Setting a role as DEFAULT Role
A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.
The syntax for setting a role as a DEFAULT role is:
ALTER USER user_name
DEFAULT ROLE
( role_name
| ALL [EXCEPT role1, role2, ... ]
| NONE );
DEFAULT ROLE
( role_name
| ALL [EXCEPT role1, role2, ... ]
| NONE );
The user_namephrase is the name of the user whose role you are setting as DEFAULT.
The role_namephrase is the name of the role that you wish to set as DEFAULT.
The ALLphrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONEphrase disables all roles as DEFAULT.
For example:
ALTER USER smithj
DEFAULT ROLE
test_role;
DEFAULT ROLE
test_role;
This example would set the role called test_role as a DEFAULT role for the user named smithj.
ALTER USER smithj
DEFAULT ROLE
ALL;
DEFAULT ROLE
ALL;
This example would set all roles assigned to smithj as DEFAULT.
ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;
DEFAULT ROLE
ALL EXCEPT test_role;
This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.
Dropping a Role
It is also possible to drop a role. The syntax for dropping a role is:
DROP ROLE role_name;
For example:
DROP ROLE test_role;
This drop statement would drop the role called test_role that we defined earlier.
Oracle/PLSQL: Change a user's password in Oracle
Question: How do I change the password for a user in Oracle?
Answer: To change a user's password in Oracle, you need to execute the alter user command.
The syntax for changing a password is:
alter user user_name identified by new_password;
user_name is the user whose password you wish to change.
new_password is the new password to assign.
For example:
If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:
alter user smithj identified by autumn;
Oracle/PLSQL: Synonyms
A synonymis an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
for [schema .] object_name [@ dblink];
The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
The publicphrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.
The schemaphrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.
The object_namephrase is the name of the object for which you are creating the synonym. It can be one of the following:
table | package |
view | materialized view |
sequence | java class schema object |
stored procedure | user-defined object |
function | synonym |
For example:
create public synonym suppliers
for app.suppliers;
for app.suppliers;
This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called supplierswithout having to prefix the table name with the schema named app. For example:
select * from suppliers;
If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:
create or replace public synonym suppliers
for app.suppliers;
for app.suppliers;
Dropping a synonym
It is also possible to drop a synonym. The syntax for dropping a synonym is:
drop [public] synonym [schema .] synonym_name [force];
The publicphrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.
The forcephrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.
For example:
drop public synonym suppliers;
This drop statement would drop the synonym called suppliers that we defined earlier.
No comments:
Post a Comment