sql存储过程算法

MSSQL执行： exec proc_NAME

ORACLE ：

begin
proc_NAME;
commit;
end;

1.求素数

MSSQL;

 1 CREATE proc [dbo].[EXEC003] as
 2 declare @num int;
 3 begin
 4 set @num = 10;
 5 while (@num > 0 )
 6 BEGIN
 7    IF(@num %2 = 0 )
 8       BEGIN
 9       PRINT(@NUM);
10       END
11    SET @num -= 1;
12 END
13 end

View Code

ORACLE:

 1 create or replace procedure EXEC003 as 
 2  List int := 10;
 3  begin
 4 
 5  while( List > 0 ) loop
 6     if mod(List,2)  = 0 then
 7            Dbms_Output.put_line(List);
 8           end if;
 9     List := List - 1;
10  end loop;
11  end;
12 
13 
14 --测试
15 begin
16 
17 exec003 ;
18 commit;
19 end;

View Code

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

View Code

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;

View Code

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

View Code

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;

*/

View Code