求助修改一下FUNCTION

 
create function toBinary ( in_num bigint )
returns varchar(100)
language sql
begin
declare global temporary table gbl_temp (idx smallint,val varchar(2)) on commit delete rows not logged;  -- 创建临时表
declare n smallint default 0;  -- 插入临时表字段，用于游标排序
declare tmp1 smallint;  -- 余数
declare tmp2 integer default 0;  -- 商
declare r_value varchar(100);  -- 返回值
declare mycur cursor for select val from gbl_temp order by idx; -- 游标拼接返回值
set tmp2 = in_num;
 
while tmp2 >= 2 do
set tmp1 = mod(tmp2,2);
set tmp2 = floor(tmp2/2);
insert into gbl_temp values(n,tmp1);
set n = n+1;
if tmp2 < 2 then
insert into gbl_temp values(n,tmp2);
end if;
end while;
 
if in_num < 2 then
   return char(in_num);
end if;
 
for mycur do
set r_value = mycur.val||value(r_value,'');
end for;
return r_value;
end;

 

create procedure to_binary (in in_num bigint, out out_num varchar(100))
dynamic result sets 1
language sql
begin
declare global temporary table gbl_temp (idx smallint,val varchar(2)) on commit delete rows not logged;
declare n smallint default 0;
declare tmp1 smallint default 0;
declare tmp2 bigint default 0;
declare r_value varchar(100) default '';
declare mycur cursor for select val from gbl_temp order by idx;
set tmp2 = in_num;
while tmp2 >= 2 do
        set tmp1 = mod(tmp2,2);
        set tmp2 = floor(tmp2/2);
        insert into gbl_temp values(n,tmp1);
        set n = n+1;
        if tmp2 < 2 then
                insert into gbl_temp values(n,tmp2);
        end if;
end while;
if in_num < 2 then
        set out_num = char(in_num);
else  
        for mycur do
                set r_value = mycur.val||value(r_value,'');
        end for;
        set out_num = r_value;
end if;
end;