Overview
Unlike Cursor Variables(Ref Cursors), explicit cursor is less flexible in usage than regular cursor due to its binding with a specific statement.
The usage of explicit cursor in Altibase have so much resemblance with that of Oracle.
Examples of converting an explicit cursors from ORACLE to ALTIBASE HDB can be found from the following table.
Example
Oracle | AlLTIBASE HDB | Comments |
|---|
DECLARE
v_jobid employees.eno%TYPE; -- variable for job_id
v_lastname employees.e_lastname%TYPE; -- variable for last_name
CURSOR c1 IS SELECT e_lastname, eno FROM employees
WHERE REGEXP_LIKE (emp_job, 'S[HT]_CLERK');
v_employees employees%ROWTYPE; -- record variable for row
CURSOR c2 is SELECT * FROM employees
WHERE REGEXP_LIKE (emp_job, '[ACADFIMKSA]_M[ANGR]');
BEGIN
OPEN c1; -- open the cursor before fetching
LOOP
FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
OPEN c2;
LOOP
FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.e_lastname, 25, ' ') ||
v_employees.emp_job );
END LOOP;
CLOSE c2;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
v_jobid employees.emp_job%TYPE; -- variable for job_id
v_lastname employees.e_lastname%TYPE; -- variable for last_name
CURSOR c1 IS SELECT e_lastname, emp_job FROM employees
WHERE
(emp_job LIKE 'SH_CLERK%' OR emp_job LIKE 'ST_CLERK% ');
v_employees employees%ROWTYPE; -- record variable for row
CURSOR c2 is SELECT * FROM employees
WHERE
(emp_job LIKE 'A_MA%' OR emp_job LIKE 'A_MN%' OR emp_job
LIKE 'A_MG%', OR emp_job LIKE 'A_MR%',.... );
BEGIN
OPEN c1; -- open the cursor before fetching
LOOP
FETCH c1 INTO v_lastname, v_jobid; -- fetches 2 columns into variables
EXIT WHEN c1%NOTFOUND;
SYSTEM_.PRINTLN( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
CLOSE c1;
SYSTEM_.PRINTLN( '-------------------------------------' );
OPEN c2;
LOOP
FETCH c2 INTO v_employees; -- fetches entire row into the v_employees record
EXIT WHEN c2%NOTFOUND;
SYSTEM_.PRINTLN( RPAD(v_employees.e_lastname, 25, ' ') || v_employees.job_id );
END LOOP;
CLOSE c2;
END;
/
| The usages of explicit function are almost same
between ALTIBASE HDB and Oracle.
For reference, regular expression functionality displayed on the left
has not been supported in Altibase to ALTIBASE HDB V6.
But it will be applied in Altibase HDB V7. |
DECLARE
my_sal employees.salary%TYPE;
my_job employees.emp_job%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT factor*salary FROM employees WHERE emp_job = my_job;
BEGIN
OPEN c1; -- factor initially equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; -- does not affect FETCH
END LOOP;
CLOSE c1;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
my_sal employees.salary%TYPE;
my_job employees.emp_job%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT factor*salary FROM employees WHERE emp_job = my_job;
BEGIN
OPEN c1; -- factor initially equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; -- does not affect FETCH
END LOOP;
CLOSE c1;
END;
/ | |
DECLARE
CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
name1 employees.last_name%TYPE;
name2 employees.last_name%TYPE;
name3 employees.last_name%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
CURSOR c1 IS SELECT e_lastname FROM employees ORDER BY e_lastname;
name1 employees.e_lastname%TYPE;
name2 employees.e_lastname%TYPE;
name3 employees.e_lastname%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;
END;
/ | |