--创建表空间
CREATE TABLESPACE TableSpaceName
NOLOGGING
DATAFILE 'path/tablespacename.dbf' SIZE 20M
autoExtend on next 10M
permanent online;
--创建用户
CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE TsinghuaLMS ACCOUNT UNLOCK;
--授权
GRANT "CONNECT" TO username;
GRANT "RESOURCE" TO username;
GRANT "DBA" TO username;
本文链接
在Oracle中使用分页存储过程相比较Sql Server来说，要复杂一些，数据集的返回需要使用游标。本人的分页存储过程是在Oracle包中实现的。
--首先定义包头部分
--pFilter参数为查询条件字符串，如“ NCID='1' and NDate='2012-1-1'”
CREATE OR REPLACE PACKAGE PKG_NEWS IS
type CurBase is ref cursor;
procedure SearchNews (pFilter in varchar2,pPage number,pRecordPerPage out number,pAllRecord out number,CurNews out CurBase);
END;
--然后定义包的主体部分
CREATE OR REPLACE PACKAGE BODY PKG_NEWS IS
--搜索新闻，注意分页部分还可以再优化一下。
procedure SearchNews (pFilter in varchar2,pPage number,pRecordPerPage out number,pAllRecord out number,CurNews out CurBase) is
vSQL Varchar(1000);
begin
pRecordPerPage:=30;
vSQL:='select count(*) from V3News where (1=1) ' || pFilter;
execute immediate vSQL into pAllRecord;
vSQL:='select RNum,NID,NCID,NCName,NFrom,NName,NDate,NClick from (select RowNum RNum,V3News.* from V3News where (1=1) '|| pFilter || ') V3News where RNum>' ||
to_char((pPage-1)*pRecordPerPage) || ' and RNum<=' || to_char(pPage*pRecordPerPage);
open CurNews for vSQL ;
end;
END;
.net程序DAO层
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
namespace NewsDal
{
/// <summary>
/// 新闻Oracle数据访问层
/// 作者：
/// 创建时间：2007-5-25
/// 最后修改：
/// 最后修改时间：
/// </summary>
public class News
{
private OracleProvider objDBConn = new OracleProvider();
private OracleConnection objConn;
private OracleCommand objCmd;
private OracleDataAdapter objDA;
private DataSet objDS;
private OracleParameter objPA;
/// <summary>
/// 分页获取新闻
/// </summary>
public DataTable GetPageNews(int ipage, out int irecordperpage, out double iallrecord)
{
irecordperpage = 0;
iallrecord = 0;
try
{
objConn = objDBConn.Conn;
objCmd = new OracleCommand("PKG_News.GetPageNews", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objPA = new OracleParameter("pPage", ipage);
objCmd.Parameters.Add(objPA);
objPA = new OracleParameter("pRecordPerPage", OracleType.Number);
objPA.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(objPA);
objPA = new OracleParameter("pAllRecord", OracleType.Number);
objPA.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(objPA);
OracleParameter curNews = new OracleParameter("CurNews", OracleType.Cursor);
curNews.Direction = ParameterDirection.Output;
objCmd.Parameters.Add(curNews);
objDA = new OracleDataAdapter(objCmd);
objDS = new DataSet();
if (objConn.State == ConnectionState.Closed)
objConn.Open();
objCmd.ExecuteNonQuery();
objDA.Fill(objDS, "V3News");
if (objCmd.Parameters["pRecordPerPage"].Value == DBNull.Value || objCmd.Parameters["pRecordPerPage"].Value.ToString() == "")
irecordperpage = 0;
else
irecordperpage = Convert.ToInt32(objCmd.Parameters["pRecordPerPage"].Value);
if (objCmd.Parameters["pAllRecord"].Value == DBNull.Value || objCmd.Parameters["pAllRecord"].Value.ToString() == "")
iallrecord = 0;
else
iallrecord = Convert.ToDouble(objCmd.Parameters["pAllRecord"].Value);
return objDS.Tables["V3News"];
}
catch (Exception e)
{
throw e;
}
finally
{
2.alter system kill session '144（SID）,12602(SERIAL#)';
本文链接