两个创建function的脚本,db2cmd下面都执行都成功了,
db2 -td@ -vf o2.sql
db2 -tvf create_udf.sql
但是控制中心里面没有啊!
脚本一02.sql:
----部署存储过程和用户自定义函数
@
CONNECT TO GWFX
@
SET CURRENT SCHEMA 'db2admin'
@
SET CURRENT PATH SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,DB2ADMIN
@
------------
--部署用户函数(2个)
------------
@
DROP FUNCTION FUN_DIV (DECIMAL(10, 2), DECIMAL(10, 2))
@
DROP FUNCTION FUN_INLIST (VARCHAR(1000), VARCHAR(20000))
@
CREATE FUNCTION FUN_DIV ( N1 DECIMAL(10,2), N2 DECIMAL(10,2))
RETURNS DECIMAL(10,4)
LANGUAGE SQL
--此函数用在存储过程中,处理分母为0的情况
--N1为分子,N2为分母
BEGIN ATOMIC
DECLARE N DECIMAL(10,2) DEFAULT 1.0000;
DECLARE TEMP DECIMAL(10,4) DEFAULT 0.0000;
IF N2 <>0 THEN
SET N=N2;
SET TEMP=round (N1/N,4);
ELSE
SET TEMP=0.0000;
END IF;
RETURN TEMP;
END
@
CREATE FUNCTION FUN_INLIST ( S1 VARCHAR(1000), S2 VARCHAR(20000))
RETURNS INTEGER
LANGUAGE SQL
NO EXTERNAL ACTION
--此函数处理s2中ids(list)是否 包含s1中各id的情况
--s1="id1,id2",s2="id1,id2...."
--返回大于0包含,0 不包含
BEGIN ATOMIC
DECLARE V_TEMP VARCHAR(2000);--
DECLARE V_POS INT;--
DECLARE V_LENGTH INT;--
DECLARE V_CURRENT VARCHAR(2000);--
DECLARE V_STR VARCHAR(32);--
DECLARE V_COUNT INT;--
DECLARE V_RESULT INTEGER DEFAULT 0;
SET V_TEMP = ','||S1||',' ; --
SET V_LENGTH = LENGTH(V_TEMP); --
WHILE V_LENGTH>0 DO
SET V_POS = POSSTR(V_TEMP,','); --
SET V_CURRENT = SUBSTR( V_TEMP, 1, V_POS-1 ); --
SET V_TEMP = SUBSTR( V_TEMP, V_POS+1 ); --
SET V_LENGTH = LENGTH(V_TEMP); --
--最后一个字段,不再截取
IF(LENGTH(V_CURRENT)>0) THEN
SET V_STR = ','||V_CURRENT||',';--
SET V_RESULT= LOCATE (V_STR ,','||S2||',');
IF (V_RESULT >0) THEN
SET V_LENGTH =0;
END IF;
END IF;
END WHILE;
RETURN V_RESULT;
END
@
脚本2(create_udf.sql):
drop function app_inlist(BIGINT,VARCHAR(20000));
drop function app_inlist(VARCHAR(1000),VARCHAR(20000));
drop function app_div;
call sqlj.remove_jar('APPUDF');
--call sqlj.install_jar('C:/a/appudf.jar', 'APPUDF');
call sqlj.install_jar('file:C:/a/appudf.jar', 'APPUDF');
call sqlj.refresh_classes;
CREATE FUNCTION app_inlist(BIGINT, VARCHAR(20000))
RETURNS INTEGER
EXTERNAL NAME 'APPUDF:cn.gov.pbc.njv.platform.dao.AppUDF!inlist_l'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
NO SQL
EXTERNAL ACTION;
CREATE FUNCTION app_inlist(VARCHAR(1000), VARCHAR(20000))