oracle的视图、序列、数据链以及使用idea连接oracle数据库查询数据

1.视图

• 创建视图

create or replace view view_emp
as
    select a.*,b.dname,b.loc from emp a,dept b  where a.deptno=b.deptno
   order by empno desc;

• 执行视图

select * from view_emp;

• 统计各部门的人数

--方式1
select dname,count(empno) from view_emp group by dname;

--方式2
select dname,count(e.deptno) from emp e,dept d where e.deptno=d.deptno group by dname;

--方式3
select d.dname,rs from (select deptno,count(empno) rs from emp group by deptno) a,dept d where a.deptno=d.deptno;

• 删除视图

drop view view_emp;

2.序列

• 创建序列

create sequence seq_empid
start with 1 --起始值
increment by 1 --增量
--nominvalue 无最小值
minvalue 1
--nomaxvalue 无最大值
maxvalue 888888
cache 20 ;--缓存

• 修改序列

alter sequence seq_empid
increment by 2
minvalue 0 --minvalue不能大于当前的值
maxvalue 6666 --maxvalue不能小于当前的值
cache 30;

• 查询序列当前的值

select seq_empid.currval from dual;

• 查询序列的下一个值

select seq_empid.nextval from dual;

• 删除序列

drop sequence seq_empid;

3.数据链

• 创建数据链

create database link link_cust
 connect to c##lxl identified by www123
 using '(DESCRIPTION =
    (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.001)(PORT = 1521)))
         (CONNECT_DATA =(SERVICE_NAME = orcl))
    )';

• 删除数据链

drop database link link_cust;

• 执行数据链

select * from player@link_cust

4.使用idea连接oracle数据库

• 首先下载oracle的官方驱动jar包（官网可下，我也分享了）
• 执行一下代码

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.2.0 -
Dpackaging=jar -Dfile=D:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar

• 其中-DartifactId=ojdbc6根据你选择的jar来写
• -Dversion=11.2.0.2.0也根据你选择的jar来写
• -Dfile=后面写你下载的jar包的路径
• 然后在cmd中执行（前提你有maven，而且配置好了环境变量）
• 这样你再maven的仓库的路径下就能看到对应的jar包了
• 然后你再idea的依赖中就可以这样写

<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.2.0</version>
        </dependency>

接着就来运行一下试一试（根据你数据库的信息来填相关的信息）

package com.hkd.test;

import oracle.jdbc.OracleTypes;

import java.sql.*;

public class TestOracle1 {
    public static void main(String[] args) {
        t5();

    }

    /**
     * 测试执行存储过程
     */
    public static void t1(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn= DriverManager.getConnection(url,"c##cust","www123");
            CallableStatement cs=conn.prepareCall("{call PROC_PLAYER_INSERT(?,?,?,?,?)}");
            //输入参数的设置
            cs.setInt(1,21);
            cs.setString(2,"jim");
            cs.setString(3,"男");
            //输出参数注册
            cs.registerOutParameter(4, Types.NUMERIC);
            cs.registerOutParameter(5,Types.VARCHAR);
            //执行语句
            cs.execute();
            //获取输出参数
            int result=cs.getInt(4);
            String message=cs.getString(5);
            System.out.println("result:"+result+"\t"+"message:"+message);
            //关闭资源
            cs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试执行函数
     */
    public static void t2(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn= DriverManager.getConnection(url,"c##cust","www123");
            String sql="select fun_getplayercount('男') from dual";
            PreparedStatement ps=conn.prepareStatement(sql);
            ResultSet rs= ps.executeQuery();

            while (rs.next()){
                System.out.println("男性的人数是"+rs.getObject(1));
            }
            //关闭资源
            rs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 测试调用包头的存储过程
     */
    public static void t3(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn= DriverManager.getConnection(url,"c##cust","www123");
            CallableStatement cs=conn.prepareCall("{call pck_player.prop_player_search(?,?)}");
            //输入参数的设置
            cs.setString(1,"女");
            //输出参数注册
            cs.registerOutParameter(2, Types.INTEGER);
            //执行语句
            cs.execute();
            //获取输出参数
            int number=cs.getInt(2);
            System.out.println("number:"+number);
            //关闭资源
            cs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public static void t4() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn = DriverManager.getConnection(url, "c##cust", "www123");
            String sql = "select pck_player.fun_player_search('jim') from dual";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.println("jim" + rs.getObject(1));
            }
            //关闭资源
            rs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 调用返回游标的存储过程
     */
    public static void t5(){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@localhost:1521:orcl1";
            Connection conn= DriverManager.getConnection(url,"c##cust","www123");
            CallableStatement cs=conn.prepareCall("{call proc_player_returnref(?,?)}");
            //输入参数的设置
            cs.setString(1,"女");
            //输出参数注册
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            //两个都行
            //cs.registerOutParameter(2,Types.REF_CURSOR);
            //执行语句
            cs.execute();

            ResultSet rs=(ResultSet) cs.getObject(2);
            while (rs.next()){
                System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
            }
            //关闭资源
            cs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}