Explain the returned result set of Oracle block programming

In Oracle block programming (begin series), because it does not support the form of select... from... returned result set, results can only be returned in the form of output parameters. Game

In Oracle block programming (begin series), because it does not support the form of select... from... returned result set, results can only be returned in the form of output parameters. Game

I. Overview

In Oracle block programming (begin series), because it does not support the form of select... from... returned result set, results can only be returned in the form of output parameters. As a structure that encapsulates the result set into reading data with a monotonous pointer, a cursor is similar to a queue that only leaves the queue and deletes the operation. It is exactly the type of the output parameter. To use this method, you must ensure that the parameters of the stored procedure are consistent during the Declaration and call, so you have to use the package. Therefore, the package + cursor + stored procedure or function becomes the method for returning the result set through block programming. The following describes how to implement pl/SQL and ODP.net.

Ii. Definition

1. Table Structure

Create table Grade
(
GradeId, number,
GradeName varchar2 (20)
)

2. Block Programming

-- Define the package specification
Create package Grades is
Type Result is ref cursor;
Procedure FenYe_Grade (p_PageSize in number, p_PageIndex in number, p_Result out Result );
End Grades;
-- Define the package body
Create package body Grades is
Procedure FenYe_Grade (p_PageSize in number, p_PageIndex in number, p_Result out Result) is
Begin
Open p_Result for select GradeId, GradeName
From (select GradeId, GradeName, rownum rn from Grade where rownum <= p_Page * p_PageSize)
Where rn> p_PageSize * (p_Page-1 );
End FenYe _

Now that we have talked about rownum, let's talk about it in detail. rownum is the sequence number when reading data. Remember the sequence number when reading data. It is like placing all the data in a room. There is a numbered machine at the door (starting with 1 and increasing with 1). One machine is displayed in the room, the machine is painted with a number on it. The number cannot be interrupted. Once interrupted, it starts from 1. Since it starts from 1 when it is interrupted, rownum> n or rownum = n (n> 1) is always false. According to this metaphor, rownum> 2 is used: first, the first one is 1, which does not meet the filtering conditions and is discarded. At this time, the Ampersand is interrupted, starting from 1, and then the second one is 1, so the loop goes on, all numbers are 1. let's take the above code example "where rn> p_pageSize * (p_Page-1)" to discuss a situation:

A. change to "where rownum> p_pageSize * (p_Page-1 )"

After this change, the result must be blank. Why? When rownum is used, it means that the result set is re-numbered. According to the above inference, the result must be null. Why can I use rn? Rn is the alias of rownum in a result set. When rownum is output with an alias, it is equivalent to rownum. It is already used as a column in the result set, therefore, when using rn for determination, do not rename it.

For more details, please continue to read the highlights on the next page:

Related reading:

Stored Procedure ref cursor of the returned result set in Oralce

JDBC calls the Oracle stored procedure to return the result set and obtain the output parameters