首页 > 数据库 >oracle存储过程

oracle存储过程

时间:2023-09-21 13:03:17浏览次数:90  
标签:存储 END rs 过程 hyq oracle null proc conn


1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGINEND 存储过程名字

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

资料引用:http://www.knowsky.com/344816.html

这段时间开始学习写存储过程,主要原因还是因为工作需要吧,本来以为很简单的,但几经挫折,豪气消磨殆尽,但总算搞通了,为了避免后来者少走弯路,特记述与此,同时亦对自己进行鼓励。

一:无返回值的存储过程

存储过程为:

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)  AS
BEGIN 
   INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (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路径里,否则会报错的。

 SQL server 的存储过程返回结果集很简单

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; 
/

标签:存储,END,rs,过程,hyq,oracle,null,proc,conn
From: https://blog.51cto.com/u_16255870/7552985

相关文章

  • oracle 数据库对象自动备份
         本文介绍一种数据库对象备份的方法。其原因是因为我在开发时有两个数据库用户,他们的数据表结构相同,所开发的存储过程也相同,但是数据却是不同地方的数据。因为懒惰所以在修改过程包时只想修改一次就ok,所以想了一个半自动的办法,让数据库自动备份数据库对象,然后编写bat文件......
  • oracle 内置函数备忘录
    这两天在写oracle包,蛮大的一个,为了方便查找,就丢上来个。黄色是经常用到的,顺便看看对大家有没有帮助。  SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL>selectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;       A    ......
  • 讲清楚 kafka 拉取消息的过程
    kafka是一个高吞吐的消息服务中间件,当然这一切都是有原因的,今天我从kafka拉取消息这个场景剖析下broker的实现。问题:kafkaconsumer在poll的时候传递了一个timeout的参数,broker是怎么处理这个参数的?如果leaderbroker有消息,肯定是立刻返回,如果没有呢,kafka应该是等......
  • Oracle 数据库11g版本dataguard创建的简单方法
    作者:ArupNanda DataGuard了解ActiveDataGuard如何通过实时查询,同时应用归档的的日志、将物理备用数据库转换为快照备用数据库以及对基础架构的一系列改进措施,让您对备份环境的投资物有所值。下载Oracle数据库11gOracle数据库11g对DataGuard功能进行了多方面的增强,......
  • greenplum到oracle的dbi_link的配置、调试与测试
    为了解决greenplum到oracle的数据库的数据互联互通的问题,特提供的dbi_link做了研究与测试,dbi_link的基本原理是用Perl的DBI和相应数据库的DBD来访问异构数据库,实现数据的互访与数据传递,这次研究的目的是想解决oracle数据仓库到greenplum的数据传输的问题,这次的研究大概可以分为以......
  • Oracle 数据库日常维护(3)
    常用DBA管理脚本一、数据库构架体系1、表空间的监控是一个重要的任务,我们必须时刻关心表空间的设置,是否满足现在应用的需求,以下的语句可以查询到表空间的详细信息SELECTTABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTE......
  • 【引用】Oracle全文检索方面的研究(全5)
    3.5Storage属性Oracle全文检索通常会生成一系列的辅助表,生成规则是dr$+索引名+$+表用途标识,由于这些表是oracle自动生成的,通常没有办法为这些表指定存储空间。为构造text索引所生成的辅助表指定表空间、存储参数(usethestoragepreferencetospecifytablespaceandcreation......
  • 【引用】Oracle全文检索方面的研究(全3)
    3.3Lexer属性               Oracle全文检索的lexer属性用于处理各种不同的语言,最基本的英文使用basic_lexer,中文则可以使用chinese_vgram_lexer或chinese_lexer。   3.3.1Basic_lexerbasic_lexer属性支持如英语、德语、荷兰语、挪威语、瑞典语等以空格......
  • 【引用】Oracle全文检索方面的研究(全6)
    3.6Wordlist属性Oracle全文检索的wordlist属性用来设置模糊查询和同词根查询,wordlist属性还支持子查询和前缀查询,oracle的wordlist属性只有basic_wordlist一种(原文:Usethewordlistpreferencetoenablethequeryoptionssuchasstemming,fuzzymatchingforyourlang......
  • 【引用】Oracle全文检索方面的研究(全2)
    3.2Filter属性过滤器负责将各种文件格式的数据转换为纯文本格式,索引管道中的其他组件只能处理纯文本数据,不能识别microsoftword或excel等文件格式,filter有charset_filter、inso_filter、null_filter、user_filter、procedure_filter几种类型。(可将文档格式转化为数据库文......