精华内容
下载资源
问答
  • oracle函数调用存储过程.doc
  • oracle 函数调用存储过程

    千次阅读 2012-04-04 19:26:57
    开始是使用拼接sql的方式在存储过程中对应sql的外面包装一层来进行处理,但是觉得这种方式拼接的sql太长,而且以后业务逻辑改变要同时修改存储过程和代码,容易发生遗漏,故产生了在函数中处理存储过程的结果(游标...

      项目中开发了一个存储过程来给报表提供数据源,而程序业务逻辑中需要有一个对该数据源进行判断的功能,开始是使用拼接sql的方式在存储过程中对应sql的外面包装一层来进行处理,但是觉得这种方式拼接的sql太长,而且以后业务逻辑改变要同时修改存储过程和代码,容易发生遗漏,故产生了在函数中处理存储过程的结果(游标)的想法。但是在网上搜索发现对存储过程返回的游标进行处理的例子很少,自己弄了半天终于成功了,在此记录一下。

    存储过程plan_station_contrast

    create or replace procedure plan_station_contrast(
     groupid in varchar2 ,--到站计划主表id,非空
     refCursor out sys_refcursor)
    is
    planDate  varchar2(6);--计划年月
    deptCode  varchar2(10);--上报单位编码
    
    begin
      select p.plan_date,p.dept_code into planDate,deptCode from p_station_group p where id=to_number(groupid);
    
     open refcursor for 'select nvl(station.oil_code,config.oil_code) oilCode,nvl(station.oil_name,config.oil_name) oilName
      ,nvl(station.planQuantity,0) planQuantity  --到站上报
      ,nvl(station.configStock,0)  configStock   --外采上报
      ,nvl(config.configQuantity,0) configQuantity --直炼配置
      ,nvl(config.stockQuantity,0) stockQuantity --外采配置
      
      from
      --到站计划
      (select p.oil_code,p.oil_name
              ,sum(plan_Quantity)-sum(special_Quantity) planQuantity 
              ,sum(stock_Quantity) configStock
        from
          (select oil_code,oil_name
             ,decode(special,''0'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0)plan_Quantity
             ,decode(special,''1'',nvl(train_quantity, 0),0) special_Quantity
             ,decode(special,''2'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0) stock_Quantity
          from p_station where sid = '||groupid||' 
        )p
      group by p.oil_code,p.oil_name) station
      full join
      --配置计划
      (
        select p.oil_code,p.oil_name
        ,sum(decode(a.dtype,''factory'',decode(a.f_type,1,p.plan_quantity,0),''stock'',0,p.plan_quantity)) configQuantity --配置量 
        ,sum(decode(a.dtype,''factory'',decode(a.f_type,1,0,p.plan_quantity),''stock'',p.plan_quantity,0)) stockQuantity --外采量 
        from p_disbtn p
        left outer join P_CONFIG_PLAN_FLOW f on p.flow_code=f.flow_code --关联配置计划流向
        left outer join acc_domain a on p.company_code=a.code 
        where bill_month='''||planDate||'''
        and (select status from p_disbtn_group where bill_month='''||planDate||''')=''2''
        and f.company_code='''||deptCode||'''
        and p.plan_quantity>0 
        group by p.oil_code,p.oil_name
      ) config
      on station.oil_code=config.oil_code ';
    end plan_station_contrast;


    函数fun_plan_station_contrast

    create or replace function fun_plan_station_contrast(groupid varchar2)
      return number is
      tmp varchar2(200);
      mycur sys_refcursor;
      res number :=0;--不符合条件的记录条数
      type station_record is record(
      strCode varchar2(200),
      strName varchar2(200),
      planQuantity number(19,2),
      configStock number(19,2),
      configQuantity number(19,2),
      stockQuantity number(19,2)
      );--定义新类型来存放存储过程返回的结果
    
      tmp_record station_record;
    begin
      tmp := 'call plan_station_contrast(:param1,:param2) ';
      execute immediate tmp using groupid,out mycur ;--调用存储过程
    
      --游标默认已打开,因为存储过程中是open refcursor for 
      if(mycur%isopen) then
        dbms_output.put_line('打开');
      else
        dbms_output.put_line('关闭');
      end if;
    
      loop
        fetch mycur into tmp_record;
        exit when mycur%notfound;
         if tmp_record.planQuantity<>tmp_record.configQuantity or tmp_record.stockQuantity<>tmp_record.configStock then
          res:=res+1;
        end if;
      end loop;
      
      close mycur;--关闭游标
      
      return res;
    end fun_plan_station_contrast;

    结果如下:


     

    展开全文
  • 调用oracle函数存储过程

    千次阅读 2010-07-02 09:29:00
     try { <br /> // 调用一个没有参数的函数; 函数返回 a VARCHAR  // 预处理callable语句  cs = connection.prepareCall("{? = call myfunc}");  // 注册返回值类型  cs....

    1.在java里面调用

    CallableStatement cs;
      try {
      

         // 调用一个没有参数的函数; 函数返回 a VARCHAR
      // 预处理callable语句
      cs = connection.prepareCall("{? = call myfunc}");
      // 注册返回值类型
      cs.registerOutParameter(1, i);
      // Execute and retrieve the returned value
      cs.execute();
      String retValue = cs.getString(1);
      


      // 调用有一个in参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncin(?)}"); 
      // Register the type of the return value
      cs.registerOutParameter(1, Types.VARCHAR);
      // Set the value for the IN parameter
      cs.setString(2, "a string");
      // Execute and retrieve the returned value
      cs.execute();
      retValue = cs.getString(1);
      


      // 调用有一个out参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncout(?)}");
      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);
      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1);      // return value
      String outParam = cs.getString(2);  // OUT parameter
      


      // 调用有一个in/out参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncinout(?)}");
      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);
      // Set the value for the IN/OUT parameter
      cs.setString(2, "a string");
      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1);      // return value
      outParam = cs.getString(2);      // IN/OUT parameter


      } catch (SQLException e) {
      }

     

    2.直接在sql语句里面调用:

         select function() from dual

     

    3.调用oracle存储过程:

    首先来了解一下以下几个概念:

    1)、什么是存储过程。

         存储过程是数据库服务器端的一段程序,它有两种类型。一种类似于SELECT查询,用于检索数据,检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询,它不返回数据,只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。
    2)、什么时候需要用存储过程
      如果服务器定义了存储过程,应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务,这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程,可以改善应用程序的性能。这是因为: .服务器往往具有强大的计算能力和速度。 避免把大量的数据下载到客户端,减少网络上的传输量。 例如,假设一个应用程序需要计算一个数据,这个数据需要涉及到许多记录。如果不使用存储过程的话,把这些数据下载到客户端,导致网络上的流量剧增。不仅如此,客户端可能是一台老掉牙的计算机,它的运算速度很慢。而改用存储过程后,服务器会很快地把数据计算出来,并且只需传递一个数据给客户端,其效率之高是非常明显的。
    3)、存储过程的参数
      要执行服务器上的存储过程,往往要传递一些参数。这些参数分为四种类型:
      第一种称为输入参数,由客户程序向存储过程传递值。
      第二种称为输出参数,由存储过程向客户程序返回结果。
      第三种称为输入/输出参数,既可以由客户程序向存储过程传递值,也可以由存储过程向客户程序返回结果。
      第四种称为状态参数,由存储过程向客户程序返回错误信息。
      要说明的是,并不是所有的服务器都支持上述四种类型的参数,例如,InterBase就不支持状态参数。
    4)、oracle 存储过程的基本语法
    1.基本结构
    CREATE OR REPLACEPROCEDURE 存储过程名字
    (
    参数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;


    一:无返回值的存储过程
    存储过程为:

    CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
    BEGIN
    INSERT INTO HYQ.B_ID (I_ID,I_NAME) S (PARA1, PARA2);
    END TESTA;

    然后呢,在java里调用时就用下面的代码:

    package com.hyq.src;

    import java.sql.*;
    import java.sql.ResultSet;

    public class TestProcedureOne {
    public TestProcedureOne() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    CallableStatement cstmt = null;

    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
    CallableStatement proc = null;
    proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
    proc.setString(1, "100");
    proc.setString(2, "TestOne");
    proc.execute();
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。

    二:有返回值的存储过程(非列表)
    存储过程为:

    CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
    BEGIN
    SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
    END TESTB;

    在java里调用时就用下面的代码:

    package com.hyq.src;

    public class TestProcedureTWO {
    public TestProcedureTWO() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
    CallableStatement proc = null;
    proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
    proc.setString(1, "100");
    proc.registerOutParameter(2, Types.VARCHAR);
    proc.execute();
    String testPrint = proc.getString(2);
    System.out.println("=testPrint=is="+testPrint);
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    }

    注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。

    三:返回列表
    由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
    1, 建一个程序包。如下:

    CREATE OR REPLACE PACKAGE TESTPACKAGE AS
    TYPE Test_CURSOR IS REF CURSOR;
    end TESTPACKAGE;

    2,建立存储过程,存储过程为:

    CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
    BEGIN
    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
    END TESTC;

    可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
    在java里调用时就用下面的代码:

    package com.hyq.src;
    import java.sql.*;
    import java.io.OutputStream;
    import java.io.Writer;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import oracle.jdbc.driver.*;


    public class TestProcedureTHREE {
    public TestProcedureTHREE() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;

    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, "hyq", "hyq");

    CallableStatement proc = null;
    proc = conn.prepareCall("{ call hyq.testc(?) }");
    proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
    proc.execute();
    rs = (ResultSet)proc.getObject(1);

    while(rs.next())
    {
    System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
    }
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    在这里要注意,在执行前一定要先把oracle的驱动包放到class路径里,否则会报错。

     

    4.Oracle的存储过程和函数区别

    展开全文
  • 如何调用oracle函数存储过程
  • oracle 函数存储过程

    万次阅读 2014-02-12 13:30:50
    6.3.2 调用存储过程 6.3.3 AUTHID 6.3.4 PRAGMA AUTONOMOUS_TRANSACTION 6.3.5 开发存储过程步骤 6.3.6 删除过程和函数 6.3.7 过程与函数的比较   6.1 引言 过程与函数(另外还有包与触发器)是...

    6.1 引言

    6.2 创建函数

    6.3 存储过程

    6.3.1 创建过程

    6.3.2 调用存储过程

    6.3.3 AUTHID

    6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

    6.3.5 开发存储过程步骤

    6.3.6 删除过程和函数

    6.3.7 过程与函数的比较


     

    6.1 引言

    过程与函数(另外还有包与触发器)是命名的PL/SQL块(也是用户的方案对象),被编译后存储在数据库中,以备执行。因此,其它PL/SQL块可以按名称来使用他们。所以,可以将商业逻辑、企业规则写成函数或过程保存到数据库中,以便共享。

    过程和函数统称为PL/SQL子程序,他们是被命名的PL/SQL块,均存储在数据库中,并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。在本节中,主要介绍:

    1.   创建存储过程和函数。

    2.   正确使用系统级的异常处理和用户定义的异常处理。

    3.   建立和管理存储过程和函数。

    6.2 创建函数

    1. 创建函数

     

    语法如下:

     

    复制代码
    CREATE   [ OR REPLACE ]   FUNCTION  function_name
     (arg1 
    [  { IN | OUT | IN OUT } ]  type1  [ DEFAULT value1 ] ,
     
    [ arg2 [ { IN | OUT | IN OUT } ]  type2  [ DEFAULT value1 ] ],
     ......
     
    [ argn [ { IN | OUT | IN OUT } ]  typen  [ DEFAULT valuen ] ])
     
    [  AUTHID DEFINER | CURRENT_USER  ]
    RETURN  return_type 
     
    IS   |   AS
        
    < 类型.变量的声明部分 >  
    BEGIN
        执行部分
        
    RETURN  expression
    EXCEPTION
        异常处理部分
    END  function_name;
    复制代码

     

    l         IN,OUT,IN OUT 是形参的模式。若省略,则为 IN 模式。 IN 模式的形参只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变。 OUT 模式的形参会忽略调用时的实参值(或说该形参的初始值总是 NULL ),但在函数内部可以被读或写,函数返回时形参的值会赋予给实参。 IN OUT 具有前两种模式的特性,即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。调用时,对于 IN 模式的实参可以是常量或变量,但对于 OUT IN OUT 模式的实参必须是变量。

     

    l         一般,只有在确认function_name函数是新函数或是要更新的函数时,才使用OR REPALCE关键字,否则容易删除有用的函数。

     

    例1.           获取某部门的工资总和:

     

    复制代码
    -- 获取某部门的工资总和
    CREATE   OR   REPLACE
    FUNCTION  get_salary(
      Dept_no 
    NUMBER ,
      Emp_count OUT 
    NUMBER )
      
    RETURN   NUMBER  
    IS
      V_sum 
    NUMBER ;
    BEGIN
      
    SELECT   SUM (SALARY),  count ( * INTO  V_sum, emp_count
        
    FROM  EMPLOYEES  WHERE  DEPARTMENT_ID = dept_no;
      
    RETURN  v_sum;
    EXCEPTION
       
    WHEN  NO_DATA_FOUND  THEN  
          DBMS_OUTPUT.PUT_LINE(
    ' 你需要的数据不存在! ' );
       
    WHEN  OTHERS  THEN  
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  get_salary;
    复制代码

     

    2. 函数的调用

    函数声明时所定义的参数称为形式参数,应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时,可以使用以下三种方法向函数传递参数:

     

    第一种参数传递格式:位置表示法。

    即在调用时按形参的排列顺序,依次写出实参的名称,而将形参与实参关联起来进行传递。用这种方法进行调用,形参与实参的名称是相互独立,没有关系,强调次序才是重要的。

    格式为:

           argument_value1[,argument_value2 …]

     

    2计算某部门的工资总和:

     

    复制代码
    DECLARE
      V_num 
    NUMBER ;
      V_sum 
    NUMBER ;
    BEGIN
      V_sum :
    = get_salary( 10 , v_num);
      DBMS_OUTPUT.PUT_LINE(
    ' 部门号为:10的工资总和: ' || v_sum || ' ,人数为: ' || v_num);
    END ;
    复制代码

    第二种参数传递格式:名称表示法。

    即在调用时按形参的名称与实参的名称,写出实参对应的形参,而将形参与实参关联起来进行传递。这种方法,形参与实参的名称是相互独立的,没有关系,名称的对应关系才是最重要的,次序并不重要。

    格式为:

           argument => parameter [,…]

    其中:argument 为形式参数,它必须与函数定义时所声明的形式参数名称相同parameter 为实际参数。

    在这种格式中,形势参数与实际参数成对出现,相互间关系唯一确定,所以参数的顺序可以任意排列。

    3计算某部门的工资总和:

     

    复制代码
    DECLARE
      V_num 
    NUMBER ;
        V_sum 
    NUMBER ;
    BEGIN
        V_sum :
    = get_salary(emp_count  =>  v_num, dept_no  =>   10 );
        DBMS_OUTPUT.PUT_LINE(
    ' 部门号为:10的工资总和: ' || v_sum || ' ,人数为: ' || v_num);
    END ;
     
    复制代码

    第三种参数传递格式:组合传递。

    即在调用一个函数时,同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时,使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说,无论函数具有多少个参数,只要其中有一个参数使用名称表示法,其后所有的参数都必须使用名称表示法。

     

    4

    复制代码
    CREATE   OR   REPLACE   FUNCTION  demo_fun(
      Name 
    VARCHAR2 , -- 注意VARCHAR2不能给精度,如:VARCHAR2(10),其它类似
      Age  INTEGER ,
      Sex 
    VARCHAR2 )
      
    RETURN   VARCHAR2  
    AS
      V_var 
    VARCHAR2 ( 32 );
    BEGIN
      V_var :
    =  name || ' ' || TO_CHAR(age) || ' 岁. ' || sex;
      
    RETURN  v_var;
    END ;

    DECLARE  
      
    Var   VARCHAR ( 32 );
    BEGIN
      
    Var  : =  demo_fun( ' user1 ' 30 , sex  =>   ' ' );
      DBMS_OUTPUT.PUT_LINE(
    var );

      
    Var  : =  demo_fun( ' user2 ' , age  =>   40 , sex  =>   ' ' );
      DBMS_OUTPUT.PUT_LINE(
    var );

      
    Var  : =  demo_fun( ' user3 ' , sex  =>   ' ' , age  =>   20 );
      DBMS_OUTPUT.PUT_LINE(
    var );
    END ;
    复制代码

     

    无论采用哪一种参数传递方法,实际参数和形式参数之间的数据传递只有两种方法:传址法和传值法。所谓传址法是指在调用函数时,将实际参数的地址指针传递给形式参数,使形式参数和实际参数指向内存中的同一区域,从而实现参数数据的传递。这种方法又称作参照法,即形式参数参照实际参数数据。输入参数均采用传址法传递数据。

           传值法是指将实际参数的数据拷贝到形式参数,而不是传递实际参数的地址。默认时,输出参数和输入/输出参数均采用传值法。在函数调用时,ORACLE将实际参数数据拷贝到输入/输出参数,而当函数正常运行退出时,又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

     

    3. 参数默认值

    CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。

     

    5

    复制代码
    CREATE   OR   REPLACE   FUNCTION  demo_fun(
      Name 
    VARCHAR2 ,
      Age 
    INTEGER ,
      Sex 
    VARCHAR2   DEFAULT   ' ' )
      
    RETURN   VARCHAR2  
    AS
      V_var 
    VARCHAR2 ( 32 );
    BEGIN
      V_var :
    =  name || ' ' || TO_CHAR(age) || ' 岁. ' || sex;
      
    RETURN  v_var;
    END ;
    复制代码

     

    具有默认值的函数创建后,在函数调用时,如果没有为具有默认值的参数提供实际参数值,函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时,函数将使用实际参数值。在创建函数时,只能为输入参数设置默认值,而不能为输入/输出参数设置默认值。

    DECLARE

     var VARCHAR(32);

    BEGIN

     Var := demo_fun('user1', 30);

     DBMS_OUTPUT.PUT_LINE(var);

     Var := demo_fun('user2', age => 40);

     DBMS_OUTPUT.PUT_LINE(var);

     Var := demo_fun('user3', sex => '', age => 20);

     DBMS_OUTPUT.PUT_LINE(var);

    END;

    6.3 存储过程

    6.3.1 创建过程

     

    建立存储过程

    ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

     

    创建过程语法:

     

    复制代码
    CREATE   [ OR REPLACE ]   PROCEDURE  procedure_name
    (
    [ arg1 [ IN | OUT | IN OUT  ] ] type1  [ DEFAULT value1 ] ,
     
    [ arg2 [ IN | OUT | IN OUT  ] ] type2  [ DEFAULT value1 ] ],
     ......
     
    [ argn [ IN | OUT | IN OUT  ] ] typen  [ DEFAULT valuen ] )
        
    [  AUTHID DEFINER | CURRENT_USER  ]
    IS   |   AS  }
      
    < 声明部分 >  
    BEGIN
      
    < 执行部分 >
    EXCEPTION
      
    < 可选的异常错误处理程序 >
    END  procedure_name;
    复制代码

     

    说明:相关参数说明参见函数的语法说明。

     

    6用户连接登记记录;

     

    复制代码
    CREATE   TABLE  logtable (userid  VARCHAR2 ( 10 ), logdate date);

    CREATE   OR   REPLACE   PROCEDURE  logexecution 
    IS
    BEGIN
    INSERT   INTO  logtable (userid, logdate)  VALUES  ( USER , SYSDATE);
    END ;
    复制代码

     

    7删除指定员工记录;

     

    复制代码
    CREATE   OR   REPLACE
    PROCEDURE  DelEmp
    (v_empno 
    IN  employees.employee_id % TYPE) 
    AS
    No_result EXCEPTION;
    BEGIN
       
    DELETE   FROM  employees  WHERE  employee_id  =  v_empno;
       
    IF  SQL % NOTFOUND  THEN
          RAISE no_result;
       
    END   IF ;
       DBMS_OUTPUT.PUT_LINE(
    ' 编码为 ' || v_empno || ' 的员工已被删除! ' );
    EXCEPTION
       
    WHEN  no_result  THEN  
          DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:你需要的数据不存在! ' );
       
    WHEN  OTHERS  THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  DelEmp;
    复制代码

     

    8插入员工记录:

     

    复制代码
    CREATE   OR   REPLACE
    PROCEDURE  InsertEmp(
       v_empno     
    in  employees.employee_id % TYPE,
       v_firstname 
    in  employees.first_name % TYPE,
       v_lastname  
    in  employees.last_name % TYPE,
       v_deptno    
    in  employees.department_id % TYPE
       ) 
    AS
       empno_remaining EXCEPTION;
       PRAGMA EXCEPTION_INIT(empno_remaining, 
    - 1 );
       
    /*  -1 是违反唯一约束条件的错误代码  */
    BEGIN
       
    INSERT   INTO  EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
       
    VALUES (v_empno, v_firstname,v_lastname, sysdate, v_deptno);
       DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:插入数据记录成功! ' );
    EXCEPTION
       
    WHEN  empno_remaining  THEN  
          DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:违反数据完整性约束! ' );
       
    WHEN  OTHERS  THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  InsertEmp;
    复制代码

    9使用存储过程向departments表中插入数据。

     

    复制代码
    CREATE   OR   REPLACE
    PROCEDURE  insert_dept
      (v_dept_id 
    IN  departments.department_id % TYPE,
       v_dept_name 
    IN  departments.department_name % TYPE,
       v_mgr_id 
    IN  departments.manager_id % TYPE,
       v_loc_id 
    IN  departments.location_id % TYPE)
    IS
       ept_null_error EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_null_error, 
    - 1400 );
       ept_no_loc_id EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_no_loc_id, 
    - 2291 );
    BEGIN
       
    INSERT   INTO  departments
       (department_id, department_name, manager_id, location_id)
       
    VALUES
       (v_dept_id, v_dept_name, v_mgr_id, v_loc_id);
       DBMS_OUTPUT.PUT_LINE(
    ' 插入部门 ' || v_dept_id || ' 成功 ' );
    EXCEPTION
       
    WHEN  DUP_VAL_ON_INDEX  THEN
          RAISE_APPLICATION_ERROR(
    - 20000 ' 部门编码不能重复 ' );
       
    WHEN  ept_null_error  THEN
          RAISE_APPLICATION_ERROR(
    - 20001 ' 部门编码、部门名称不能为空 ' );
       
    WHEN  ept_no_loc_id  THEN
          RAISE_APPLICATION_ERROR(
    - 20002 ' 没有该地点 ' );
    END  insert_dept;

    /* 调用实例一:
    DECLARE
       ept_20000 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20000, -20000);
       ept_20001 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20001, -20001);
       ept_20002 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20002, -20002);
    BEGIN
       insert_dept(300, '部门300', 100, 2400);
       insert_dept(310, NULL, 100, 2400);
       insert_dept(310, '部门310', 100, 900);
    EXCEPTION
       WHEN ept_20000 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');
       WHEN ept_20001 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');
       WHEN ept_20002 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');
    END;

    调用实例二:
    DECLARE
       ept_20000 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20000, -20000);
       ept_20001 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20001, -20001);
       ept_20002 EXCEPTION;
       PRAGMA EXCEPTION_INIT(ept_20002, -20002);
    BEGIN
       insert_dept(v_dept_name => '部门310', v_dept_id => 310, 
                   v_mgr_id => 100, v_loc_id => 2400);
       insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900);
    EXCEPTION
       WHEN ept_20000 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');
       WHEN ept_20001 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');
       WHEN ept_20002 THEN
          DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');
    END;
    */
    复制代码

     

    6.3.2 调用存储过程

     

        存储过程建立完成后,只要通过授权,用户就可以在SQLPLUS ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种:按位置传递、按名称传递和组合传递,传递方法与函数的一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用:

     

    EXEC [ UTE ]  procedure_name( parameter1, parameter2…);

     

    10

     

    EXECUTE  logexecution;

     

    11查询指定员工记录;

     

    复制代码
    CREATE   OR   REPLACE
    PROCEDURE  QueryEmp
    (v_empno 
    IN   employees.employee_id % TYPE,
     v_ename OUT employees.first_name
    % TYPE,
     v_sal   OUT employees.salary
    % TYPE) 
    AS
    BEGIN
           
    SELECT  last_name  ||  last_name, salary  INTO  v_ename, v_sal 
        
    FROM  employees 
        
    WHERE  employee_id  =  v_empno; 
           DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:编码为 ' || v_empno || ' 的员工已经查到! ' );
    EXCEPTION
           
    WHEN  NO_DATA_FOUND  THEN  
          DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:你需要的数据不存在! ' );
          
    WHEN  OTHERS  THEN  
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  QueryEmp;
    -- 调用
      DECLARE
        v1 employees.first_name
    % TYPE;
        v2 employees.salary
    % TYPE;
     
    BEGIN
       QueryEmp(
    100 , v1, v2);
       DBMS_OUTPUT.PUT_LINE(
    ' 姓名: ' || v1);
       DBMS_OUTPUT.PUT_LINE(
    ' 工资: ' || v2);
       QueryEmp(
    103 , v1, v2);
       DBMS_OUTPUT.PUT_LINE(
    ' 姓名: ' || v1);
       DBMS_OUTPUT.PUT_LINE(
    ' 工资: ' || v2);
       QueryEmp(
    104 , v1, v2);
       DBMS_OUTPUT.PUT_LINE(
    ' 姓名: ' || v1);
       DBMS_OUTPUT.PUT_LINE(
    ' 工资: ' || v2);
    END ;
    复制代码

     

    12计算指定部门的工资总和,并统计其中的职工数量。

     

    复制代码
    CREATE   OR   REPLACE
    PROCEDURE  proc_demo
    (
      dept_no 
    NUMBER   DEFAULT   10 ,
        sal_sum OUT 
    NUMBER ,
        emp_count OUT 
    NUMBER
      )
    IS
    BEGIN
        
    SELECT   SUM (salary),  COUNT ( * INTO  sal_sum, emp_count
      
    FROM  employees  WHERE  department_id  =  dept_no;
    EXCEPTION
       
    WHEN  NO_DATA_FOUND  THEN
          DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:你需要的数据不存在! ' );
       
    WHEN  OTHERS  THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  proc_demo;

    DECLARE
    V_num 
    NUMBER ;
    V_sum 
    NUMBER ( 8 2 );
    BEGIN
      Proc_demo(
    30 , v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:30号部门工资总和: ' || v_sum || ' ,人数: ' || v_num);
      Proc_demo(sal_sum 
    =>  v_sum, emp_count  =>  v_num);
    DBMS_OUTPUT.PUT_LINE(
    ' 温馨提示:10号部门工资总和: ' || v_sum || ' ,人数: ' || v_num);
    END ;
    复制代码

           PL/SQL 程序中还可以在块内建立本地函数和过程,这些函数和过程不存储在数据库中,但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义,它们的语法格式与存储函数和过程相同,但不能使用CREATE OR REPLACE 关键字。

     

    13建立本地过程,用于计算指定部门的工资总和,并统计其中的职工数量;

     

    复制代码
    DECLARE
    V_num 
    NUMBER ;
    V_sum 
    NUMBER ( 8 2 );
    PROCEDURE  proc_demo
      (
        Dept_no 
    NUMBER   DEFAULT   10 ,
        Sal_sum OUT 
    NUMBER ,
        Emp_count OUT 
    NUMBER
      )
    IS
    BEGIN
        
    SELECT   SUM (salary),  COUNT ( * INTO  sal_sum, emp_count 
        
    FROM  employees  WHERE  department_id = dept_no;
    EXCEPTION
       
    WHEN  NO_DATA_FOUND  THEN  
          DBMS_OUTPUT.PUT_LINE(
    ' 你需要的数据不存在! ' );
       
    WHEN  OTHERS  THEN  
          DBMS_OUTPUT.PUT_LINE(SQLCODE
    || ' --- ' || SQLERRM);
    END  proc_demo;
    -- 调用方法:
    BEGIN
        Proc_demo(
    30 , v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE(
    ' 30号部门工资总和: ' || v_sum || ' ,人数: ' || v_num);
        Proc_demo(sal_sum 
    =>  v_sum, emp_count  =>  v_num);
    DBMS_OUTPUT.PUT_LINE(
    ' 10号部门工资总和: ' || v_sum || ' ,人数: ' || v_num);
    END ;
    复制代码

    6.3.3 AUTHID

    过程中的AUTHID 指令可以告诉ORACLE ,这个过程使用谁的权限运行.默任情况下,存储过程会作为调用者的过程运行,但是具有设计者的特权.这称为设计者权利运行.

     

    14建立过程,使用AUTOID DEFINER

     

    复制代码
    Connect HR / qaz
    DROP   TABLE  logtable;
    CREATE   table  logtable (userid  VARCHAR2 ( 10 ), logdate date);

    CREATE   OR   REPLACE   PROCEDURE  logexecution 
        AUTHID DEFINER
    IS
    BEGIN
       
    INSERT   INTO  logtable (userid, logdate)  VALUES  ( USER , SYSDATE);
    END ;

    GRANT   EXECUTE   ON  logexecution  TO   PUBLIC ;

    CONNECT 
    /   AS  SYSDBA
    GRANT  CONNECT  TO  testuser1 IDENTIFIED  BY  userpwd1;

    CONNECT testuser1
    / userpwd1
    INSERT   INTO  HR.LOGTABLE  VALUES  ( USER , SYSDATE);
    EXECUTE  HR.logexecution

    CONNECT HR
    / qaz
    SELECT   *   FROM  HR.logtable;
    复制代码

     

    15建立过程,使用AUTOID CURRENT_USER

     

    复制代码
    CONNECT HR / qaz

    CREATE   OR   REPLACE   PROCEDURE  logexecution 
      AUTHID 
    CURRENT_USER
    IS
    BEGIN
       
    INSERT   INTO  logtable (userid, logdate)  VALUES  ( USER , SYSDATE);
    END ;

    GRANT   EXECUTE   ON  logexecution  TO   PUBLIC ;

    CONNECT testuser1
    / userpwd1
    INSERT   INTO  HR.LOGTABLE  VALUES  ( USER , SYSDATE);
    EXECUTE  HR.logexecution
    复制代码

     

    6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

     

    ORACLE8i 可以支持事务处理中的事务处理的概念.这种子事务处理可以完成它自己的工作,独立于父事务处理进行提交或者回滚.通过使用这种方法,开发者就能够这样的过程,无论父事务处理是提交还是回滚,它都可以成功执行.

     

    16建立过程,使用自动事务处理进行日志记录;

     

    复制代码
    DROP   TABLE  logtable;

    CREATE   TABLE  logtable(
      Username 
    varchar2 ( 20 ),
      Dassate_time date,
      Mege 
    varchar2 ( 60 )
    );

    CREATE   TABLE  temp_table( N  number  );

    CREATE   OR   REPLACE   PROCEDURE  log_message(p_message  varchar2 )
      
    AS
      PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
      
    INSERT   INTO  logtable  VALUES  (  user , sysdate, p_message );
      
    COMMIT ;
    END  log_message;

    BEGIN
      Log_message (‘About 
    to   insert   into  temp_table‘);
      
    INSERT   INTO  temp_table  VALUES  ( 1 );
      Log_message (‘
    Rollback   to   insert   into  temp_table‘);
      
    ROLLBACK ;
    END ;

    SELECT   *   FROM  logtable;
    SELECT   *   FROM  temp_table;
    复制代码

     

    17建立过程,没有使用自动事务处理进行日志记录;

     

    复制代码
    CREATE   OR   REPLACE   PROCEDURE  log_message(p_message  varchar2 )
      
    AS
    BEGIN
      
    INSERT   INTO  logtable  VALUES  (  user , sysdate, p_message );
      
    COMMIT ;
    END  log_message;

    BEGIN
      Log_message (
    ' About to insert into temp_table ' );
      
    INSERT   INTO  temp_table  VALUES  ( 1 );
      Log_message (
    ' Rollback to insert into temp_table ' );
      
    ROLLBACK ;
    END ;

    SELECT   *   FROM  logtable;
    SELECT   *   FROM  temp_table;
    复制代码

     

    6.3.5 开发存储过程步骤

        开发存储过程、函数、包及触发器的步骤如下:

     

    6.3.5.1 使用文字编辑处理软件编辑存储过程源码

        使用文字编辑处理软件编辑存储过程源码,要用类似WORD 文字处理软件进行编辑时,要将源码存为文本格式。

     

    6.3.5.2 SQLPLUS或用调试工具将存储过程程序进行解释

        SQLPLUS或用调试工具将存储过程程序进行解释;

        SQL>下调试,可用START GET ORACLE命令来启动解释。如:

    SQL>START c:\stat1.sql

        如果使用调式工具,可直接编辑和点击相应的按钮即可生成存储过程。

     

    6.3.5.3 调试源码直到正确

        我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是:

    l         使用 SHOW ERROR命令来提示源码的错误位置;

    l         使用 user_errors 数据字典来查看各存储过程的错误位置。

     

    6.3.5.4 授权执行权给相关的用户或角色

    如果调式正确的存储过程没有进行授权,那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。

     

    GRANT语法:

     

    复制代码
    GRANT  system_privilege  |  role 
    TO   user   |  role  |   PUBLIC   [ WITH ADMIN OPTION ]

    GRANT  object_privilege  |   ALL   ON   schema .object 
    TO   user   |  role  |   PUBLIC   [ WITH GRANT OPTION ]

    -- 例子:

    CREATE   OR   REPLACE   PUBLIC  SYNONYM dbms_job  FOR  dbms_job

    GRANT   EXECUTE   ON  dbms_job  TO   PUBLIC   WITH   GRANT   OPTION
    复制代码

     

    6.3.5.5 与过程相关数据字典

     

    USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

    ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

     

    相关的权限:

    CREATE ANY PROCEDURE

    DROP ANY PROCEDURE

     

    SQL*PLUS 中,可以用DESCRIBE 命令查看过程的名字及其参数表。

     

    DESC[RIBE] Procedure_name;

     

    6.3.6 删除过程和函数

     

    1.删除过程

    可以使用DROP PROCEDURE命令对不需要的过程进行删除,语法如下:

    DROP PROCEDURE [user.]Procudure_name;

     

    2.删除函数

    可以使用DROP FUNCTION 命令对不需要的函数进行删除,语法如下:

     

    复制代码
    DROP   FUNCTION   [ user. ] Function_name;

    -- 删除上面实例创建的存储过程与函数
    DROP   PROCEDURE  logexecution;
    DROP   PROCEDURE  delemp;
    DROP   PROCEDURE  insertemp;
    DROP   PROCEDURE  fireemp;
    DROP   PROCEDURE  queryemp;
    DROP   PROCEDURE  proc_demo;
    DROP   PROCEDURE  log_message;
    DROP   FUNCTION  demo_fun;
    DROP   FUNCTION  get_salary;
    复制代码

     

    6.3.7        过程与函数的比较

     

    使用过程与函数具有如下优点:

     

    1、共同使用的代码可以只需要被编写和测试一次,而被需要该代码的任何应用程序(如:.NETC++JAVAVB程序,也可以是DLL库)调用。

    2、这种集中编写、集中维护更新、大家共享(或重用)的方法,简化了应用程序的开发和维护,提高了效率与性能。

    3、这种模块化的方法,使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分,进行分别编写、调试。因此使程序的结构清晰、简单,也容易实现。

    4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。

    5、节省内存空间。它们以一种压缩的形式被存储在外存中,当被调用时才被放入内存进行处理。并且,如果多个用户要执行相同的过程或函数时,就只需要在内存中加载一个该过程或函数。

    6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中,就可以通过是否授予用户有执行该过程或的权限,来限制某些用户对数据进行这些操作。

     

    过程与函数的相同功能有:

    1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。

    2、 输入参数都可以接受默认值,都可以传值或传引导。

    3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。

    4、 都有声明部分、执行部分和异常处理部分。

    5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

     

    使用过程与函数的原则:

    1、如果需要返回多个值和不返回值,就使用过程;如果只需要返回一个值,就使用函数。

    2、过程一般用于执行一个指定的动作,函数一般用于计算和返回一个值。

    3、可以SQL语句内部(如表达式)调用函数来完成复杂的计算问题,但不能调用过程。所以这是函数的特色。

    展开全文
  • JAVA调用ORACLE存储过程函数的返回结果集 2017年05月11日 21:03:18 彖爻之辞 阅读数:1091 标签: oracle存储 更多 个人分类: --------1.1 ORACLE 具体过程如下: CREATE TABLE STOCK_PRICES( RIC ...

    JAVA调用ORACLE的存储过程、函数的返回结果集

    2017年05月11日 21:03:18 彖爻之辞 阅读数:1091 标签: oracle存储 更多

    个人分类: --------1.1 ORACLE

    具体过程如下:
    
    CREATE TABLE STOCK_PRICES(
        RIC VARCHAR(6) PRIMARY KEY,
        PRICE NUMBER(7,2),
        UPDATED DATE );
    
    --对表插入数据
    INSERT INTO stock_prices SELECT '1110',1.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1111',2.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1112',3.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1113',4.0,SYSDATE FROM DUAL;
    
    --建立一个返回游标
    CREATE OR REPLACE PACKAGE ZZH_TEST.PKG_PUB_UTILS IS
        --动态游标
        TYPE REFCURSOR IS REF CURSOR;
    END PKG_PUB_UTILS;
    
    --创建一个测试用的存储过程
    CREATE OR REPLACE PROCEDURE ZZH_TEST.P_GET_PRICE(AN_O_RET_CODE         OUT NUMBER,
                                                     AC_O_RET_MSG          OUT VARCHAR2,
                                                     CUR_RET               OUT PKG_PUB_UTILS.REFCURSOR,
                                                     AN_I_PRICE IN NUMBER
                                                     ) IS
    BEGIN
        AN_O_RET_CODE := 0;
        AC_O_RET_MSG  := '操作成功';
    
        OPEN CUR_RET FOR
            SELECT * FROM STOCK_PRICES WHERE PRICE<AN_I_PRICE;
    EXCEPTION
        WHEN OTHERS THEN
            AN_O_RET_CODE := -1;
            AC_O_RET_MSG  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
    
    END P_GET_PRICE;
    
    --创建一个测试用的函数:
    CREATE OR REPLACE FUNCTION ZZH_TEST.F_GET_PRICE(v_price IN NUMBER)
        RETURN PKG_PUB_UTILS.REFCURSOR
    AS
        stock_cursor PKG_PUB_UTILS.REFCURSOR;
    BEGIN
        OPEN stock_cursor FOR
        SELECT ric,price,updated FROM stock_prices WHERE price < v_price;
    
        RETURN stock_cursor;
    END;
    
    --开发JAVA调用存储过程返回结果集的例子:JDBCoracle10G_INVOKEPROCEDURE 
    --开发JAVA调用函数返回结果集的例子:JDBCoracle10G_INVOKEFUNCTION
    
    --2份JAVA源代码:
    
    -- 开发JAVA调用存储过程返回结果集的例子:JDBCoracle10G_INVOKEPROCEDURE
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /* 本例是通过调用oracle的存储过程来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
      */
     public class JDBCoracle10G_INVOKEPROCEDURE {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEPROCEDURE()
      {   
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid:必须为要连接机器的sid名称,否则会包以下错:
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
    
      }
    
      public void init()
      {
    
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
             // conn.setAutoCommit(false);
    
          //输入参数
           in_price="3.0";
    
           //调用函数
           stmt = conn.prepareCall( "call P_GET_PRICE(?,?,?,?)");
           stmt.registerOutParameter(1, java.sql.Types.FLOAT);
           stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(4, in_price);
           stmt.executeUpdate();
           int retCode = stmt.getInt(1);
           String retMsg = stmt.getString(2);
           if (retCode == -1) { //如果出错时,返回错误信息
           System.out.println("报错!");
           } else {
           //取的结果集的方式一:
               rs = ((OracleCallableStatement)stmt).getCursor(3);
            //取的结果集的方式二:
                 //  rs = (ResultSet) stmt.getObject(3);
    
            String ric ;
         String price ;
         String updated ;
            //对结果进行输出
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);      
    
          System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
           }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换[]
      {
       new JDBCoracle10G_INVOKEPROCEDURE();
      }
      }
    
    --=======================================================================
    
    --源代码:
    
    --开发JAVA调用函数返回结果集的例子:JDBCoracle10G_INVOKEFUNCTION
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /*
     /* 本例是通过调用oracle的函数来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip  
      */
     public class JDBCoracle10G_INVOKEFUNCTION {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEFUNCTION()
      {
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid:必须为要连接机器的sid名称,否则会包以下错:
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
      }
    
      public void init()
      {
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
           //conn.setAutoCommit(false);
    
          //输入参数      
           in_price="5.0";
    
           //调用函数
           stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
          // stmt.registerOutParameter(1, java.sql.Types.FLOAT);
          // stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(2, in_price);
           stmt.executeUpdate();
           //取的结果集的方式一:
                rs = ((OracleCallableStatement)stmt).getCursor(1);
           //取的结果集的方式二:
              //  rs = (ResultSet) stmt.getObject(1);
             String ric ;
         String price ;
         String updated ;
    
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);                 
             System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换[]
      {
       new JDBCoracle10G_INVOKEFUNCTION();
      }
      }
    

    https://blog.csdn.net/paul50060049/article/details/71698108

     

     

     

     

     

    oracle调用存储过程和函数返回结果集

    2010年10月21日 13:56:00  阅读数:25787

       在程序开发中,常用到返回结果集的存储过程,这个在mysqlsql server 里比较好处理,直接返回查询结果就可以了,
    但在oracle里面 要 out 出去,就多了一个步骤,对于不熟悉的兄弟们还得出上一头汗:),这里我简单介绍一下,以供参考,
       1  定义包
          oracle 返回的结果集需要自定义一个 CURSOR (游标变量)性质的变量,这个要在包头定义,所以要建立一个包,如下是包头

    Pl/sql代码

    CREATE OR REPLACE PACKAGE PAK_rstest

      IS

        TYPE   retcursor    IS   REF   CURSOR;

       PROCEDURE pro_read

         (

           outcurse  IN OUT retcursor

         );

       END; -- Package spec

     

     

     

     

     

     

     

     

         上面是建立了一个名称为PAK_rstest的包头,里面定义了一个CURSOR 类型,类型名为retcursor ,有了这个定义我们就可以用他来返回结果集了,比如该包里面的 pro_read 过程就是 一个返回结果集的过程,下面是他的包体,

    Pl/sql代码

    CREATE OR REPLACE PACKAGE BODY PAK_rstest IS

       PROCEDURE pro_read

       (

           outcurse  IN OUT retcursor

       )

       IS

       begin

            OPEN outcurse FOR

             select * from tbl_test

                    where rownum<6;

            return;

       end;

     

    END;

     

     

     

     

     

     

     

     

     

     

     

     

     

     

        这样就定义好了一个包,这个包里面有个返回结果集的过程 pro_read
      

     2 在程序里面调用,
         下面就是如果在程序里面调用了,这里用java为例子简单介绍一下,
         假设你现在已经有一个Connection  conn 对象连接上了数据库(如何连接数据库我这里就不详细说了), 
         
    则用下面的代码调用过程,

    Pl/sql代码

     

    if(conn !=null){

        String sqlstr = "{call  PAK_SMS2_ROUTE.MO_ISSUE(?)}";

         CallableStatement cstmt = conn.prepareCall(sqlstr);

         cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //outcurse

         cstmt.executeUpdate();

         ResultSet rs = (ResultSet) cstmt.getObject(1); // 这里吧信息已经读入rs结果集里面,剩下的大家都熟悉了吧

       while (rs.next()) {

            System.out.println(rs.getString("s_date1")); //tbl_test 表里的字段名称或是结果集的列名称

         System.out.println(rs.getString("s_date2"));

          }   

             conn.close();

             conn = null;       

    }

     

     

     

     

     

     

     

     

     

     

     

     

     

      好了到这里就可以看到返回的结果集内容了,是不是比较简单啊,:)

       Oracle 存储过程返回结果集:

    过程返回记录集代码

     

    CREATE OR REPLACE PACKAGE pkg_test

    AS

        TYPE myrctype IS REF CURSOR;

     

        PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);

    END pkg_test;

     

     

    CREATE OR REPLACE PACKAGE BODY pkg_test

    AS

        PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)

        IS

           sqlstr   VARCHAR2 (500);

        BEGIN

           IF p_id = 0 THEN

              OPEN p_rc FOR

                 SELECT ID, NAME, sex, address, postcode, birthday

                   FROM student;

           ELSE

              sqlstr :=

                 'select id,name,sex,address,postcode,birthday

                from student where id=:w_id';

              OPEN p_rc FOR sqlstr USING p_id;

           END IF;

        END get;

    END pkg_test;  

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    函数返回记录集:
    建立带ref cursor定义的包和包体及函数:

     

    函数返回记录集代码

    CREATE OR REPLACE

    package pkg_test as

    /* 定义ref cursor类型

        不加return类型,为弱类型,允许动态sql查询,

        否则为强类型,无法使用动态sql查询;

    */

     

       type myrctype is ref cursor; 

      

    --函数申明

       function get(intID number) return myrctype;

    end pkg_test; 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    包体代码

     

    CREATE OR REPLACE

    package body pkg_test as

    --函数体

        function get(intID number) return myrctype is

          rc myrctype;  --定义ref cursor变量

          sqlstr varchar2(500);

        begin

          if intID=0 then

             --静态测试,直接用select语句直接返回结果

             open rc for select id,name,sex,address,postcode,birthday from

    student;

          else

             --动态sql赋值,用:w_id来申明该变量从外部获得

             sqlstr := 'select id,name,sex,address,postcode,birthday from

    student where id=:w_id';

             --动态测试,用sqlstr字符串返回结果,用using关键词传递参数

             open rc for sqlstr using intid;

          end if;

      

          return rc;

        end get;

      

    end pkg_test;

     

    Java调用oracle函数返回游标处理代码

     

    CallableStatement cstmt = null;

    ResultSet rs = null;

    try {

    String callSql = "{? = call AAAAA(?)}";

    cstmt = conn.prepareCall(callSql);

       

    cstmt.setString(2, "userName");

    cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

    cstmt.execute();

    rs = (ResultSet) cstmt.getObject(1);

    if (rs != null) {

           System.out.print("usercd");

           System.out.print("userName");               

           System.out.println("EMAIL");

           while (rs.next()) {       

        System.out.print(rs.getString(1)+"   ");

        System.out.print(rs.getString(2)+"   ");               

        System.out.println(rs.getString(3));

           }   

    }

    https://blog.csdn.net/whaosy/article/details/5956460

     

     

     

    展开全文
  • ibatis调用oracle函数存储过程的方法,从网上收集的,还没有整理。
  • Java,PL/SQL调用 ORACLE存储函数以及存储过程 ONE Goal , ONE Passion ! 准备工作 创建表 --- 创建测试用表 school CREATE TABLE school( ID number, --学校id NAME VARCHAR2(30) ---学校名
  • 2.调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用. 函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作(比如修改、插入数据库表或执行某些DDL语句等等) 参数的...
  • Oracle函数存储过程 Oracle函数 以下查询基于此表: (1)行转列 表中成绩是这样的,现在要求显示 张三 语文成绩 数学成绩 英语成绩 SELECT NAME, MAX(CASE WHEN SUBS='语文' THEN CJ END) AS 语文, MAX(CASE ...
  • 本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
  • Oracle函数存储过程和程序包

    千次阅读 2018-08-19 08:23:13
    函数一般是工具性的,存储过程一般是DML的复杂操作 函数有返回类型 return create function getBookCount return number as begin declare book_count number; begin select count(*) into book_count from t_...
  • 存储过程调用与删除;例子:--调用无参的存储过程 begin first_proc; end;--调用带有输出参数的存储过程 DECLARE v_avgsalary NUMBER; v_count NUMBER; BEGIN PROC2(10,v_avgsalary,v_count); DBMS_OUTPUT.put...
  • java调用oracle存储过程或者函数
  • 其实我们可以将比较复杂的查询写成函数,然后到存储过程调用这些函数   在 ORACLE 中,存储过程可以做到的函数都可以做到;而有些函数的功能(如,返回值,用于 SQL 语句中),存储过程却不能实现。 但是在...
  • 如果在SQL语句(DML或SELECT)中调用的话一定是存储函数或存储的封装函数不可以是存储过程,但调用存储函数的时候还有好多限制以及函数的纯度等级的问题,你可以参考《ORACLE 9I PLSQL程序设计》(机械工业出版社)...
  • package com.dada.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; ...//测试调用oracle函数 public int fun_add() { System.o
  • --创建测试表名 ...--创建存储过程 create or replace procedure job_proc as begin  insert into job_table (run_date) values (sysdate); end; --创建job并指定一分钟执行一次 declare
  • Oracle 函数存储过程 的应用

    千次阅读 2012-03-09 23:38:08
    这几天 预习了一遍 Oracle , 此处把 Oralce 数据库 中 视图 的笔记 拿出来 分享 一下, 该文中 有的地方 或许存在 点错误, 希望 看到的朋友 帮...函数存储过程 Oracle 数据库中不仅可以使用 单条语句对数据库进行
  • 你还在为如何调用oracle中的存储过程函数而烦恼吗,请看看该文档。
  • oracle存储过程函数的区别 2017年08月15日 22:49:38 冷月葬花魂iiii 阅读数:7800更多 个人分类: oracle 定义: 存储过程(Stored Procedure )是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库...
  • 1、写了一个调用远端sayHello存储过程存储过程,远端sayHello存储过程如下:   -- 远端 sayHello 定义如下: create or replace procedure sayHello(currentTime out varchar) is begin select 'Hello World'...
  • 1、存储过程是自定义的且封装在数据库服务器上的 PLSQL 代码片段,它已经编译好了,可以像调用数据库函数一样来调用存储过程,可以给它传递参数,它也可以有返回值。 2、因为存储过程是编译好放在数据库服务器上的...
  • oracle函数存储过程的区别和联系  在oracle中,函数存储过程是经常使用到的,他们的语法中有很多相似的地方,但也有自己的特点。刚学完函数存储过程,下面来和大家分享一下自己总结的关于函数存储过程的...
  • oracle中,函数存储过程是经常使用到的,他们的语法中有很多相似的地方,可是也有它们的不同之处,这段时间刚学完函数存储过程,来给自己做一个总结: 一:存储过程:简单来说就是有名字的pl/sql块。语法结构...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 80,686
精华内容 32,274
关键字:

oracle函数调用存储过程