📄 functions.out
字号:
ij> select id from allTypes where length(charForBitCol) > 5 order by id;ID -----------1 ij> select id from allTypes where length(varcharCol) > 5 order by id;ID -----------ij> select id from allTypes where length(varcharForBitCol) > 5 order by id;ID -----------ij> select id from allTypes where length(longVarcharCol) > 5 order by id;ID -----------1 ij> select id from allTypes where length(blobCol) > 5 order by id;ID -----------ij> select id from allTypes where length(clobCol) > 5 order by id;ID -----------1 ij> select id from allTypes where length(dateCol) > 5 order by id;ID -----------ij> select id from allTypes where length(timeCol) > 5 order by id;ID -----------ij> select id from allTypes where length(timestampCol) > 5 order by id;ID -----------1 ij> -- try an expressionselect id, length( charCol || 'abc') from allTypes order by id;ID |2 -----------------------1 |4 2 |NULL ij> -- bug 5761 & 5627-- JDBC escape length function has the following behavior-- LENGTH (RTRIM (xxxx))values {FN LENGTH('xxxx ')};1 -----------4 ij> values {FN LENGTH(' xxxx ')};1 -----------5 ij> values {FN LENGTH(' xxxx ')};1 -----------6 ij> values {FN LENGTH(' xxxx ')};1 -----------7 ij> CREATE FUNCTION SV_RNNI(P1 INT) RETURNS VARCHAR(10) RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION SV_CNI(P1 INT) RETURNS VARCHAR(10) CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION SV_DEF(P1 INT) RETURNS VARCHAR(10) EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION MAX_RNNI(P1 INT, P2 INT) RETURNS INT RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION MAX_CNI(P1 INT, P2 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION MAX_DEF(P1 INT, P2 INT) RETURNS INT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> VALUES SV_RNNI(3);1 --------------------------------------------------------------------------------------------------------------------------------3 ij> VALUES SV_CNI(4);1 --------------------------------------------------------------------------------------------------------------------------------4 ij> VALUES SV_DEF(5);1 --------------------------------------------------------------------------------------------------------------------------------5 ij> create table SV_TAB(I INT);0 rows inserted/updated/deletedij> insert into SV_TAB values(null);1 row inserted/updated/deletedij> insert into SV_TAB values(7);1 row inserted/updated/deletedij> select SV_RNNI(I) from SV_TAB where I = 7;1 --------------------------------------------------------------------------------------------------------------------------------7 ij> select SV_CNI(I) from SV_TAB where I = 7;1 --------------------------------------------------------------------------------------------------------------------------------7 ij> select SV_DEF(I) from SV_TAB where I = 7;1 --------------------------------------------------------------------------------------------------------------------------------7 ij> select SV_RNNI(I) from SV_TAB where I IS NULL;1 --------------------------------------------------------------------------------------------------------------------------------NULL ij> select SV_CNI(I) from SV_TAB where I IS NULL;1 --------------------------------------------------------------------------------------------------------------------------------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> select SV_DEF(I) from SV_TAB where I IS NULL;1 --------------------------------------------------------------------------------------------------------------------------------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> VALUES MAX_RNNI(67, 12);1 -----------67 ij> VALUES MAX_RNNI(-3, -98);1 ------------3 ij> VALUES MAX_CNI(5, 3);1 -----------5 ij> VALUES MAX_DEF(99, -45);1 -----------99 ij> select MAX_RNNI(5, I) from SV_TAB where I = 7;1 -----------7 ij> select MAX_CNI(6, I) from SV_TAB where I = 7;1 -----------7 ij> select MAX_DEF(2, I) from SV_TAB where I = 7;1 -----------7 ij> select MAX_RNNI(I, 34) from SV_TAB where I = 7;1 -----------34 ij> select MAX_CNI(I, 24) from SV_TAB where I = 7;1 -----------24 ij> select MAX_DEF(I, 14) from SV_TAB where I = 7;1 -----------14 ij> select MAX_RNNI(5, I) from SV_TAB where I IS NULL;1 -----------NULL ij> select MAX_CNI(6, I) from SV_TAB where I IS NULL;1 -----------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> select MAX_DEF(2, I) from SV_TAB where I IS NULL;1 -----------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> select MAX_RNNI(I, 34) from SV_TAB where I IS NULL;1 -----------NULL ij> select MAX_CNI(I, 24) from SV_TAB where I IS NULL;1 -----------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> select MAX_DEF(I, 14) from SV_TAB where I IS NULL;1 -----------ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'.ij> CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INTREADS SQL DATAEXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows'LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE FUNCTION FN_ABS(P1 INT) RETURNS INTNO SQLRETURNS NULL ON NULL INPUTEXTERNAL NAME 'java.lang.Math.abs'LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> select FN_ABS(i) FROM SV_TAB;1 -----------NULL 7 ij> select COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB;1 -----------2 2 ij> select FN_ABS(i), COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB;1 |2 -----------------------NULL |2 7 |2 ij> DROP FUNCTION SV_RNNI;0 rows inserted/updated/deletedij> DROP FUNCTION SV_CNI;0 rows inserted/updated/deletedij> DROP FUNCTION SV_DEF;0 rows inserted/updated/deletedij> DROP FUNCTION MAX_RNNI;0 rows inserted/updated/deletedij> DROP FUNCTION MAX_CNI;0 rows inserted/updated/deletedij> DROP FUNCTION MAX_DEF;0 rows inserted/updated/deletedij> DROP FUNCTION FN_ABS;0 rows inserted/updated/deletedij> DROP FUNCTION COUNT_ROWS;0 rows inserted/updated/deletedij> DROP TABLE SV_TAB;0 rows inserted/updated/deletedij> -- check MODIFIES SQL DATA not allowed with FUNCTIONCREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INTMODIFIES SQL DATAEXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows'LANGUAGE JAVA PARAMETER STYLE JAVA;ERROR 42X01: Syntax error: MODIFIES SQL DATA.ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_D(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20)LANGUAGE JAVA PARAMETER STYLE JAVA NO SQLEXTERNAL NAME 'java.lang.Integer.toString(int, int)';0 rows inserted/updated/deletedij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_NS(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20)LANGUAGE JAVA PARAMETER STYLE JAVA NO SQLEXTERNAL NAME 'java.lang.Integer.toString';0 rows inserted/updated/deletedij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_E() RETURNS VARCHAR(20)LANGUAGE JAVA PARAMETER STYLE JAVA NO SQLEXTERNAL NAME 'java.lang.Integer.toXXString()';0 rows inserted/updated/deletedij> -- these are okVALUES SIGNATURE_BUG_DERBY_258_NS(2356, 16);1 --------------------------------------------------------------------------------------------------------------------------------934 ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 10);1 --------------------------------------------------------------------------------------------------------------------------------2356 ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 2);1 --------------------------------------------------------------------------------------------------------------------------------100100110100 ij> -- Must resolve as aboveVALUES SIGNATURE_BUG_DERBY_258_D(2356, 16);1 --------------------------------------------------------------------------------------------------------------------------------934 ij> -- no method to resolve to (with specified signature)VALUES SIGNATURE_BUG_DERBY_258_E();ERROR 42X50: No method was found that matched the method call java.lang.Integer.toXXString(), tried all combinations of object and primitive types and any possible type conversion for any parameters the method call may have. The method might exist but it is not public and/or static, or the parameter types are not method invocation convertible.ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_D;0 rows inserted/updated/deletedij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_E;0 rows inserted/updated/deletedij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_NS;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -