How To Process Query Result in PL/SQL

Q

How To Process Query Result in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL

✍: FYIcenter.com

A

You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example:

SQL> set serveroutput on;

SQL> BEGIN
  2     FOR row IN
  3         (SELECT * FROM employees WHERE manager_id = 101)
  4         LOOP
  5       DBMS_OUTPUT.PUT_LINE('Name = ' || row.last_name);
  6     END LOOP;
  7  END;
  8  /
Name = Greenberg
Name = Whalen
Name = Mavris
Name = Baer
Name = Higgins

The loop statement in this script automatically sets a temporary variable "row" each row in the result set returned from the SELECT statement.

2007-04-26, 7355👍, 0💬

How To Manage Transaction Isolation Level
How To Manage Transaction Isolation Level? - Oracle DBA FAQ - Introduction to PL/SQL Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level: SQL> CREATE OR REPLACE PROCEDURE HR... 2007-04-26, 7531👍, 0💬

How To Process Query Result in PL/SQL
How To Process Query Result in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL You can run queries (SELECT statements) in a PL/SQL code blocks, and process the results a loop as shown in the following script example: SQL> set serveroutput on; SQL> BEGIN 2 FOR row IN 3 (SELECT * FROM employees WHER... 2007-04-26, 7356👍, 0💬

How To Pass Parameters to Procedures
How To Pass Parameters to Procedures? - Oracle DBA FAQ - Introduction to PL/SQL Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this: SQ... 2007-04-26, 5367👍, 0💬

How To Execute a Stored Program Unit
How To Execute a Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL If you want to execute a stored program unit, you can use the EXECUTE statement. The example script below shows how to executes a stored program unit: SQL> set serveroutput on; SQL> CREATE PROCEDURE Hello AS 2 BEGIN 3 DB... 2007-04-25, 5182👍, 0💬

How To Define a Procedure inside Another Procedure
How To Define a Procedure inside Another Procedure? - Oracle DBA FAQ - Introduction to PL/SQL Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example: SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS 2 PROCEDURE DBA_TASK (day VARCHAR2) AS... 2007-04-26, 5136👍, 0💬

How To Create a Stored Program Unit
How To Create a Stored Program Unit? - Oracle DBA FAQ - Introduction to PL/SQL If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit: SQL> set serveroutput on; SQL> CREATE PROCEDURE Hello AS 2 BEGIN ... 2007-04-25, 5083👍, 0💬

How To Use SQL Statements in PL/SQL
How To Use SQL Statements in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL SQL DML (Data Manipulation Language) statements can be included in PL/SQL code blocks directly without any changes. See the script below for examples: SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY, 2 subject VARCHAR(80)... 2007-04-25, 5035👍, 0💬

What Are the Execution Control Statements
What Are the Execution Control Statements? - Oracle DBA FAQ - Introduction to PL/SQL PL/SQL supports three groups of execution control statements: IF Statements - Conditionally executes a block of statements. CASE Statements - Selectively executes a block of statements. LOOP Statements - Repeatedly ... 2007-04-25, 4967👍, 0💬

How Many Data Types Are Supported
How Many Data Types Are Supported? - Oracle DBA FAQ - Introduction to PL/SQL PL/SQL supports two groups of data types: SQL Data Types - All data types used for table columns. PL/SQL Special Data Types - Like BOOLEAN or PLS_INTEGER. The script below shows some data type examples: SQL> set serveroutpu... 2007-04-25, 4913👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.