DB2 Create function (i)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

CREATE FUNCTION "FAS"."GET_ALL_NAME" ( "A_ID" BIGINT )   RETURNS VARCHAR(9000)   SPECIFIC "SQL131107190046233"--SPECIFIC名称不能与其他FUNCTION的重复   LANGUAGE SQL   NOT DETERMINISTIC   EXTERNAL ACTION   READS SQL DATA   CALLED ON NULL INPUT   INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST   INHERIT SPECIAL REGISTERS BEGIN ATOMIC   --声明变量   DECLARE A_CURR_ID BIGINT DEFAULT 0 ;   DECLARE A_CURR_NAME VARCHAR(50) DEFAULT ‘‘ ;   DECLARE SRETURN VARCHAR(1000) DEFAULT ‘‘ ;   --设置变量值   SET A_CURR_NAME= (SELECT NAME FROM FA_DEPARTMENT WHERE ID=A_ID);--当前名称   SET A_CURR_ID= (SELECT PARENT_ID FROM FA_DEPARTMENT WHERE ID=A_ID);--当前名称   SET SRETURN=‘_‘||A_CURR_NAME;   --WHILE循环   WHILE A_CURR_ID>0 DO   SET A_CURR_NAME= (SELECT NAME FROM FA_DEPARTMENT WHERE ID=A_CURR_ID);--当前名称   SET A_CURR_ID= (SELECT PARENT_ID FROM FA_DEPARTMENT WHERE ID=A_CURR_ID);--当前名称    SET SRETURN=‘_‘||A_CURR_NAME||SRETURN;   END WHILE;   --返回值   RETURN SRETURN; END;

The example passes in the parameter a_id and queries the relevant data by A_ID. and returns the result in string format.

This method can be called in an SQL statement. As follows

?

1	select GET_ALL_NAME(21) fromSYSIBM.SYSDUMMY1

The

can be used as a column field value for a SQL query statement, or in the WHERE clause