DB2 Create function (ii)

DB2 Create function (a), which describes the use of function content as a field value, or as a where condition.

DB2 creates function (ii), which describes the creation of the returned content as a table collection. A call example is shown below

?

1	select * fromtable(GET_EFFECTIVE_USER_ID(21))--GET_EFFECTIVE_USER_ID为建立的function函数。

The created function examples are as follows:

?

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 29 30 31 32 33 34 35

CREATE FUNCTION "FAS"."GET_EFFECTIVE_USER_ID" ( "A_USER_ID" BIGINT )   RETURNS TABLE( "ID" BIGINT )--注意返回的为table(col1,col2,col3) 括号里返回的表列，要一一对应   SPECIFIC "SQL131104183947721"--唯一值   LANGUAGE SQL   DETERMINISTIC   NO EXTERNAL ACTION   READS SQL DATA   CALLED ON NULL INPUT   INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST   INHERIT SPECIAL REGISTERS RETURN--直接返回，返回的为之后查询的结果集   SELECT CASE           WHEN (CURR_ROLE_ID = 2 OR CURR_ROLE_ID = 7) AND USER_ID <> A_USER_ID              THEN 0           ELSE USER_ID        END ID   FROM (SELECT A.ID USER_ID, B.ROLE_ID, C.ROLE_ID CURR_ROLE_ID           FROM FA_USER_INFO A,                FA_USER_ROLE B,                (SELECT ROLE_ID                   FROM FA_USER_ROLE                  WHERE USER_ID = A_USER_ID) C          WHERE A.DEPARTMENT_ID IN (                   SELECT DEPARTMENT_ID                     FROM FA_DEPARTMENT A, FA_DEPARTMENT_INFO B                    WHERE A.ID = B.DEPARTMENT_ID                      AND B.PARENT_DEPARTMENT_ID = (SELECT DEPARTMENT_ID                                                      FROM FA_USER_INFO                                                     WHERE ID = A_USER_ID)                   UNION                   SELECT DEPARTMENT_ID                     FROM FA_USER_INFO                    WHERE ID = A_USER_ID)            AND A.ID = B.USER_ID)   END;--结束标记

When function returns to table, it is important to note that there can be only one query statement after return. Cannot have logical judgment statements like if

For example, create Funtion get_name (Var), return If ... then sql...else sql...end if; End; This is a pass-through.