Oracle stored procedure returns recordset

Oracle stored procedure returns recordset
the
Oracle’s stored procedure returns a recordset, the key point is to use a cursor.
the
You must know a lot about the cursor of the database. We can control the cursor to perform various convenient operations through OPEN, FETCH, and CLOSE operations. I will not repeat the examples in this regard. We are now going to introduce the cursor variable (cursor variable). Like cursors, cursor variables also point to the current row of a query result set. The difference is that cursor variables can be opened for any type-compatible query, rather than being bound to a specific query. With cursor variables, you can gain more convenience in data extraction from the database.
The first is to create a table.
create TABLE LIHUAN.BILL_POINTS
( www.2cto.com
POINTS_ID NUMBER(10,0) NOT NULL,
CUSTOMER_ID NUMBER(10,0) NOT NULL,
BILL_POINT_NO NUMBER(2,0) DEFAULT 1 NOT NULL,
CONSTRAINT PK_BILL_POINTS PRIMARY KEY (POINTS_ID)
)
/
Second, build a PACKAGE. the
create OR REPLACE PACKAGE LIHUAN.YY_PKG_BILL_POINT_NO/*Get all billing serial numbers of users*/
IS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE BILL_POINT_NO(P_CUSTOMER_ID BILL_POINTS.CUSTOMER_ID%TYPE,
Re_CURSOR OUT T_CURSOR);
END;
/
Again, build a PACKAGE BODY.
create OR REPLACE PACKAGE BODY LIHUAN.YY_PKG_BILL_POINT_NO/*Get all billing serial numbers of users*/ www.2cto.com
IS
PROCEDURE BILL_POINT_NO(P_CUSTOMER_ID BILL_POINTS.CUSTOMER_ID%TYPE,
Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select Bill_Point_No from BILL_POINTS where CUSTOMER_ID =P_CUSTOMER_ID;
Re_CURSOR := V_CURSOR;
END;
END;
/
the
the
Author szstephenzhou