在存储过程中执行sql语句的两种写法:
1、直接在存储过程中写DML语句,如
SELECT * INTO lv_response FROM DBA.Device FOR XML RAW ;
2、根据条件动态拼写出sql语句,如
SET lv_sql = 'SELECT * INTO lv_response FROM DBA.Device FOR XML RAW' ;
EXECUTE IMMEDIATE lv_sql ;
记录一下,贴出完整小例子例子
ALTER PROCEDURE "DBA"."ws_justsy_execute_pro"()
BEGIN
DECLARE lv_sql LONG VARCHAR ;
DECLARE lv_response XML ;
-- SELECT * INTO lv_response FROM DBA.Device FOR XML RAW ;
SET lv_sql = 'SELECT * INTO lv_response FROM DBA.Device FOR XML RAW' ;
EXECUTE IMMEDIATE lv_sql ;
CALL DBA.web_response('',lv_response) ;
END
数据统一输入出口
ALTER PROCEDURE "DBA"."web_response"(
IN p_encoded VARCHAR(10),
IN p_response XML
)
RESULT( response XML )
BEGIN
DECLARE lv_response XML ;
SET lv_response = XMLELEMENT('root',p_response) ;
INSERT INTO DBA.ml_temp(PFName,PFValue) VALUES('web_response',lv_response) ;
IF(p_encoded IS NOT NULL AND p_encoded = '64') THEN
SELECT BASE64_ENCODE(p_response) ;
ELSE
SELECT lv_response ;
ENDIF;
END
执行
CALL "DBA"."ws_justsy_execute_pro"() ;