17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION Fn_GETBH
(
var_orgcode varchar2,
--var_deptID varchar2,
var_DEID varchar2
)
RETURN integer
IS
int_bh integer ;
tablename varchar2(100);
BEGIN
if var_DEID='test' THEN
select max(bh)as bh into int_bh from test t where t.orgcode=var_orgcode and TRUNC(SYSDATE,'YEAR')=TRUNC(T.CREATEDATE,'YEAR');
end if;
if int_bh is null THEN
int_bh:=0;
end if;
int_bh:=int_bh+1;
return int_bh;
End Fn_GETBH;
-- 不用写存储过程 ,或者你改成存储过程也可以的
SQL>
SQL> create table test(id int, create_date date);
Table created
SQL> insert into test
2 select nvl(max(id),0) + 1 ,sysdate from test where create_date > trunc(sysdate);
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> select * from test ;
ID CREATE_DATE
--------------------------------------- -----------
1 2016-06-02
2 2016-06-02
3 2016-06-02
4 2016-06-02
SQL> drop table test purge ;
Table dropped
SQL>