oracle存储过程返回多个游标实例 - 浪迹天涯 - BlogJava
oracle存储过程返回多个游标实例- 浪迹天涯- BlogJava
oracle
存储过程返回多个游标实例
定义两个包（package）
CREATE OR REPLACE PACKAGE
pro_package_test_001 AS
TYPE Test_CURSOR01 IS REF CURSOR;
end pro_package_test_001;
CREATE OR REPLACE PACKAGE
pro_package_test_002 AS
TYPE Test_CURSOR02 IS REF CURSOR;
end pro_package_test_002;
定义存储过程
CREATE OR REPLACE PROCEDURE pro_query_001 (
--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合
法类型。
in_lx IN Varchar2,
p_cus_01 OUT pro_package_test_001.Test_CURSOR01, p_cus_02 OUT pro_package_test_002.Test_CURSOR02 )
AS
--定义变量
vs_lx VARCHAR2(1); --变量
vs_test1_id VARCHAR2(100); --变量
vs_test1_mc VARCHAR2(100); --变量
vs_test2_id VARCHAR2(100); --变量
vs_test2_mc VARCHAR2(100); --变量
--default_c SYS_REFCURSOR;
BEGIN
--用输入参数给变量赋初值。
vs_lx:= in_lx;
--插入test1表。
OPEN p_cus_01 FOR Select
a.id As id1,
a.mc As mc1,
b.id As id2,
b.mc As mc2
Into
vs_test1_id,
vs_test1_mc,
vs_test2_id,
vs_test2_mc
From test1 a,test2 b Where a.id = b.id And a.lx = vs_lx;
--if p_cus_01%rowcount = 0 then
-- p_cus_01:=default_c;
-- end if;
OPEN p_cus_02 FOR Select
id As id1,
mc As mc1
Into
vs_test1_id,
vs_test1_mc
From test2 Where lx = vs_lx;
-- if p_cus_02%rowcount = 0 then
-- p_cus_02:=default_c;
-- end if;
--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。Exception
WHEN OTHERS Then
ROLLBACK;
Return;
End pro_query_001;
java 代码调用
src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_2.gif" align="top"> id="Codehighlighter1_80_1743_Closed_Image" style="display: none;" onclick="this.style.display='none'; ne'; Codehighlighter1_80_1743_Open_Image.style.display='inli ne'; Codehighlighter1_80_1743_Open_Text.style.display='inlin e';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_3.gif" align="top"> public void ProcQuery(String procString,String [] params) throws Exception { Session session = null; Connection conn = null; ResultSet rs1 = null; ResultSet rs2 = null; CallableStatement proc = null; procString = "{call pro_query_001(?,?,?)}"; params = new String [1]; params[0]="0"; src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_6.gif" align="top"> id="Codehighlighter1_349_1347_Closed_Image" style="display: none;" onclick="this.style.display='none'; Codehighlighter1_349_1347_Closed_Text.style.display='n one'; Codehighlighter1_349_1347_Open_Image.style.display='i nline'; Codehighlighter1_349_1347_Open_Text.style.display='inli ne';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_7.gif" align="top"> try { session = getHibernateTemplate().getSessionFactory() .openSession(); conn = session.connection(); proc = conn.prepareCall(procString); proc.setString(1, params[0]); //传入的 参数 proc.registerOutParameter(2,oracle.jdbc.OracleTypes.CU RSOR); proc.registerOutParameter(3,oracle.jdbc.OracleTypes.CU RSOR); proc.execute(); rs1 = (ResultSet)proc.getObject(2);//返回第一个游标 rs2 = (ResultSet)proc.getObject(3);//返回第二个游标 src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_6.gif" align="top"> id="Codehighlighter1_877_1143_Closed_Image" style="display: none;" onclick="this.style.display='none'; Codehighlighter1_877_1143_Closed_Text.style.display='n one'; Codehighlighter1_877_1143_Open_Image.style.display='i nline'; Codehighlighter1_877_1143_Open_Text.style.display='inli ne';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_7.gif" align="top"> while(rs1 != null && rs1.next()) { System.out.println(">>>"+rs1.getString("id1")); System.out.println(">>>"+rs1.getString("mc1")); System.out.println(">>>"+rs1.getString("id2")); System.out.println(">>>"+rs1.getString("mc2")); } src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_6.gif" align="top"> id="Codehighlighter1_1190_1324_Closed_Image" style="display: none;" onclick="this.style.display='none'; Codehighlighter1_1190_1324_Closed_Text.style.display=' none'; Codehighlighter1_1190_1324_Open_Image.style.display=' inline'; Codehighlighter1_1190_1324_Open_Text.style.display='inl ine';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_7.gif" align="top"> while(rs2 != null && rs2.next()) { System.out.println(">>>"+rs1.getString("id1")); System.out.println(">>>"+rs1.getString("mc1")); } src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_6.gif" align="top"> id="Codehighlighter1_1372_1512_Closed_Image" style="display: none;" onclick="this.style.display='none'; Codehighlighter1_1372_1512_Closed_Text.style.display=' none'; Codehighlighter1_1372_1512_Open_Image.style.display=' inline'; Codehighlighter1_1372_1512_Open_Text.style.display='inl ine';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_7.gif" align="top"> } catch (SQLException e) { e.printStackTrace(); throw new Exception("调 用存储过程的时候发生错误[sql = " + procString + "]", e); src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_6.gif" align="top"> id="Codehighlighter1_1523_1735_Closed_Image" style="display: none;" onclick="this.style.display='none'; Codehighlighter1_1523_1735_Closed_Text.style.display=' none'; Codehighlighter1_1523_1735_Open_Image.style.display=' inline'; Codehighlighter1_1523_1735_Open_Text.style.display='inl ine';" src="https://www.360docs.net/doc/b34432495.html,/DownloadImg/2010/05/11 23/3169057_7.gif" align="top"> } finally { if (proc != null) proc.close(); if (rs1 != null) rs1.close(); if (rs2 != null) rs2.close(); if (conn != null) conn.close(); } } 存储过程 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) , ｏｒａｃｌｅ游标的使用及属性 oracle游标的使用 游标是从数据表中提取出来的数据，以临时表的形式存放到内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回到数据库中。 一：定义游标 ｃｕｒｓｏｒ游标名ｉｓｓｅｌｅｃｔ语句； 示例： ｓｅｔｓｅｒｖｅｒｏｕｔｐｕｔｏｎ ｄｅｃｌａｒｅ ｔｅｍｐｓａｌｓｃｏｔｔ．ｅｍｐ．ｓａｌ％ｔｙｐｅ； ｃｕｒｓｏｒｍｙｃｕｒｓｏｒｉｓｓｅｌｅｃｔ ＊ｆｒｏｍｓｃｏｔｔ．ｅｍｐｗｈｅｒｅｓａｌ＞ｔｅｍｐｓａｌ； ｂｅｇｉｎ ｔｅｍｐｓａｌ：＝８００； ｏｐｅｎｍｙｃｕｒｓｏｒ； ｅｎｄ； 二：打开游标 语法结构：ｏｐｅｎ游标名 打开游标分为两步：１将符合条件的记录送入内存２将指针指向第一条记录 三：提取游标数据 语法形式：ｆｅｔｃｈ游标名ｉｎｔｏ变量名１，变量名２，．．．．．；或者 ｆｅｔｃｈ游标名ｉｎｔｏ记录型变量名； 示例： ｓｅｔｓｅｒｖｅｒｏｕｔｐｕｔｏｎ ｄｅｃｌａｒｅ ｔｅｍｐｓａｌｓｃｏｔｔ．ｅｍｐ．ｓａｌ％ｔｙｐｅ； ｃｕｒｓｏｒｍｙｃｕｒｓｏｒｉｓｓｅｌｅｃｔ＊ｆｒｏｍｓｃｏｔｔ．ｅｍｐｗｈｅｒｅｓａｌ＞ｔｅｍｐｓａｌ； ｃｕｒｓｏｒｒｅｃｏｒｄｍｙｃｕｒｓｏｒ％ｒｏｗｔｙｐｅ； ｂｅｇｉｎ ｔｅｍｐｓａｌ：＝８００； ｏｐｅｎｍｙｃｕｒｓｏｒ； ｆｅｔｃｈｍｙｃｕｒｓｏｒｉｎｔｏｃｕｒｓｏｒｒｅｃｏｒｄ； ｄｂｍｓ＿ｏｕｔｐｕｔ．ｐｕｔ＿ｌｉｎｅ（ｔｏ＿ｃｈａｒ（ｃｕｒｓｏｒｒｅｃｏｒｄ．ｄｅｐｔｎｏ））； ｅｎｄ； 四：关闭游标 ｃｌｏｓｅ游标名； Oracle游标的属性之一------%isopen %isopen 属性----测试游标是否打开，没打开的情况下使用fetch语句将提示错误。 示例： 实验八存储过程的使用 一、实验目的 1、熟练掌握存储过程的定义及使用 二、实验要求 1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成 实验内容的预习准备工作； 2、能认真独立完成实验内容； 3、实验后做好实验总结，根据实验情况完成实验报告。 三、实验内容 创建图书管理库的图书、读者和借阅三个基本表的表结构： 图书表： BOOK ( BOOK_ID NUMBER(10), SORT V ARCHAR2(10), BOOK_NAME V ARCHAR2(50), WRITER V ARCHAR2(10), OUTPUT V ARCHAR2(50), PRICE NUMBER(3)); 读者表 READER ( READER_ID NUMBER(3), COMPANY V ARCHAR2(10), NAME V ARCHAR2(10), SEX V ARCHAR2(2), GRADE V ARCHAR2(10), ADDR V ARCHAR2(50)); 借阅表 BORROW ( READER_ID NUMBER(3), BOOK_ID NUMBER(10), BORROW_DA TE DA TE); 插入数据: BOOK表: insert into book values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00); insert into book values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60); insert into book values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00); insert into book values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80); insert into book values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50); insert into book values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); insert into book values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); READER表: insert into reader values(111,'信息系','王维利','女','教授','1号楼424'); insert into reader values(112,'财会系','李立','男','副教授','2号楼316'); insert into reader values(113,'经济系','张三','男','讲师','3号楼105'); insert into reader values(114,'信息系','周华发','男','讲师','1号楼316'); insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224'); insert into reader values(116,'信息系','李明','男','副教授','1号楼318'); insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214'); insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216'); insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318'); insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506'); insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510'); insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512'); insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202'); insert into reader values(124,'财会系','朱海','男','讲师','2号楼210'); insert into reader values(125,'财会系','马英明','男','副教授','2号楼212'); BORROW表: -- 声明游标；CURSOR cursor_name IS select_statement --For 循环游标 --（1）定义游标 --（2）定义游标变量 --（3）使用for循环来使用这个游标 declare --类型定义 cursor c_job is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型 c_row c_job%rowtype; begin for c_row in c_job loop dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); end loop; end; --Fetch游标 --使用的时候必须要明确的打开和关闭 declare --类型定义 cursor c_job is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量 c_row c_job%rowtype; begin open c_job; loop --提取一行数据到c_row fetch c_job into c_row; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound 是false --取不到值c_job%notfound 是true exit when c_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal); Oracle显式游标和隐式游标 SQL是用于访问Oracle数据库的语言，PL/SQL扩展和加强了SQL的功能，它同时引入了更强的程序逻辑, 下面在本文中将对游标的使用进行一下讲解,希望可以和大家共同学习进步。 游标字面理解就是游动的光标。游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。用数据库语言来描述游标就是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。 游标有两种类型：显式游标和隐式游标。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。 当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个处理操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种,分别是 SQL %ISOPEN，SQL %FOUND，SQL %NOTFOUND，SQL %ROWCOUNT。 SQL%ISOPEN返回的类型为布尔型,判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false,即执行过程中为真,结束后为假。 SQL%NOTFOUND返回值为布尔型,判断游标所在的行是否有效，如果有效， 则%FOUNDD等于true，否则等于false,即与%FOUND属性返回值相反。 SQL%FOUND返回值的类型为布尔型，值为TRUE代表插入删除更新或单行查询操作成功。 SQL%ROWCOUNT返回值类型为整型，返回当前位置为止游标读取的记录行数, oracle11g 游标： 1. 当在PL/SQL中使用SQL语句时，Oracle会为其分配上下文区域，这是一段 私有的内存区域，用于暂时保存SQL语句影响到的数据。游标是指向这段内存区域的指针。 2. Oracle中主要有两种类型的游标: (1) 隐式游标:所有的DML语句和PL/SQL SELECT 语句都有; (2) 显式游标:由开发人员声明和控制。 3. 可以使用的游标属性包括四种：%ROWCOUNT、%FOUND、%NOTFOUND、 %ISOPEN，这四种属性对于显式游标和隐式游标都有用，但是含义和使用方法略有不同。游标在使用属性时，需要以游标名称作为前缀，以表明该属性是哪个游标的，隐式游标没有名称，所以在使用隐式游标时采取了统一的一个名称SQL。 4. 在PL/SQL中的SELECT语句只能且必须取出一行数据，取出多行或者零行都 被认为是异常，所以在对多行数据进行操作时，必须使用显式游标来实现。 5. 使用显式游标的步骤： (1)声明游标：CURSOR cursor_name is select_statement; (2)打开游标：OPEN cursor_name; (3)取游标中的数据：FETCH cursor_name INTO variable1,variable2,...; (4)关闭游标：CLOSE cursor_name; 6.用变量接收游标中的数据 sql> declare v_name emp.ename%TYPE; v_sal emp.sal%TYPE; cursor emp_cursor is select ename,sal from emp where deptno=10; begin open emp_cursor; loop fetch emp_cursor into v_name,v_sal; exit when emp_cursor%NOTFOUND; dbms_output.put_line(v_name || ‘的薪水是’ || v_sal); oracle存储过程学习过程 一直没有使用过存储过程 今天特意学习一下oracle的存储过程 一步一步学习，今天学习如下： 建立一个最简单的存储过程 create or replace procedure test_xg_p1 is begin dbms_output.put_line('hello world! this is the first procedure'); end; 建立一个带输入输出参数的存储过程：把输入的数据传给输出参数 create or replace procedure test_xg_p2(a in number,x out number) is begin x:=a; end test_xg_p2; 建立一个逻辑判断的存储过程，并包含输入输出参数：近似分数的登记判断create or replace procedure test_xg_p3(a in number,x out varchar2) is begin if a>=90 then begin x := 'A'; end; end if; if a<90 then begin x:='B'; end; end if; if a<80 then begin x:='C'; end; end if; if a<70 then begin x:='D'; end; end if; if a<60 then begin x:='E'; end; end if; end test_xg_p3; 建立一个带循环逻辑的存储过程：近似累加函数 create or replace procedure test_xg_p4(a in number,x out varchar2) is tempresult number(16); begin tempresult :=0; for tempa in 0..a loop begin tempresult := tempresult + tempa; end; end loop; x:=tempresult; end test_xg_p4; 建立一个能从数据库中特定表中返回数据的存储过程： create or replace procedure test_xg_p5(x out varchar2) is tempresult varchar2(1024); begin tempresult := 'start->'; select hotelid||hotelname into tempresult from hotel where hotelid =10041764; x:=tempresult; end test_xg_p5; 建立一个能使用游标的带循环的存储过程: create or replace procedure test_xg_p6(x out varchar2) is tempresult varchar2(10240); cursor cursor1 is select * from hotel where hotelname like '浙江%'; begin tempresult := 'start->'; for cursor_result in cursor1 loop begin tempresult :=tempresult||cursor_result.hotelid||cursor_result.hotelname; end; end loop; x:=tempresult; end test_xg_p6; Oracle函数返回游标的方法及应用简析 王凤利康俊霞 河北北方学院张家口职业技术学院 【摘要】Oracle函数除了可以返回数值类型和字符类型等常用数据类型的数 据以外，还可以返回游标类型的数据，在某些情况下，返回游标类型的函数为我们 解决一些应用中的难题。本文就是通过一个实例来介绍返回游标函数的创建过程。 【关键字】Oracle，函数，包，游标。 1 问题的提出 在油田信息系统建设过程中，遇到了这样一个问题：输油站泵运行数据表(DHC05)的结构为：时间(SJ)、单位名称(DWMC)、泵号(BH)、泵压(BY1)、电压(DY)、电流(DL)、排量(PL)，主键为：时间、单位名称、泵号，每整点对运行泵取一次数据，不存储不运行泵(运行时间为0)的数据。现要输出如下报表： 报表的查询条件为单位名称和日期(yyyymmdd格式字符串)。运行泵号及泵台数根据各单位实际运行情况而定。 2 系统简介 系统采用B/S三层体系结构，数据库采用Oracle9.2版本，WEB服务器采用IIS6.0版本,客户端采用IE6.0及以上版本。 报表采用了统一的制表解释程序进行输出，该解释程序可以从一个单一的Oracle查询语句中提取数据，报表的输出样式为简单的二维表。 3 解决方案 根据目前的系统现状，要想直接利用当前系统输出本报表是不可能的，经过分析认为共有以下几种解决方案： a)修改数据表结构 将原始数据表的结构进行调整，把时间、单位名称作为主键，将同一时刻的各个泵的数据逐个列出作为数据列，大致结构为：时间、单位名称、1号泵泵压、1号泵电压、1号泵电流、1号泵排量、2号泵泵压、2号泵电压、2号泵电流、2号泵排量、…。 本方案违背了数据库设计的基本原则，因各个单位的泵数不相等，只能按最大泵数进行数据库结构设计，当站库继续扩大、泵数继续增加时需要对数据库结构和所有用到该数据表 【黑马程序员】Oracle 游标使用全解 有很多同学在使用oracle 数据库的时候对游标这个东西不知道如何处理，感觉使用起来很难，今天我们就讨论一下游标的使用，满满的都是干货，以下代码几乎包含了oracle 游标使用的方方面面，全部通过了测试！ -- 声明游标； 1 C URSOR cursor_name IS select_statement --For 循环游标 --（1）定义游标 --（2）定义游标变量 --（3）使用for 循环来使用这个游标 01 02 03 04 05 06 07 08 09 10 11 12 13 14 declare --类型定义 cursor c_job is select empno ,ename ,job ,sal from emp where job ='MANAGER'; --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp 中的一行数据类型 c_row c_job%rowtype; begin for c_row in c_job loop dbms_output.put_line (c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal ); end loop; end ; --Fetch 游标 --使用的时候必须要明确的打开和关闭 01 02 03 declare --类型定义 cursor c_job 04 05 06 07 08 09 10 11 12 13 14 15 16 is select empno,ename,job,sal from emp where job='MANAGER'; --定义一个游标变量 c_row c_job%rowtype; begin open c_job; loop --提取一行数据到c_row fetch c_job into c_row; --判读是否提取到值，没取到值就退出 --取到值c_job%notfound 是false --取不到值c_job%notfound 是true exit when c_job%notfound; dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||' -'||c_row.sal); end loop; --关闭游标 close c_job; end; --1：任意执行一个update操作，用隐式游标sql的属 性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。 begin update emp set ENAME='ALEARK' WHERE EMPNO=7469; if sql%isopen then dbms_output.put_line('Openging'); else dbms_output.put_line('closing'); end if; if sql%found then dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行 else dbms_output.put_line('Sorry'); end if; if sql%notfound then dbms_output.put_line('Also Sorry'); else dbms_output.put_line('Haha'); end if; Oracle存储过程基本语法 存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracl e数据库去创建一个叫做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 t ype，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，变量带取值范围，后面接分号 oracle存储过程函数汇总() Character function return character value These functions all received is the character parameter type group (except CHR) and returns the character value? In addition to the special instructions, the function returns VARCHAR2 most numerical types? The restrictions on the return type of the character function are the same as those for the basic database type? The maximum value of character variable storage: The VARCHAR2 value is limited to 2000 characters (ORACLE 8 to 4000 characters) The CHAR value is limited to 255 characters (0RACLE8 2000) The long type is 2GB The Clob type is 4GB 1,CHR Syntax: Chr (x) Function: return in the database character set with numerical equivalence with the character of X. CHR and ASCII are a pair of inverse functions? After CHR conversion character after ASCII conversion and obtained the original word 游标CURSOR的使用学习 游标的类型： 1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐 式游标，名字固定叫sql。 2，显式游标：显式游标用于处理返回多行的查询。 3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询 的结果 一、隐式游标 在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息q隐式游标的属性有： q%FOUND – SQL 语句影响了一行或多行时为 TRUE q%NOTFOUND – SQL 语句没有影响任何行时为TRUE q%ROWCOUNT – SQL 语句影响的行数 q%ISOPEN - 游标是否打开，始终为FALSE begin update student s set s.sage = s.sage + 10; if sql %FOUND then dbms_output.put_line('这次更新了' || sql% rowcount); else dbms_output.put_line('一行也没有更新'); end if; end; 在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS declare sname1 student.sname%TYPE; begin select sname into sname1 from student; if sql%found then dbms_output.put_line(sql%rowcount); else dbms_output.put_line('没有找到数据'); end if; exception when too_many_rows then dbms_output.put_line('查找的行记录多于1行'); when no_data_found then dbms_output.put_line('未找到匹配的行'); end; 显式游标： sqlserver与oracle的不同之处在于：最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步：声明游标、打开游标、使用游标读取记录、关闭 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct wher e xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF; 4.while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5.变量赋值 V_TEST := 123; 6.用for in 使用cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END; 7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHE RE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER; 8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 关于oracle存储过程的若干问题备忘 1.在oracle中，数据表别名不能加as，如： --创建学员信息表 create table stuInfo ( stuId varchar2(15) not null, --学员Id,主键 stuName varchar2(10) not null, --学员姓名 stuNo varchar2(10) not null, --学号,外键应用stuMarks的stuNo stuAge int not null, --年龄 stuAddress varchar2(100) default('中国') not null,--家庭住址 stuEmail varchar2(100) not null --电子邮箱 ); alter table stuInfo add constraint PK_stuId primary key(stuId); alter table stuInfo add constraint CK_stuAge check(stuAge between 18 and 40); alter table stuInfo add constraint CK_stuEmail check(stuEmail like '%@%'); --创建序列 create sequence SQ_ID increment by 1 start with 10000; --为学员信息表创建触发器TG_STUID create or replace trigger TG_STUID before insert on stuInfo for each row begin select 'SID'||SQ_ID.Nextval into :new.stuId from dual; end; --向学员信息表中添加数据 insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('张飞','s1t0102',30,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('关羽','s1t0830',35,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('马超','s2t1326',25,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('刘备','s3t0403',40,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('诸葛亮','s2t1521',21,'蜀国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('刘翔','s3t0706',29,'上海',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('曹操','s3t0915',34,'魏国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('孙权','s1t1123',32,'东吴',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values ('董卓','s2t0507',35,'三国',''); insert into stuInfo(stuName,stuNo,stuAge,stuAddress,stuEmail) values Oracl e 存储过程 目录 Oracle 存储过程 (1) Oracle存储过程基础知识 (1) Oracle存储过程的基本语法 (2) 关于Oracle存储过程的若干问题备忘 (4) 1. 在Oracle中，数据表别名不能加as。 (4) 2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利 用游标的话就另当别论了。 (5) 3. 在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 (5) 4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段 会报错 (5) 5. 在存储过程中，关于出现null的问题 (5) 6. Hibernate调用Oracle存储过程 (6) 用Java调用Oracle存储过程总结 (6) 一、无返回值的存储过程 (6) 二、有返回值的存储过程（非列表） (8) 三、返回列表 (9) 在存储过程中做简单动态查询 (11) 一、本地动态SQL (12) 二、使用DBMS_SQL包 (13) Oracle存储过程调用Java方法 (16) Oracle高效分页存储过程实例 (17) Oracle存储过程基础知识 商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。 存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。 要创建一个过程对象（procedural object），必须有CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有CREATE ANY PROCEDURE 权限。执行procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。 Oracle10.2并发条件下更新游标数据的研究 本文测试在PL/SQL编程中，更新游标数据的2种方式以及并发条件下各种方式的实际表现。2种方式的效率问题不在此文讨论之列！ 一、环境准备 数据库：Oracle10.2.0.4 测试工具：PL/SQL Developer9 二、数据准备 我们使用oracle自带的演示用户scott登录数据库，为清楚看到数据变化，执行以下语句： 查询emp表： 结果如图1所示： 图1 Sal字段已经全部更新为1000 三、创建存储过程ps_cursor_for_update 四、测试ps_cursor_for_update PL/SQL Developer工具具有很强大的plsql调试功能，我们使用两个test窗口进行模拟并发执行的情况 在编辑存储过程的界面，在打开游标的代码行加入一个断点： 图2 在存储过程ps_cursor_for_update上点击右键，打开两个test窗口： 窗口1中，输入参数填写2000 窗口2中，输入参数填写3000，如图3、4所示： 图3 图4 两个窗口分别点击start bugger按钮，开始调试，并点击run按钮，分别运行到打开游标的一行，并在窗口2中进行单步调试，运行到如图5所示位置： 图5 此时窗口1中，开始单步调试，发现状态栏处于运行中（图7），但调试光标始终停在断点行（图6） 图6 图7 说明游标打开的数据已经被窗口2的进程锁定，所以窗口1的进程无法打开数据 下面把窗口2的断点去掉，并点击run按钮使此过程执行完毕，可以发现，此时窗口1中，代码已经执行到了原断点位置的下一行： 图8 结论：窗口2执行了commit语句，PL/SQL过程结束，并解锁操作的数据，使窗口1的过程得以打开游标。 查询emp表的数据： Oracle 数据库实验报告 系别：******* 班级：******* 姓名：******* 学号：******* 指导老师:**** 一．实验名称 用带参数游标的FOR循环依此输出每一个部门名称，在部门名称的下面输出该部门的员工姓名和工资，按工资的升序排列。二．实验目的 通过本次实验，逐渐熟悉oracle数据库的应用及输出的格式，更加深刻的了解其输出的语法，变量的定义及赋值和操作环境以及循环结构，异常的捕获，定义，处理，Oracle数据库中表的创建，插入，及表中所需数据的提取,掌握游标的定义，使用。三．实验步骤 开始——运行输入cmd，出来oracle运行界面，代码如下： DECLARE --变量，游标的声明； CURSOR dept_cursor IS SELECT Dname,Deptno FROM DEPT; --游标的定义； CURSOR emp_cursor(v_dept CHAR) IS SELECT Ename,Salary FROM EMP WHERE deptno=v_dept ORDER BY Salary;--按工资升序排序格式输出； BEGIN FOR dept_record IN dept_cursor LOOP --for循环，查询的结果单独的输出； DBMS_OUTPUT.PUT_LINE('部门名称为： '||dept_record.Dname||chr(10)||'部门编号：'||dept_record.Deptno); --输出语句； FOR emp_record IN emp_cursor(dept_record.Deptno) LOOP DBMS_OUTPUT.PUT_LINE('员工姓名为：'||emp_record.Ename||'员工工资为:'||emp_record.Salary); END LOOP; END LOOP; END; / 四．实验结果oracle存储过程讲解及实例
oracle游标的使用及属性
oracle实验--存储过程
Oracle 游标使用全解
Oracle显式游标和隐式游标
oracle11g游标及触发器相关知识
oracle存储过程例子
Oracle 函数返回游标的方法及应用
【黑马程序员】Oracle 游标使用全解
Oracle存储过程基本语法
oracle存储过程函数汇总().docx
Oracle存储过程学习_游标CURSOR使用
Oracle存储过程基本结构
Oracle数据库游标在包中的使用
Oracle+PlSql存储过程
Oracle10.2并发条件下更新游标数据的研究
Oracle 数据库中 游标实验报告