csharp: Oracle Stored Procedure DAL using ODP.NET

Oracle 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_SEQINCREMENT 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 rowdeclare 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)ASncount 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 thenbeginINSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);commit;end;elsebegin 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 涂聚文 20150526exec 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 )ASncount 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 thenbegin--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;elsebegin 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 涂聚文 20150526declaremid 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 thendbms_output.put_line('添加成功!输出参数:'||mid);elsedbms_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) ISncount number; BEGIN SELECT count(*) INTO ncount FROM BookKindList where BookKindName=p_name;if ncount<=0 thenbeginUPDATE BookKindList SET BookKindName=p_name,BookKindParent=p_parent,BookKindCode=p_code WHERE BookKindID=p_id; COMMIT; end;elsebegin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=p_name; dbms_output.put_line('存在相同的记录,修改不成功!'||ncount); end; end if;END procUpdateBookKindList; --测试beginprocUpdateBookKindList(8,'哲学',1,'Geovin Du'); end;--删除CREATE OR REPLACE PROCEDURE procDeleteBookKindList(p_BookKindID IN BookKindList.BookKindID%TYPE)ISBEGIN 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;

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：csharp: Oracle Stored Procedure DAL using ODP.NET

关键词：.NET