java下促成调用oracle的储存进程和函数

2019-04-07 21:54栏目:IT操作系统

ca888会员登录 1ca888会员登录 2

肆.创造和储存进度:P_GET_PRICE

ca888会员登录 3View Code

ca888会员登录 4

--创建存储过程CREATEORREPLACEPROCEDURE 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 INNUMBER) ISBEGIN    AN_O_RET_CODE :=0;    AC_O_RET_MSG  :='操作成功';        OPEN CUR_RET FORSELECT*FROM STOCK_PRICES WHERE PRICE< span>AN_I_PRICE;EXCEPTION    WHEN OTHERS THEN        AN_O_RET_CODE :=-1;        AC_O_RET_MSG  :='错误代码:'|| SQLCODE || CHR(13) ||'错误信息:'|| SQLERRM;END P_GET_PRICE;

ca888会员登录 5

</span>

java下达成调用oracle的蕴藏进度和函数

在oracle下成立二个test的账户,然后按一下步骤执行:

五    ——2: 游标的品质

ca888会员登录 6

ca888会员登录 7

ca888会员登录 8ca888会员登录 9

  1 set serveroutput on
  2 declare
  3    var_ename varchar2(50);/*声明变量,用来储存雇员名称*/
  4    var_job   varchar2(50);/*声明变量,用来储存雇员的职务*/
  5    /*声明游标,检索指定员工编号的雇员信息*/
  6   cursor cur_emp /*定义游标,检索指定编号的记录信息*/
  7    is select ename ,job from emp where empno=7499;
  8 begin
  9    open cur_emp;/*打开游标*/
 10    fetch cur_emp into var_ename,var_job ;/*读取游标,并且储存雇员名称和职务*/
 11      if cur_emp%found then /*若检索到数据记录,则输出雇员信息*/
 12        dbms_output.put_line('编号是7499的雇员名称为'||var_ename||',职务是:'||var_job);
 13       else
 14         dbms_output.put_line('无数据记录');/*提示无记录信息*/
 15       end if;
 16 end;
 17 /

View Code

ca888会员登录 10

ca888会员登录 11

 1 create proc tb1_proc (
 2 
 3 @cur cursor varying output
 4 
 5 )
 6 
 7 as
 8 
 9 begin
10 
11   set @cur=cursor for
12 
13   select * from tb1
14 
15 end
16 
17 open @cur

三.建立1个回来游标: PKG_PUB_UTILS

ca888会员登录 12View Code

--建立一个返回游标CREATEORREPLACE PACKAGE PKG_PUB_UTILS IS--动态游标    TYPE REFCURSOR IS REF CURSOR;END PKG_PUB_UTILS;

3.起家二个赶回游标: PKG_PUB_UTILS

ca888会员登录 13

--建立一个返回游标
CREATE OR REPLACE PACKAGE PKG_PUB_UTILS IS
    --动态游标
    TYPE REFCURSOR IS REF CURSOR;
END PKG_PUB_UTILS;

二     —— 2_____3:PL/SQL字符集

ca888会员登录 14

1 1 create table tb1(
2 2 
3 3 id int ,
4 4 
5 5 name nvarchar(20)
6 6 
7 7 )

1.创建表:STOCK_PRICES

ca888会员登录 15View Code

--创建表格CREATETABLE STOCK_PRICES(    RIC VARCHAR(6) PRIMARYKEY,    PRICE NUMBER(7,2),    UPDATED DATE );

2.插入测试数据:

ca888会员登录 16

--插入数据
INSERT INTO stock_prices values('1111',1.0,SYSDATE);
INSERT INTO stock_prices values('1112',2.0,SYSDATE);
INSERT INTO stock_prices values('1113',3.0,SYSDATE);
INSERT INTO stock_prices values('1114',4.0,SYSDATE);

三     —— 2_____2:  record  类型

ca888会员登录 17

ca888会员登录 18

ca888会员登录 19

ca888会员登录 20ca888会员登录 21

  1 set serveroutput on  /**/
  2 declare
  3   type emp_type is record
  4    (
  5     var_ename varchar2(20),  /*定义字段--成员变量  */
  6     var_job varchar2(20),
  7     var_sal number
  8    );
  9    empinfo emp_type;  /*定义变量*/
 10 begin
 11   select ename,job,sal into empinfo from emp where empno=7839 ;/*检索数据*/
 12     dbms_output.put_line( '雇员'||empinfo.var_ename||'的职位是'||empinfo.var_job||'、工资是'||empinfo.var_sal);
 13 
 14 end;
 15 /

View Code

ca888会员登录 22

 1 declare @my_cur cursor
 2 
 3 declare @id int, @name nvarchar(20)
 4 
 5 exec tb1_proc @my_cur output
 6 
 7 --open @cursor    -- @cursor already opened
 8 
 9 fetch next from @my_cur into @id, @name
10 
11 while(@@fetch_status=0)
12 
13   begin
14 
15     print '编号:'   convert(nvarchar,@id)
16 
17     print '姓名:'   @name
18 
19     print '......................'
20 
21     fetch next from @my_cur into @id, @name
22 
23   end

在oracle下创立一个test的账户,然后按一下步骤执行:

三     —— 2_____1: %TYPE 类型

ca888会员登录 23

ca888会员登录 24

ca888会员登录 25

ca888会员登录 26

ca888会员登录 27ca888会员登录 28

  1 SQL>  set serveroutput on      /*在服务器端 输出结果*/
  2 SQL>  declare
  3   2     var_ename emp.ename%type; /*声明与ename 列类型相同的变量*/
  4   3     var_job   emp.job%type;   /*声明与job列类型相同的变量*/
  5   4   begin
  6   5      select ename,job into var_ename,var_job from emp where empno=7839 ;/*检索数据,并保存在变量中*/
  7   6      dbms_output.put_line(var_ename||'工资是'||var_job);
  8   7  end;
  9   8  /
 10 
 11 KING工资是PRESIDENT
 12 
 13 PL/SQL procedure successfully completed

View Code

ca888会员登录 29

ca888会员登录 30

创建表:

五.创建函数:

ca888会员登录 31View Code

ca888会员登录 32

--创建函数:F_GET_PRICECREATEORREPLACEFUNCTION F_GET_PRICE(v_price INNUMBER)    RETURN PKG_PUB_UTILS.REFCURSORAS    stock_cursor PKG_PUB_UTILS.REFCURSOR;BEGINOPEN stock_cursor FORSELECT*FROM stock_prices WHERE price < span> v_price;    RETURN stock_cursor;END;

ca888会员登录 33

</span>

1.创建表:STOCK_PRICES

ca888会员登录 34

--创建表格
CREATE TABLE STOCK_PRICES(
    RIC VARCHAR(6) PRIMARY KEY,
    PRICE NUMBER(7,2),
    UPDATED DATE );

五ca888会员登录,    ——1_____二:打开游标 

ca888会员登录 35


6.JAVA调用存款和储蓄进度重临结果集

代码示例:JDBCoracle十G_INVOKEPROCEDURE.java

ca888会员登录 36View Code

ca888会员登录 37

import java.sql.*;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;/* 本例是通过调用oracle的存储过程来返回结果集: * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip */publicclass 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:@localhost:1521:ORCL";        // oracle 用户        user = "test";        // oracle 密码        pwd = "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 );    }    publicvoid 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 ");        }    }    publicstaticvoid main(String args[])// 自己替换[]    {        new JDBCoracle10G_INVOKEPROCEDURE();    }}

ca888会员登录 38

五.创办函数:

ca888会员登录 39View Code

二:Pl/Sql 概述

ca888会员登录 40

ca888会员登录 41ca888会员登录 42

2.插入测试数据:

ca888会员登录 43View Code

--插入数据INSERTINTO stock_prices values('1111',1.0,SYSDATE);INSERTINTO stock_prices values('1112',2.0,SYSDATE);INSERTINTO stock_prices values('1113',3.0,SYSDATE);INSERTINTO stock_prices values('1114',4.0,SYSDATE);

四.成立和仓库储存进程:P_GET_PRICE

ca888会员登录 44

ca888会员登录 45

--创建存储过程
CREATE OR REPLACE PROCEDURE 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;

ca888会员登录 46

四  :流程序控制制语句

ca888会员登录 47

View Code

柒.开发JAVA调用函数重返结果集

代码示例:JDBCoracle拾G_INVOKEFUNCTION.java

ca888会员登录 48View Code

ca888会员登录 49

import java.sql.*;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;/* /* 本例是通过调用oracle的函数来返回结果集: * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip   */publicclass 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:@localhost:1521:ORCL";        // oracle 用户        user = "test";        // oracle 密码        pwd = "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 );    }    publicvoid 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 ");        }    }    publicstaticvoid main(String args[])// 自己替换[]    {        new JDBCoracle10G_INVOKEFUNCTION();    }}

ca888会员登录 50

 

 

 

7.开发JAVA调用函数再次来到结果集

代码示例:JDBCoracle十G_INVOKEFUNCTION.java

ca888会员登录 51

ca888会员登录 52

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:@localhost:1521:ORCL";
        // oracle 用户
        user = "test";
        // oracle 密码
        pwd = "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();
    }
}

三     —— 1_____1:数值类型

ca888会员登录 53

成立重临游标的存款和储蓄进度:

陆.JAVA调用存储进程再次回到结果集

代码示例:JDBCoracle10G_INVOKEPROCEDURE.java

ca888会员登录 54

ca888会员登录 55

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:@localhost:1521:ORCL";
        // oracle 用户
        user = "test";
        // oracle 密码
        pwd = "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();
    }
}

ca888会员登录 56

五    ——1_____三: 读取游标 

ca888会员登录 57

ca888会员登录 58

ca888会员登录 59

ca888会员登录 60ca888会员登录 61

  1 
  2 set serveroutput on
  3 declare
  4   /*声明游标,检索雇员信息*/
  5   cursor cur_emp(var_job varchar2 := 'SALESMAN') is
  6     select empno, ename, sal from emp where job = var_job;
  7   type record_emp is record /*声明一个记录类型 record 类型*/
  8   (
  9     /*定义当前记录的成员变量*/
 10     var_empno emp.empno%type,
 11     var_ename emp.ename%type,
 12     var_sal   emp.sal%type);
 13   emp_row record_emp; /*声明一个record_emp 类型变量*/
 14 begin
 15   open cur_emp('MANAGER'); /*打开游标*/
 16   fetch cur_emp
 17     into emp_row; /*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 18   while cur_emp%found loop
 19     dbms_output.put_line(emp_row.var_ename || '的编号是' || emp_row.var_empno ||
 20                          ',工资是' || emp_row.var_sal);
 21 
 22     fetch cur_emp
 23       into emp_row; /*让指针指向结果集的下一行,并将值保存到emp_row中*/
 24   end loop;
 25   close cur_emp; /*关闭游标*/
 26 end;
 27 /

View Code

ca888会员登录 62

ca888会员登录 63

 使用存储进程再次回到的游标:

3     —— 一:基本数据类型

ca888会员登录 64


Pl/SQL 编程

版权声明:本文由ca888发布于IT操作系统,转载请注明出处:java下促成调用oracle的储存进程和函数