Working with REF CURSOR in PL/SQL

Read more »

Avoid overlapping months and years

Useful way to group data by week to avoid the quandary of weeks overlapping months and years.

CREATE OR REPLACE PROCEDURE weekly_proc IS

CURSOR x_cur IS
SELECT DISTINCT SUBSTR(TO_CHAR(date1),4,3) m, SUBSTR(TO_CHAR(date1),1,2) w, COUNT (*) cnt
FROM cpad_errors
GROUP BY SUBSTR(TO_CHAR(date1),4,3), SUBSTR(TO_CHAR(date1),1,2);

x_rec x_cur%ROWTYPE;

week_var NUMBER;

BEGIN

EXECUTE IMMEDIATE 'truncate table week_test';

OPEN x_cur;

LOOP

FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;

IF TO_NUMBER(x_rec.w) < 8
THEN week_var := 1;
ELSIF TO_NUMBER(x_rec.w) < 15 AND TO_NUMBER(x_rec.w) > 7
THEN week_var := 2;
ELSIF TO_NUMBER(x_rec.w) < 22 AND TO_NUMBER(x_rec.w) > 16
THEN week_var := 3;
ELSE week_var := 4;
END IF;


INSERT INTO week_test (WEEK_NUM, TTL, MNTH)
VALUES (week_var, x_rec.cnt, x_rec.m);

END LOOP;

CLOSE x_cur;

COMMIT;


END weekly_proc;


*********************************
SELECT mnth|| ' week '|| week_num, SUM(ttl)
FROM week_test
GROUP BY mnth|| ' week '|| week_num;
*********************************

Read more »

%TYPE vs %ROWTYPE

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database.

The %TYPE and %ROWTYPE constructs provide data independence, reduce maintenance costs, and allows programs to adapt as the database changes

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/



-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

Read more »

Oracle Learning - 13

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.
NoWait specifies that the database should not wait for a lock to be released.
Oracle/PLSQL Topics: Cursors

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
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;
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

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 ;

For example, you could 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

The final step of working with cursors is to close the cursor once you have finished using it.
The basic syntax to CLOSE the cursor is:
CLOSE cursor_name;

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;

Read more »

Oracle Learning - 12

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

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

The syntax for the COMMIT statement is:
COMMIT [WORK] [COMMENT text];
The Commit statement commits all changes for the current session. Once a commit is issued, other users will be able to see your changes.
Oracle/PLSQL: Commit

The syntax for the COMMIT statement is:
COMMIT [WORK] [COMMENT text];
The Commit statement commits all changes for the current session. Once a commit is issued, other users will be able to see your changes.

Read more »

Oracle Learning - 11

Oracle/PLSQL: FOR Loop

The syntax for the FOR Loop is:
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;
You would use a FOR Loop when you want to execute the loop body a fixed number of times.

Let's take a look at an example.
FOR Lcntr IN 1..20
LOOP
LCalc := Lcntr * 31;
END LOOP;
This example will loop 20 times. The counter will start at 1 and end at 20.

The FOR Loop can also loop in reverse. For example:
FOR Lcntr IN REVERSE 1..15
LOOP
LCalc := Lcntr * 31;
END LOOP;
This example will loop 15 times. The counter will start at 15 and end at 1. (loops backwards)

Oracle/PLSQL: CURSOR FOR Loop

The syntax for the CURSOR FOR Loop is:
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;
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.
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: While Loop

The syntax for the WHILE Loop is:
WHILE condition
LOOP
{.statements.}
END LOOP;
You would use a WHILE Loop when you are not sure how many times you will execute the loop body. Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
Let's take a look at an example:
WHILE monthly_value <= 4000
LOOP
monthly_value := daily_value * 31;
END LOOP;
In this example, the WHILE Loop would terminate once the monthly_value exceeded 4000.

Oracle/PLSQL: Repeat Until Loop

Oracle doesn't have a Repeat Until loop, but you can emulate one. The syntax for emulating a REPEAT UNTIL Loop is:
LOOP
{.statements.}
EXIT WHEN boolean_condition;
END LOOP;
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.
Let's take a look at an example:
LOOP
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
In this example, the LOOP would repeat until the monthly_value exceeded 4000.
Oracle/PLSQL: Exit Statement

The syntax for the EXIT statement is:
EXIT [WHEN boolean_condition];
The EXIT statement is most commonly used to terminate LOOP statements.
Let's take a look at an example:
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.

Read more »