ORACLE/PLSQL: COMMENTS WITHIN SQL

This Oracle tutorial explains how to use comments within your SQL statements in Oracle/PLSQL with syntax and examples.

DESCRIPTION

Did you know that you can place comments within your SQL statements in Oracle? These comments can appear on a single line or span across multiple lines. Let's look at how to do this.

SYNTAX

There are two syntaxes that you can use to create a comment within your SQL statement in Oracle/PLSQL.

Syntax Using -- symbol

The syntax for creating a SQL comment in Oracle using -- symbol is:

-- comment goes here

In Oracle, a comment started with -- symbol must be at the end of a line in your SQL statement with a line break after it. This method of commenting can only span a single line within your SQL and must be at the end of the line.

Syntax Using /* and */ symbols

The syntax for creating a SQL comment in Oracle using /* and */ symbols is:

/* comment goes here */

In Oracle, a comment that starts with /* symbol and ends with */ and can be anywhere in your SQL statement. This method of commenting can span several lines within your SQL.

EXAMPLE - COMMENT ON A SINGLE LINE

You can create a SQL comment on a single line in your SQL statement in Oracle/PLSQL.

Let's look at a SQL comment example that shows a SQL comment on its own line:

SELECT suppliers.supplier_id
/* Author: TechOnTheNet.com */
FROM suppliers;

Here is a SQL comment that appears in the middle of the line:

SELECT  /* Author: TechOnTheNet.com */  suppliers.supplier_id
FROM suppliers;

Here is a SQL comment that appears at the end of the line:

SELECT suppliers.supplier_id  /* Author: TechOnTheNet.com */
FROM suppliers;

SELECT suppliers.supplier_id  -- Author: TechOnTheNet.com
FROM suppliers;

EXAMPLE - COMMENT ON MULTIPLE LINES

In Oracle, you can create a SQL comment that spans multiple lines in your SQL statement. For example:

SELECT suppliers.supplier_id
/*
 * Author: TechOnTheNet.com
 * Purpose: To show a comment that spans multiple lines in your SQL statement.
 */
FROM suppliers;

This SQL comment spans across multiple lines in Oracle - in this example, it spans across 4 lines.

In Oracle, you can also create a SQL comment that spans multiple lines using this syntax:

SELECT suppliers.supplier_id /* Author: TechOnTheNet.com
Purpose: To show a comment that spans multiple lines in your SQL statement. */
FROM suppliers;

Oracle/PLSQL will assume that everything after the /* symbol is a comment until it reaches the */ symbol, even if it spans multiple lines within the SQL statement. So in this example, the SQL comment will span across 2 lines.

ORACLE/PLSQL: LITERALS

This Oracle tutorial explains how to use literals (text, integer, and number) in Oracle with examples.

DESCRIPTION

In Oracle, a literal is the same as a constant. We'll cover three types of literals - text literals, integer literals, and number literals.

TEXT LITERALS

Text literals are always surrounded by single quotes ('). For example:

'Hewlett Packard'
'28-MAY-03'

INTEGER LITERALS

Integer literals can be up to 38 digits. Integer literals can be either positive numbers or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid integer literals:

23
+23
-23

NUMBER LITERALS

Number literals can be up to 38 digits. Number literals can be either positive or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid number literals:

25
+25
-25
25e-04
25.607

ORACLE/PLSQL: DECLARING VARIABLES

This Oracle tutorial explains how to declare variables in Oracle/PLSQL with syntax and examples.

WHAT IS A VARIABLE IN ORACLE?

In Oracle/PLSQL, a variable allows a programmer to store data temporarily during the execution of code.

SYNTAX

The syntax for declaring variables in Oracle is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Parameters or Arguments

variable_name is the name to assign to the variable.

CONSTANT is optional. If specified, the variable's value is constant and can not be changed.

datatype is the datatype to assign to the variable.

EXAMPLE - DECLARING A VARIABLE

Below is an example of how to declare a variable in Oracle called LDescription.

LDescription varchar2(40);

You can then later set or change the value of the LDescription variable, as follows:

LDescription := 'techonthenet.com Example';

EXAMPLE - DECLARING A VARIABLE WITH AN INITIAL VALUE (NOT A CONSTANT)

Below is an example of how to declare a variable in Oracle and give it an initial value. This is different from a constant in that the variable's value can be changed later.

LType varchar2(40) := 'techonthenet.com Example';

You could later change the variable's value, as follows:

LType := 'My value has changed';

EXAMPLE - DECLARING A CONSTANT

Below is an example of how to declare a constant in Oracle. The value of a constant can not be changed.

LTotal CONSTANT numeric(8,1) := 8363934.1;

ORACLE/PLSQL: SEQUENCES (AUTONUMBER)

This Oracle tutorial explains how to create and drop sequences in Oracle with syntax and examples.

DESCRIPTION

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.

CREATE SEQUENCE

You may wish to create a sequence in Oracle to handle an autonumber field.

Syntax

The syntax to create a sequence in Oracle is:

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

Example

Let's look at an example of how to create a sequence in Oracle.

For example:

CREATE SEQUENCE supplier_seq
  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;

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 nextval statement needs to be used in a SQL statement. For example:

INSERT INTO suppliers
(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_seq sequence. The supplier_name field would be set to Kraft Foods.

DROP SEQUENCE

Once you have created your sequence in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a sequence in Oracle is:

DROP SEQUENCE sequence_name;

sequence_name is the name of the sequence that you wish to drop.

Example

Let's look at an example of how to drop a sequence in Oracle.

For example:

DROP SEQUENCE supplier_seq;

This example would drop the sequence called supplier_seq.

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;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
  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 havenot 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;

SELECT seq_name.nextval FROM dual;

ALTER SEQUENCE seq_name
INCREMENT BY 1;

Now, the next value to be served by the sequence will be 225.

ORACLE/PLSQL: FUNCTIONS

This Oracle tutorial explains how to create and drop functions in Oracle/PLSQL with syntax and examples.

CREATE FUNCTION

Just as you can in other languages, you can create your own functions in Oracle.

Syntax

The syntax to create a function in Oracle 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];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

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.
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.
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.

Example

Let's look at an example of how to create a function in Oracle.

The following is a simple example of an Oracle function:

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;

EXCEPTION
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 a SQL statement as follows:

SELECT course_name, FindCourse(course_name) AS course_id
FROM courses
WHERE subject = 'Mathematics';

DROP FUNCTION

Once you have created your function in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in Oracle is:

DROP FUNCTION function_name;

function_name is the name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in Oracle.

For example:

DROP FUNCTION FindCourse;

This example would drop the function called FindCourse.

ORACLE/PLSQL: PROCEDURES

This Oracle tutorial explains how to create and drop procedures in Oracle/PLSQL with syntax and examples.

CREATE PROCEDURE

Just as you can in other languages, you can create your own procedures in Oracle.

Syntax

The syntax to create a procedure in Oracle is:

CREATE [OR REPLACE] PROCEDURE 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:

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.
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.
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.

Example

Let's look at an example of how to create a procedure in Oracle.

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )
   
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;
   
   INSERT INTO student_courses
   ( 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;

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.

DROP PROCEDURE

Once you have created your procedure in Oracle, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in Oracle is:

DROP PROCEDURE procedure_name;

procedure_name is the name of the procedure that you wish to drop.

Example

Let's look at an example of how to drop a procedure in Oracle.

For example:

DROP PROCEDURE UpdateCourse;

This example would drop the procedure called UpdateCourse.

ORACLE/PLSQL: IF-THEN-ELSE STATEMENT

This Oracle tutorial explains how to use the IF-THEN-ELSE statement in Oracle with syntax and examples.

DESCRIPTION

In Oracle, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE.

SYNTAX

There are different syntaxes for the IF-THEN-ELSE statement.

Syntax (IF-THEN)

The syntax is for IF-THEN in Oracle/PLSQL is:

IF condition THEN
   {...statements to execute when condition is TRUE...}
END IF;

You use the the IF-THEN syntax, when you want to execute statements only when condition is TRUE.

Syntax (IF-THEN-ELSE)

The syntax is for IF-THEN-ELSE in Oracle/PLSQL is:

IF condition THEN
   {...statements to execute when condition is TRUE...}

ELSE
   {...statements to execute when condition is FALSE...}

END IF;

You use the IF-THEN-ELSE syntax, when you want to execute one set of statements when condition is TRUE or a different set of statements when condition is FALSE.

Syntax (IF-THEN-ELSIF)

The syntax for IF-THEN-ELSIF in Oracle/PLSQL is:

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}

END IF;

You use the IF-THEN-ELSIF syntax, when you want to execute one set of statements when condition1 is TRUE or a different set of statements when condition2 is TRUE.

Syntax (IF-THEN-ELSIF-ELSE)

The syntax for IF-THEN-ELSIF-ELSE in Oracle/PLSQL is:

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}

ELSE
   {...statements to execute when both condition1 and condition2 are FALSE...}

END IF;

You use the IF-THEN-ELSIF-ELSE syntax, when you want to execute one set of statements when condition1 is TRUE, a different set of statements when condition2 is TRUE, or a different set of statements when all previous conditions (ie:condition1 and condition2) are FALSE.

NOTE

Once a condition is found to be TRUE, the IF-THEN-ELSE statement will execute the corresponding code and not evaluate the conditions any further.
If no condition is met, the ELSE portion of the IF-THEN-ELSE statement will be executed.
It is important to note that the ELSIF and ELSE portions are optional.

EXAMPLE

The following is example using the IF-THEN-ELSE statement in an Oracle function:

CREATE OR REPLACE Function IncomeLevel
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   monthly_value number(6);
   ILevel varchar2(20);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   open c1;
   fetch c1 into monthly_value;
   close c1;

   IF monthly_value <= 4000 THEN
      ILevel := 'Low Income';

   ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
      ILevel := 'Avg Income';

   ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
      ILevel := 'Moderate Income';

   ELSE
      ILevel := 'High Income';

   END IF;

   RETURN ILevel;

END;

In this IF-THEN-ELSE statement example, we've created a function called IncomeLevel. It has one parameter called name_inand it returns a varchar2. The function will return the income level based on the employee's name.

ORACLE/PLSQL: WHILE LOOP

This Oracle tutorial explains how to use the WHILE LOOP in Oracle with syntax and examples.

DESCRIPTION

In Oracle, you use a WHILE LOOP when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

SYNTAX

The syntax for the WHILE Loop in Oracle/PLSQL is:

WHILE condition
LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

condition is the condition is test each pass through the loop. If condition evaluates to TRUE, the loop body is executed. Ifcondition evaluates to FALSE, the loop is terminated.

statements are the statements of code to execute each pass through the loop.

NOTE

You would use a WHILE LOOP statement when you are unsure of how many times you want the loop body to execute.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.

EXAMPLE

Let's look at a WHILE LOOP example in Oracle:

WHILE monthly_value <= 4000
LOOP
   monthly_value := daily_value * 31;
END LOOP;

In this WHILE LOOP example, the loop would terminate once the monthly_value exceeded 4000 as specified by:

WHILE monthly_value <= 4000

The WHILE LOOP will continue while monthly_value <= 4000. And once monthly_value is > 4000, the loop will terminate.

ORACLE/PLSQL: FOR LOOP

This Oracle tutorial explains how to use the FOR LOOP in Oracle with syntax and examples.

DESCRIPTION

In Oracle, the FOR LOOP allows you to execute code repeatedly for a fixed number of times.

SYNTAX

The syntax for the FOR Loop in Oracle/PLSQL is:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

loop_counter is the loop counter variable in the FOR LOOP.

REVERSE is optional. If REVERSE is specified, the LOOP counter will count in reverse.

lowest_number is the starting value for loop_counter.

highest_number is the ending value for loop_counter.

statements are the statements of code to execute each pass through the FOR LOOP.

NOTE

You would use a FOR LOOP when you want to execute the loop body a fixed number of times.
If REVERSE is specified, then the highest_number will be the starting value for loop_counter and lowest_number will be the ending value for loop_counter.

EXAMPLE

Let's look at an example of how to use a FOR LOOP in Oracle.

FOR Lcntr IN 1..20
LOOP
   LCalc := Lcntr * 31;
END LOOP;

This FOR LOOP example will loop 20 times. The counter called Lcntr will start at 1 and end at 20.

You can use the REVERSE modifier to run the FOR LOOP in reverse order. For example:

FOR Lcntr IN REVERSE 1..15
LOOP
   LCalc := Lcntr * 31;
END LOOP;

This FOR LOOP example will loop 15 times. However, because REVERSE is specified, the counter called Lcntr will start at 15 and end at 1 (in essence, looping backwards).

ORACLE/PLSQL: EXIT STATEMENT

This Oracle tutorial explains how to use the EXIT statement in Oracle with syntax and examples.

DESCRIPTION

In Oracle, the EXIT statement is most commonly used to terminate LOOP statements.

SYNTAX

The syntax for the EXIT statement in Oracle/PLSQL is:

EXIT [WHEN boolean_condition];

Parameters or Arguments

WHEN boolean_condition is optional. It is the condition to terminate the LOOP.

EXAMPLE

Let's look at an EXIT Example in Oracle/PLSQL:

LOOP
   monthly_value := daily_value * 31;
   EXIT WHEN monthly_value > 4000;
END LOOP;

In this example, the LOOP would terminate when the monthly_value exceeded 4000.

ORACLE/PLSQL: LOOP STATEMENT

This Oracle tutorial explains how to use the LOOP statement in Oracle with syntax and examples.

DESCRIPTION

In Oracle, the LOOP statement is used when you are not sure how many times you want the loop body to execute and you want the loop body to execute at least once.

SYNTAX

The syntax for the LOOP statement in Oracle/PLSQL is:

LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

statements are the statements of code to execute each pass through the LOOP.

NOTE

You would use a LOOP statement when you are unsure of how many times you want the loop body to execute.
You can terminate a LOOP statement with either an EXIT statement or when it encounters an EXIT WHEN statementthat evaluates to TRUE.

EXAMPLE

Let's look at a LOOP example in Oracle:

LOOP
   monthly_value := daily_value * 31;
   EXIT WHEN monthly_value > 4000;
END LOOP;

In this LOOP example, the LOOP would terminate when the monthly_value exceeded 4000.

ORACLE/PLSQL: REPEAT UNTIL LOOP

This Oracle tutorial explains how to use the REPEAT UNTIL LOOP in Oracle with syntax and examples.

DESCRIPTION

Oracle doesn't have a REPEAT UNTIL LOOP, but you can emulate one with a LOOP statement.

SYNTAX

The syntax for emulating a REPEAT UNTIL LOOP in Oracle/PLSQL is:

LOOP

   {...statements...}

   EXIT [ WHEN boolean_condition ];

END LOOP;

Parameters or Arguments

statements are the statements of code to execute each pass through the LOOP.

WHEN boolean_condition is optional. It is the condition to terminate the LOOP.

NOTE

You would use an emulated REPEAT UNTIL LOOP when you do not know how many times you want the loop body to execute.
The REPEAT UNTIL LOOP would terminate when a certain condition was met.

EXAMPLE

Let's look at an example of how to emulate a REPEAT UNTIL LOOP in Oracle/PLSQL:

LOOP
   monthly_value := daily_value * 31;
   EXIT WHEN monthly_value > 4000;
END LOOP;

In this example, we want the loop to repeat until monthly_value is greater than 4000, so we use the EXIT WHEN statement.

EXIT WHEN monthly_value > 4000;

Now, the LOOP would repeat until the monthly_value exceeded 4000.

ORACLE/PLSQL: GOTO STATEMENT

This Oracle tutorial explains how to use the GOTO statement in Oracle with syntax and examples.

DESCRIPTION

The GOTO statement causes the code to branch to the label after the GOTO statement.

SYNTAX

The syntax for the GOTO statement in Oracle/PLSQL consists of two parts - the GOTO statement and the Label Declaration:

GOTO statement

The GOTO statement consists of the GOTO keyword, followed by a label_name.

GOTO label_name;

Label Declaration

The Label Declaration consists of the label_name encapsulated in << >>, followed by at least one statement to execute.

<>
 {...statements...}

NOTE

label_name must be unique within the scope of the code.
There must be at least one statement to execute after the Label Declaration.

EXAMPLE

Let's look at an Oracle example that uses the GOTO statement.

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
   cnumber number;

   CURSOR c1
   IS
     SELECT MAX(course_number)
     FROM courses_tbl
     WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   IF c1%notfound then
      GOTO default_number;

   ELSE
      GOTO increment_number;
   END IF;

<>
   cnumber := 0;

<>
   cnumber := cnumber + 1;

   close c1;

RETURN cnumber;
   
END;

In this GOTO example, we have created two GOTO statements. The first one is called default_number and the second one is called increment_number.

ORACLE/PLSQL: CURSOR FOR LOOP

This Oracle tutorial explains how to use the CURSOR FOR LOOP in Oracle with syntax and examples.

DESCRIPTION

You would use a CURSOR FOR LOOP when you want to fetch and process every record in a cursor. The CURSOR FOR LOOP will terminate when all of the records in the cursor have been fetched.

SYNTAX

The syntax for the CURSOR FOR LOOP in Oracle/PLSQL is:

FOR record_index in cursor_name
LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

record_index is the index of the record.

cursor_name is the name of the cursor that you wish to fetch records from.

statements are the statements of code to execute each pass through the CURSOR FOR LOOP.

EXAMPLE

Here is an example of a function that uses a CURSOR FOR LOOP:

CREATE OR REPLACE Function TotalIncome
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   total_val number(6);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   total_val := 0;

   FOR employee_rec in c1
   LOOP
      total_val := total_val + employee_rec.monthly_income;
   END LOOP;

   RETURN total_val;

END;

In this example, we've created a cursor called c1. The CURSOR FOR Loop will terminate after all records have been fetched from the cursor c1.

ORACLE/PLSQL: NAMED SYSTEM EXCEPTIONS

This Oracle tutorial explains how to use Named System Exceptions in Oracle/PLSQL with syntax and examples.

WHAT IS A NAMED SYSTEM EXCEPTION IN ORACLE?

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: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package.
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 a 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.

SYNTAX

We will take a look at the syntax for Named System Exceptions in both procedures and functions.

Syntax for Procedures

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

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];

Syntax for Functions

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

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [function_name];

EXAMPLE

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

BEGIN
   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 OTHERS THEN
      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 clause to trap all remaining exceptions.

ORACLE/PLSQL: NAMED PROGRAMMER-DEFINED EXCEPTIONS

This Oracle tutorial explains how to use Named Programmer-Defined Exceptions in Oracle/PLSQL with syntax and examples.

WHAT IS A NAMED PROGRAMMER-DEFINED EXCEPTION IN ORACLE?

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.

SYNTAX

We will take a look at the syntax for Named Programmer-Defined Exceptions in both procedures and functions.

Syntax for Procedures

The syntax for the Named Programmer-Defined Exception in a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]

   exception_name EXCEPTION;
   
BEGIN
   executable_section
   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]
      
   WHEN OTHERS THEN
      [statements]

END [procedure_name];

Syntax for Functions

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]

   exception_name EXCEPTION;

BEGIN
   executable_section

   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]

   WHEN OTHERS THEN
      [statements]
      
END [function_name];

EXAMPLE

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;

BEGIN
   IF sales_in = 0 THEN
      RAISE no_sales;

   ELSE
      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 OTHERS THEN
      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_sales in 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;

Now if the sales_in variable contains a zero, our code will jump directly to the Named Programmer-Defined Exception calledno_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.');

We are also using the WHEN OTHERS clause to trap all remaining exceptions:

WHEN OTHERS THEN
   raise_application_error (-20002,'An error has occurred inserting an order.');

ORACLE/PLSQL: WHEN OTHERS CLAUSE

This Oracle tutorial explains how to use the Oracle WHEN OTHERS clause with syntax and examples.

WHAT IS THE WHEN OTHERS CLAUSE IN ORACLE?

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.

SYNTAX

We will take a look at the syntax for the WHEN OTHERS clause in both procedures and functions.

Syntax for Procedures

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

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];

Syntax for Functions

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

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [function_name];

EXAMPLE

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;

BEGIN
   IF sales_in = 0 THEN
      RAISE no_sales;

   ELSE
      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 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.');

END;

In this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or 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 happened.'

Answer: Yes, you can use SQLCODE function to retrieve the error number and SQLERRM function to 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;

Or you could log the error to a table as follows:

EXCEPTION
   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;

ORACLE/PLSQL: SQLCODE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SQLCODE function with syntax and examples.

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:

SYNTAX

The syntax for the Oracle/PLSQL SQLCODE function is:

SQLCODE

Parameters or Arguments

There are no parameters or arguments for the SQLCODE function.

NOTE

See also the SQLERRM function.

EXAMPLE

Since EXCEPTION HANDLING is usually written with the following syntax:

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [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;

Or you could log the error to a table using the SQLCODE function as follows:

EXCEPTION
   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;

ORACLE/PLSQL: SQLERRM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SQLERRM function with syntax and examples.

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.

SYNTAX

The syntax for the Oracle/PLSQL SQLERRM function is:

SQLERRM

Parameters or Arguments

There are no parameters or arguments for the SQLERRM function.

NOTE

See also the SQLCODE function.

EXAMPLE

Since EXCEPTION HANDLING is usually written with the following syntax:

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [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;

Or you could log the error to a table using the SQLERRM function as follows:

EXCEPTION
   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;

ORACLE/PLSQL: DECLARE A CURSOR

This Oracle tutorial explains how to declare a cursor in Oracle/PLSQL with syntax and examples.

DESCRIPTION

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 to declare a cursor.

CURSOR WITHOUT PARAMETERS (SIMPLEST)

Declaring a cursor without any parameters is the simplest cursor. Let's take a closer look.

Syntax

The syntax for a cursor without parameters in Oracle/PLSQL is:

CURSOR cursor_name
IS
  SELECT_statement;

Example

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

As we get more complicated, we can declare cursors with parameters.

Syntax

The syntax for a cursor with parameters in Oracle/PLSQL is:

CURSOR cursor_name (parameter_list)
IS
  SELECT_statement;

Example

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

Finally, we can declare a cursor with a return clause.

Syntax

The syntax for a cursor with a return clause in Oracle/PLSQL is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
   SELECT_statement;

Example

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

This Oracle tutorial explains how to use the Oracle/PLSQL OPEN statement with syntax and examples.

DESCRIPTION

Once you've declared your cursor, the next step is to use the OPEN statement to open the cursor.

SYNTAX

The syntax to open a cursor using the OPEN statement in Oracle/PLSQL is:

OPEN cursor_name;

Parameters or Arguments

cursor_name is the name of the cursor that you wish to open.

EXAMPLE

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;

   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;

ORACLE/PLSQL: FETCH STATEMENT

This Oracle tutorial explains how to use the Oracle/PLSQL FETCH statement with syntax and examples.

DESCRIPTION

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 use the FETCH statement to fetch rows from your cursor.

SYNTAX

The syntax for the FETCH statement in Oracle/PLSQL is:

FETCH cursor_name INTO variable_list;

Parameters or Arguments

cursor_name is the name of the cursor that you wish to fetch rows.

variable_list is the list of variables, comma delimited, that you wish to store the cursor result set in.

EXAMPLE

For example, you could have a cursor defined as:

CURSOR c1
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;

   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;

ORACLE/PLSQL: CLOSE STATEMENT

This Oracle tutorial explains how to use the Oracle/PLSQL CLOSE statement with syntax and examples.

DESCRIPTION

The final step of working with cursors is to close the cursor once you have finished using it.

SYNTAX

The syntax to CLOSE a cursor in Oracle/PLSQL is:

CLOSE cursor_name;

Parameters or Arguments

cursor_name is the name of the cursor that you wish to close.

EXAMPLE

For example, you could close a cursor called c1 with the following command:

CLOSE c1;

Below is a function that demonstrates how to use the CLOSE statement:

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;

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; 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;

ORACLE/PLSQL: SELECT FOR UPDATE STATEMENT

This Oracle tutorial explains how to use the Oracle/PLSQL SELECT FOR UPDATE statement with syntax and examples.

DESCRIPTION

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.

SYNTAX

The syntax for the SELECT FOR UPDATE statement in Oracle/PLSQL is:

CURSOR cursor_name
IS
   select_statement
   FOR UPDATE [OF column_list] [NOWAIT];

Parameters or Arguments

cursor_name is the name of the cursor.

select_statement is a SELECT statement that will populate your cursor result set.

column_list are the columns in the cursor result set that you wish to update.

NOWAIT is optional. The cursor does not wait for resources.

EXAMPLE

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;

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

ORACLE/PLSQL: WHERE CURRENT OF STATEMENT

This Oracle tutorial explains how to use the Oracle/PLSQL WHERE CURRENT OF statement with syntax and examples.

DESCRIPTION

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

SYNTAX

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

NOTE

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

EXAMPLE

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the WHERE CURRENT OF Statement:

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
     FOR UPDATE of instructor;

BEGIN

   OPEN c1;
   FETCH c1 INTO cnumber;

   if c1%notfound then
      cnumber := 9999;

   else
      UPDATE courses_tbl
        SET instructor = 'SMITH'
        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   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 OF Statement:

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
     FOR UPDATE of instructor;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;

   else
      DELETE FROM courses_tbl
        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   close c1;

RETURN cnumber;

END;

ORACLE/PLSQL: BEFORE INSERT TRIGGER

This Oracle tutorial explains how to create a BEFORE INSERT Trigger in Oracle with syntax and examples.

DESCRIPTION

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

SYNTAX

The syntax to create a BEFORE INSERT Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

BEFORE INSERT indicates that the trigger will fire before the INSERT operation is executed.

table_name is the name of the table that the trigger is created on.

RESTRICTIONS

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

NOTE

See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE UPDATE triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an BEFORE INSERT trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an BEFORE INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing INSERT into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update create_date field to current system date
   :new.create_date := sysdate;
   
   -- Update created_by field to the username of the person performing the INSERT
   :new.created_by := v_username;
   
END;

ORACLE/PLSQL: AFTER INSERT TRIGGER

This Oracle tutorial explains how to create an AFTER INSERT Trigger in Oracle with syntax and examples.

DESCRIPTION

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

SYNTAX

The syntax to create an AFTER INSERT Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

AFTER INSERT indicates that the trigger will fire after the INSERT operation is executed.

table_name is the name of the table that the trigger is created on.

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.

NOTE

See also how to create AFTER DELETE, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an AFTER INSERT trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN
   
   -- 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 );
     
END;

ORACLE/PLSQL: BEFORE UPDATE TRIGGER

This Oracle tutorial explains how to create a BEFORE UPDATE Trigger in Oracle with syntax and examples.

DESCRIPTION

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.

SYNTAX

The syntax to create a BEFORE UPDATE Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

BEFORE UPDATE indicates that the trigger will fire before the UPDATE operation is executed.

table_name is the name of the table that the trigger is created on.

RESTRICTIONS

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

NOTE

See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE DELETE, and BEFORE INSERT triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an BEFORE UPDATE trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE on the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update updated_date field to current system date
   :new.updated_date := sysdate;
   
   -- Update updated_by field to the username of the person performing the UPDATE
   :new.updated_by := v_username;
   
END;

ORACLE/PLSQL: AFTER UPDATE TRIGGER

This Oracle tutorial explains how to create an AFTER UPDATE Trigger in Oracle with syntax and examples.

DESCRIPTION

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

SYNTAX

The syntax to create an AFTER UPDATE Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

AFTER UPDATE indicates that the trigger will fire after the UPDATE operation is executed.

table_name is the name of the table that the trigger is created on.

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.

NOTE

See also how to create AFTER DELETE, AFTER INSERT, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an AFTER UPDATE trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an AFTER UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE into table
   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 );
     
END;

ORACLE/PLSQL: BEFORE DELETE TRIGGER

This Oracle tutorial explains how to create a BEFORE DELETE Trigger in Oracle with syntax and examples.

DESCRIPTION

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.

SYNTAX

The syntax to create a BEFORE DELETE Trigger in Oracle/PLSQL 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;

Parameters or Arguments

OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.

trigger_name is the name of the trigger to create.

BEFORE DELETE indicates that the trigger will fire before the DELETE operation is executed.

table_name is the name of the table that the trigger is created on.

RESTRICTIONS

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

NOTE

See also how to create AFTER DELETE, AFTER INSERT, AFTER UPDATE, BEFORE INSERT, and BEFORE UPDATE triggers.
See also how to drop a trigger.

EXAMPLE

Let's look at an example of how to create an BEFORE DELETE trigger using the CREATE TRIGGER statement.

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 use the CREATE TRIGGER statement to create an BEFORE DELETE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing the DELETE on 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,
     delete_date,
     deleted_by )
   VALUES
   ( :old.order_id,
     :old.quantity,
     :old.cost_per_item,
     :old.total_cost,
      sysdate,
      v_username );
      
END;

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 6

ORACLE/PLSQL: COMMENTS WITHIN SQL

DESCRIPTION

SYNTAX

Syntax Using -- symbol

Syntax Using /* and */ symbols

EXAMPLE - COMMENT ON A SINGLE LINE

EXAMPLE - COMMENT ON MULTIPLE LINES

ORACLE/PLSQL: LITERALS

DESCRIPTION

TEXT LITERALS

INTEGER LITERALS

NUMBER LITERALS

ORACLE/PLSQL: DECLARING VARIABLES

WHAT IS A VARIABLE IN ORACLE?

SYNTAX

Parameters or Arguments

EXAMPLE - DECLARING A VARIABLE

EXAMPLE - DECLARING A VARIABLE WITH AN INITIAL VALUE (NOT A CONSTANT)

EXAMPLE - DECLARING A CONSTANT

ORACLE/PLSQL: SEQUENCES (AUTONUMBER)

DESCRIPTION

CREATE SEQUENCE

Syntax

Example

DROP SEQUENCE

Syntax

Example

FREQUENTLY ASKED QUESTIONS

ORACLE/PLSQL: FUNCTIONS

CREATE FUNCTION

Syntax

Example

DROP FUNCTION

Syntax

Example

ORACLE/PLSQL: PROCEDURES

CREATE PROCEDURE

Syntax

Example

DROP PROCEDURE

Syntax

Example

ORACLE/PLSQL: IF-THEN-ELSE STATEMENT

DESCRIPTION

SYNTAX

Syntax (IF-THEN)

Syntax (IF-THEN-ELSE)

Syntax (IF-THEN-ELSIF)

Syntax (IF-THEN-ELSIF-ELSE)

NOTE

EXAMPLE

ORACLE/PLSQL: WHILE LOOP

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: FOR LOOP

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE

ORACLE/PLSQL: EXIT STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

EXAMPLE

ORACLE/PLSQL: LOOP STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE