Process Query Result in PL/SQL in Oracle

Q

How To Process Query Result in PL/SQL in Oracle?

✍: 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.

⇒ Create an Array in PL/SQL in Oracle

⇐ Use SQL Statements in PL/SQL in Oracle

⇑ Introduction to Oracle PL/SQL

⇑⇑ Oracle Database Tutorials

2019-03-08, 3352🔥, 0💬

Create an Array in PL/SQL in Oracle
How To Create an Array in PL/SQL in Oracle? If you want create an array data structure, you can use the collection type VARRAY. VARRAY stands for Variable Array. Here is a sample script on how to use VARRAY: SQL> set serveroutput on; SQL> DECLARE 2 TYPE list IS VARRAY(3) OF VARCHAR(16)... 2019-03-08, 3805🔥, 0💬

Execution Flow Control Statements in Oracle
What Are the Execution Flow Control Statements in Oracle? 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 executes a block of stateme... 2019-03-27, 3745🔥, 1💬

Manage Transaction Isolation Levels in Oracle
How To Manage Transaction Isolation Level in Oracle? 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.TOTAL_SALARY AS 2 total_... 2019-03-08, 3562🔥, 0💬

Process Query Result in PL/SQL in Oracle
How To Process Query Result in PL/SQL in Oracle? 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... 2019-03-08, 3353🔥, 0💬

Execute a Simple Stored Procedure in Oracle
How To Execute a Stored Program Unit in Oracle? 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 DBMS_OUTPUT.PUT_LINE... 2019-03-20, 3309🔥, 0💬

Use SQL Statements in PL/SQL in Oracle
How To Use SQL Statements in PL/SQL in Oracle? 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) NOT NULL, 3 description ... 2019-03-08, 3234🔥, 0💬

Data Types Supported in PL/SQL in Oracle
How Many Data Types Are Supported in Oracle? 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 serveroutput on; SQL> DECLARE... 2019-03-20, 3228🔥, 0💬

Example of Passing Parameters to Procedures in Oracle
How To Pass Parameters to Procedures in Oracle? 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: SQL> CREATE OR REPLACE PROC... 2019-03-08, 3228🔥, 0💬

Define a Procedure inside another Procedure in Oracle
How To Define a Procedure inside Another Procedure in Oracle? 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 3 BEGIN 4 IF day = 'MOND... 2019-02-18, 3074🔥, 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.