mysql到达梦存储过程常见问题
1.1 变量的使用
create or replace procedure e_test()
as
begin
set strsql ='select id into @eid from test2 order by id limit 1,10';
insert into test select id,name from test2 where id in (eid);
set stst =strsql;
execute stst;
end;
变量需要提前定义
create or replace procedure e_test()
as
strsql varchar(1000);
stst varchar(1000);
eid int;
begin
set strsql ='select id into eid from test2 order by id limit 1,10';
insert into test select id, name from test2 where id in (eid);
set stst =strsql;
execute stst;
end;
执行变量不能使用execute ,采用print
create or replace procedure e_test()
as
strsql varchar(1000);
stst varchar(1000);
eid int;
begin
set strsql ='select id into eid from test2 order by id limit 1,10';
insert into test select id, name from test2 where id in (eid);
set stst =strsql;
print stst;
end;
1.2 Interval包含变量计算
mysql
select date_format(date_add(current_date(),interval -DAY(current_date())+1 day),'%Y-%M-%D');
达梦interval只能接常量字符串，不支持计算表达式
select date_format (trunc(current_date()-DAY(current_date())+1) ,'%Y-%M-%D') ;
1.3 DATEDIFF 函数
mysql
SELECT DATEDIFF(date'2003-01-01', DATE '2002-01-01');
达梦
SELECT DATEDIFF(day,DATE'2002-01-01', DATE '2003-01-01');
1.4 Delete using 连表删除
mysql
delete from s using st1 s,(select id from st2 where cbf=0 and status=1)z where s.id=z.id
达梦
delete from st1 s where exists (select 1 from st2 z where s.id=z.id and z.cbf=0 and z.status=1)
1.5 GROUP_CONCAT
mysql
SELECT GROUP_CONCAT(name SEPARATOR ';') from wm_test;
达梦
select replace(WM_CONCAT(name),',', ';') from wm_test;
select listagg( name,';') within group (order by name) from wm_test
--如报字符串截断错误，改成listagg2
select listagg2( name,';') within group (order by name) from wm_test
1.6 convert
mysql
select convert('12',signed);
select convert('2012-01-01',date)
达梦
select convert(integer,'12');
select convert(date ,'2012-01-01')
1.7 information_schema.columns
mysql
select table_name ,CHARACTER_MAXIMUM_LENGTH from information_schema.columns
where table_schema ='TEST' and CHARACTER_MAXIMUM_LENGTH is not null;
达梦
select u.table_name ,max(u.data_length) from dba_tab_columns u,dba_tables d
where u.table_name =d.table_name and d.owner='DMHR' and u.data_type='VARCHAR' group by u.table_name;
1.8 FIND_IN_SET
mysql
select t.* from TABLE_T t where find_in_set('6', t.ids) > 0
达梦老版本不支持，新版本执行，改写如下
https://eco.dameng.com/community/article/a92bc26bbf73142116a7ec0d3daadcdc
1.9 Concat
mysql
select concat('a');
达梦需要两个参数
select concat('a','');
1.10 存储过程动态sql
create or replace procedure atmp ()
as
begin
drop table if exists test;
create table test (id int,name varchar(50));
insert into test values (1,'aa'),(2,'bb');
commit;
end;
将ddl语句改成动态sql之后，编译不报错，但执行报错
create or replace procedure atmp ()
as
begin
execute immediate 'drop table if exists test';
execute immediate 'create table test (id int,name varchar(50))';
insert into test values (1,'aa'),(2,'bb');
commit;
end;
需要存储过程里面的表全部改写成动态sql
create or replace procedure atmp ()
as
begin
execute immediate 'drop table if exists test';
execute immediate 'create table test (id int,name varchar(50))';
execute immediate 'insert into test values (1,''aa''),(2,''bb'')';
commit;
end;
--如果动态sql语句里面语法错误，编译不报错，但调用存储过程时会报错。如下所示
create or replace procedure atmp ()
as
begin
execute immediate 'drop table if exists test';
execute immediate 'create table test (id int(8),name varchar(50))';
execute immediate 'insert into test values (1,''aa''),(2,''bb'')';
commit;
end;
1.11 存储过程调用包含临时表
创建两个存储过程，存储过程tmpc2 包含创建临时表tmp2和调用存储过程tmpc，而存储过程tmp包含临时表tmp2的使用。编译存储过程tmpc2 时会提示存储过程tmpc无效，编译存储过程tmpc时，会提示tmp2表不存在，陷入死循环。
create or replace procedure tmpc2 ()
as
begin
execute immediate 'drop table if exists tmp2';
execute immediate 'create temporary table tmp2( id int,name varchar(200))';
call tmpc();
end;
create or replace procedure tmpc ()
as
begin
insert into tmp2 values (1,'aa');
end;
将临时表改成普通表
drop table if exists tmp2;
create temporary table tmp2( id int,name varchar(200));
create or replace procedure tmpc ()
as
begin
insert into tmp2 values (1,'aa');
commit;
end;
create or replace procedure tmpc2 ()
as
begin
execute immediate 'truncate table tmp2';
call tmpc();
end;
