oracle的操作
用了两年Oracle还没写过存储过程,真是十分惭愧,从今天开始学习Oracle存储过程,完全零起点,争取每日一篇学习笔记,可能开始认识的不全面甚至有错误,但坚持下来一定会有收获。 1. 建立一个存储过程 create or replace PROCEDURE firstPro IS BEGIN DBMS_OUTPUT.PUT_LINE(‘Hello World!‘); END; 其中IS关键字替换为AS关键字结果不会出现任何变化,大多认为他们是等同的,但也有一种说法解释为:一般PACKAGE 或者单独的FUNCTION, PROCEDURE 都用AS,PACKAGE 中的FUNCTION, PROCEDURE 用IS。 DBMS_OUTPUT.PUT_LINE(‘Hello World!‘); 是一个输出语句。 2. 执行存储过程 Oracle返回结果需要使用包,那么存储过程似乎只能在数据库中执行或被其他调用,编程语言似乎并不能直接调用存储过程返回数据,是否能执行他有待研究。那么首先在数库中执行上面的存储过程。 BEGIN FirstPro();//注意有括号 END; 运行后输出Hello World。 3. 下面写一个稍复杂的存储过程,他定义了变量,进行了运算,输出一个count操作所用的时间。 CREATE OR REPLACE procedure testtime is n_start number; n_end number; samplenum number; use_time number; begin n_start:=dbms_utility.get_time; select count(*) into samplenum from emp; n_end:=dbms_utility.get_time; use_time:= n_end - n_start; dbms_output.put_line(‘This statement cost ‘|| use_time ||‘ miliseconds‘); end; 4. 下面试验下怎么能给存储过程赋值 CREATE OR REPLACE procedure test(num in number) is begin dbms_output.put_line(‘The input numer is:‘ || num); end ; 今天的就到这,明天将调用这个存储过程,并试验一写对表的操作。 1. 首先把昨天带参的存储过程执行一下 declare n number; begin n:=1; test(num=>n); end; 注;在调用存储过程时,=>前面的变量为存储过程的形参且必须于存储过程中定义的一致,而=>后的参数为实际参数。当然也不可以不定义变量保存实参,可写成如下形式: Begin test(num=>1); end; 这样我们就能更清楚得看到给存储过程赋值的格式了。后面打算用存储过程操作一些表,按照增删改查的顺序依次建立存储过程。 2. 插入 CREATE OR REPLACE procedure proc_test_Insert_Single(e_no in number,e_name in varchar ,s in varchar,d in varchar) as begin insert into emp (emp_id,emp_name,salary,birthday) values (e_no,e_name,s,d); end; 调用: DECLARE i NUMBER; n varchar(5); s varchar(11); d varchar(10); BEGIN i:=10; n := ‘text11‘; s:=‘3998‘; d:=‘1998-02-02‘; PROc_TEST_Insert_single(e_no => i,e_name=>n,s=>s,d=>d); END; 注:调用存储过程声明varchar时,必须限定长度,即斜体的部分不能少。同时如果给变量赋值时大于限定的长度了,则会提示ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小。 3. 更新 create or replace procedure proc_test_update_Single(e_no in number,s in varchar) as begin UPDATE emp set salary =s where emp_id=e_no; end; 调用: DECLARE n NUMBER; s varchar(11); BEGIN n := 2; s:=3998; PROc_TEST_UPdate_single(e_no => n,s=>s); END; 4. 号外,今天在开发过程中正好有个数据库更新操作可用存储过程实现,顺便练习一下,需求是将一个表中的ID字段,查出来更新到另一个表中,两个表通过b_bs和b_kgh关联。存储过程如下: create or replace procedure update_yygzdbid as bs varchar(20); kgh varchar(20); bid number; cursor c_db is select b_id,b_bs,b_kgh from pmdcdb; begin for temp in c_db loop update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs and g_bh=temp.b_kgh; end loop; end; 运行这个存储过程: Begin update_yygzdbid(); end; 说明: (1).在没有参数的存储过程定义时存储过程的名称不需要括号,写成update_yygzdbid()是错误的, (2). cursor c_db是定义一个游标,获得查询语句的结果集, (3). For temp in c_bd loop Begin End; End loop 是循环游标,其形式类似于C#中的foreach, 获得字段:temp.b_id。 5. 查询 最后我们做一个查询的存储过程,能够返回一个值,注意不是结果集,结果集是明天的目标。 CREATE OR REPLACE procedure proc_test_Select_Single(t in varchar,r out varchar ) as begin select salary into r from emp where emp_name=t; end; 这个存储过程使用了2个参数,并分别出现了IN和OUT,in代表输入,out用于输出,从下面的语句也可以看到salary写入到变量r中了,这个r我们可以在调用存储过程后得到。 这时编译后会出现一个Warning(1,48): PLW-07203: 使用 NOCOPY 编译器提示可能对参数 ‘R‘ 有所帮助,那么nocopy是什么呢,nocopy主要是针对in|out record/index-by table/varray/varchar2提高效率使用的, 对于number使用nocopy与否基本没有影响.所以在‘enable:performance‘情况下不会对number提示warning. 我们把第一行改为:procedure proc_test_Select_Single(t in varchar,r out nocopy varchar ) 现在即使对in的varchar没有使用nocopy也不会提示警告, DECLARE T varchar2(4); R VARCHAR2(4); BEGIN T := ‘zz‘; PROC_TEST_SELECT_SINGLE(T => T,R => R ); DBMS_OUTPUT.PUT_LINE(‘R = ‘ || R); END; 运行后即可在输出中看到结果了。 三、 1. 今天我们首先写一个涨工资的存储过程,给每个低于5k工资的人涨点钱。 CREATE OR REPLACE PROCEDURE p_test(forRaise in number) as begin for v_emp in (select * from emp) loop if(v_emp.salary<‘5000‘) then update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id; end if; end loop; end; 调用: DECLARE FORRAISE NUMBER; BEGIN FORRAISE :=1; P_TEST(FORRAISE => FORRAISE); END; 这里要注意两个地方: (1) 循环中begin和end不是必须的 (2) 这里增加了if语句,其格式比较简单就不细说了。 (3) 这里没有定义游标,在游标的位置直接用select语句代替了。 2. 这里顺便介绍下另外一种循环,while循环,实现同样的功能 CREATE OR REPLACE PROCEDURE p_test(forRaise in number) as cursor c is select * from emp; v_row emp%rowtype; begin open c; fetch c into v_row; while c%found Loop if(v_row.salary<‘5000‘) then update emp set salary =(v_row.salary+forRaise) where emp_id=v_row.emp_id; end if; fetch c into v_row; end loop; close c; end; 说明: (1) 这里需要定义一个游标,还要定义一个emp%rowtype类型的变量,%前面是表名,后面表示这个表的一行, (2) 在使用游标前还要显示的打开游标,并将其赋值到row中,使用后关闭游标。 (3) C%found表示只有row中有值的时候才会进行循环。 (4) 经过对比发现于while循环相比,for循环更像是C#中的foreach,使用起来方便很多。 (5) 另从9i开始提供的动态游标类型sys_refcursor,以前的版本必须要先创建一个ref cursor的类型,现在多个 3. 现在我们使用程序调用下涨工资的存储过程,这个存储过程是没有返回值的。 OracleConnection conn = new OracleConnection(); //创建一个新连接 conn.ConnectionString = "Data Source=‘ds‘;user id=‘id ‘;password=‘pwd‘;"; OracleCommand cmd = new OracleCommand("P_TEST", conn); cmd.CommandType = CommandType.StoredProcedure; OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32); p1.Value = 1; p1.Direction = System.Data.ParameterDirection.Input; cmd.Parameters.Add(p1); conn.Open(); int r=cmd.ExecuteNonQuery(); conn.Close(); 这样我们就可以给员工涨工资了,说明: (1) 虽然给多个人涨了公司,但r的值是1,他只调用了1个存储过程,或者说受影响的只是1个。 (2) 参数P1的名字必须和存储过程中的一样否则会提示:调用 ‘P_TEST‘ 时参数个数或类型错误。 4. 现在我们试着从存储过程中得到点结果吧,我先看看我给几个人涨了工资,我每个月一共要多付多少钱了。 改动存储过程: CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res out number) is begin res:=0; for v_emp in (select * from emp) loop if(v_emp.salary<‘4000‘) then update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id; res:=res+1; end if; end loop; end; 增加了一个out 的number型,记录改动的次数。然后相应的调整C#程序,获得这个改动的次数。 OracleCommand cmd = new OracleCommand("P_TEST", conn); cmd.CommandType = CommandType.StoredProcedure; OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32); p1.Value = 4; p1.Direction = System.Data.ParameterDirection.Input; OracleParameter p2 = new OracleParameter("res", OracleType.UInt32); p2.Value = 10; p2.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); conn.Open(); int r=cmd.ExecuteNonQuery(); conn.Close(); MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”); 好了,今天就到这,下次返回数据集。 Oracle使用存储过程返回结果集必须使用包,包包括包头和包体两部分,包头是定义部分包体是具体的实现 包头: CREATE OR REPLACE PACKAGE pkg_test_select_mul AS TYPE myrctype IS REF CURSOR; PROCEDURE proc(s number, res OUT myrctype); END pkg_test_select_mul; 这里定义了个一个游标和一个存储过程。 包体: CREATE OR REPLACE PACKAGE BODY "PKG_TEST_SELECT_MUL" AS PROCEDURE proc(s in number,res OUT myrctype) IS BEGIN OPEN res FOR Select emp_id,emp_Name, salary,birthday From emp where salary> s; END proc; END PKG_TEST_SELECT_MUL; 这里实现里包头中定义的存储过程,实现了查询工资超过一定数额的人的信息,而游标则不用重新定义了,且存储过程中的参数名必须和定义中的一致。下面我们看一下C#的调用部分。 OracleConnection conn = new OracleConnection(); //创建一个新连接 conn.ConnectionString = "Data Source=‘" + "MyTest" + "‘;user id=‘" + "azkaser" + "‘;password=‘" + "sti" + "‘;"; //写连接串 OracleCommand cmd = new OracleCommand("PKG_TEST_SELECT_MUL.proc", conn); cmd.CommandType = CommandType.StoredProcedure; OracleParameter p1 = new OracleParameter("s", OracleType.Number); p1.Value = 4000; p1.Direction = ParameterDirection.Input; OracleParameter p2 = new OracleParameter("res", OracleType.Cursor); p2.Direction = ParameterDirection.Output; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); conn.Open(); OracleDataReader myReader = cmd.ExecuteReader(); while (myReader.Read()) { MessageBox.Show(myReader.GetString(1)); } conn.Close(); 程序将得到的结果存放在OracleDataReader的对象中。
存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体。 行3: BEGIN关键词表明PL/SQL体的开始。 行4: NULL PL/SQL语句表明什么事都不做,这句不能删去,因为PL/SQL体中至少需要有一句; 行5: END关键词表明PL/SQL体的结束 存储过程创建语法: create or replace procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); --vs_msg VARCHAR2(4000); 变量2 类型(值范围); Begin Select count(*) into 变量1 from 表A where列名=param1; If (判断条件) then Select 列名 into 变量2 from 表A where列名=param1; Dbms_output。Put_line(‘打印信息’); Elsif (判断条件) then Dbms_output。Put_line(‘打印信息’); Else Raise 异常名(NO_DATA_FOUND); End if; Exception When others then Rollback; End; 注意事项: 1, 存储过程参数不带取值范围,in表示传入,out表示输出 类型可以使用任意Oracle中的合法类型。 2, 变量带取值范围,后面接分号 3, 在判断语句前最好先用count(*)函数判断是否存在该条操作记录 4, 用select 。。。into。。。给变量赋值 5, 在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) , the_count OUT NUMBER, ) AS --定义变量 vs_msg VARCHAR2(4000); --错误信息变量 vs_ym_beg CHAR(6); --起始月份 vs_ym_end CHAR(6); --终止月份 vs_ym_sn_beg CHAR(6); --同期起始月份 vs_ym_sn_end CHAR(6); --同期终止月份 --定义游标(简单的说就是一个可以遍历的结果集)
简要记录存储过程语法与Java程序的调用方式 一 存储过程 首先,我们建立一个简单的表进行存储过程的测试 create table xuesheng(id integer, xing_ming varchar2(25), yu_wen number, shu_xue number); insert into xuesheng values(1,‘zhangsan‘,80,90) insert into xuesheng values(2,‘lisi‘,85,87) 1)无返回值的存储过程 create or replace procedure xs_proc_no is begin insert into xuesheng values (3, ‘wangwu‘, 90, 90); commit; end xs_proc_no; 2)有单个数据值返回的存储过程 复制代码 create or replace procedure xs_proc(temp_name in varchar2, temp_num out number) is num_1 number; num_2 number; begin select yu_wen, shu_xue into num_1, num_2 from xuesheng where xing_ming = temp_name; --dbms_output.put_line(num_1 + num_2); temp_num := num_1 + num_2; end; 复制代码 其中,以上两种与sql server基本类似,而对于返回数据集时,上述方法则不能满足我们的要求。在Oracle中,一般使用ref cursor来返回数据集。示例代码如下: 3)有返回值的存储过程(列表返回) 首先,建立我们自己的包。并定义包中的一个自定义ref cursor create or replace package mypackage as type my_cursor is ref cursor; end mypackage; 在定义了ref cursor后,可以书写我们的程序代码 create or replace procedure xs_proc_list(shuxue in number, p_cursor out mypackage.my_cursor) is begin open p_cursor for select * from xuesheng where shu_xue > shuxue; end xs_proc_list; 二、程序调用 在本节中,我们使用java语言调用存储过程。其中,关键是使用CallableStatement这个对象,代码如下: String oracleDriverName = "oracle.jdbc.driver.OracleDriver"; // 以下使用的Test就是Oracle里的表空间 String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; Connection myConnection = null; try { Class.forName(oracleDriverName); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } try { myConnection = DriverManager.getConnection(oracleUrlToConnect, "xxxx", "xxxx");//此处为数据库用户名与密码 } catch (Exception ex) { ex.printStackTrace(); } try { CallableStatement proc=null; proc=myConnection.prepareCall("{call xs_proc(?,?)}"); proc.setString(1, "zhangsan"); proc.registerOutParameter(2, Types.NUMERIC); proc.execute(); String teststring=proc.getString(2); System.out.println(teststring); } catch (Exception ex) { ex.printStackTrace(); } 对于列表返回值的存储过程,在上述代码中做简单修改。如下 复制代码 CallableStatement proc=null; proc=myConnection.prepareCall("{call getdcsj(?,?,?,?,?)}"); proc.setString(1, strDate); proc.setString(2, jzbh); proc.registerOutParameter(3, Types.NUMERIC); proc.registerOutParameter(4, OracleTypes.CURSOR); proc.registerOutParameter(5, OracleTypes.CURSOR); proc.execute(); ResultSet rs=null; int total_number=proc.getInt(3); rs=(ResultSet)proc.getObject(4); 复制代码 上述存储过程修改完毕。另外,一个复杂的工程项目中的例子:查询一段数据中间隔不超过十分钟且连续超过100条的数据。即上述代码所调用的getdcsj存储过程
转载地址:http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。