ORACLE开发培训公司版.docx

数据库开发存储过程相关1. 存储过程的说明2. 存储过程的创建语法3. 使用存储过程说明（参数传递、变量声明、异常捕获）4. 存储过程中的游标使用5. 存储过程中的数组6. 存储过程中的异常7. 存储过程中的自定义类型8. 存储过程中返回参数的说明（游标、类型、表（基于提交的、基于会话的、普通的）9. 存储过程的调试10. 存储过程的死锁和解锁11. 嵌套调用过程、函数、包12. 存储过程的返回值和JAVA 如何接受返回值函数相关1. 函数的说明2. 函数的创建语法3. 函数的调用4. 函数应该注意的地方（必须有返回值，每一行都是重新调用这个函数）5. 函数中调用其他高级对象6. 函数的异常包相关1. 包的说明2. 包中声明变量3. 包中定义存储过程和函数4. 包中使用重载5. 包中如何同义词和调试存储过程相关存储过程的说明存储过程就是PL/SQL 块，就是一堆SQL的组成，是存入数据库的PL/SQL 块存储过程和函数都是把PL/SQL块以命令的方式存放到数据库中的，代码不用保存到本地，执行速度快安全 针对不同用户授予不同权限方便开发 可以传递返回参数，针对复杂的逻辑和包表可以提高速度存储过程的创建删除执行语法存储过程创建时需要权限的，一般可以授予create procedure 或者create any procedure创建语法如下：CREATE OR REPLACE PROCEDURE 存储过程名( 参数IN|OUT|IN OUT 数据类型.)AS|IS说明部分BEGIN可执行部分EXCEPTION错误处理部分END 过程名;Or replace 是说如果已经存在就覆盖这个过程参数有三种类型 in out inout 分别问输入参数，输出参数 输入输出参数其中输入参数在过程中是不允许被修改的AS|IS 是关键字，写一个就行，就免就是过程的说明部分，也可以再这里定义局部变量存储过程一般只有拥有者才能删除，或者拥有DROP ANY procedure权限的用户删除语法为：drop procedure procedureName存储调用一般只能是拥有者才能调用，或者拥有EXECUTE ANY PROCEDURE权限的人才能调用，语法为：EXECUTE 过程名（参数） BEGIN过程名（参数.）END ;存储过程中的游标和循环使用四种循环：EXIT WHEN循环它和loop 差不多，都是判断条件是否满足，这种循环我用的少，除非是那种找数据，非要找到不可的时候，给个例子：create or replace procedure proc_test isi number;begini:=0;LOOPExit When(i5);Dbms_Output.put_line(i);i:=i+1;END LOOP;end proc_test;LOOP循环create or replace procedure proc_test isi number;begini:=0;loopi:=i+1;dbms_output.put_line(i);if i5 thenexit;end if;end loop;end proc_test; while循环循和前面唯一的区别就是先判断后执行，也是基于条件的create or replace procedure proc_test isi number;begini:=0;while i5 loopi:=i+1;dbms_output.put_line(i);end loop;end proc_test;for 循环create or replace procedure proc_test isbeginfor cur in (select * from tb_pubhd hd where rownum10) loop dbms_output.put_line(cur.pubhd001); end loop;end proc_test;差不多对于简单的循环来说，以上四种是常用的，条件是已知的，比如前面的loop循环我们知道要循环到5了就跳出，for循环则是我们知道要循环的SQL.但是对于实际开发中，很多时候我们自己都不知道要循环什么SQL，甚至不知道要遍历SQL的哪些字段，或者一些其他稀奇古怪的业务逻辑，用循环无法实现，比如：我现在要你循环指定DBNAME下面的salha表的前面 N调记录，我想用前面的循环应该很难实现，循环N条记录还好，但是在DBNAME下面的，我估计就难了，这个时候我们用游标可以很简单的搞定这个问题游标是什么？简单的说：游标就是能够从多条记录中每次提取一条记录的机制，这个多条记录是未知的，甚至字段都是未知的游标的两个方面： SQL查询的结果集，当前行游标的两个状态：打开，关闭游标的类型：静态游标：静态游标是程序执行的时候不需要解析SQL语言，因为他在编译的时候就确定了，静态游标又分为显示游标和隐士游标REF游标：有些程序只有等运行起来我们才知道要执行什么SQL，需要传递参数的，所以ref是在运行的时候加载结果集。相对来说：静态游标比动态游标效率稍微高点隐士游标：在PLSQL中使用DML语言，ORACLE预先定义一个名为SQL的隐士游标显示游标：就是用户自己定义的游标的四个属性：%fund; 游标指针在结果集中找到记录%notfund； 游标指针在结果中没有找到数据%rowcount； 当前时刻已经获取的记录条数%isopen； 是否打开例题：-华丽的分割线-需求：现在要求用游标实现从all_tables视图中取TABLE_NAME字段包含 TB_PUBHD的表，并且打印出来-答案一create or replace procedure proc_test is /*声明一个游标*/ cursor cur is select * from all_tables d where d.TABLE_NAME like %TB_PUBHD%; R_CUR all_tables%rowtype;begin open cur; loop fetch cur into R_CUR; exit when cur%notfound; dbms_output.put_line(r_cur.TABLE_NAME); end loop;end proc_test; -华丽的分割线需求：现在要求用游标实现从all_tables视图中取TABLE_NAME字段包含某个条件的表，并且打印出来或许传TB_PUBHD 或许传TB_PUBHC,这个字段的值是未知的-答案一-tname是需要查询的表名create or replace procedure proc_test(tname varchar2) is /*声明一个游标*/ cursor cur(t_name varchar2) is select * from all_tables d where d.TABLE_NAME like %|t_name|%; R_CUR all_tables%rowtype;begin open cur(tname); loop fetch cur into R_CUR; exit when cur%notfound; dbms_output.put_line(r_cur.TABLE_NAME); end loop;end proc_test;-答案二create or replace procedure proc_test(tname varchar2) is /*声明一个游标*/ cursor cur(t_name varchar2) is select * from all_tables d where d.TABLE_NAME like % | t_name | %;begin for ccur in cur(tname) loop dbms_output.put_line(ccur.table_name); end loop;end proc_test;-华丽的分割线-现在搞不清楚有是一个什么样子的SQL（反正是一个查询语句）-这个SQL反正有三个字段分别是V1 VARCAHR2(100)V2 VARCAHR2(100),V3 VARCAHR2(100)-要你依次循环这个SQL的结果集，并且打印出来三个字段create or replace procedure proc_test(v_sql varchar2) is /*声明一个类型*/ type R_CUR_ROW is ref cursor; C_CUR_ROW R_CUR_ROW; v1 varchar2(100); v2 varchar2(100); v3 varchar2(100);begin open C_CUR_ROW FOR v_sql; loop fetch C_CUR_ROW into v1,v2,v3; exit when C_CUR_ROW%NOTFOUND; DBMS_OUTPUT.put_line(V1|-|V2|-|V3); end loop;end proc_test;select pubhd_id,pubhd001,pubhd002 from tb_pubhd hd where rownum10-华丽的分割线-现在搞不清楚有是一个什么样子的SQL（反正是一个查询语句）-这个SQL是根据实际情况组的，结果和dept表结构一致-要你依次循环这个SQL的结果集，并且打印这个结果集的 d_name字段create or replace procedure tmp_test(v_sql varchar2) is /*声明一个类型*/ type R_CUR_ROW is ref cursor; C_CUR_ROW R_CUR_ROW; v_dept dept%rowtype;begin open C_CUR_ROW FOR v_sql; loop fetch C_CUR_ROW into v_dept; exit when C_CUR_ROW%NOTFOUND; DBMS_OUTPUT.put_line(v_dept.d_name); end loop;end;要注意的地方：GotoCREATE OR REPLACE PROCEDURE proc_test(pubhd_id VARCHAR2,pubhd002 varchar2) AS i number; v_sql varchar2(1000);BEGIN i:=0; for cur in (select pubhd_id,pubhd001,pubhd002 from tb_pubhd hd where rownum10) loop i:=1+i; if i8 then goto end_loop; end if; dbms_output.put_line(cur.pubhd_id); dbms_output.put_line(); end loop;END proc_test;赋值为空字符串类型的SQLCREATE OR REPLACE PROCEDURE proc_test(pubhd_id VARCHAR2,pubhd002 varchar2) AS var number(10); v_sql varchar2(1000);BEGIN v_sql := select count(1) from tb_pubhd hd where hd.pubhd_id=:pubhd_id and pubhd002=:pubhd002; execute immediate v_sql into var using pubhd_id,pubhd002; dbms_output.put_line(var);END proc_test;存储过程中的集合我们在实际开发中，难免要把某一个对象存起来，放到以后再用集合有三种：pl/sql集合类型包括索引表（pl/sql table）、嵌套表（Nested Table）、变长数组（VARRAY） -集合方法 exists(index) 索引处的元素是否存在 count 当前集合中的元素总个数 limit 集合元素索引的最大值 索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 first 返回集合第一个元素索引 last 返回集合最后一个元素索引 prior 当前元素的前一个 next 当前元素的后一个 extend 扩展集合的容量，增加元素 只是用于嵌套表和varry类型 x.extend 增加一个null元素 x.extend(n) 增加n个null元素 x.extend(n,i) 增加n个元素，元素值与第i个元素相同 trim 从集合的尾部删除元素 只用于NEST TABLE和VARRY类型 trim 从集合尾部删除一个元素 trim(n) 从集合尾部删除n个元素 delete 按索引删除集合元素 delete 删除所有 delete(index) 删除第index个 delete(a,b) 删除a-b之间的所有元素 -1 索引表/*下标无限制，可以为负数元素个数无限制定义 TYPE type_name IS TABLE OF element_type NOT NULL INDEX BY key_type; type_name：用户自定义数据类型的名字 element_type：索引表中元素类型 key_type：索引表元素下标的数据类型（BINARY_INTEGER，PLS_INTEGER,VARCHAR2）*/declare type index_tab_type is table of varchar2(30) index by BINARY_INTEGER; v_table index_tab_type;begin v_table(-1) :=hello;-设定下标为-1的元素的值 v_table(1) :=,; dbms_output.put_line(v_table(-1)|-|v_table(1); dbms_output.put_line(元素个数：|v_table.count); v_table(5) :=world; dbms_output.put_line(元素个数：|v_table.count); dbms_output.put_line(第一个元素|v_table.first); dbms_output.put_line(最后一个元素|v_table.last);end;-使用varchar2作为索引元素类型 ,其实也就和java中的map一样了 key-value形式存储declare type index_tab_type is table of varchar2(30) index by varchar2(30); v_table index_tab_type;i number:=0;begin -我们将商品资料表的前面十条记录保存到集合，然后又输出出来 for cur in (select * from tb_pubhd hd where rownum11) loop v_table(cur.pubhd001):= cur.pubhd_id; dbms_output.put_line(v_table(cur.pubhd001);- end loop; dbms_output.put_line(v_table.count);-3end;-2嵌套表 NESTED TABLE/*下标从1开始，元素个数没有限制(*使用时必须先初始化，用extend属性可以扩展元素个数)可以作为表定义数据类型，但是前提是要先create 创造嵌套表类型，这就可以实现1对多了定义 TYPE type_name IS TABLE OF element_type; 和索引表的区别也就是看看有无index by语句，嵌套表的索引固定是int型的*/declare type nest_tab_type is table of tb_pubhd.pubhd001%type; v_tab nest_tab_type;begin v_tab := nest_tab_type(); /*必须初始化，不然会报错*/ select tb_pubhd.pubhd001 into v_tab(1) from tb_pubhd where tb_pubhd.pubhd_id = 268845; dbms_output.put_line(v_tab(1); v_tab.extend; select tb_pubhd.pubhd001 into v_tab(2) from tb_pubhd where tb_pubhd.pubhd_id = 268850; dbms_output.put_line(v_tab(2);end;-如何在表里面嵌套类型在表列中使用嵌套表 嵌套表类型的列是单独一个表存储- -先创建一个这样的类型 必须 才能使用create type pubhd_type is table of varchar2(30); create table test_pubhd_type( id int, pubhd002 pubhd_type -使用 ) nested table pubhd002 store as TB_PUBHD_TYPE;- pubhd002字段用嵌套表存储，表名 -上面语句执行完之后，在生成test_pubhd_type的同时会生出一个关联表TB_PUBHD_TYPE用来存储关联表的数据-插入数据insert into test_pubhd_type values(2,pubhd_type(one,two,three,four); -查询数据declare v_id int; v_tab pubhd_type;begin select * into v_id,v_tab from test_pubhd_type where id=2; dbms_output.put_line(v_id); for i in 1.v_tab.count loop dbms_output.put_line(v_tab(i); end loop;end;-3 VARRY 可变数组 变长数组/*定义 TYPE type_name IS VARRAY(size_limit) OF element_typeNOT NULL; 这个就和java中的数组差不多了，下标from 1 ，定义时先指定最大元素个数，也和varchar2（size）这种一样。 使用时也必须先用构造方法初始化 可以作为表列类型*/declare type varr is VARRAY(10) of int; v_varr varr :=varr();begin -dbms_output.put_line(varr.count); for i in 1.5 loop v_varr.extend; v_varr(i) :=i*i; end loop; for i in 1.5 loop dbms_output.put_line(v_varr(i); end loop;end;-可变数组作为表列类型 可变数组是存储在表内部的，不同于嵌套表create type varr_type is varray(10) of varchar2(30);-先创建类型create table test_varray( id int, name varchar2(30), params varr_type -param是使用可变数组类型);-插入数据insert into test_varray values(1,bird,varr_type(a,b,c);-查询数据declare v_varr varr_type; v_name test_%type;begin select name,params into v_name,v_varr from test_varray where id=1; for i in 1.v_varr.count loop dbms_output.put_line(v_varr(i); end loop;end; -记录表 集合类型是表的一行，集合元素不仅可以是简单类型，也可以是复合类型declare type emp_tab_type is table of TB_PUBHD%rowtype index by binary_integer; v_tab emp_tab_type;begin select * into v_tab(1) from TB_PUBHD where TB_PUBHD.PUBHD_ID=268848; dbms_output.put_line(v_tab(1).PUBHD001|-|v_tab(1).PUBHD002|-|v_tab(1).PUBHD003);-500000897-AP-66三位-AP-66三位end;-集合方法综合使用例子 主要是遍历集合declare type v_table_type is table of varchar2(30); v_table v_table_type :=v_table_type();begin dbms_output.put_line(v_table.count|-|v_table.limit|-|v_table.first|-|v_table.last); - 0- if v_table.exists(1) then -判断index1是否有值 null; else v_table.extend;-扩充一个值 select TB_PUBHD.PUBHD001 into v_table(1) from TB_PUBHD where TB_PUBHD.PUBHD_ID=268848; dbms_output.put_line(v_table(1); -SCOTT dbms_output.put_line(v_table.count|-|v_table.limit|-|v_table.first|-|v_table.last);- 1-1-1 end if;end;-遍历集合（不连续）declare type v_tab_type is table of varchar2(30) index by binary_integer; v_tab v_tab_type; v_index int;begin -添加几个无序有间隔的元素 v_tab(-1) :=a; v_tab(2) :=b; v_tab(3) :=c; v_tab(5) :=d; v_tab(-2) :=e; -method 1 v_index :=v_tab.first; for i in 1.v_tab.count loop dbms_output.put_line(v_tab(v_index); v_index :=v_tab.next(v_index); end loop; -method2 for i in v_tab.first.v_tab.last loop if v_tab.exists(i) then dbms_output.put_line(v_tab(i); else dbms_output.put_line(元素不存在); end if; end loop;end;-修改集合中的元素declare type v_tab_type is table of int; v_tab v_tab_type :=v_tab_type();begin for i in 1.5 loop if v_tab.exists(i) then null; else v_tab.extend;-扩展一个 v_tab(i) := i*i; end if; end loop; dbms_output.put_line(v_tab.count);-5 for i in 1.5 loop dbms_output.put_line(v_tab(i); if v_tab(i)=9 then -删除3*3=9的元素 v_tab.delete(i); end if; end loop; dbms_output.put_line(v_tab.count);-4 end ;存储过程中的异常即使是写的非常好的PL/SQL程序也会遇到错误或者无法预料的事情，如：数据错误或者未知的逻辑错误等等。一般来说遇到未知的错误或者无法预料的事情就会导致程序直接挂掉或者显示一窜ORACLE错误（ORA-XXXXX）,为了显示友好和让程序继续跑下去，我们就需要捕获异常异常有三种1预定义(Predefined)错误ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。 2非预定义(Predefined)错误 即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。3用户定义(User_define)错误程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。异常一般放在PL/SQL的后半部分，语法结构为：EXCEPTION WHEN first_exception THEN WHEN second_exception THEN WHEN OTHERS THEN END;非自定异常（ORACLE自带的）DECLARE v_id number(32);BEGIN select pubhd_id into v_id from tb_pubhd hd where rownum3;END;-捕获异常DECLARE v_id number(32);BEGIN select pubhd_id into v_id from tb_pubhd hd where rownum3; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(错误代码: | SQLCODE | CHR(13) | 错误信息: | SQLERRM);END;用户自定义异常当我们判断一个数据或者传过来的参数不是我们预计的范围或者值的时候，我们可以通过自定义异常来处理，通过通过RAISE语句来触发，当引发一个异常的时候EXCEPTION块的异常部分1 在PL/SQL 块的定义部分定义异常情况： EXCEPTION;2 RAISE ；3 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。例子：CREATE OR REPLACE PROCEDURE test_proc(O_RET_CODE OUT VARCHAR2, O_RET_MSG OUT VARCHAR2, O_GOODS_NAME OUT VARCHAR2, i_PUBHD_ID NUMBER) AS v_id number(32); V_goodsName varchar2(1000); no_result EXCEPTION;BEGIN O_RET_CODE := 0; O_RET_MSG := 操作成功; select count(pubhd_id) into v_id from tb_pubhd hd where HD.PUBHD_ID = i_PUBHD_ID; if v_id1then O_GOODS_NAME:=; RAISE no_result; else select pubhd002 into O_GOODS_NAME from tb_pubhd hd where HD.PUBHD_ID = i_PUBHD_ID; END IF;EXCEPTION WHEN no_result THEN O_RET_CODE := -1; O_RET_MSG := 商品ID: | i_PUBHD_ID | 错误,请核对!; WHEN OTHERS THEN O_RET_CODE := -1; O_RET_MSG := SQLERRM; END;调用：declarev_ret_code varchar2(100);v_ret_msg varchar2(100);V_GOODSNmae varchar(100);begin test_proc(v_ret_code,v_ret_msg,V_GOODSNmae,268846); dbms_output.put_line(V_GOODSNmae|-|v_ret_code|-|v_ret_msg); end ;存储过程中的自定义类型我们知道游标中操作的一行数据时 当前结果集中的某一行赋值给一个变量的时候我们才能操作的，那么如果我们有时候操作一个未知的SQL，这个SQL 有2个字段，我们怎么操作这一行了？解决办法有三种：一种是写成静态游标，即：已知当前SQL，然后通过FOR循环来操作，二种是写两个字段每次循环这个游标的时候将字段重新赋值例子：declare /*声明一个游标*/ cursor cur(t_name varchar2) is select D.TABLE_NAME,D.OWNER from all_tables d where d.TABLE_NAME like %|t_name|%; begin FOR c_CUR IN cur(TB_PUBHD) loop dbms_output.put_line(c_CUR.TABLE_NAME|-|c_CUR.OWNER); end loop;end ;/*上面的例子按理说可以解决我刚刚说的问题，但是如果这个SQL是未知的，即我们要根据程序跑起来之后 才能去执行的SQL，或许条件都不一样，那静态游标显然有问题，我们试试动态游标*/例子:declare /*声明一个类型*/ type R_CUR_ROW is ref cursor; C_CUR_ROW R_CUR_ROW; v1 varchar2(100); v2 varchar2(100); v3 varchar2(100); v_sql varchar2(1000);begin v_sql:= select hd.pubhd001,hd.pubhd002,hd.pubhd003 from tb_pubhd hd where rownum10; open C_CUR_ROW FOR v_sql; loop fetch C_CUR_ROW into v1,v2,v3; exit when C_CUR_ROW%NOTFOUND; DBMS_OUTPUT.put_line(V1|-|V2|-|V3); end loop;end proc_test;/*上面的例子解决了动态游标获取值的问题，但是如果有100个字段，怎么办？ Into 100次？ 不太现实，也很难搞，我们可以再过程中自定义一个类型*/declare /*声明一个类型*/ type R_CUR_ROW is ref cursor; C_CUR_ROW R_CUR_ROW; v1 varchar2(100); v2 varchar2(100); v3 varchar2(100); v_sql varchar2(1000); /*用自定义类型*/ TYPE c_row_goodsRow IS RECORD( goodsName tb_pubhd.pubhd003%TYPE, PPName TB_PUBHB.PUBHB002%TYPE ); c_row_goodsRow_list c_row_goodsRow; begin v_sql:= select hd.pubhd003,hb.pubhb002 from tb_pubhd hd join tb_pubhb hb on hd.pubhd005=hb.pubhb_id where rownum10; open C_CUR_ROW FOR v_sql; loop fetch C_CUR_ROW into c_row_goodsRow_list; exit when C_CUR_ROW%NOTFOUND; DBMS_OUTPUT.put_line(c_row_goodsRow_list.goodsName|c_row_goodsRow_list.PPName); end loop;end ;存储过程中返回参数的说明（游标、类型、表（基于提交的、基于会话的、普通的）一般来说存储过程可以返回很多类型的对象，具体多少我也搞不清楚，反正很多。但是我们常用的也就几种：自定义对象，游标，字符串，表我们先看一个返回字符串的存储过程和JAVA代码样例：-调用返回字符串的存储过程：CREATE OR REPLACE PROCEDURE test_proc(O_RET_CODE OUT VARCHAR2, O_RET_MSG OUT VARCHAR2, I_NUMBER NUMBER) AS no_result EXCEPTION;BEGIN O_RET_CODE := 0; O_RET_MSG := 操作成功; IF I_NUMBER0 THEN raise no_result; END IF;EXCEPTION WHEN no_result THEN O_RET_CODE := -1; O_RET_MSG := 编号: | I_NUMBER | 错误,请核对!; WHEN OTHERS THEN O_RET_CODE := -1; O_RET_MSG := SQLERRM;END;返回游标的存储过程：CREATE OR REPLACE PROCEDURE test_proc(O_RET_CODE OUT VARCHAR2, O_RET_MSG OUT VARCHAR2, cur_list out sys_refcursor) ASBEGIN O_RET_CODE := 0; O_RET_MSG := 操作成功; open cur_list for select * from tb_pubhd hd where rownum11;EXCEPTION WHEN OTHERS THEN O_RET_CODE := -1; O_RET_MSG := SQLERRM; END;JAVA代码：import java.sql.*;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.dom4j.DocumentHelper;import org.dom4j.Element;public class testReturnString /* * param args * throws SQLException * throws ClassNotFoun