1:oracle函数写法
create or replace FUNCTION getmaxcatid(cat1 IN NUMBER,
cat2 IN NUMBER,
cat3 IN NUMBER)
RETURN NVARCHAR2
AS
--示例,从两张表中查找123分类的最大流水并加1;
nvitem NVARCHAR2(30);
nvcat1 NVARCHAR2(10);
nvcat2 NVARCHAR2(10);
nvcat3 NVARCHAR2(10);
avg1 NVARCHAR2(50);
avgtemp NVARCHAR2(50);
avgall NVARCHAR2(50);
BEGIN
--处理1的类型;
IF cat1 > 0 AND cat1 < 10 THEN
nvcat1 := substr('0' || cat1, 1, 5);
ELSIF cat1 < 100 AND cat1 > 9 THEN
nvcat1 := cat1;
END IF;
--处理2的类型;
IF cat2 > 0 AND cat2 < 10 THEN
nvcat2 := substr('00' || cat2, 1, 5);
ELSIF cat2 < 100 AND cat2 >= 10 THEN
nvcat2 := substr('0' || cat2, 1, 5);
ELSIF cat2 < 1000 AND cat2 >= 100 THEN
nvcat2 := cat2;
END IF;
--处理3的类型;
IF cat3 > 0 AND cat3 < 10 THEN
nvcat3 := substr('00' || cat3, 5);
ELSIF cat3 < 100 AND cat3 >= 10 THEN
nvcat3 := substr('0' || cat3, 5);