1. 最简单的动态sql
在SAP HANA中动态sql必须写在store procedure内,下面的code实现将Hello World 插入表中功能:
DROP TABLE t_h;
CREATE COLUMN TABLE t_h(
word varchar(300)
);
DROP PROCEDURE t_hello_world;
CREATE PROCEDURE t_hello_world
LANGUAGE SQLSCRIPT AS
v_sql varchar(100);
BEGIN
v_sql := 'INSERT INTO t_h VALUES(''hello world'')';
exec :v_sql;
END;
CALL t_hello_world;
2. 可以查看结果集的动态sql:
使用Excuete Immediate 可以将result set展现在tab面板上:
DROP TABLE t_h;
CREATE COLUMN TABLE t_h(
word varchar(300)
);
DROP PROCEDURE t_hello_world;
CREATE PROCEDURE t_hello_world
LANGUAGE SQLSCRIPT AS
v_sql varchar(100);
BEGIN
v_sql := 'INSERT INTO t_h VALUES(''hello world'')';
EXEC :v_sql;
EXECUTE IMMEDIATE 'SELECT * FROM t_h';
END;
CALL t_hello_world;
3. 在store procedure中使用dynamic sql的结果集
目前SAP HANA并不支持将dynamic sql的结果集赋值给table变量,目前有一种临时的解决方案,即:首先将结果集插入一个全局的临时表,然后对此临时表进行操作,sample code如下:
DROP TABLE TFR_DYNAMIC_RESULT;
CREATE GLOBAL TEMPORARY TABLE TFR_DYNAMIC_RESULT
( WORD VARCHAR(40));
DROP PROCEDURE TFR_DYNAMIC_SELECT;
CREATE PROCEDURE TFR_DYNAMIC_SELECT
LANGUAGE SQLSCRIPT
AS
BEGIN
TRUNCATE TABLE TFR_DYNAMIC_RESULT;
EXEC 'INSERT INTO TFR_DYNAMIC_RESULT ( SELECT * FROM T_H)';
t_result = SELECT * FROM TFR_DYNAMIC_RESULT;
-- further processing of :t_result
SELECT * FROM :t_result;
END;
CALL TFR_DYNAMIC_SELECT;
关于SAP HANA中的dynamic sql的局限及其替代方法见:
http://scn.sap.com/thread/3300805