SQL:
--前10行
SELECT * FROM JFIT.T_SYS_LOG FETCH FIRST 10 ROWS ONLY ;
--当前时间
SELECT current timestamp FROM sysibm.sysdummy1;
--空值转换
SELECT a.ID,a.CREATE_USER,VALUE(a.REMARK,'') FROM JFIT.T_SYS_LOG a;
--类型转换 V8支持to_char,to_date
SELECT a.ID,a.CREATE_USER,VARCHAR(a.OPER_TIME) FROM JFIT.T_SYS_LOG a;
--清空大表(类型truncate)
-- ALTER TABLE JFIT.T_SYS_LOG active NOT logged initially WITH empty TABLE;
--行ID
SELECT ROWID FROM JFIT.T_SYS_LOG;
--类型转换,类似to_number
SELECT cast('123' AS integer) FROM sysibm.sysdummy1;
--创建类似表(空表)
CREATE TABLE T_SYS_LOG_BAK LIKE T_SYS_LOG ;
--逻辑判断(类似decode)
SELECT CASE WHEN a.status=0 THEN '可用' WHEN a.status=1 THEN '不可用' ELSE '异常' END FROM JFIT.T_SYS_LOG a;
--子查询
WITH a AS (SELECT current timestamp a1 FROM sysibm.SYSDUMMY1) SELECT ID,CREATE_USER,a1 FROM JFIT.T_SYS_LOG,a;
--层次化查询,递归
WITH PAR_CH(ID, PARENT_ID,NAME) AS
( SELECT ID, PARENT_ID,NAME FROM JFIT.T_SYS_RESOURCE
WHERE ID = 1
UNION ALL
SELECT T.ID, T.PARENT_ID,T.NAME
FROM PAR_CH VT, JFIT.T_SYS_RESOURCE T
WHERE VT.ID = T.PARENT_ID
)
SELECT DISTINCT ID,PARENT_ID,NAME
FROM PAR_CH WHERE ID<>1 ORDER BY ID
函数:
--判断是2个不同的ID是否为父子关系。0:是,1:不是
CREATE OR REPLACE FUNCTION FUNC_TEST (
in_opId VARCHAR(20),
in_id VARCHAR(20)
)
RETURNS INTEGER
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_id VARCHAR(20);
DECLARE v_PId VARCHAR(20);
IF in_opId = in_id THEN
RETURN 0;
END IF;
SET v_id = in_id;
WHILE v_id IS NOT NULL DO
SET v_PId =( SELECT PARENT_ID FROM JFIT.T_SYS_RESOURCE WHERE ID = v_id);
IF v_PId = in_opId THEN
RETURN 0;
END IF;
SET v_id = v_PId;
END WHILE;
RETURN 1;
END
@
--判断是否为父子关系
SELECT FUNC_TEST('1','2') FROM sysibm.sysdummy1;
SELECT FUNC_TEST('1','20') FROM sysibm.sysdummy1;
存储过程:
CREATE PROCEDURE BAT_IMPORT2()
LANGUAGE SQL
BEGIN
DECLARE USER_ID VARCHAR(30);
DECLARE APP_USER_ID VARCHAR(30);
DECLARE COUNTS INTEGER;
SELECT COUNT(*) INTO COUNTS FROM
PUB_STRU S,
PUB_ORGAN O,
PUB_USER_EMPLOYEE E,
PUB_USER_IMPORT_TEMP T
WHERE
S.STRU_ID=E.EMPLOYEE_ID
AND S.ORGAN_ID=O.ORGAN_ID
AND S.STRU_NAME=T.USER_ID;
BEGIN
DECLARE CUR CURSOR FOR
SELECT
E.USER_ID,T.USER_NAME
FROM
PUB_STRU S,
PUB_ORGAN O,
PUB_USER_EMPLOYEE E,
PUB_USER_IMPORT_TEMP T
WHERE
S.STRU_ID=E.EMPLOYEE_ID
AND S.ORGAN_ID=O.ORGAN_ID
AND S.STRU_NAME=T.USER_ID;
OPEN CUR;
WHILE COUNTS > 0 DO
FETCH
CUR
INTO
USER_ID,
APP_USER_ID;
INSERT INTO PUB_USER_SSO(APP_CODE,USER_ID,APP_USER_ID,CERTIFICATE)VALUES('10277',USER_ID,APP_USER_ID,NULL);
SET COUNTS = COUNTS - 1;
END WHILE;
END;
END
/
函数请在DB2 命令编辑器或命令窗口下执行。默认是/ 为语句结束符。下面介绍如何设置@为语句结束符。
(1) 若通过“命令编辑器”执行程序,可以在面板下方将“语句终止符”设为"@",
(
启动db2cmd, 输入db2set db2options=-td@ ,
查询db2 list command options可以看到“命令选项 (DB2OPTIONS) = -td@“及”-t 设置语句终止字符 ON“ ,
这样就设置成功。在命令行中每次输入语句后都需要@作为语句终止符。
取消终止符只需在db2cmd中输入db2set db2options=td即可。
)
然后在程序结尾处加上"@",这时就可执行成功;db2 -vf d:\a.sql
(2) 若通过“命令窗口”,将程序保存到文件中并在文件末尾加"@",在命令行窗口执行db2 -td@ -vf [File],执行成功。db2 -td@ -vf d:\a.sql
这里的"-t"选项用来指定命令终止符,"@"是选项值,既是用"@"作为命令终止符;
可通过在“命令窗口”中执行命令"db2 ? options"查看参数的用途。