8.哈哈,今天帮别人弄存储过程的经验@04.08.05
搞了半天搞不定，一查原来的文档才了，原来tmd该死的DB2的存储过程，是转换为C后，进行编译的。因此在数据库服务器上要安装一个C编译器才能完成存储过程的编译。并且需要使用DB2SET命令，设置DB2_SQLROUTINE_COMPILER_PATH指向C编译器的安装路径。如:db2set
DB2_SQLROUTINE_COMPILER_PATH=E:\ProgramFiles\MicrosoftVisualStudio\VC98\Bin\vcvars32.bat
9.对变量的赋值不能用select ..into ..方式而要用set v=(select ..)的方式，代码示例如下。
| drop function SXFM.ISORDERSUBMITDATE; CREATE FUNCTION SXFM.ISORDERSUBMITDATE(IN_ROW_ID DECIMAL(16,0)) RETURNS DATE LANGUAGE SQL BEGIN ATOMIC DECLARE V_SUBMIT_DATE DATE; DECLARE V_SELL_ID DECIMAL(16, 0); DECLARE V_BUY_ID DECIMAL(16, 0); set V_SELL_ID = (SELECT COALESCE(RECEIVE_ID,-1) FROM IS_ORDER WHERE ROW_ID=IN_ROW_ID); set V_BUY_ID = (SELECT COALESCE(PAY_ID,-1) FROM IS_ORDER WHERE ROW_ID=IN_ROW_ID); set V_SUBMIT_DATE = (SELECT DATE(MAX(A.SUBMIT_DATE)) FROM AM_AUDIT_QUEUE A,SM_USER B,SM_USER C WHERE A.TABLE_CODE='IS_ORDER' AND A.TABLE_ROW_ID=IN_ROW_ID AND A.AUDIT_EMP_ID=C.ROW_ID AND C.BRANCH_ID=V_BUY_ID --审核方为付款方 AND A.SUBMIT_EMP_ID=B.ROW_ID AND B.BRANCH_ID=V_SELL_ID); --提交方为收款方 RETURN V_SUBMIT_DATE; END; #SYNC 10; |
10.DB2的游标打开后遇到commit和rollback默认是会关闭的。保持游标打开的方法是在定义游标时加上with hold选项
11.F:导出某张表的数据，且该表包含long varchar型数据，该如何操作Q:Export:db2 connect to [dbname] user [user] using [password]db2move [dbname] export -tn [tablename] -u [user] -p[password] (单表)db2move [dbname] export -tn [tablename1,tablename2,...] -u [user] -p [password] (多表)Import:db2move [dbname] import