Question

SQL SERVER 存储过程，转换成ORACLE存储过程

ALTER procedure [dbo].[UP_UserInfo_List_select]@StartIndex int=1,--分页开始序号 参数@EndIndex int=10,--分页结束序号@WhereSql nvarchar(2000)='',--分页查询条件@RecordCount int=0 output --当前条件下的总记录数asbegindeclare @sql nvarchar(max)declare @total intset @sql='select @n=count(1) from UserInfo a where 1=1 '+@WhereSqlexec sp_executesql @sql, N'@n int output',@total output --@sql相当于procedure主体，N'@n int'相当于参数部分，相当于procedure调用set @RecordCount=@totalset @sql='select * from (select row_number() over(order by t1.UserId) as Row, t1.UserId,t1.UserName,t1.RoleId,t1.IsLock,t1.[Desc] from UserInfo t1 ) a where Row between '+convert(varchar(100), @StartIndex)+' and '+convert(varchar(100), @EndIndex) +@WhereSqlexec(@sql) end-------如何把这个sql server存储过程改成oracle呢？

Answer 1

你这个过程其实就是分页查询用户信息是吧

--利用游标返回结果集
CREATE OR REPLACE PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR;
END pkg_query;


CREATE OR REPLACE PROCEDURE UP_UserInfo_List_select
       (
        WhereSql         in  varchar2,   --查询条件
       橡袭 StartIndex       in  Number,  --当前页
        EndIndex         in  Number,  --每页显示记录条数
        RecordCount      out Number,
        v_cur         弯答   out SYS_REFCURSOR)   --返回的结果集
IS
   v_sql VARCHAR2(1000) := '';      --sql语句
BEGIN
   --查总记录数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM UserInfo WHERE 1=1';
   IF WhereSql IS NOT NULL or WhereSql <> '' THEN
       v_sql := v_sql || WhereSql;
   END IF;
   EXECUTE IMMEDIATE v_sql INTO RecordCount;

   --实现分页查询
   v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM (SELECT UserId,UserName,RoleId,IsLock,Desc from UserInfo ';
   IF WhereSql IS NOT NULL or WhereSql <> '' THEN
       v_sql := v_sql || ' WHERE 1=1' || WhereSql || ' ORDER BY UserId';
   END IF;

   v_sql := v_sql || ') A WHERE rownum <= ' || EndIndex || ') B WHERE r >= '|| StartIndex;
   DBMS_OUTPUT.put_line(v_sql);
   
   OPEN v_cur FOR v_sql;
   
END UP_UserInfo_List_select;

--执行埋如慧，plsql这里是看不到结果集，找到这个过程点测试，才能看到。当然你在程序里使用也是能返回的。
declare
RecordCount number :=0;
v_cur pkg_query.cur_query;
begin
  UP_UserInfo_List_select('',1,10,RecordCount,v_cur);
  DBMS_OUTPUT.put_line(RecordCount);
end;