package com.swift.jdbc_oracle;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import oracle.jdbc.OracleTypes;

import oracle.jdbc.driver.OracleCallableStatement;

public class Oracle_jdbc {

    String driver = "oracle.jdbc.driver.OracleDriver";

    String url = "jdbc:oracle:thin:@192.168.189.101:1521:orcl";

    String username = "scott";

    String password = "tiger";

    Connection conn =null;

    PreparedStatement statement = null;

    ResultSet rs = null;

    CallableStatement call = null;

    OracleCallableStatement oraclecall = null;

    @Before

    public void init() throws ClassNotFoundException, SQLException {

        Class.forName(driver);

        conn = DriverManager.getConnection(url,username,password);

    }

    //普通查询

    public void test() throws ClassNotFoundException, SQLException {

        String sql="select * from emp";

        statement = conn.prepareStatement(sql);

        rs = statement.executeQuery();

        while(rs.next()) {

            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"

            +rs.getString("comm")+"~"+rs.getString("deptno"));

        }

        System.out.println("Test~~~~~~~~~");

    }

    //访问单返回值存储过程create or replace procedure getYearSal(eno in number,yearsal out number)

    //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

    //sql语法格式     {call <procedure-name>[(<arg1>,<arg2>, ...)]}

    public void procedure() throws SQLException {

        String sql="{call getYearSal(?,?)}";

        call = conn.prepareCall(sql);

        //输入的和之前preparestatement一样

        call.setInt(1, 7788);

        //输出的要注册参数是什么类型

        call.registerOutParameter(2,OracleTypes.NUMBER);

        call.execute();

        //要先查询存储过程再输出结果

        double yearsal = call.getDouble(2);

        System.out.println(yearsal);

    }

    //访问游标返回值存储过程create or replace procedure getEmps(dno in number,emps out sys_refcursor)

    public void procedure2() throws SQLException {

        String sql="{call getEmps(?,?)}";

        call = conn.prepareCall(sql);

        //输入的和之前preparestatement一样

        call.setInt(1, 20);

        //输出的要注册参数是什么类型

        call.registerOutParameter(2,OracleTypes.CURSOR);

        call.execute();

        //要先查询存储过程再输出结果

        Object o = call.getObject(2);

        rs=(ResultSet)o;

        while(rs.next()) {

            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"

            +rs.getString("comm")+"~"+rs.getString("deptno"));

        }

    }

    //访问游标返回值存储过程(OracleCallableStatement)create or replace procedure getEmps(dno in number,emps out sys_refcursor)

    public void procedure3() throws SQLException {

        String sql="{call getEmps(?,?)}";

        oraclecall = (OracleCallableStatement) conn.prepareCall(sql);

        //输入的和之前preparestatement一样

        oraclecall.setInt(1, 20);

        //输出的要注册参数是什么类型

        oraclecall.registerOutParameter(2,OracleTypes.CURSOR);

        oraclecall.execute();

        //要先查询存储过程再输出结果

        rs = oraclecall.getCursor(2);

        while(rs.next()) {

            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"

                    +rs.getString("comm")+"~"+rs.getString("deptno"));

        }

    }

    //访问单返回值存储函数create or replace function x(eno in number) return number

    //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

    public void function() throws SQLException {

        String sql="{?= call x(?)}";

        call = conn.prepareCall(sql);

        //输入的和之前preparestatement一样

        call.setInt(2, 7788);

        //输出的要注册参数是什么类型

        call.registerOutParameter(1,OracleTypes.NUMBER);

        call.execute();

        //要先查询存储过程再输出结果

        double yearsal = call.getDouble(1);

        System.out.println(yearsal); 

    }

    //访问单返回值存储函数create or replace function x(eno in number) return number

    //不用这个格式{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}用存储函数select方法

    @Test

    public void function2() throws SQLException{

        String sql="select x(7788) yearsal from dual";//不能多写一个; 写;会报无效字符异常

        statement=conn.prepareStatement(sql);

        rs= statement.executeQuery();

        while(rs.next()) {

            System.out.println(rs.getInt("yearsal"));

        }

    }

    @After

    public void close() throws SQLException {

        if(rs!=null) {

            rs.close();

        }

        if(statement!=null) {

            statement.close();

        }

        if(conn!=null) {

            conn.close();

        }

    }

}