www.35222.com【学习】java下落成调用oracle的蕴藏进程和函数

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

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

代码示例:JDBCoracle10G_INVOKEFUNCTION.java

www.35222.com 1View
Code

www.35222.com 2

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();    }}

www.35222.com 3

 

 

 

Pl/SQL 编程

View Code

6.JAVA调用存款和储蓄进程再次回到结果集

代码示例:JDBCoracle10G_INVOKEPROCEDURE.java

www.35222.com 4View
Code

www.35222.com 5

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();    }}

www.35222.com 6

五 :Pl/Sql 游标

www.35222.com 7

创建再次回到游标的蕴藏进度:

在oracle下开创四个test的账户,然后按一入手续实行:

四  :流程序调整制语句

www.35222.com 8

 使用存款和储蓄进程重临的游标:

1.创建表:STOCK_PRICES

www.35222.com 9View
Code

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

五    ——1_____3: 读取游标 

www.35222.com 10

www.35222.com 11

www.35222.com 12

www.35222.com 13www.35222.com 14

  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

www.35222.com 15

www.35222.com 16

www.35222.com 17www.35222.com 18

5.创建函数:

www.35222.com 19View
Code

www.35222.com 20

--创建函数: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;

www.35222.com 21

</span>

二     —— 2_____1:单行注释

www.35222.com 22

www.35222.com 23www.35222.com 24

  1 SQL>  set serveroutput on;       --在服务器端 输出结果
  2 SQL>  declare
  3   2
  4   3     Num_sal number;          --- 声明一个数值变量
  5   4     Var_ename varchar(20);   --- 声明一个字符串变量
  6   5   begin
  7   6      select e.ename,e.sal into Var_ename,Num_sal  from emp e where empno=7839;  --检索指定的值并储存到变量中
  8   7      dbms_output.put_line(Var_ename||'工资是'||Num_sal);
  9   8  end;
 10   9
 11  10
 12  11  /
 13 
 14 KING工资是5000
 15 
 16 PL/SQL procedure successfully completed

View Code

www.35222.com 25

www.35222.com 26

View Code

4.创办和积存进程:P_GET_PRICE

www.35222.com 27View
Code

www.35222.com 28

--创建存储过程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;

www.35222.com 29

</span>

三     —— 2_____1: %TYPE 类型

www.35222.com 30

www.35222.com 31

www.35222.com 32

www.35222.com 33

www.35222.com 34www.35222.com 35

  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

www.35222.com 36

www.35222.com 37

 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

2.插入测量检验数据:

www.35222.com 38View
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);

五    ——3: 通过for 语句循环游标

www.35222.com 39

www.35222.com 40

www.35222.com 41www.35222.com 42

  1 /*使用隐式游标和for语句检索出职务是销售员的雇员信息并输出*/
  2 set serveroutput on
  3 begin
  4    for emp_record in(select empno,ename,sal from emp where job='SALESMAN') /*遍历隐式游标中的记录*/
  5    loop
  6         dbms_output.put_line('雇员编号:'|| emp_record.empno); /*输出雇员编号*/
  7         dbms_output.put_line('雇员名称:'|| emp_record.ename);/*输出雇员名称*/
  8         dbms_output.put_line('雇员工资:'|| emp_record.sal); /*输出雇员工资*/
  9    end loop;
 10 end;
 11 /

View Code

www.35222.com 43

www.35222.com 44

www.35222.com 45

www.35222.com 46www.35222.com 47

  1 /*使用显示游标 和 for 语句检索出部门编号是30的雇员信息并输出 */
  2 set serveroutput on
  3 declare
  4   cursor cur_emp is select * from emp where deptno =30;  /*检索部门编号为30的雇员信息*/
  5 begin
  6   for emp_rocord in cur_emp/*遍历雇员信息*/
  7    loop
  8         dbms_output.put_line('雇员编号:'|| emp_rocord.empno); /*输出雇员编号*/
  9         dbms_output.put_line('雇员名称:'|| emp_rocord.ename);/*输出雇员名称*/
 10         dbms_output.put_line('雇员职务:'|| emp_rocord.job); /*输出雇员工资*/
 11    end loop;
 12 end;
 13 /

View Code

www.35222.com 48

View Code

3.起家三个回到游标: PKG_PUB_UTILS

www.35222.com 49View
Code

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

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

www.35222.com 50

www.35222.com 51www.35222.com 52

二:Pl/Sql 概述

www.35222.com 53


五    ——1_____2:展开游标 

www.35222.com 54

创建表:

三     —— 1_____3:日期类型

www.35222.com 55

www.35222.com,

 注意:存款和储蓄进程中创立游标后要开发

三     —— 1:基本数据类型

www.35222.com 56


三     —— 1_____4:布尔类型

www.35222.com 57

 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

二     —— 2_____2:多行注释

www.35222.com 58

www.35222.com 59www.35222.com 60

  1 set serveroutput on;      /*在服务器端 输出结果*/
  2  declare
  3    Num_sal number;
  4    Var_ename varchar2(20);
  5  begin
  6    /*检索指定的值并储存到变量中*/
  7       select e.ename,e.sal into Var_ename,Num_sal  from emp e where empno=7839;  --检索指定的值并储存到变量中
  8     dbms_output.put_line(Var_ename||'工资是'||Num_sal);
  9 end;
 10 /

View Code

www.35222.com 61

www.35222.com 62

www.35222.com 63www.35222.com 64

六   ——1_____1: 预订义分外

www.35222.com 65

www.35222.com 66

www.35222.com 67

www.35222.com 68www.35222.com 69

  1 /*使用select into语句检索emp 表中部门编号为10 的雇员编号记录信息  然后使用too_many_rows一定*/
  2 
  3 
  4 set serveroutput on
  5 declare
  6    var_empno number ;/*定义变量,储存雇员编号*/
  7    var_ename varchar2(50);/*定义变量,储存雇员名称*/
  8 begin
  9    select empno,ename into var_empno,var_ename from  emp where deptno=10;/*查询部门编号为10的雇员的信息*/
 10  if sql%found then /*如果检索成功,则输出雇员信息*/
 11    dbms_output.put_line('雇员编号:'||var_empno||';雇员名称:'||var_ename);
 12  end if;
 13 exception   /*捕获异常*/
 14    when too_many_rows then    /*若 select into 语句返回的记录超过一行*/
 15     dbms_output.put_line('返回记录超出一行');
 16    when no_data_found then   /*若select into  语句的返回结果为0行*/
 17      dbms_output.put_line('无数据记录');
 18 end;
 19 /

View Code

www.35222.com 70

发表评论

电子邮件地址不会被公开。 必填项已用*标注