Question

oracle存储过程 转为 sql servler存储过程

CREATE OR REPLACE

PROCEDURE PAGINATION

(

pageIndex IN NUMBER DEFAULT 1,

pageSize IN NUMBER,

pageSql IN VARCHAR2,

pageTotal OUT NUMBER,

rs OUT Sys_Refcursor,

recordTotal OUT NUMBER

) AS

v_sql VARCHAR2(1000); ---计算总条数

v_count number; ---总条数

v_Plow number; ---闭区间下限

v_Phei number; ---闭区间上限

BEGIN

-----------取分页总数 -----------------------

v_sql := 'select count(*) from (' || pageSql || ')';

execute immediate v_sql into v_count;

pageTotal := ceil(v_count/pageSize);

recordTotal := v_count;

---------------自己加的:判断pageIndex是否超过页面总数 ----------------

IF pageIndex > pageTotal THEN

BEGIN

v_Phei := pageTotal * pageSize ;

END;

ELSE

v_Phei := pageIndex * pageSize ;

END IF;

---------------显示任意页内容 ----------------

--- v_Phei := pageIndex * pageSize ;---

v_Plow := v_Phei - pageSize + 1;

-----v_sql := 'select * from (select rownum rn,t.* from (' || pageSql || ')t) where rn between ' || v_Plow || ' and ' || v_Phei ;-----

v_sql := 'select * from (select rownum rn,t.* from (' || pageSql || ')t) where rn between ' || v_Plow || ' and ' || v_Phei ;

-----------------得到记录集-------------------

open rs for v_sql;

END PAGINATION;

Answer 1

CREATE OR REPLACE PROCEDURE proc_Inf_Add
(
v_Name in varchar2,
v_Size in number,
v_Tname in varchar2,
v_Text in varchar2,
v_Date in varchar2,
v_ture in blob --oracle中没有image，需要在Inf表中修改这个字段的类型，改为blob即可
)
is
v_Tid number;
begin
select Tid into v_Tid from Type where Tname=v_Tname;
if v_Tid is not null then
insert into Inf(Name,Size,Tid,Text,Date,ture) values(v_Name,v_Size,v_Tid,v_Text,to_date(v_Date, 'yyyy-mm-dd'),v_ture);
end if;
end;

追问

oracle转sql server，你的答案还是oracle