![]() |
以下是笔者根据FB1.5文档译过来的. EXECUTE STATEMENT语句是一个动态执行的sql语句的语句,用在存储过程与触发器中.他相当于MS SQLSERVER中的exec 语句,不过他比该语句用法丰富,在ms sql中如要实现后面两种EXECUTE STATEMENT语句的功能,必须用exec +游标来实现.他有三种典型的用法. 第一种:语法如下:EXECUTE STATEMENT <string>; <string>就指是sql语句. 样例如下: CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100)) AS DECLARE VARIABLE Sql VARCHAR(1024);--定义字符型变量,长度1024 DECLARE VARIABLE Par INTEGER;--定义整形变量Par BEGIN SELECT MIN(SomeField) FROM SomeTable INTO :Par;--把Sometalbe中somefied最小值的列---赋给par Sql = ’EXECUTE PROCEDURE ’ || Pname || ’(’; Sql = Sql || CAST(Par AS VARCHAR(20)) || ’)’; EXECUTE STATEMENT Sql;--把存储过程名当参数来动态执行 END 第二种,语法如下: EXECUTE STATEMENT <string> INTO :var1, […, :varn] ; 举个例子,我自已写的一个过程用的: CREATE PROCEDURE FIN_FUN0021 ( TABLENAME VARCHAR(20))--传入表名 RETURNS ( NEWKEY BIGINT)--返回这个表的主K的最新值(我建的表所有的主K都有整形) AS DECLARE VARIABLE BASNUM INTEGER; DECLARE VARIABLE CURRSHOPID INTEGER; DECLARE VARIABLE STRMK VARCHAR(50); DECLARE VARIABLE NEWMAXKEY BIGINT; DECLARE VARIABLE STRSQL VARCHAR(250); BEGIN execute procedure FIN_FUN0026('BASNUM') Returning_values (BASNUM);--增长的步长 execute procedure FIN_FUN0026('SHOPID') Returning_values (CurrShopID); --分店号 /*得到传来的表格的主K名*/ select first 1 A.RDB$FIELD_NAME FROM RDB$INDEX_SEGMENTS A, RDB$RELATION_CONSTRAINTS B WHERE B.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' AND UPPER(B.RDB$RELATION_NAME) = UPPER(:TableName) AND A.RDB$INDEX_NAME = B.RDB$INDEX_NAME ORDER BY A.RDB$FIELD_POSITION into :STRMK; if (STRMK IS NOT NULL) then -- 为防止没有主K的情形出现。 BEGIN STRMK=uf_trim(STRMK);--uf_trim为主定义函数,相当于delphi的trim /*得到当前主K的最大值 */ STRSQL='SELECT CAST(MAX(' ||STRMK|| ')' ||' AS bigint) from ' ||TableName ; execute STATEMENT STRSQL INTO :NewMaxKey ; if(NewMaxKey is NULL) then --如果主K是空值 begin NEWKEY = CURRSHOPID; end else --不是空的. NEWKEY = NewMaxkey+BASNUM; END SUSPEND; END 第三种形式:语法: FOR EXECUTE STATEMENT <string> INTO :var1, …, :varn DO <compound-statement>; 例子: /*传入表与广本字段,得到该表中该字段的所有数据*/ CREATE PROCEDURE DynamicSampleThree ( TextField VARCHAR(100), TableName VARCHAR(100)) RETURNS (Line VARCHAR(32000)) AS DECLARE VARIABLE OneLine VARCHAR(100); BEGIN Line = ’’; FOR EXECUTE STATEMENT ’SELECT ’ || TextField || ’ FROM ’ || TableName INTO :OneLine DO IF (OneLine IS NOT NULL) THEN Line = Line || OneLine || ’ ’; SUSPEND; END |
联系客服