EXECUTE IMMEDIATE SQLStatement 如何得到它的返回值？？

[msm]

如题，或者其他方法可以得到动态sql语句的返回值？

[hyxdtj]

SQLCA.SQLCode 执行成功返回0

[tchatcha]

fetch

The following is a general overview of the commands covered in this example:

Create a Stored Procedure
Use the CREATE command to create the procedure in the database. You must be connected to the database, and a procedure with the same name can not already exist in the database. If the procedure already exists and you wish to re-create it, it must first be dropped from the database.

Declare a Stored Procedure
Use the DECLARE command to define the variables to be used for input and output parameters. Note that the procedure must already exist in the database before it can be declared successfully. The procedure must be declared in PowerBuilder before it is executed.

Execute a Stored Procedure
Use the EXECUTE command to execute the procedure on the database server.
//兄弟看这理 //兄弟看这理 //兄弟看这理
Fetch the Stored Procedure results //兄弟看这理
Use the FETCH command to obtain the result set, output parameter value(s), and the return value. All rows from the result set are first fetched in a loop, then an additional fetch is used to obtain the output parameter(s) and the return value.

Drop the Stored Procedure
Use the DROP command, if desired, to delete the stored procedure form the database.

Sample PBL Description

In this example, a stored procedure is used to obtain a list of employees in a specific department. The procedure uses an input parameter containing the desired department id, and sets an output parameter to the count of employees in the selected department. In addition, a listbox is populated with the employee names from the procedure result set.

The following sections describes the objects and scripts used to CREATE, DECLARE, EXECUTE, FETCH, and DROP the stored procedure.

Error checking is included. If any of the scripts fail for any reason, a MessageBox is displayed containing error codes and the error message text.

Window instance variables
string is_msg, is_command
int input1
DECLARE myproc PROCEDURE FOR @rv = test_proc
@inparm1 = :input1,
@outparm1 = 0 OUTPUT;

The procedure DECLARE is done as a window instance variable in this example.

Command buttons
These are the command buttons defined on the window. See the next section for the scripts for each of these buttons.

cb_connect Connect to the database
cb_create Create the stored procedure in the database
cb_execute Execute the stored procedure
cb_fetch Fetch the result set, return code, and output parameter
cb_drop Drop the stored procedure from the database

Window Objects
lb_resultset List box that is populated with the data from the result set


Application Script
a_stored_proc

// Open the window
Open(w_syb10_sp)


Window command button Scripts

cb_connect
// Note: Please put appropriate values in these variables
SQLCA.DBMS="syc"
SQLCA.Database="dbname"
SQLCA.LogPass="password"
SQLCA.ServerName="server"
SQLCA.LogId="loginid"
SQLCA.AutoCommit = TRUE

// Connect to the database
CONNECT USING SQLCA;

// Display an error if the connect fails
IF SQLCA.SQLCode <> 0 THEN
is_msg = "SQLCode: " + string(SQLCA.SQLCode)
MessageBox("Connect Error", is_msg)
END IF


cb_create
// Populate the string variable with the SQL command to create the procedure.
// Note that in this example, the procedure will always return a 3 for the
// return value, since it is hardcoded in the procedure definition.

is_command = "create procedure test_proc @inparm1 int, @outparm1 int " + &
"OUTPUT as " + &
"BEGIN " + &
"select @outparm1 = COUNT(emp_id) from employee where dept_id = @inparm1 " + &
"select emp_fname, emp_lname from employee where dept_id = @inparm1 " + &
"return 3 " + &
"END"

// Use dynamic SQL to execute the Create command
EXECUTE IMMEDIATE :is_command;

// Display error information if the create failed
IF SQLCA.SQLCode <> 0 THEN
is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
" DBErrText: " + String(SQLCA.SQLErrText)
MessageBox("CREATE failed", is_msg)
END IF

cb_execute
// Populate the input parameter with the desired value
input1 = 300 // Use Department ID 300 as selection criteria

// Execute the procedure
EXECUTE myproc;

// Display error information if the execute failed
IF SQLCA.SQLCode <> 0 THEN
is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
" DBErrText: " + String(SQLCA.SQLErrText)
MessageBox("Execute failed", is_msg)
END IF

cb_fetch
// Define variables to hold the return value and output parameter
int li_ret_value, output1

// Define variables to hold the result set data from the fetch
string ls_lname, ls_fname

// Loop to Fetch the result set, one row at a time
DO WHILE SQLCA.SQLCode = 0
FETCH myproc INTO :ls_lname, :ls_fname;
IF SQLCA.SQLCode = 0 THEN
// Add the employee name to the listbox
lb_ResultSet.AddItem(ls_fname + ls_lname)
ELSE
IF SQLCA.SQLCode <> 100 THEN
MessageBox("FETCH error", SQLCA.SQLErrText)
Return
END IF
END IF
LOOP

// Fetch the return value and output parameter into the variables
FETCH myproc INTO :li_ret_value, :output1;

// Check to ensure the return value and output parameter were obtained
IF SQLCA.SQLCode = 0 THEN
// Display the return value and output parameter in a message box
is_msg = "Return value: " + string(li_ret_value) + &
" Output parameter value: " + string(li_ret_value)
MessageBox("Results", is_msg)
ELSE
// Display error
is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
" DBErrText: " + String(SQLCA.SQLErrText)
MessageBox("Execute of FETCH failed", is_msg)
END IF

cb_drop
// populate the string variable with the SQL command to drop the procedure
is_command = "DROP PROCEDURE test_proc"

// Use dynamic SQL to execute the drop command
EXECUTE IMMEDIATE :is_command;

// Display error information if the drop failed
IF SQLCA.SQLCode <> 0 THEN
is_msg = "SQLCode: " + String(SQLCA.SQLCode) + " DBCode: " + &
String(SQLCA.SQLDBCode) + " ReturnData: " + SQLCA.SQLReturnData + &
" DBErrText: " + String(SQLCA.SQLErrText)
MessageBox("DROP failed", is_msg)
END IF