ORACLE存储过程简析及C#调用

ORACLEPACKAGE编写及C#调用方法ORACLEPACKAGE编写Package介绍及和T-SQL的比较：PL/SQL和T-SQL中的存储过程之间的一个重大差异是PL/SQL所使用的Oraclepackage结构。在T-SQL中没有等效元素。package是在逻辑上相关的编程块（如存储过程和函数）的容器。在T-SQL中不允许有重名的存储过程，但是不同表中拥有相同或相似功能的存储过程实在让人不好命名，PL/SQL中利用Package有效的解决了这个问题，不同的package中可以拥有相同的存储过程名称。在PL/SQL中存储过程不能通过Select*fromtable类似的语句来获得记录集，只能使用RefCursor游标来返回。在PL/SQL中需要用CREATESEQUENCE（创建序列）来替代T-SQL中的identity()。关于文中涉及的NUMBER和CURSOR以及VARCHAR2等相关数据类型不再讲解。Package的书写Package的每个存储过程或函数的参数都出现在括号内，并且用逗号分隔。每个参数还根据需要用以下三个标识符中的一个进行标记：IN：该值从调用应用程序传递到PL/SQL块。如果未指定标识符，则IN为默认传递方向。OUT：该值由存储过程生成，并传递回调用应用程序。INOUT：该值被传递到PL/SQL块，可能在内部进行修改，然后返回到调用应用程序。每个参数也都被标记以指示数据类型。Package的书写主要分为两个部分：Package中需要的表结构以及相应的字段类型。相对于SQLServer而言，id字段由Varchar变更为NUMBER，字符型字段变更为Varchar2（Oracle数据库的需要），Price由float变更为NUMBER。ProductionPKIDNUMBERProNameVARCHAR2PriceNUMBERPNNVARCHAR2DescriptionNVARCHAR2ID：SEQUENCE自增NUMBERProName：商品名称NVARCHAR2Price：商品价格NUMBERPN：商品序列号NVARCHAR2Description：商品描述NVARCHAR21.package的规范：定义包的名称，并为包中的每个存储过程或函数提供方法签名（原型）。定义所有全局变量的声明。规范的样式类似于C或C++头文件。--创建一个名为OPERATE_PRODUCTION的package，并声明一个游标变量CREATEORREPLACEPACKAGEOPERATE_PRODUCTIONASTYPET_CURSORISREFCURSOR;PROCEDUREGetPro(PidINNUMBER);PROCEDUREGetProductions(cur_ProductionOUTT_CURSOR);PROCEDUREDeleteProductions(PidINNUMBER);PROCEDURECreateProductions(PidOUTNUMBER,PnameINNVARCHAR2,PNINVARCHAR2,PriceINNumber,PDescriptionINNVARCHAR2);ENDOPERATE_PRODUCTION;2.package的正文包含包头中定义的存储过程和函数的代码。--Package中函数的实现CREATEORREPLACEPACKAGEBODYOPERATE_PRODUCTIONAS--获取商品记录集，输出引用游标PROCEDUREGetProductions(cur_ProductionOUTT_CURSOR)ISBEGINOPENcur_ProductionFORSELECT*FROMProductionENDGetProductions;--获取商品记录集，输入参数为PIDPROCEDUREGetPro(PidINNUMBERPnameOUTNVARCHAR2,PNOUTVARCHAR2,PriceOUTNumber,PDescriptionOUTNVARCHAR2)ISBEGINSELECT*FROMProductionWHEREid=pid;ENDGetPro;--删除商品信息PROCEDUREDeleteProductions(PIDINNUMBER)ISBEGINDELETEFROMPRODUCTIONWHEREID=PID;ENDDeleteProductions;--创建一个商品并返回一个序列值PROCEDURECreateProductions(PIDOUTNUMBER,PnameINNVARCHAR2,PNINVARCHAR2,PriceINNumber,PDescriptionINNVARCHAR2)ASBEGININSERTINTOPRODUCTION(ID,ProName,PN,Price,Description)VALUES(PRODUCTION_SEQ.NEXTVAL,Pname,PN,Price,PDescription);SELECTPRODUCTION_SEQ.CURRVALINTOPIDFROMDUAL;ENDCreateProductions;ENDOPERATE_PRODUCTION注意：上述两段代码完整阐述了一个Package的诞生，一个引用游标变量和进行CURD操作的存储过程。其中在CreateProductions中提到了几个对于SQLSERVER开发者来说比较陌生的关键字PRODUCTION_SEQ.NEXTVAL，PRODUCTION_SEQ.CURRVAL以及一个系统表DUAL。PRODUCTION_SEQ：是一个表中的序列，具有唯一性，在作用上类似NewID()所获取唯一ID，但是Oracle中所产生的序列是NUMBER数据类型，所以在功能上和SQLSERVER的identity()更为接近。下面用一段简单的代码来创建一个PRODUCTION_SEQ序列：CREATESEQUENCEPRODUCTION_SEQINCREMENTBY1–-增量为1STARTWITH1--从1开始NomaxValue--没有最大NOCYCLE--不循环NOCACHE--不缓存一旦定义了PRODUCTION_SEQ，就可以使用CURRVAL，NEXTVALCURRVAL=返回sequence的当前值NEXTVAL=增加sequence的值，然后返回sequence值使用方法是：PRODUCTION_SEQ.CURRVAL在这里介绍一下OracleCACHE，cache顾名思义，是SEQUENCE的缓存，就是为了加快序列的创建速度。使用序列的注意事项：1.我个人并不提倡开启缓存，因为一旦系统down机，Oracle中的缓存也会被清空，在此时并不会触发Oracle这是再用cache创建序列时会产生序列重复等麻烦。毕竟没有永远不down机的系统。2.事务对sequence是无法恢复的，所以就会出现跳值的现象，就如同自增的跳值是一样的自增的实质也是序列，也是不管插入与否，都会自增。3.数据库中无法避免跳值现象的，主键插入就是用nextval方法去作。而且序列的nextval是完全可以避免并发冲突的，因为里面有并发机制在里面，不会出现两个人同时nextval，但是取出了相同值的情况。4.事务回滚、系统崩溃、两个或多个表共用一个sequence时，会发生跳值现象。DUAL表1.DUAL是什么？selectobject_name,object_typefromdba_objectswhereobject_name=\'DUAL\'结果:OWNEROBJECT_NAMEOBJECT_TYPE------------------------------------------------SYSDUALTABLEPUBLICDUALSYNONYM由此可见DUAL是SYS用户的一个TABLE2.DUAL有哪些FIELD？SQLdescdualNameTypeNullableDefaultComments---------------------------------------DUMMYVARCHAR2(1)Y它只有一个DUMMYField.3.DUAL能做什么？3.1查找当天日期SQLselectsysdatefromdual;SYSDATE-----------2008-3-263.2查找当前日期的当月第一天SQLselecttrunc(sysdate,\'MONTH\')fromdual;TRUNC(SYSDATE,\'MONTH\')----------------------2008-3-13.3查找当前日期的当月最后一天SQLselecttrunc(last_day(sysdate))fromdual;TRUNC(LAST_DAY(SYSDATE))------------------------2008-3-30注意：原则上来说，获取ORACLE的系统时间等操作都需要从DUAL中取得TRUNC()函数的用法1.TRUNC(fordates)TRUNC函数为指定元素而截去的日期值。其具体的语法格式如下：TRUNC(date[,fmt])其中：date一个日期值fmt日期格式，该日期将由指定的元素格式所截去,忽略它则由最近的日期截去下面是该函数的使用情况：TRUNC(TO_DATE(’-Nov-199908:00pm’,’dd-mon-yyyyhh:miam’))=’-Nov-199912:00:00am’TRUNC(TO_DATE(’-Nov-199908:37pm’,’dd-mon-yyyyhh:miam’,’hh’))=’-Nov-199908:00:00am’2.TRUNC(fornumber)TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。其具体的语法格式如下TRUNC(number[,decimals])其中：number待做截取处理的数值decimals指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分下面是该函数的使用情况：TRUNC(89.985,)=89.98TRUNC(89.985)=89TRUNC(89.985,-1)=80注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去。C#调用ORACLEPACKAGE的方法Oracle客户端连接配置及数据源建立1.Oracle客户端的安装下载最新的OracleDatabase11gRelease1Client(11.1.0.6.0)forMicrosoftWindows(32-bit)客户端并安装。2.Oracle客户端的配置点击OraclenetConfigurationAssistant进入客户端配置页面第一步：选择本地Net服务器名配置第二步：选择添加第三步：填写服务名即数据库实例名(instance_name)第四步：选择连接方式本例中为TCP方式第五步：填写主机名并设置端口号第六步：点击进行测试在一般情况下，需要点击‘更改登陆’按钮来设置正确的用户名和口令，然后重复进行测试操作，此时会提示测试成功。一个Oracle数据源就添加好了。用C#调用OraclePackage准备工作：1.需要vs2005打好SP1的补丁2.添加System.Data.OracleClient的引用代码编写：与SQLServer的操作类相似，都将拥有OracleConnection、OracleCommand等方法和属性usingSystem;usingSystem.Data;usingSystem.Configuration;usingSystem.Web;usingSystem.Text;usingSystem.Web.Security;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSys