csharp: Oracle Stored Procedure DAL using ODP.NET

Oracel sql:

--书分类目录kind
-- Geovin Du
create table BookKindList
(
	BookKindID INT   PRIMARY KEY,
	BookKindName nvarchar2(500) not null,
	BookKindParent INT  null,
	BookKindCode varchar(100)   ---編號
);
--序列创建

drop SEQUENCE BookKindList_SEQ;

CREATE SEQUENCE BookKindList_SEQ
INCREMENT BY 1     -- 每次加几个
START WITH 1     -- 从1开始计数
NOMAXVALUE        -- 不设置最大值
NOCYCLE            -- 一直累加，不循环
NOCACHE;           --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE

--自增长触发器
drop trigger BookKindList_ID_AUTO;

 create or replace trigger BookKindList_ID_AUTO
  before insert on BookKindList   --BookKindList 是表名
  for each row
declare
  nextid number;
begin
  IF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名
    select BookKindList_SEQ.Nextval --BookKindList_SEQ正是刚才创建的
    into nextid
    from dual;
    :new.BookKindID:=nextid;
  end if;
end;    

-- 添加
drop PROCEDURE proc_Insert_BookKindList;

CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList
(
temTypeName nvarchar2,
temParent number
)
AS
ncount number;
begin
--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where  EXISTS (SELECT BookKindName from BookKindList fm2 where  fm2.BookKindName=temTypeName);--判斷是否存
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
if ncount<=0 then
begin
INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);
commit;
end;
else
begin
  SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
  dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
end;
end if;
Exception
    When others then
      dbms_output.put_line('存在问题,添加不成功!'||ncount);
       Rollback;
end proc_Insert_BookKindList;

--测试 oracle 11g 涂聚文 20150526
exec proc_Insert_BookKindList ('油彩画',3);

drop PROCEDURE proc_Insert_BookKindOut;

drop PROCEDURE procInsertBookKindOut;
 -- 添加有返回值
CREATE OR REPLACE PROCEDURE proc_Insert_BookKindOut --添加返回ID
(
temTypeName nvarchar2,
temParent int,
temId out int
)
AS
ncount number;
reid number;
begin
--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where  EXISTS (SELECT BookKindName from BookKindList fm2 where  fm2.BookKindName=temTypeName);--判斷是否存
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
if ncount<=0 then
begin
--INSERT INTO BookKindList (BookKindID,BookKindName,BookKindParent) VALUES(BookKindList_SEQ.nextval,temTypeName,temParent);
INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);
select BookKindList_SEQ.currval into reid from dual;
temId:=reid;
dbms_output.put_line('添加成功!'||temId);
commit;
end;
else
begin
  SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
  dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
  temId:=0;
end;
end if;
Exception
    When others then
    begin
      dbms_output.put_line('存在问题,添加不成功!'||ncount);
      temId:=0;
       Rollback;
    end;
end proc_Insert_BookKindOut;

--测试 oracle 11g 涂聚文 20150526
declare
mid  number:=0;
nam  nvarchar2(100):='黑白画';
par  number:=3;
begin
--proc_Insert_BookKindOut(nam in nvarchar2,par in int,mid in out int);
proc_Insert_BookKindOut(nam,par ,mid);
if mid>0 then
dbms_output.put_line('添加成功!输出参数:'||mid);
else
dbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);
end if;
end;

--修改
CREATE OR REPLACE PROCEDURE procUpdateBookKindList (
p_id IN INT,--BookKindList.BookKindID%TYPE,
p_name IN nvarchar2,--BookKindList.BookKindName%TYPE,
p_parent IN INT,--BookKindList.BookKindParent%TYPE,
p_code IN varchar--BookKindList.BookKindCode%TYPE
)
IS
ncount number;
BEGIN
SELECT count(*) INTO ncount FROM BookKindList where BookKindName=p_name;
if ncount<=0 then
begin
UPDATE BookKindList SET BookKindName=p_name,BookKindParent=p_parent,BookKindCode=p_code WHERE BookKindID=p_id;
COMMIT;
end;
else
begin
  SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=p_name;
  dbms_output.put_line('存在相同的记录,修改不成功!'||ncount);
end;
end if;
END procUpdateBookKindList;  

--测试
begin
procUpdateBookKindList(8,'哲学',1,'Geovin Du');
end;

--删除
CREATE OR REPLACE PROCEDURE procDeleteBookKindList(p_BookKindID IN BookKindList.BookKindID%TYPE)
IS
BEGIN

  DELETE BookKindList where BookKindID = p_BookKindID;
  COMMIT;
END;

---一条记录
--创建包:
create or replace package pack_BookKindId is
       type cur_BookKindId is ref cursor;
end pack_BookKindId; 

--创建存储过程
create or replace procedure procSelectBookKindList
(p_id in int,p_cur out pack_BookKindId.cur_BookKindId)
is
       v_sql varchar2(400);
begin  

       if p_id = 0 then   --0 查询所有
          open p_cur for select * from BookKindList;
       else
          v_sql := 'select * from BookKindList where BookKindID =: p_id';
          open p_cur for v_sql using p_id;
       end if;
end procSelectBookKindList;

--创建包以游标的形式返回BookKindList表的所有记录结果集

drop package pkg_Select_BookKindListAll;

drop procedure proc_Select_BookKindListAll;

create or replace package pkgSelectBookKindListAll is
-- Author  : geovindu
  type mycur is ref cursor;
  procedure procSelectBookKindListAll(cur_return out mycur);
end pkgSelectBookKindListAll;

create or replace package body pkgSelectBookKindListAll is
  -- Function and procedure implementations
 procedure procSelectBookKindListAll(cur_return out mycur)
  is
  begin
   open cur_return for select * from BookKindList;

  end procSelectBookKindListAll;

end pkgSelectBookKindListAll;

-- 测试包和存储过程查询表中所有内容
declare
--定义游标类型的变量
cur_return pkgSelectBookKindListAll.mycur;
--定义行类型
pdtrow BookKindList%rowtype;
begin
  --执行存储过程
  pkgSelectBookKindListAll.procSelectBookKindListAll(cur_return);
  --遍历游标中的数据
       LOOP
         --取当前行数据存入pdtrow
           FETCH cur_return INTO pdtrow;
           --如果未获取数据就结束循环
           EXIT WHEN cur_return%NOTFOUND;
           --输出获取到的数据
           DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||'，'||pdtrow.BookKindName);
         END LOOP;
         CLOSE cur_return;
end;

csharp code:

/// <summary>
    /// 20160918 涂聚文
    /// Geovin Du
    /// </summary>
    public class BookKindListDAL : IBookKindList
    {
        //private static string connectionString =@"DATA SOURCE=oracle11g;USER ID=geovin;password=geovindu;";
        ///<summary>
        /// 追加记录
        ///</summary>
        ///<param name="BookKindListInfo"></param>
        ///<returns></returns>
        public int InsertBookKindList(BookKindListInfo bookKindList)
        {
            int ret = 0;
            try
            {
                OracleParameter[] par = new OracleParameter[]{
				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
				new OracleParameter("temParent",OracleDbType.Int32,4),
				};
                par[0].Value = bookKindList.BookKindName;
                par[0].Direction = ParameterDirection.Input;
                par[1].Value = bookKindList.BookKindParent;
                par[1].Direction = ParameterDirection.Input;
                ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return ret;
        }
        /// <summary>
        /// 追加记录返回
        /// </summary>
        /// <param name="authorList"></param>
        /// <param name="authorID"></param>
        /// <returns></returns>
        public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)
        {
            bookKindLID = 0;
            int ret = 0;
            try
            {
                OracleParameter[] par = new OracleParameter[]{
				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
                new OracleParameter("temParent",OracleDbType.Int32,4),
                new OracleParameter("temId",OracleDbType.Int32,4),
				};
                par[0].Value = bookKindList.BookKindName;
                par[0].Direction = ParameterDirection.Input;
                par[1].Value = bookKindList.BookKindParent;
                par[1].Direction = ParameterDirection.Input;
                par[2].Direction = ParameterDirection.Output;
                ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);
                if (ret > 0)
                {
                    bookKindLID =int.Parse(par[2].Value.ToString());
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        ///修改记录
        ///涂聚文 20160920
        ///</summary>
        ///<param name="BookKindListInfo"></param>
        ///<returns></returns>
        public int UpdateBookKindList(BookKindListInfo bookKindList)
        {
            int ret = 0;
            try
            {
                OracleParameter[] par = new OracleParameter[]{
				new OracleParameter("p_id",OracleDbType.Int32,4),
				new OracleParameter("p_name",OracleDbType.NVarchar2,1000),
				new OracleParameter("p_parent",OracleDbType.Int32,4),
                new OracleParameter("p_code",OracleDbType.Varchar2,1000),
				};
                par[0].Value = bookKindList.BookKindID;
                par[0].Direction = ParameterDirection.Input;
                par[1].Value = bookKindList.BookKindName;
                par[1].Direction = ParameterDirection.Input;
                par[2].Value = bookKindList.BookKindParent;
                par[2].Direction = ParameterDirection.Input;
                par[3].Value = bookKindList.BookKindCode;
                par[3].Direction = ParameterDirection.Input;
                ret = OracleHelper.ExecuteSql("procUpdateBookKindList", CommandType.StoredProcedure, par);
               // ret = 1;
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 删除记录
        ///</summary>
        ///<param name="bookKindIDInfo"></param>
        ///<returns></returns>
        public bool DeleteBookKindList(int bookKindID)
        {
            bool ret = false;
            try
            {
                OracleParameter par = new OracleParameter("p_BookKindID", bookKindID);
                par.Direction = ParameterDirection.Input;
                int temp = 0;
                temp = OracleHelper.ExecuteSql("procDeleteBookKindList", CommandType.StoredProcedure, par);
                if (temp != 0)
                {
                    ret = true;
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return ret;
        }
        ///<summary>
        /// 查询记录
        ///</summary>
        ///<param name="bookKindIDInfo"></param>
        ///<returns></returns>
        public BookKindListInfo SelectBookKindList(int bookKindID)
        {
            BookKindListInfo bookKindList = null;
            try
            {
                OracleParameter[] par = new OracleParameter[]{
                new OracleParameter("p_id",OracleDbType.Int32,4),
               new OracleParameter("p_cur",OracleDbType.RefCursor),
            };
                par[0].Value = bookKindID;
                par[0].Direction = ParameterDirection.Input;
                par[1].Direction = ParameterDirection.Output;
                using (OracleDataReader reader = OracleHelper.GetReader("procSelectBookKindList", CommandType.StoredProcedure, par)) //proc_Select_BookKindList 提示名称过长Oracle
                {
                    if (reader.Read())
                    {
                        bookKindList = new BookKindListInfo();
                        bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;
                        bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
                        bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;

                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return bookKindList;
        }

        ///<summary>
        /// 查询所有记录
        ///</summary>
        ///<returns></returns>
        public List<BookKindListInfo> SelectBookKindListAll()
        {
            List<BookKindListInfo> list = new List<BookKindListInfo>();
            BookKindListInfo bookKindList = null;
            try
            {
                //定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
                OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
                //设置参数为输出类型
                cur_set.Direction = ParameterDirection.Output;
                //
                //OracleHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "pkg_Select_BookKindListAll.proc_Select_BookKindListAll", cur_set)
                using (OracleDataReader reader = OracleHelper.GetReader("pkg_Select_BookKindListAll.proc_Select_BookKindListAll", CommandType.StoredProcedure, cur_set))
                {
                    while (reader.Read())
                    {
                        bookKindList = new BookKindListInfo();
                        string s = reader["BookKindID"].ToString();
                        bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;
                        bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
                        bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;
                        list.Add(bookKindList);

                    }
                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return list;
        }
        ///<summary>
        /// 查询所有记录
        ///</summary>
        ///<returns></returns>
        public DataTable SelectBookKindListDataTableAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
                OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
                //设置参数为输出类型
                cur_set.Direction = ParameterDirection.Output;
                //添加参数
                //comm.Parameters.Add(cur_set);
                using (DataTable reader = OracleHelper.GetTable("pkgSelectBookKindListAll.procSelectBookKindListAll", CommandType.StoredProcedure, cur_set))
                {
                    dt = reader;

                }
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 填充dataSet数据集－Oracle库
        /// </summary>
        /// <param name="pindex">当前页</param>
        /// <param name="psql">执行查询的SQL语句</param>
        /// <param name="psize">每页显示的记录数</param>
        /// <returns></returns>
        private bool gridbind(int pindex, string psql, int psize)
        {
            OracleConnection conn = new OracleConnection();
            OracleCommand cmd = new OracleCommand();
            OracleDataAdapter dr = new OracleDataAdapter();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            cmd.CommandText = "DotNet.DotNetPageRecordsCount";
            cmd.Parameters.Add("psqlcount", OracleDbType.Varchar2).Value = psql;
            cmd.Parameters.Add("prcount", OracleDbType.Int32).Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            string PCount = cmd.Parameters["prcount"].Value.ToString();
            cmd.Parameters.Clear();
            cmd.CommandText = "DotNet.DotNetPagination";
            if (pindex != 0)
            {
                cmd.Parameters.Add("pindex", OracleDbType.Int32).Value = pindex - 1;
            }
            else
            {
                cmd.Parameters.Add("pindex", OracleDbType.Int32).Value = pindex;
            }
            cmd.Parameters.Add("psql", OracleDbType.Varchar2).Value = psql;
            cmd.Parameters.Add("psize", OracleDbType.Int32).Value = psize;
            cmd.Parameters.Add("v_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("pcount", OracleDbType.Int32).Direction = ParameterDirection.Output;
            dr.SelectCommand = cmd;
            try
            {
                DataSet ds = new DataSet();
                dr.Fill(ds);
                //显示页码条的状态
                //showStatus(Convert.ToInt32(cmd.Parameters["pindex"].Value + 1,
                //    Convert.ToInt32(cmd.Parameters["pcount"].Value),
                //    Convert.ToInt32(PCount));
                for (int i = 0; i < ds.Tables.Count; i++)
                { //把数据行为零的表删除
                    if (ds.Tables[i].Rows.Count == 0)
                        ds.Tables.Remove(ds.Tables[i].TableName);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }

            conn.Close();
            return true;
        }
    }
时间： 2024-10-03 22:14:32

csharp: Oracle Stored Procedure DAL using ODP.NET的相关文章

csharp: MySQL Stored Procedure using DAL

# 建表 塗聚文 20160907 drop table attendrecord; create table attendrecord ( seq INT NOT NULL PRIMARY KEY AUTO_INCREMENT, emp_no varchar(20) null, rdate datetime not null, rtime time not null, rdescription varchar(100), rdes_reasnon varchar(100), branch va

Oracle中procedure/cursor深入分析

procedure概述 存储过程( Stored Procedure )是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中. 用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它. 存储过程是由流控制和 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可. 在Oracle 中,若干个有联系的过程可以组合在一起构成程序包. procedure优点 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 S

运用Stored Procedure验证login的输入

本文提供一种运用Stored Procedure验证login输入的方法,这种方法与目前大多数ASP程序的验证方法相比,执行效率高,代码简捷.在这里我运用了一个sp_login的SP来获得customerID,然后把它作为Session全程使用.看了许多商城代码,大多数使用cookie来保存,我认为不是一种好的方法.另外,运用COMMAND对象连接ADO是执行效率最高的方法,可以参考MSDN的相关文章,这是MS推荐的连接方法.在以后的文章中,我将频繁使用COMMAND对象和Stored Proc

Stored Procedure（存储过程）编写经验和优化措施

存储过程|优化 Stored Procedure(存储过程)编写经验和优化措施 一.前言:在经过一段时间的存储过程开发之后,写下了一些开发时候的小结和经验与大家共享,希望对大家有益,主要是针对Sybase和SQL Server数据库,但其它数据库应该有一些共性. 二.适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人. 三.介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作

User Tips: Using Return Values from a SQL Server Stored Procedure to Customize Error Messages

error|server When I started developing web pages that interact with a SQL Server database, I probably started like everbody else: with inline SQL statements. I then progressed to using the connection object to call stored procedures and eventually st

SQL Server 在多个数据库中创建同一个存储过程（Create Same Stored Procedure in All Databases）

原文:SQL Server 在多个数据库中创建同一个存储过程(Create Same Stored Procedure in All Databases) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 遇到的问题(Problems) 实现代码(SQL Codes) 方法一:拼接SQL: 方法二:调用模板存储过程创建存储过程: 总结 扩展阅读 参考文献(References) 二.背景(Contexts) 在我的数据库服务器上,同一个实例

Stored Procedure 里的 WITH RECOMPILE 到底是干麻的？

原文:Stored Procedure 里的 WITH RECOMPILE 到底是干麻的? 在 SQL Server 创建或修改「存储过程(stored procedure)」时,可加上 WITH RECOMPILE 选项,但多数文档或书籍都写得语焉不详,或只解释为「每次执行此存储过程时,都要重新编译」.事实上,是指执行此一存储过程时,要强制重新产生「执行计划(execution plan)」,而不要从「缓存(cache)」去取得旧的「执行计划」. SQL Server 在评估与产生「执行计划」

JDBC连接执行 MySQL 存储过程报权限错误：User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted,

国内私募机构九鼎控股打造APP,来就送 20元现金领取地址:http://jdb.jiudingcapital.com/phone.html 内部邀请码:C8E245J (不写邀请码,没有现金送)国内私募机构九鼎控股打造,九鼎投资是在全国股份转让系统挂牌的公众公司,股票代码为430719,为"中国PE第一股",市值超1000亿元.  -----------------------------------------------------------------------------

MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql sql server script: --- 涂聚文 20160906 IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount') DROP PROCEDURE proc_Select_DuDeptUserCount GO CREATE PROCEDURE proc_Select_DuDeptUserCount ( @where NV