ORACLE FETCH Syntax
ORACLE
In Oracle, FETCH is used in two primary contexts, as a clause in an SQL SELECT statement and as a PL/SQL statement.
1. FETCH in SQL SELECT statement.
➔ The FETCH clause is often used as a row-limiting clause.
Example
/* Fetch the first 10 rows */
SELECT *
FROM employees
ORDER BY employee_id
FETCH FIRST 10 ROWS ONLY;
Copy the code and try it out practically in your learning environment.
Example
/* Skip the first 10 rows and fetch the next 10 */
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Copy the code and try it out practically in your learning environment.
2. The FETCH Statement in PL/SQL.
➔ The FETCH statement in PL/SQL is used with explicit cursors to process a result set.
Example
/* Using FETCH in a PL/SQL Cursor */
DECLARE
CURSOR emp_cursor IS SELECT ename, sal FROM demo_emp;
v_ename demo_emp.ename%TYPE; -- declare a varriable v_name same type of demo_emp.ename
v_sal demo_emp.sal%TYPE; -- declare a varriable v_sal same type of demo_emp.sal
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename, v_sal; -- Fetch one row at a time
EXIT WHEN emp_cursor%NOTFOUND; -- Exit when no more rows
-- The is printed in the output.
DBMS_OUTPUT.PUT_LINE(v_ename || ' salary ' || v_sal);
END LOOP;
CLOSE emp_cursor;
END;
/
Copy the code and try it out practically in your learning environment.
Practice this lesson in your Oracle learning environment.
The best way to learn is to implement it yourself after any lesson.
Type or copy the code and try it out practically in your learning environment.
Lesson: Oracle fetch
Create table:
Example
/* Drop table if the table name is already exists */
DROP TABLE IF EXISTS demo_fetch;
/* Create Table*/
CREATE TABLE demo_fetch (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
salary NUMBER(8, 2),
department_id NUMBER(4)
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
INSERT INTO demo_fetch(employee_id, first_name, last_name, salary, department_id)
VALUES (101, 'Neena', 'Kochhar', 17000, 90);
INSERT INTO demo_fetch(employee_id, first_name, last_name, salary, department_id)
VALUES (102, 'Lex', 'De Haan', 17000, 90);
INSERT INTO demo_fetch(employee_id, first_name, last_name, salary, department_id)
VALUES (103, 'Alexander', 'Hunold', 9000, 60);
INSERT INTO demo_fetch(employee_id, first_name, last_name, salary, department_id)
VALUES (104, 'Bruce', 'Ernst', 6000, 60);
INSERT INTO demo_fetch(employee_id, first_name, last_name, salary, department_id)
VALUES (105, 'David', 'Austin', 4800, 60);
Copy the code and try it out in your learning environment.
Check the query results against the data.
Example
SELECT *
FROM demo_fetch
ORDER BY employee_id
OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY;
Copy the code and try it out practically in your learning environment.
If desired, delete the table after finishing the exercise.
Example
/* Drop the table from the schema. */
DROP TABLE demo_fetch;
Copy the code and try it out practically in your learning environment.