情况说明
由于项目的数据库要从oracle换成mysql,因此涉及到的函数,需要重新编写以下
Oracle函数
create or replace function createmaxno(v_notype VARCHAR, v_nolimit VARCHAR,v_pad VARCHAR,v_len integer)
return VARCHAR is
PRAGMA AUTONOMOUS_TRANSACTION;
v_maxno VARCHAR(120);
v_val number := 0;
v_suffix VARCHAR(100);
v_limit VARCHAR(100);
begin
v_suffix := v_nolimit;
v_limit := v_nolimit;
if v_nolimit is null then
v_limit := 'SN';
v_suffix := '';
end if;
select max(sm.maxno) into v_val from ficrm_sys_maxno sm where sm.notype=upper(v_notype) and sm.nolimit=v_limit;
if v_val is null then
insert into ficrm_sys_maxno(NOTYPE,NOLIMIT,MAXNO,CREATEOPERATOR,CREATEOPTDATE,CREATEOPTTIME,LASTOPERATOR,LASTOPTDATE,LASTOPTTIME)
values (upper(v_notype),v_limit,1,'admin',to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),to_char(sysdate,'hh24-mi-ss'),'admin',to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),to_char(sysdate,'hh24-mi-ss'));
commit;
v_val := '1';
else
v_val := v_val+1;
update ficrm_sys_maxno set MAXNO=v_val where NOTYPE=upper(v_notype) and NOLIMIT=v_limit;
commit;
end if;
if v_suffix is null then
v_maxno := lpad(v_val,v_len,v_pad);
else
v_maxno := v_suffix||lpad(v_val,v_len-length(v_suffix),v_pad);
end if;
return(v_maxno);
Exception
when NO_DATA_FOUND THEN
dbms_output.put_line('»ñÈ¡×î´óºÅÂëʧ°Ü!');
end createmaxno;
Mysql函数(更改后)
-- ----------------------------
-- Function structure for createmaxno
-- ----------------------------
DROP FUNCTION IF EXISTS `createmaxno`;
DELIMITER ;;
CREATE FUNCTION `createmaxno`(v_notype VARCHAR(120), v_nolimit VARCHAR(120),v_pad VARCHAR(120),v_len integer) RETURNS varchar(120) CHARSET utf8
BEGIN
DECLARE v_maxno,v_suffix,v_limit VARCHAR(120);
DECLARE v_val int DEFAULT 0;
SET v_suffix = v_nolimit;
SET v_limit = v_nolimit;
if v_nolimit is null
then
set v_limit = 'SN';
SET v_suffix = '';
end if;
select max(sm.maxno) into v_val from ficrm_sys_maxno sm where sm.notype=upper(v_notype) and sm.nolimit=v_limit;
if v_val is null then
insert into ficrm_sys_maxno(NOTYPE,NOLIMIT,MAXNO,CREATEOPERATOR,CREATEOPTDATE,CREATEOPTTIME,LASTOPERATOR,LASTOPTDATE,LASTOPTTIME)
values (upper(v_notype),v_limit,1,'admin',STR_TO_DATE(date_format(SYSDATE(),"%M %d %Y"),"%M %d %Y"),date_format(SYSDATE(),"%H:%i:%s"),'admin',STR_TO_DATE(date_format(SYSDATE(),"%M %d %Y"),"%M %d %Y"),date_format(SYSDATE(),"%H:%i:%s"));
set v_val = 1;
ELSE
set v_val = v_val+1;
update ficrm_sys_maxno set MAXNO=v_val where NOTYPE=upper(v_notype) and NOLIMIT=v_limit;
end if;
if v_suffix is null then
set v_maxno = lpad(v_val,v_len,v_pad);
ELSE
set v_maxno = CONCAT(v_suffix,lpad(v_val,v_len-length(v_suffix),v_pad));
end if;
RETURN (v_maxno);
END
;;
DELIMITER ;
可以看到明显有几个地方不同,返回值、定义变量和赋值
其他小函数
-- ----------------------------
-- Function structure for test
-- ----------------------------
DROP FUNCTION IF EXISTS `test`;
DELIMITER ;;
CREATE FUNCTION `test`(id_index INTEGER) RETURNS int(11)
BEGIN
DECLARE temp INTEGER;
SELECT
COUNT(*) INTO temp
FROM
gzb_table
WHERE
id = id_index;
RETURN temp;
END
;;
DELIMITER ;
-- ----------------------------
-- Function structure for to_char
-- ----------------------------
DROP FUNCTION IF EXISTS `to_char`;
DELIMITER ;;
CREATE FUNCTION `to_char`() RETURNS varchar(120) CHARSET utf8
BEGIN
RETURN (SELECT date_format(SYSDATE(),"%Y%m%d") from DUAL);
END
;;
DELIMITER ;
author:su1573
鄙人记录生活点滴,学习并分享,请多指教!!!
如需交流,请联系 sph1573@163.com,鄙人看到会及时回复