Pl SQL Cursor

Sir I am doing an oracle course and I am having the PL/SQL cursor language so can you please tell me some details about this term ? What are the most-common ways programmers execute SELECT statements in PL/SQL

[Shaleen]

Hey buddy the The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database.

You must, of course, use the SQL language to access tables, and each time you do so, you use a cursor to get the job done

The most-common ways programmers execute SELECT statements in PL/SQL, namely

Using the SELECT-INTO statement

Fetching from an explicit cursor

Using a cursor FOR loop

Using EXECUTE IMMEDIATE INTO for dynamic queries

Using cursor variables


SELECT-INTO
SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is



Some examples of using SELECT-INTO:

Get the last name for a specific employee ID (the primary key in the employees table):

DECLARE
l_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_last_name
FROM employees
WHERE employee_id = 138;

DBMS_OUTPUT.put_line (
l_last_name);
END;

If there is a row in the employees table with ID 138, this block will display the last name of that employee. If there is no such row, the block will fail with an unhandled NO_DATA_FOUND exception. Assuming that a unique index is defined on the employee_id column, this block will never raise the TOO_MANY_ROWS exception.

Fetch an entire row from the employees table for a specific employee ID:

DECLARE
l_employee employees%ROWTYPE;
BEGIN
SELECT
INTO l_employee
FROM employees
WHERE employee_id = 138;

DBMS_OUTPUT.put_line (
l_employee.last_name);
END;

Again, if an employee exists for that ID, the last name will be displayed. In this case, I declare a record based on the employees table and fetch all columns (with a SELECT ) into that record for the specified row.

Fetch columns from different tables:

DECLARE
l_last_name
employees.last_name%TYPE;
l_department_name
departments.department_name%TYPE;
BEGIN
SELECT last_name, department_name
INTO l_last_name, l_department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.employee_id=138;

DBMS_OUTPUT.put_line (
l_last_name ||
' in ' ||
l_department_name);
END;


If you want more details feel free to contact again