Sql*Plus User’s Guide and Reference
Download 5.34 Mb. Pdf ko'rish
|
E18404-12
- Bu sahifa navigatsiya:
- Example 5–16 Using REFCURSOR Variables in Stored Functions
Example 5–15
Using REFCURSOR Variables in Stored Procedures A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter has a REF CURSOR type. First, define the type. CREATE OR REPLACE PACKAGE EmpPack AS TYPE EmpInfoTyp IS REF CURSOR; PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp); END EmpPack; / Next, create the stored procedure containing an OPEN... FOR SELECT statement. PL/SQL procedure successfully completed. EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 Package created. Using REFCURSOR Bind Variables 5-24 SQL*Plus User's Guide and Reference CREATE OR REPLACE PACKAGE BODY EmpPack AS PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS BEGIN OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ; END; END; / Execute the procedure with a SQL*Plus bind variable as the parameter. VARIABLE cv REFCURSOR EXECUTE EmpPack.EmpInfoRpt(:cv) Now print the bind variable. PRINT cv The procedure can be executed multiple times using the same or a different REFCURSOR bind variable. VARIABLE pcv REFCURSOR EXECUTE EmpInfo_rpt(:pcv) PRINT pcv Example 5–16 Using REFCURSOR Variables in Stored Functions Create a stored function containing an OPEN... FOR SELECT statement: CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN - cv_types.EmpInfo IS resultset cv_types.EmpInfoTyp; BEGIN Procedure created. PL/SQL procedure successfully completed. EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 PL/SQL procedure successfully completed. EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 Fetching Iterative Results from a SELECT inside a PL/SQL Block Using Scripts in SQL*Plus 5-25 OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN'; RETURN(resultset); END; / Execute the function. VARIABLE rc REFCURSOR EXECUTE :rc := EmpInfo_fn Now print the bind variable. PRINT rc The function can be executed multiple times using the same or a different REFCURSOR bind variable. EXECUTE :rc := EmpInfo_fn Download 5.34 Mb. Do'stlaringiz bilan baham: |
Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2024
ma'muriyatiga murojaat qiling
ma'muriyatiga murojaat qiling