Oracle通用分页存储过程的创建与使用

Oracle通用分页存储过程的创建与使用Postedon2011-09-0416:27孤独者阅读(724)评论(0)编辑收藏1.创建Oracle包的定义。使用REFCURSOR数据类型来处理Oracle结果集。REFCURSOR是一个指向PL/SQL查询所返回的结果集的指针。与普通的游标不同，REFCURSOR是一个变量，它是对游标的引用，可以在执行时将其设置为指向不同的结果集。使用REFCURSOR输出参数可以将Oracle结构化程序的结果集传递回调用应用程序。通过在调用应用程序中定义OracleType.Cursor数据类型的输出参数，可以访问REFCURSOR所指向的结果集。createorreplacepackageMF_PAK_001istypet_cursorisrefcursor;procedureGetDataByPage(p_tableNamevarchar2,p_fieldsvarchar2,p_filtervarchar2,p_sortvarchar2,p_curPagenumber,p_pageSizenumber,p_cursoroutt_cursor,p_totalRecordsoutnumber);endMF_PAK_001;2.创建包体。在包体中实现具体的分页存储过程。createorreplacepackagebodyMF_PAK_001isprocedureGetDataByPage(p_tableNamevarchar2,--要查询的表名p_fieldsvarchar2,--要查询的字段p_filtervarchar2,--过滤条件p_sortvarchar2,--排序字段及方向p_curPagenumber,p_pageSizenumber,p_cursoroutt_cursor,p_totalRecordsoutnumber)isv_sqlvarchar2(1000):='';v_startRecordnumber(4);v_endRecordnumber(4);begin--获取总的记录数v_sql:='selectto_number(count(*))from'||p_tableName;ifp_filterisnotnullthenv_sql:=v_sql||'where1=1and'||p_filter;endif;executeimmediatev_sqlintop_totalRecords;v_startRecord:=(p_curPage-1)*p_pageSize;v_endRecord:=p_curPage*p_pageSize;v_sql:='select'||p_fields||'from(select'||p_fields||',rownumrfrom'||'(select'||p_fields||'from'||p_tableName;ifp_filterisnotnullthenv_sql:=v_sql||'where1=1and'||p_filter;endif;ifp_sortisnotnullthenv_sql:=v_sql||'orderby'||p_sort;endif;v_sql:=v_sql||')Awhererownum='||to_char(v_endRecord)||')Bwherer='||to_char(v_startRecord);openp_cursorforv_sql;endGetDataByPage;endMF_PAK_001;3.在Oracle中编写查询语句，执行包体中的分页存储过程，看是否能够正确执行。declarev_curMF_PAK_001.t_cursor;v_jobjobs%rowtype;v_totalRecordsnumber;beginMF_PAK_001.GetDataByPage('jobs','job_id,job_title,min_salary,max_salary','min_salary0','job_idasc',1,10,v_cur,v_totalRecords);fetchv_curintov_job;whilev_cur%foundloopdbms_output.put_line(v_job.job_id||','||v_job.job_title);fetchv_curintov_job;endloop;dbms_output.put_line('总记录数为：'||v_totalRecords);end;4.在.NET中调用该分页存储过程。stringconnString=DataSource=ORCL;UserId=hr;Password=Pwd123456;OracleConnectionconn=newOracleConnection(connString);OracleCommandcmd=newOracleCommand();cmd.Connection=conn;cmd.CommandText=MF_PAK_001.GetDataByPage;cmd.CommandType=CommandType.StoredProcedure;cmd.Parameters.Add(p_tableName,OracleType.VarChar).Value=jobs;cmd.Parameters.Add(p_fields,OracleType.VarChar).Value=job_id,job_title,min_salary,max_salary;cmd.Parameters.Add(p_filter,OracleType.VarChar).Value=;cmd.Parameters.Add(p_sort,OracleType.VarChar).Value=job_idasc;cmd.Parameters.Add(p_curPage,OracleType.Number).Value=1;cmd.Parameters.Add(p_pageSize,OracleType.Number).Value=10;cmd.Parameters.Add(p_cursor,OracleType.Cursor).Direction=ParameterDirection.Output;cmd.Parameters.Add(p_totalRecords,OracleType.Number).Direction=ParameterDirection.Output;conn.Open();OracleDataReaderdr=cmd.ExecuteReader();while(dr.Read()){for(inti=0;idr.FieldCount;i++)Response.Write(dr[i].ToString()+;);Response.Write(br/);}conn.Close();