PL/SQL   «Prev 

PL/SQL Cursor Block

Question: How do PL/SQL Cursor Blocks make it easy to query and change the contents of Oracle tables?
PL/SQL is appropriate or required for querying object tables in Oracle in certain situations, such as:
  1. When complex business logic is involved: If the querying of an object table involves complex business logic that cannot be expressed using standard SQL queries, PL/SQL may be required. PL/SQL allows developers to write complex algorithms and manipulate data in ways that would be difficult or impossible to do using SQL alone.
  2. When using user-defined types: If an object table contains user-defined types, PL/SQL may be required to manipulate the data. This is because SQL has limited support for user-defined types, and PL/SQL provides more flexibility and control over how the data is accessed and manipulated.
  3. When using advanced features: Oracle provides advanced features such as pipelined table functions, which enable developers to process data in a streaming fashion, and row-level security, which restricts access to specific rows in a table. These features often require the use of PL/SQL code to implement.
  4. When using dynamic SQL: If the query against an object table needs to be constructed dynamically at runtime, PL/SQL may be required. This is because SQL does not provide a way to dynamically generate queries, and PL/SQL must be used to generate the necessary SQL statements.
In summary, PL/SQL may be appropriate or required for querying object tables in Oracle when the querying involves complex business logic, user-defined types, advanced features, or dynamic SQL.
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 use the SQL language to access tables, and each time you do so, you use a cursor to execute your task.
Question:What is a cursor?
A cursor
  1. is a pointer to a private SQL area that stores information about the processing of a SELECT or
  2. (DML)data manipulation language statement (INSERT, UPDATE, DELETE, or MERGE).
Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements.

1) Within the DECLARE section, the cursor and the variables are defined.
1) Within the DECLARE section, the cursor and the variables are defined.. Defining a PL/SQL cursor that queries an object table is very similar to defining a PL/SQL cursor that queries a relational table.

2) For signals the beginning of the FOR Loop
2) 
BEGIN
FOR see_all_date IN get_all_date LOOP
  dbms_output.put_line( The Customer
  '|| see_all_dat.firstname||'
  '||see_all_data.Lastname|| 'reside in 
  '||see_all_data.city||' ,
  '||see_all_data.state);')
For signals the beginning of the FOR Loop. After retrieving

3) END signals closing of the cursor after data processing is complete
3) 
END LOOP;
END;
/
END signals closing of the cursor after data processing is complete