Details about Bind Variables.doc

Using Bind Variables Suppose that you want to be able to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus. Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms. Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in PL/SQL subprograms that you run in SQL*Plus. Creating Bind Variables You create bind variables in SQL*Plus with the VARIABLE command. For example VARIABLE ret_val NUMBER This command creates a bind variable named ret_val with a datatype of NUMBER. Referencing Bind Variables You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example :ret_val := 1; To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example: VARIABLE ret_val NUMBER BEGIN :ret_val:=4; END; / PL/SQL procedure successfully completed. This command assigns a value to the bind variable named ret_val. Displaying Bind Variables To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example: PRINT RET_VAL RET_VAL ---------- 4 This command displays a bind variable named ret_val. Using REFCURSOR Bind Variables SQL*Plus REFCURSOR bind variables allow SQL*Plus to fetch and format the results of a SELECT statement contained in a PL/SQL block. REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This allows you to store SELECT statements in the database and reference them from SQL*Plus. A REFCURSOR bind variable can also be returned from a stored function. Example 3-17 Creating, Referencing, and Displaying REFCURSOR Bind Variables To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype VARIABLE employee_info REFCURSOR Next, enter a PL/SQL block that uses the bind variable in an OPEN... FOR SELECT statement. This statement opens a cursor variable and executes a query. In this example we are binding the SQL*Plus employee_info bind variable to the cursor variable. BEGIN OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ; END; / PL/SQL procedure successfully completed. The results from the SELECT statement can now be displayed in SQL*Plus with the PRINT command. PRINT employee_info EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block must be executed again before using PRINT. Example 3-18 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 cv_types AS TYPE EmpInfoTyp is REF CURSOR RETURN emp%ROWTYPE; END cv_types;/ Package created. Next, create the stored procedure containing an OPEN... FOR SELECT statement. CREATE OR REPLACE PROCEDURE EmpInfo_rpt (emp_cv IN OUT cv_types.EmpInfoTyp) AS BEGIN OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW - WHERE JOB_ID='SA_MAN' ; END; / Procedure created. Execute the procedure with a SQL*Plus bind variable as the parameter. VARIABLE odcv REFCURSOR EXECUTE EmpInfo_rpt(:odcv) PL/SQL procedure successfully completed. Now print the bind variable. PRINT odcv EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 The procedure can be executed multiple times using the same or a different REFCURSOR bind variable. VARIABLE pcv REFCURSOR EXECUTE EmpInfo_rpt(:pcv) PL/SQL procedure successfully completed. PRINT pcv EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 Example 3-19 Using REFCURSOR Variables in Stored Functions Create a stored function containing an OPEN... FOR SELECT statement: INCLUDEPICTURE "http://www.cs.uvm.edu/oracle9doc/server.901/a88827/sqlkey.gif" \* MERGEFORMATINET CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN - cv_types.EmpInfo IS resultset cv_types.EmpInfoTyp; BEGIN OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW - WHERE JOB_ID='SA_MAN' ; RETURN(resultset); END; / Function created. Execute the function. VARIABLE rc REFCURSOR EXECUTE :rc := EmpInfo_fn PL/SQL procedure successfully completed. Now print the bind variable. PRINT rc EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500 The function can be executed multiple times using the same or a different REFCURSOR bind variable. EXECUTE :rc := EmpInfo_fn PL/SQL procedure successfully completed. PRINT rc EMPLOYEE_ID SALARY ----------- ---------- 145 14000 146 13500 147 12000 148 11000 149 10500