sql存储过程算法

MSSQL执行 ： exec proc_NAME

ORACLE ：

begin
proc_NAME;
commit;
end;

1.求素数

MSSQL; 

CREATE proc [dbo].[EXEC003] as
declare @num int;
begin
set @num = 10;
while (@num > 0 )
BEGIN
   IF(@num %2 = 0 )
      BEGIN
      PRINT(@NUM);
      END
   SET @num -= 1;
END
end

ORACLE:

create or replace procedure EXEC003 as 
 List int := 10;
 begin

 while( List > 0 ) loop
    if mod(List,2)  = 0 then
           Dbms_Output.put_line(List);
          end if;
    List := List - 1;
 end loop;
 end;


--测试
begin

exec003 ;
commit;
end;

2.费布拉切数列

MSSQL

alter proc Exec004
as
declare @num int;
declare @Sum int;
declare @min int;
declare @max int;
declare @curr int;
set @num = 8;
set @min = 1;
set @max = 1;
set @curr = 2;
set @Sum = 1;
--1、2、3、5、8、13、21、34
begin
    if( @num = 0)
       begin
        set @max = 0;
       end
     else if (@num = 1)
        begin
        set @max = 1;
        end
     else 
         begin 
    while ( @curr <= @num ) 
      begin    
        set @Sum = @max + @min;
        set @min = @max;
        set @max = @Sum;
        set @curr += 1;     
      end
          end
      print( @max);
end

ORACLE

create or replace procedure Exec004
as
curr  int := 2;
max1  int := 1;
min1  int := 1;
sum1   int := 1;
num   int := 1;
begin
   if(num = 0 ) then
      begin
        sum1 := 0;
      end;
    elsif  ( num = 1 ) then
       begin
         sum1 := 1;
       end;
    else 
           while( curr <= num ) loop
              sum1 := max1 + min1;
              min1 := max1;
              max1 := sum1;
              curr := curr + 1;
           end loop;
    end if;
    DBMS_OUTPUT.PUT_LINE(sum1);
end;

3.判断表是否10列，有10列则打出制定列数

MYYSQL

alter proc forEachData(@num int)  as
declare @curr int;
declare @temId varchar(20);
begin
set @curr = 1;
   if( (select COUNT(*) from jcms_module_article ) > 0) 
      begin
            print('this number gt 10');
            while(@curr <@num)
              begin
               select @temId = m.Title from ( select ROW_NUMBER() OVER (ORDER BY Id) rn,Title  from jcms_module_article t) m where m.rn = @curr;
               print( Convert( varchar(20), @curr) + ':' + @temId);
               set @curr += 1;
              end
      end
   else 
      print('this number lt 10');
end

ORACLE

create or replace procedure forEachData(num in out int) as
curr int :=1  ;
temValue varchar(100);
countNum int;

begin
   select  count( *) into countNum  from cod_biz_codes;
   if( curr <= 10) then
       begin
         Dbms_Output.put_line('is number gt 10');

         while( curr <= num ) loop
           select  t.doc_spec into temValue from (  select row_number() over( order by doc_type) rn , DOC_SPEC  from cod_biz_codes ) t where t.rn = curr ;
           Dbms_Output.put_line( curr || ','|| temValue);
           curr := curr + 1;           
         end loop;
       end;
    else
       DBMS_OUTPUT.put_line('is number lt 10');
    end if;
end;

/*
测试：
declare num int := 10;
begin
forEachData(num);
end;

*/