JavaEE学习日志(一百零一): Oracle高级，JDBC访问存储过程和存储函数

最新推荐文章于 2024-10-10 11:43:49 发布
最新推荐文章于 2024-10-10 11:43:49 发布
阅读量175 收藏
点赞数
分类专栏： oracle JavaEE学习日志 文章标签： oracle 存储过程 存储函数 jdbc plsql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Sakuraaaaaaa/article/details/106103214
版权

Oracle

视图

一、什么是视图

  • 能看到的，可视化，是一个虚表
  • 视图是一张虚表，封装了一个复杂的sql查询语句
  • 可以对视图进行增删改查，视图不能存储记录，所有的数据都在基本表中(table)
  • 操作视图就是操作基本表
  • 视图一般只能用来做查询

二、视图的语法：如果权限不足，可以切换system用户来提高当前用户的权限

create [or replace] view 视图名 as sql查询语句;

sql语句的语法中：[]代表可有可无

三、举例

select * from emp_view;
select * from emp;
insert into emp_view(empno,ename) values(2001,'LiSi')

数据增加之后，在原来的表中也添加了数据。
所以，操作视图，就是操作基本表

四、作用

  1. 屏蔽敏感数据
create or replace view employee as select empno,ename,job,mgr,hiredate,deptno from emp;

注意：CRUD操作只能针对于你视图中存在的列

  1. 创建一个只读的视图
create or replace view employee as select empno,ename,job,mgr,hiredate,deptno from emp with read only;
  1. 封装了一个复杂的sql语句

索引

一、什么是索引
目录就是索引
索引创建的三大前提

  1. 百万条记录以上
  2. 经常查询使用的列
  3. 不经常修改的列
    原因：修改列会导致索引重建

注意：主键列和唯一列都是索引列，不能添加索引

二、索引的作用
提高查询的效率

三、索引的语法

对这张表的哪些列来创建索引

create index 索引的名称 on 表(列,...);

四、测试单列索引的效率

  1. 创建表
create table a(
       id number primary key,
       text varchar2(200)
)
  1. 添加百万条数据
-- 创建序列
create sequence aseq;
-- 获取uuid
--select sys_guid() from dual;

declare

begin
  for i in 1..1000000 loop
insert into a values(aseq.nextval,sys_guid());
  end loop;
end;
  1. 添加索引前查询，约0.3s
-- 添加索引前查询
select * from a where text = 'A7CCD10B185546BBB52D66D45622B431'
  1. 添加索引
--创建索引
create index a_index on a(text)
  1. 添加索引后查询，约0.03s
select * from a where text = 'A7CCD10B185546BBB52D66D45622B431'

五、复合索引

如果，给两列以上来创建索引：
表名(name,address)

可以触发索引

select * from 表 where name=' ' and address = ''

不能触发索引：or会触发全表扫描

select * from 表 where name=' ' or address = ''

可以触发索引

select * from 表 where name=' '

不能触发索引：创建索引时后边的列名

select * from 表 where address = ''

plsql的基本语句（过程化语言）

基本语句

在sql语句的基础上，新增了if、循环等操作

plsql的基本结构

  • declare --声明
    声明变量的内容
  • begin --开始
    过程化语言，sql
  • [exception] --异常
    捕获异常
  • end --结束
--声明变量
declare
 -- 声明变量
 -- 声明普通的变量
 i number;
 -- 声明变量且赋值
 j number default 200;
 -- 声明变量，可以指定为引用类型
 -- pname的类型就是emp表中ename列的类型
 pname emp.ename%type;
 pjob emp.job%type;
 -- 声明记录类型变量：可以记录一行的类型
 e_row emp%rowtype;
begin
 -- 赋初始值
 i:=100;
 -- 打印语句
 dbms_output.put_line(i);
 -- ||oracle中的连接符
 -- ''表示一个字符串，""创建别名时可以使用
 dbms_output.put_line('j='||j); 
 -- 将查询出来的姓名打印到控制台上
 -- select .. into .. :select 查询某些列into赋值给某些变量 
 select ename into pname from emp where empno=7788;
 dbms_output.put_line(pname); 
 -- 查询两列
 select ename,job into pname,pjob from emp where empno=7788;
 dbms_output.put_line(pname); 
 dbms_output.put_line(pjob); 
 -- 查询该表的所有列
 select * into e_row from emp where empno=7788;
 dbms_output.put_line(e_row.empno||e_row.ename||e_row.job); 

 
end;

结果

100
j=200
SCOTT
SCOTT
ANALYST
7788SCOTTANALYST

if语句

语法一：

if 条件 then
	sql语句;
end if;

语法二：

if 条件 then
	sql语句;
else
	sql语句;
end if;

语法三：

if 条件 then
	sql语句;
elsif 条件 then
	sql语句;
elsif 条件 then
	sql语句;
...
else
	sql语句;
end if;

declare
   i number;
begin
   i:=&请输入;
   if i >0 then
     dbms_output.put_line('正数'); 
   elsif i<0 then
     dbms_output.put_line('负数');
   else
     dbms_output.put_line('0');
   end if;  
end;

循环语句

语法一：

for i in 集合|游标 loop
	过程化语言
end loop;

在控制台打印1-10所有的整数

declare
begin
  for i in 1..10 loop
    dbms_output.put_line(i); 
  end loop;
  
end;

语法二:

loop
	循环体;
	结束条件: exit when 条件;
end loop

在控制台打印1-10所有的整数

declare
  i number default 1;
begin
  loop
    dbms_output.put_line(i); 
    exit when i = 10;
    i:=i+1;
    
  end loop;
end;

语法三

while 条件 loop
	循环体;
end loop

在控制台打印1-10所有的整数

declare
  i number default 1;
begin
  while i<=10 loop
    dbms_output.put_line(i); 
    i:=i+1;
  end loop;
end;

游标

本质上是一个集合，存储大量的数据
游标主要解决返回多行多列的问题

select into 只能处理一行数据的问题，要处理多行数据的问题：使用游标

声明游标

cursor 游标名 is sql查询语句;

遍历游标

  1. 打开游标
open 游标名
  1. 提取一行的数据
fetch 游标名 into 变量;
-- 把游标中一行的内容读入到变量中
  1. 循环，遍历所有数据，退出
 exit  when  游标名%notfound;
 -- 没有提取到记录，就退出
  1. 关闭游标
close 游标名

在控制台上打印20号部门的所有员工信息

declare
   -- 声明游标
   cursor cur is 
              select * from emp where deptno = 20;
   
   e_row emp%rowtype;
begin
   -- 遍历游标
   open cur;
   loop -- 开始循环
   fetch cur into e_row;
   exit when cur%notfound; --没有就退出
   dbms_output.put_line('编号：'||e_row.empno||'姓名'||e_row.ename); 
   end loop; --结束循环
   close cur;
   
end;

对20号部门所有的员工涨工资

declare
   cursor cur is 
              select empno from emp where deptno = 20;
begin
   -- 涨工资
   -- 注意，for循环会自动打开和关闭游标
   -- 注意：i表示的是一行
   for i in cur loop
     -- dbms_output.put_line(i.empno); 
     update emp set sal = sal + 1 where empno = i.empno;
   end loop;  

end;

一般会结合存储过程使用

存储过程

存储过程：把过程存储起来，里面封装了一组sql语句，且提前编译好，存储在服务端。
优点：安全，效率高
一般由项目经理和dba编写存储过程
使用场景：买了一件商品，数据库的变化过程就可以存储到存储过程中

存储过程的语法
参数分类：

  • 输入型参数
  • 输出型参数
create or replace procedure 过程名(参数列表:参数1 in|out 数据类型,参数2 in|out 数据类型...) 
is|as --相当于plsql基本结构中的declare

begin

end;

示例一：给某员工涨工资

-- 需要的参数
-- 员工编号(输入型,number),涨多少(输入型,number)
create or replace procedure updateSal(eno in number,psal in number) 
is --相当于plsql基本结构中的declare
      oldSal emp.sal%type;
      newSal emp.sal%type;
begin
      -- 查询工资：涨前
      select sal into oldSal from emp  where empno = eno
      dbms_output.put_line('涨前'||oldSal); 
      update emp set sal = sal + psal where empno = eno;
      -- 查询工资：涨后
      select sal into newSal from emp  where empno = eno;
      dbms_output.put_line('涨后'||newSal); 
end; 

-- 访问存储过程：只有输入型参数
call updateSal(7788,200);

示例二：获取某员工的年薪
存储过程

-- 获取某员工的年薪
-- 需要的参数：员工编号(输入项，number)
-- 返回：年薪（输出型，number）
create or replace procedure getYearSal(eno in number,yearSal out number) 
is --相当于plsql基本结构中的declare
   
begin
   select sal*12 + nvl(comm,0) into yearSal from emp where empno = eno;
end;

访问存储过程：有输入有输出

--访问存储过程：访问有输入有输出的存储过程
declare
   --输出型的变量，需要声明一个变量来接收参数的值
   yearSal number;
begin
   getYearSal(7788,yearSal);
   -- 打印年薪
   dbms_output.put_line(yearSal); 
end;

示例三：查询某部门所有的员工信息

输出参数为游标类型，游标类型

  • 静态的游标类型(cursor)：在声明游标时指定sql语句
  • 动态的游标类型(sys_refcursor)：在使用时指定sql语句
create or replace procedure getEmps(dno in number,emps out sys_refcursor)
as
       
begin
  -- 给动态游标指定sql语句
  open emps for select * from emp where deptno = dno; 
  -- 查询了某部门的员工信息  select * from emp where deptno = dno;   
end;

注意不能使用for循环

-- 访问存储过程：有输入有输出
declare 
 emps sys_refcursor;
 --声明变量，接收游标的行内容
 e_row emp%rowtype;
begin
   getEmps(30,emps); 
   -- 遍历游标
   loop
     fetch emps into e_row;
     -- 退出
     exit when emps%notfound;
     dbms_output.put_line(e_row.empno||e_row.ename); 
   end loop;
end;

存储函数

和存储过程的定义相同

注意：

  • 存储过程和存储函数属于同一类对象，名称不能重复，且不可替换
  • 存储函数一般没有输出参数

存储函数和存储过程的区别

  1. 存储函数必须有一个返回值
  2. 可以在select语句中访问

存储函数的语法

create or replace function 函数名(参数列表：参数1 in|out 数据类型，参数2 in|out 数据类型,...)
return 数据类型
is|as

begin
	return 值;
end;

获取某员工的年薪

-- 参数：员工编号 in number，年薪 out number（返回值）
create or replace function getYearSalFun(eno in number) return number
is
  yearsal number;
begin
  select sal*12 +nvl(comm,0) into yearsal from emp where empno=eno;
  return yearsal;
end;


-- 访问存储函数
declare
   yearsal number;
begin
  yearsal:= getYearSalFun(7788);
  dbms_output.put_line(yearsal); 
end;

触发器

触发器：简单来说，就是监听器
作用：监听表中的数据是否发生了改变（增删改）

语法：

create or replace trigger --触发器名称
before|after --在改变数据之前（或之后）执行触发器
insert|update|delete --监听的动作
on 表 --监听哪张表
触发器的级别
declare
begin
end

添加一条记录，在控制台打印：打印一条语句

-- 添加一条记录，在控制台打印：打印一条语句
create or replace trigger insertPrint
after
insert
on emp
declare

begin
   dbms_output.put_line('添加了一条记录'); 
end;

insert into emp(empno,ename) values(2002,'wangwu');

不能给员工降薪

create or replace trigger notUpdateLowerSal
before
update
on emp
       --触发器级别
       --表级触发器：默认
          -- 更新多条记录执行一次
       --行级触发器：for each row
          -- 更新一条记录执行一次
       --注意：使用了old，new关键字，必须使用行级触发器
for each row
declare

begin
  -- 判断涨前大于涨后
  -- 相反，不管
  -- 得到之前的工资：old.sal
  -- 得到之后的工资：new.sal
  if:old.sal>:new.sal then 
     dbms_output.put_line('不能降薪'); 
     -- 弹出提示窗口
     -- raise_application_error(p1,p2);
     --p1:错误编号 -20001到-20999
     --p2：错误信息
     raise_application_error(-20001,'不能降薪!!');
  end if;
end;

update emp set sal=sal-1 where empno=7788

jdbc

ojdbc测试

一、引入依赖：可以搜missing ojdbc来解决jar包问题

<!--该jar包是oracle的驱动jar包-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.2.0</version>
        </dependency>

二、测试

public class TestJDBC {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@192.168.137.128:1521:orcl";
    String username = "scott";
    String password = "tiger";

    Connection conn;
    PreparedStatement pst;
    ResultSet rs;
    @Before
    public void init(){
        //注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //获取连接
        try {
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
        查询员工表
     */
    @Test
    public void test() throws SQLException {
        //sql语句
        String sql = "select * from emp";
        //创建预编译对象
        pst = conn.prepareStatement(sql);
        //设置参数

        //执行sql语句
        rs = pst.executeQuery();
        //处理结果集
        while (rs.next()){
            System.out.println(rs.getInt("empno")+rs.getString("ename"));
        }
    }
    @After
    public void close(){
        //关闭资源
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭资源
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭资源
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

jdbc执行SQL存储过程和存储函数

在这里插入图片描述
一、执行存储过程的sql语句写法

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

使用get(输出参数的索引)函数来获取结果

第一种：输出类型为int类型

@Test
    public void test2() throws SQLException {
        //sql语句
        String sql = "{call getYearSal(?,?)} ";
        //创建预编译对象
        cst = conn.prepareCall(sql);
        //设置参数
        //输入型参数
        cst.setInt(1,7788);
        //输出型参数：zh
        cst.registerOutParameter(2, OracleTypes.NUMBER);
        //执行sql语句
        cst.execute();
        //处理结果集
        int ys = cst.getInt(2);
        System.out.println(ys);
    }

第二种：输出类型为游标类型
使用getObject接收游标，并转换为结果集

@Test
    public void test3() throws SQLException {
        //sql语句
        String sql = "{call getEmps(?,?)} ";
        //创建预编译对象
        cst = conn.prepareCall(sql);
        //设置参数
        //输入型参数
        cst.setInt(1,20);
        //输出型参数：zh
        cst.registerOutParameter(2, OracleTypes.CURSOR);
        //执行sql语句
        cst.execute();
        //处理结果集
        Object object = cst.getObject(2);
        rs = (ResultSet) object;
        while (rs.next()){
            System.out.println(rs.getInt("empno")+rs.getString("ename"));
        }
    }

二、执行存储函数的sql语句写法

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

和存储过程没什么区别

@Test
    public void test4() throws SQLException {
        //sql语句
        String sql = "{?= call getYearSalFun(?)}";
        //创建预编译对象
        cst = conn.prepareCall(sql);
        //设置参数
        //输入型参数
        cst.setInt(2,7788);
        //输出型参数：zh
        cst.registerOutParameter(1, OracleTypes.NUMBER);
        //执行sql语句
        cst.execute();
        //处理结果集
        int ys = cst.getInt(1);
        System.out.println(ys);
    }
隔叶听风
关注
专栏目录
JavaEE学习日志(一百零四): ssm练习之产品模块实现
Sakuraaaaaaa的博客
05-18 226
企业级权限管理项目（三）之产品模块的实现： 项目创建和配置、查询所有产品，添加产品，修改产品之数据回显、修改产品、删除单个产品、删除选中的产品
JAVA学习第一天
lycstronger的专栏
07-02 2839
暂为写入内容只是写个模板
JVM：（一）JVM与Java体系结构
sd_960614的博客
08-03 504
宋红康JVM
JAVA该如何学？学习JAVA应该掌握哪些技术？
BASK2311的博客
10-09 1749
从零基础的角度来说，每一个 JAVA 工程师都是从零基础开始学的，他们并不是生来就会 JAVA；学习 JAVA 是一个漫长且煎熬的过程，因为 JAVA 的知识点很多，而且也不是很简单，需要你们花大量时间和精力在上面。而且还需要面临一个技术不断更新的情况，在时代的变化中，主流技术的掌握也是一门持久性功课。
一个牛人给JAVA初学者的建议【转】
_
05-16 723
给初学者之一：浅谈java及应用 学java不知不觉也已经三年了 从不知java为何物到现在一个小小的j2ee项目经理 虽说不上此道高手，大概也算有点斤两了吧 每次上网，泡bbs逛论坛，没少去java相关的版面 总体感觉初学者多，高手少，精通的更少 由于我国高等教育制度教材陈旧，加上java自身发展不过十年左右的时间 还有一个很重要的原因就是java这门语言更适合商业应用 所...
Web前端学习笔记（1）
maxiaoxiaohaohao的博客
10-19 3756
Web前端学习笔记（1）
java学习笔记
qq_64572515的博客
06-05 1114
学习的重点1.如何快速学习java技术2.Java运行机制3.JDK4.Java开发规范5.数据类型6.二进制7.位运算符8.循环9.数组10.成员方法11.成员方法传参机制12.构造器13.封装14.继承15.多态16.类变量和类方法17.接口18.内部类19.异常体系图20.异常处理21.String22.StringBuffer23.StringBuilder24.集合框架体系25.ArrayList26.Vector27.HashSet28.HashMap29.自定义泛型30.Synchronize
Java高阶知识体系总结（一）
Feify博客
05-12 8261
Java高阶知识体系总结 作为Java开发者多年，是否感觉自己只是凌乱，掌握的知识没有体系化？那么通过此次总结，我们来尝试将高阶知识体系化。
7个月我 自学Java 找到啦一份9k的工作，分享经历
LSFZ88888的博客
08-12 721
我是一名普通的不能在普通的大专学生，大学学的是室内设计，毕业后在一所小城市浑浑噩噩的干啦2年工资不到4k，并且在这里看不到任何提升的前景。装饰公司的图纸改的一遍又一遍，同时自己跨行的想法增长的一点又一点~~。 本人平常作图出图纸，所以对电脑这方面还是比较感兴趣的，由于作图原因经常从网络上下载一些实用的小工具，以及各种破解软件。也是于此开始对网络上的软件编程之类的事物开始感兴趣，同时也打开啦自己新世界的大门。 还有一个原因就是身边的朋友有在北京做Java开发的，他工作了三年的...
通向架构师的道路（第十八天）万能框架Spring(一）
热门推荐
lifetragedy的专栏
10-22 4万+
一、前言 前一阵列刚换了个新的工作环境，然后自己的baby也刚出生，一直没有时间去做工作以后的其它事了，担搁了一段日子。 今天儿子满一周了，我内人她家帮着照顾着，总算我可以喘口气休息一下，因此决定将这个系列的博文继续下去，同时也将此篇献给我刚出生一周的儿子和幸苦了10个月的爱人。 二、基本概念 Spring，作为一个流行框架它给我们在日常工程中的框架搭建提供了太多的便利了，它就像一个骨架一
MySQL高级
小小猿的博客
07-22 657
MySQL高级 1 Linux环境下MySQL的搭建 1.1 MySQL下载 MySQL5.7下载地址 1.2 安装MySQL 卸载 centos 中预安装的 mysql rpm -qa | grep -i mysql rpm -e mysql-xxx --nodeps centos7.6自带的类mysql数据库是mariadb，会跟mysql冲突，要先删除 rpm -qa|grep mari rpm -e --nodeps mariadb-libs 在mysql的安装文件目录下执行：
Java学习（下）
Lzdnydppx的博客
08-16 496
web开发、spring开发、spring boot开发、spring cloud开发
java工程师笔试面试题
pamguangyou的专栏
02-08 2万+
1. J2EE 是什么？它包括哪些技术？ 解答：从整体上讲，J2EE 是使用 Java 技术开发企业级应用的工业标准，它是 Java 技术不断适应和促进企业级应用过程中的产物.适用于企业级应用的 J2EE,提供一个平台独立的、可移植的、多用户的、安全的和基于标准的企业级平台，从而简化企业应用的开发、管理和部署。J2EE 是一个标准，而不是一个现成的产品。 主要包括以下这些技术： 1) Ser
MySql - 数据库操作
Golemon的博客
10-08 320
【代码】MySql - 数据库操作。
【2024】基于mysqldump的数据备份与恢复
皮皮的博客
10-03 972
本文记录了mysqldump对于MySQL数据库的备份与恢复用法
gbase8s的事务、并发控制、锁机制、隔离级别
最新发布
wangyx01的博客
10-10 560
这种隔离级别能确保所有的记录都是提交到数据库的，因而能避免读取到脏数据，能确保所有读取到的记录都是已提交的，当一个进程读完记录后，其它进程就可以修改。不仅可以看到提交的记录，也可以保证看到的记录不会被更新，其它进程不能更新或删除你锁看到的记录，当移动到下一行时，锁才会释放，记录就可以进行修改。：多个用户可以读取相同的记录,如果一个对象上没有排他锁，则共享锁可以加在该对象上，它可以防止其他事务更新数据，但同时，其他事务可以读取该数据，多个事务可以在同一个对象上加多个共享锁。表需要设置为行级锁，不能是页级锁。
Oracle rman compress 压缩于时间对比
jnrjian的博客
10-06 453
30TB 压缩备份3.7TB 19TB 不压缩17TB 30TB 不压缩 28TB。29/20*225=330分钟 压缩后时间要原来的。
JavaEE学习指南：基础、Oracle及XML详解
"这是一份详细的JavaEE学习笔记，由作者续祥在2008年11月1日撰写，主要针对Java和Oracle数据库的基础知识进行了深入讲解。笔记分为多个部分，涵盖了Java的核心概念，如Java语言的前言、标识符、关键字、类型、表达式...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值