ORACLE存储过程，函数，包，游标

uule

浏览: 6358912 次
性别:
来自: 一片神奇的土地

最近访客更多访客>>

wangenbao1

hyjqdy

逆光下的轮廓

taominghua

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle

1、 PL/SQL语句块

PL/SQL语句块只适用于Oracle数据库，使用时临时保存在客户端，而不是保存在数据库。

基本语法:

declare
  变量声明、初始化
begin
  业务处理、逻辑代码
exception
  异常捕获
end;

变量声明：<变量名> <类型及长度> [:=<初始值>]
例：v_name varchar2(20):=’张三’;

2、循环语句

loop循环语法：

    loop
      exit  when  表达式
    end loop;

while循环语法：

while 表达式
  loop
  end loop;

for循环语法:

    for  <变量>  in  <变量取值范围（小值..大值，如1..100）> loop
    end loop;

for循环的变量可不做声明及初始化。

3、 if判断语句

基本语法：

if  <表达式>  then
…
else  if  <表达式>  then
…
else
…
end  if;
end  if;

例：

declare
  v_identity number(4):=0;
begin
  loop
    if v_identity=1 then
      dbms_output.put_line('v_identity=1');
    else if v_identity=3 then
      dbms_output.put_line('v_identity=3');
    else if v_identity=6 then
      exit;
    else
      dbms_output.put_line('v_identity is not 1 or 3');
    end if;
    end if;
    end if; -- 注意，有多少个if就要有多少个end if结束标志。

    v_identity:=v_identity+1;

  end loop;

exception
  when others then dbms_output.put_line('error!');
end;

4、分支case

基本语法：

case  <变量>
when  常量  then
…
when  常量  then
…
else
      …
end case;

例：

declare
  v_number number(4):=3;
  v_string varchar(20):='abc';
begin
  case v_number
    when 1 then
      dbms_output.put_line('v_number is '||1);
    when 2 then
      dbms_output.put_line('v_number is '||2);
    when 3 then
      dbms_output.put_line('v_number is '||3);

  end case;

  case v_string
    when 'ab' then
      dbms_output.put_line('v_string is '||'ab');
    when 'bc' then
      dbms_output.put_line('v_string is '||'bc');
    else -- 缺省匹配
      dbms_output.put_line('v_string is other value');
  end case;

exception
  when others then dbms_output.put_line('error!');
end;

5、异常(exception)

声明异常语法：<异常名> exception;
抛出异常语法：raise <异常名>;
捕获异常语法：when <异常名> then 异常处理语句;

例：

declare
  v_input varchar2(1):='&throw';-- 动态输入
  v_exception_1 exception; -- 自定义异常
  v_exception_2 exception;
  others exception; -- 系统异常

begin
  if v_input='1' then
    raise v_exception_1; -- 抛出异常
  else if v_input='2' then
    raise v_exception_2;
  else
    raise others;
  end if;
  end if;

exception
  -- 捕获异常

  when v_exception_1 then dbms_output.put_line('throw exception: v_exception_1');
  when v_exception_2 then dbms_output.put_line('throw exception: v_exception_2');
  when others then dbms_output.put_line('throw exception: others');

end;

6、游标(cursor)

声明游标语法：cursor <游标名> is select语句;
声明ref游标语法:<游标名> is ref cursor;
打开游标语法：open <游标名>;
移动游标并获取数据语法：fetch <游标名> into <用于保存读取的数据的变量的名>;
关闭游标语法:close <游标名>;
游标属性（游标的属性必须在关闭游标之前）：

%isopen: 判断游标是否打开
%notfound: 找不到数据时
%found:
%rowcount: 返回当前游标已扫描的数据行数量

游标分类：1、显示游标（自定义游标）；2、隐示游标（系统游标）；3、REF游标

例：

declare
  v_row test%rowtype; -- 匹配t_test表中一行所有的数据类型
  cursor v_cur is
    select * from test;-- 声明游标
begin
  open v_cur;-- 打开游标
  loop
    fetch v_cur into v_row;-- 将游标所在行的数据转存到v_row中
    exit when v_cur%notfound; -- 当游标到最后一行时跳出
    dbms_output.put_line('id = '||v_row.t_id||'  name = '||v_row.t_name||'  msg = '||v_row.t_msg);
  end loop;
  close v_cur;-- 关闭游标
exception
  when others then dbms_output.put_line('throw exception: others');
end;

-- REF游标 --

create or replace package upk_select_test
as 
type uc_test is ref cursor; -- 声明ref游标
end upk_select_test;

-- 存储过程中调用ref游标，并将查询结果以游标的方式返回
create or replace procedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
  open uc_result for select * from t_test;
end up_select_test_2;

7、通配类型操作符

%type: 通配某行某列数据类型，如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的数据类型，如 v_row t_test%rowtype;匹配t_test表中一行所有的数据类型。

8、存储过程(procedure)

基本语法：

create  procedure  <过程名>(<参数列表,无参时忽略>)
as|is
  变量声明、初始化
begin
  业务处理、逻辑代码
exception
  异常捕获、容错处理
end  <过程名>;

参数:<参数名> in|out|in out <参数类型，无长度说明> ，如：v_name varchar2

in：入参
out：出参
in out：出入参

注：as|is表示as或is

调用语法：
1)、exec <过程名>;
2)、execute <过程名>;
3)、在PL/SQL语句块中直接调用

例：

create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
  dbms_output.put_line('交换前参数1：'||v_param1||'  参数2：'||v_param2);
  v_temp:=v_param1;
  v_param1:=v_param2;
  v_param2:=v_temp;
  dbms_output.put_line('交换后参数1：'||v_param1||'  参数2：'||v_param2);

exception
  when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;

-- 调用存储过程

declare
    v_param1 varchar2(20):='param1';
    v_param2 varchar2(20):='param2';
begin
  up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;

9、自定义函数(function)

基本语法：

create  function  <函数名>(<参数列表,无参时忽略>)
return  <返回值类型，无长度说明>
as|is
  变量声明、初始化
begin
  业务处理、逻辑代码
  return  <返回的值>;

exception
  异常捕获、容错处理
end  <函数名>;

参数：in 入参
注：只有入参的类型。

在存储过程和自定义函数中的参数的传递（入参和出参）不能使用%type或%rowtype匹配，不能使用空值null，但是存储过程可以返回空值。

例：

create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
  select t_name into v_name from t_test where t_id=v_id;
  return v_name;

exception
  when others then
    dbms_output.put_line('error');
end uf_select_name_by_id_test;


select uf_select_name_by_id_test(1) 姓名 from dual;-- select调用

declare --pl/sql语句块调用
  v_name varchar2(20);
begin
  v_name:=uf_select_name_by_id_test(1);
  dbms_output.put_line('name = '||v_name);
end;

10、包(package)

封装，可以封装过程(procedure)、函数(function)和变量。
注意，在包(package)中声明的过程(procedure)和函数(function)必须在包的实现体（package body）中定义实现。

基本语法：

create  package  <包名>
as|is
  变量声明
  存储过程声明
  自定义函数声明
end  <包名>;


create  package body <包名,与声明部分一致>
as|is
  存储过程的代码实现
  自定义函数的代码实现
end  <包名>;

例：

-- 创建包upk_hello
create or replace package upk_hello
is
  v_hello_world varchar2(20):='hello world'; -- 声明变量
  procedure up_hello_world(v_name in varchar2);-- 声明过程
  function uf_hello_world(v_name in varchar2) return varchar2;-- 声明函数

end upk_hello;


-- 实现包(upk_hello)里声明的方法
create or replace package body upk_hello
is
  procedure up_hello_world(v_name in varchar2)
  is
    v_string varchar2(100);
  begin
    v_string:=v_name||' say hello world!';
    dbms_output.put_line(v_string);
  exception
    when others then dbms_output.put_line('error');
  end up_hello_world;

  function uf_hello_world(v_name in varchar2) return varchar2
  is
    v_string varchar2(100);
  begin
    v_string:=v_name||' say hello world!';
    return v_string;
  exception
    when others then dbms_output.put_line('error');
  end uf_hello_world;

end upk_hello;


-- 包的调用

declare
  v_msg varchar2(100);
begin
  upk_hello.up_hello_world('bing');
  v_msg:=upk_hello.uf_hello_world('admin');

  dbms_output.put_line(v_msg);
  dbms_output.put_line(upk_hello.v_hello_world);

end;

存储过程中的3种循环：
1、

is
     i int;
begin
     i :=1;
     loop
         ..
         exit when i =10;
         i :=i+1;
     end loop;

2、

  i :=1;
     while i<=5 loop
         ..
         i :=i+1;
     end loop;

3、

for i in 1..100 loop
     ..........
   end loop;

这是我以前的学习笔记，LZ凑合着看看吧，应该能看懂一些吧
===================================================
55 java跟oracle 调用（存储过程，函数等）
55.1 Java调用无参的函数
1：函数为：
create or replace function MyF1 return varchar2 is
Result varchar2(20);
begin
dbms_output.put_line('now in My F1');
Result := 'Now MyF1 return';
return(Result);
end MyF1;

2：Java程序
/**
     * 演示调用有一个没有参数的函数
     * @throws Exception
     */
    private static void t1() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
        try {
            CallableStatement stmt = conn
                    .prepareCall("{?=call MyF1()}");
            stmt.registerOutParameter(1, Types.VARCHAR);
            stmt.execute();
            System.out.println(stmt.getString(1));
        } finally {
            conn.close();
        }
    }

55.2 Java调用无参但有返回值的存储过程
1：存储过程
create or replace procedure MyP1(str out Varchar2) is
begin
dbms_output.put_line('Hello Procedure.');
str :='Haha，Hello Procedure';
end MyP1;

2：程序
/**
     * 如何调用无参但有返回值的存储过程测试的存储过程
     * @throws Exception
     */
    private static void t2() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
        try {
            CallableStatement stmt = conn.prepareCall("{call MyP1(?)}");
            // 注意，这里的stmt.getInt(1)中的数值1并非任意的，而是和存储过程中的out列对应的，
            // 如果out是在第一个位置，那就是 stmt.getInt(1)，如果是第三个位置，就是getInt.getInt(3)，
            // 当然也可以同时有多个返回值，那就是再多加几个out 参数了。
            stmt.registerOutParameter(1, Types.VARCHAR);
            stmt.execute();
            System.out.println(stmt.getString(1));
        } finally {
            conn.close();
        }
    }

55.3 Java调用有参的（传入）函数
1：函数
create or replace function MyF2(a number,b varchar2) return varchar2 is
Result varchar2(50);
begin
dbms_output.put_line('a==='||a||',b=='||b);
Result := a||b;
return(Result);
end MyF2;

2：程序
/**
     * 调用有参的函数
     * @throws Exception
     */
    private static void t3() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
        try {
            CallableStatement stmt = conn
                    .prepareCall("{?=call MyF2(?,?)}");
            stmt.setInt(2, 15);
            stmt.setString(3, "HelloF2");
            stmt.registerOutParameter(1, Types.VARCHAR);
            stmt.execute();

            System.out.println(stmt.getString(1));
        } finally {
            conn.close();
        }
    }

55.4 Java调用有参的（传入传出）存储过程
1：存储过程
create or replace procedure MyP2(a in number,b in varchar2,c out varchar2) is
begin
       dbms_output.put_line('a=='||a||',b=='||b);
       c := 'ret=='||a||',b=='||b;
end MyP2;

2：程序
    /**
     * 调用有参数和返回值的存储过程
     * @throws Exception
     */
    private static void t4() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
        try {
            CallableStatement stmt = conn.prepareCall("{call MyP2(?,?,?)}");
            stmt.setInt(1, 5);
            stmt.setString(2, "测试");
            stmt.registerOutParameter(3, Types.VARCHAR);
            stmt.execute();

            System.out.println(stmt.getString(3));
        } finally {
            conn.close();
        }
    }

55.5 Java向存储过程传入传出对象的数组
1：在数据中创建对象
create or replace type UserModel as object(
uuid varchar2(20),
name varchar2(20)
);

2：在数据库中建立对象的集合类型
create or replace type userCol as table of UserModel;

create or replace type retUserCol as table of UserModel;

3：在数据库中建立包
包头：
create or replace package MyTestPackage is
       TYPE dbRs IS REF CURSOR;
       procedure MyP3(a1 in userCol,a2 out dbRs);

end MyTestPackage;

包体：
create or replace package body MyTestPackage is

procedure MyP3(a1 in userCol,a2 out dbRs) as
            umCol retUserCol := retUserCol();
begin
            for i in 1.. a1.count loop
                insert into tbl_test values (a1(i).uuid,a1(i).name);
            end loop;
            commit;

            umCol.Extend;
            umCol(1):=UserModel('retUuid11','retName11');
            umCol.Extend;
            umCol(2):=UserModel('retUuid22','retName22');

            open a2 for select * from table(cast(umCol as retUserCol));
end;

begin
null;
end MyTestPackage;

4：程序：
/**
     * 测试向pl/sql传入对象集合，从pl/sql返回任意的对象的集合
     * @param list
     * @throws Exception
     */
    private static void t5(List list) throws Exception {
        CallableStatement stmt = null;
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
            if (con != null) {
                ARRAY aArray = getArray(con, "USERMODEL", "USERCOL", list);//该函数调用的第二三个参数必须大写
                stmt = con.prepareCall("{call MyTestPackage.MyP3(?,?)}");
                ((OracleCallableStatement) stmt).setARRAY(1, aArray);
                stmt.registerOutParameter(2, OracleTypes.CURSOR);
                stmt.execute();
                ResultSet rs=(ResultSet)stmt.getObject(2);

                while(rs.next()){
                    String uuid = rs.getString("uuid");
                    String name = rs.getString("name");
                    System.out.println("the uuid="+uuid+",name="+name);
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private static ARRAY getArray(Connection con, String OracleObj, String Oraclelist,
            List objlist) throws Exception {
        ARRAY list = null;
        if (objlist != null && objlist.size() > 0) {
            StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
            STRUCT[] structs = new STRUCT[objlist.size()];
            Object[] result = new Object[0];
            for (int i = 0; i < objlist.size(); i++) {
                result = new Object[2];//数组大小应和你定义的数据库对象(UserModel)的属性的个数
                result[0] = ((UserModel)(objlist.get(i))).getUuid(); //将list中元素的数据传入result数组
                result[1] = ((UserModel)(objlist.get(i))).getName(); //

                structs[i] = new STRUCT(structdesc, con, result);
            }
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
                    con);
            list = new ARRAY(desc, con, structs);
        }
        return list;
    }

如果使用Tomcat的DBCP的连接池，需要把连接进行转换
public Connection getNativeConnection(Connection con) throws SQLException {
        if (con instanceof DelegatingConnection) {
         Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
         return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
        }
        return con;
      }

==============================

Oracle中table变量在JDBC中的运用

1.先定义一个返回数组类型的方法

create or replace type my_table_type is table of varchar2(20);

create or replace function func
return my_table_type
is
i my_table_type:=my_table_type();
begin
    select name bulk collect into i from emps;
    return i;
end;

2.在JDBC中调用,如果返回的是table变量

public void SelectAgus(String sql)
    {
        OracleCallableStatement call = null;
        try
        {
            call = (OracleCallableStatement) con.prepareCall(sql);
            //如果返回的是table则用ARRAY类型,如果返回的是OBJECT的就用STRUCT
            //第三个参数是定义table的类型名
            call.registerOutParameter(1, OracleTypes.ARRAY,"MY_TABLE_TYPE");
            call.execute();
            //获取第一个参数(这里即返回值)
            ARRAY array = call.getARRAY(1);
            //获取表中的元素
            Datum[] dat = array.getOracleArray();
            //遍历依次打印
            for(Datum d : dat)
            {
                System.out.println(new String(d.getBytes()));
            }
        }catch(Exception e)
        {
            e.printStackTrace();
        }
    }

2.如果定义的是嵌套表结构,

   如下定义:
   create or replace type all_table is object(id number,name varchar2(20));
   create or replace type emps_table_type is table of all_table;

--创建一个函数,返回类型为emps_table_type;

create or replace function funcc
return emps_table_type
is
i emps_table_type;
begin
   --把emps中的ID,NAME属性值全部读取到i中
   select all_table(id,name) bulk collect into i from emps;
   return i;--返回table
end;



public void SelectAgus(String sql)
    {
        OracleCallableStatement call = null;
        try
        {
            call = (OracleCallableStatement) con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.ARRAY,"EMPS_TABLE_TYPE");
            call.execute();
            ARRAY array = call.getARRAY(1);
            Datum[] dat = array.getOracleArray();
            for(Datum d : dat)
            {   //获取了行后,要获取一行中的元素
                STRUCT struct = (STRUCT)d;
                //这里有可能会出现乱码,所以我分别用了两种方式获取元素
                Datum[] d1 = struct.getOracleAttributes();
                Object[] d2 = struct.getAttributes();
                System.out.println("ID="+d2[0]+" "+"NAME="+
                        new String(d1[1].getBytes()));
            }

        }catch(Exception e){
            e.printStackTrace();
        }
    }

oracle 在一个存储过程中调用另一个返回游标的存储过程
实际项目当中经常需要在一个存储过程中调用另一个存储过程返回的游标，本文列举了两种情况讲述具体的操作方法。
第一种情况是返回的游标是某个具体的表或视图的数据，如：

CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT * FROM USERS;
END P_TESTA;

其中USERS就是数据库中一个表。在调用的时候只要声明一个该表的ROWTYPE类型就可以了：

CREATE OR REPLACE PROCEDURE P_TESTB
AS
    VARCURSOR SYS_REFCURSOR;
    R USERS%ROWTYPE;
BEGIN
    P_TESTA(VARCURSOR);
LOOP
    FETCH VARCURSOR INTO R;
    EXIT WHEN VARCURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(R.NAME);
END LOOP;
END P_TESTB;

第二种情况，我们返回的不是表的所有的列，或许只是其中一列或两列，如：

CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT ID,NAME FROM USERS;
END P_TESTA;

这里我们只返回了USERS表的ID,NAME这两个列，那么调用的时候也必须做相应的修改：

CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
CURSOR TMPCURSOR IS SELECT ID,NAME FROM USERS WHERE ROWNUM=1;
R TMPCURSOR%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.ID);
END LOOP;
END P_TESTB;

与之前不同的是我们声明了一个游标类型的变量TMPCURSOR ，注意TMPCURSOR 的结构必须与存储过程P_TESTA 返回的游标结构一致，否则就会出现错误。同理只要保持两个游标类型结构一致，就可以实现自由调用。

create table test(id int,name varchar(10))
insert into test select 1,'AAAA'
insert into test select 2,'BBBB'
go

create procedure sp_test1(@count int output)
as
    select @count=count(*) from test
go

create procedure sp_test2
as
begin
    declare @count int
    exec sp_test1 @count output
    select @count
end
go

exec sp_test2
go

--输出结果
/*
2
*/

drop procedure sp_test2,sp_test1
drop table test
go

oracle procedure 和function 的区别有哪些？

procedure 可多个返回参数，也就是out类型
function就一个
就这点区别
我觉得看使用的地方，如果只要执行一段sql的语句段，两个都行，如过想有返回值，一个的话用function，多个的话procedure。

procedure是存储过程相当于程序语言里面一个处理业务的方法也可以返回值
function是方法相当于程序语言里面返回一个值的方法一般较简单可以在dml语句中用这个方法加参数增删改查
package相当于程序里面一个接口里面可以定义常量数组bean 多个procedure和多个function的空实现
package body相当于程序里面一个类是对应实现接口package的

循环：
1、..
is
     i int;
begin
     i :=1;
     loop
         ..
         exit when i =10;
         i :=i+1;
     end loop;
2、
     i :=1;
     while i<=5 loop
    ..
         i :=i+1;
     end loop;
3、
     --如果指定了reverse选项，则循环控制变量会自动减1，否则自动加1
     for j in reverse 1..10 loop
    ..
     end loop;

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 where 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 WHERE 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单步调试