Oracle第二天---视图+索引+pl/sql基本语法+存储过程+存储函数+触发器+Java调用Oracle

逃离我推掉我的手 2023-03-04 11:27 55阅读 0赞

Oracle第二天

      • 视图
      • 索引
      • pl/sql基本语法
        • 什么是 PL/SQL?
        • pl/sql的基本语法
        • pl/sql变量声明示例
        • pl/sql中if循环语句
        • LOOP循环语句
        • 游标
      • 存储过程
      • 存储函数
      • 触发器
      • java调用Oracle
        • java连接Oracle数据库
        • java调用Oracle存储过程

视图

视图(VIEW)也被称作虚表，即虚拟的表，其实就是封装了一条复杂查询的语句。 是一组数据的逻辑表示,其本质是对一条SELECT语句的结果集赋予一个名字，即视图名字。

视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。开发中有时候我们希望将数据给非数据管理员参考，但是又不希望其修改我们的数据，这时候就可以使用视图来对查询的语句做一个封装，并设置为只读模式。

创建视图的三种方式

1.普通创建

  1. --建立一个视图，此视图包括了 20 部门的全部员工信息
  2. create view emp_20 as select * from emp where emp.deptno=20;

2.使用replace创建

  1. --创建一个视图，如果视图已存在，则直接覆盖原视图
  2. create or replace view emp_20 as select * from emp where emp.deptno=10;

3.创建时将视图设置为只读模式

  1. --为了防止视图数据被改变而影响原表的数据，也可以将视图设置为只读
  2. create or replace view emp_20 as select * from emp where emp.deptno=20 with read only;

修改视图数据时原表数据会改变吗？

会的，因为视图本并不包含任何数据，它只是一种映射，当我们修改视图的数据的时候，实际上也就是修改原表的原数据
修改前
在这里插入图片描述

修改视图

  1. --修改视图7369号名称
  2. update emp_20 set ename='KYXS' where empno=7369;

修改后

在这里插入图片描述

索引

索引的实质就是在表的列上构建一个二叉树,是用于加速数据存取的数据对象。 合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。但是索引会影响增删改的效率，因为每次增删改都需要重新构建索引的二叉树，索引大可分为单列索引和复合索引。

单列索引

单列索引是基于单个列所建立的索引，如下

  1. create index ind1 on emp(ename);

它的触发机制是当你在查询emp表的ename字段的时候就会触发这个名为ind1的索引。

复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同,比如：

  1. --以下索引可以同时存在，因为它们的优先检索列不同，是两个不同的索引
  2. Create index emp_idx1 on emp(ename,job);
  3. Create index emp_idx1 on emp(job,ename)

索引的使用原则：

  • 在大表上建立索引才有意义
  • 在 where 子句后面或者是连接条件上的字段建立索引
  • 表中数据修改频率高时不建议建立索引

pl/sql基本语法

什么是 PL/SQL?

PL/SQL （Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

pl/sql的基本语法

 declare
  声明部分 （变量说明， 游标申明，例外说明 〕
 begin
  语句序列 （DML 语句〕 …
 exception
  例外处理语句
 end;

pl/sql变量声明示例

  1. --pl/SQL语句
  2. --引用变量
  3. declare 
  4.   emprc emp.ename%type;
  5. begin 
  6.   select emp.ename into emprc from emp where emp.empno=7782;
  7.   dbms_output.put_line(emprc);
  8. end;
  10. --记录型变量
  11. declare 
  12.   emprow emp%rowtype;
  13. begin
  14.   select * into emprow from emp where emp.empno=7782;
  15.   dbms_output.put_line(emprow.ename||' '||emprow.sal);
  16. end;

pl/sql中if循环语句

  1. --用if语句判断成绩等级，90-100以上优秀，70-90为良好，60-70为及格，0-60为不及格
  2. declare
  3.  score number:=&score;
  4. begin
  5.   if score>=90 and score<=100 then 
  6.     dbms_output.put_line('您的分数为'||score||'   恭喜您，您的等级为优秀！');
  7.   elsif score>=70 then 
  8.     dbms_output.put_line('您的分数为'||score||'   您的等级为良好,请继续加油！');
  10.   elsif score>=60 then 
  11.      dbms_output.put_line('您的分数为'||score||'   等级达到及格，还可以更棒哦！'); 
  12.   elsif score>=0 then 
  13.      dbms_output.put_line('您的分数为'||score||'   等级为不及格，要加把劲哦！');
  14.   else
  15.      dbms_output.put_line('您的分数有误');
  16.      end if;
  17. end;

LOOP循环语句

语法 1：

WHILE total <= 25000 LOOP
… .
total : = total + salary;
END LOOP;

语法 2：

Loop
EXIT [when 条件];
……
End loop

语法 3：

FOR I IN 1 . . 3 LOOP
语句序列 ;
END LOOP ;

使用以上三种语法输出1-10

  1. --方式一
  2. declare
  3.  pnum number :=1;
  4. begin
  5.   while pnum<=10 loop
  6.         dbms_output.put_line(pnum);
  7.         pnum:=pnum+1;
  8.   end loop;
  9. end;
  10. --方式二
  11. declare
  12.     step number := 1;
  13. begin
  14.     loop
  15.     exit when step > 10;
  16.     dbms_output.put_line(step);
  17.     step := step + 1;
  18.     end loop;
  19. end;
  20. --方式三
  21. declare
  22.     step number := 1;
  23. begin
  24.     for step in 1 .. 10 loop
  25.         dbms_output.put_line(step);
  26.     end loop;
  27. end;

游标

在写 java 程序中有集合的概念，那么在 pl/sql 中也会用到多条记录，这时候我们就要用到游标，
游标可以存储查询返回的多条数据。
语法：CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,…)] IS SELECT 语句;

游标的使用步骤：

  • 打开游标： open c1; (打开游标执行查询)
  • 取一行游标的值： fetch c1 into pjob; (取一行到变量中)
  • 关闭游标： close c1;(关闭游标释放资源)
  • 游标的结束方式 exit when c1%notfound
  • 注意： 上面的 pjob 必须与 emp 表中的 job 列类型一致：
    定义： pjob emp.empjob%type;

使用游标方式输出 emp 表中的员工编号和姓名

  1. declare
  2.     cursor pc is
  3.     select * from emp;
  4.     pemp emp%rowtype;
  5. begin
  6.     open pc;
  7.         loop
  8.             fetch pc
  9.             into pemp;
  10.             exit when pc%notfound;
  11.             dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
  12.         end loop;
  13.     close pc;
  14. end;

按员工的工种涨工资,总裁 1000 元，经理涨 800 元其，他人员涨 400 元

  1. declare
  2.     cursor pc is select * from myemp;
  3.     addsal myemp.sal%type;
  4.     pemp myemp%rowtype;
  5. begin
  6.     open pc;
  7.         loop
  8.             fetch pc into pemp;
  9.             exit when pc%notfound;
  10.             if pemp.job = 'PRESIDENT' then
  11.                 addsal := 1000;
  12.             elsif pemp.job = 'MANAGER' then
  13.                 addsal := 800;
  14.             else
  15.                 addsal := 400;
  16.             end if;
  17.             update myemp t set t.sal = t.sal + addsal where t.empno =pemp.empno;
  18.         end loop;
  19.     close pc;
  20. end;

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经
编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来
执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存
储过程。

创建存储过程语法：

  1. create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
  2. AS
  3. begin
  4.     PLSQL 子程序体；
  5. End;
  7. -------------------------------
  8. create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
  9. is
  10. begin
  11.     PLSQL 子程序体；
  12. End 过程名;

示例1

  1. create or replace procedure helloworld is
  2. begin
  3.     dbms_output.put_line('helloworld');
  4. end helloworld;
  6. -----调用存储过程--------
  7. begin
  8.     helloworld;
  9. end;

示例2

  1. --给指定的员工涨 100 工资，并打印出涨前和涨后的工资
  2. create or replace procedure addsal1(eno in number) is
  3. pemp emp%rowtype;
  4. begin
  5.     select * into pemp from emp where empno = eno;
  6.     update emp set sal = sal + 100 where empno = eno;
  7.     dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' ||
  8.     (pemp.sal + 100));
  9. end addsal1;
  11. --调用
  12. begin
  13. addsal1(eno => 7902);
  14. commit;
  15. end;

存储函数

语法

  1. create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is
  2. 结果变量 数据类型;
  3. begin
  4.     return(结果变量);
  5. end 函数名;

示例

  1. create or replace function empincome(eno in emp.empno%type) return
  2. number is 
  3.     psal emp.sal%type;
  4.     pcomm emp.comm%type;
  5. begin
  6.     select t.sal into psal from emp t where t.empno = eno;
  7.     return psal * 12 + nvl(pcomm, 0);
  8. end;
  10. --调用
  11. declare
  12.     income number;
  13. begin
  14.     empincomep(7369, income);
  15.     dbms_output.put_line(income);
  16. end;

触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时， Oracle 自动地执行触发器中定义的语句序列。

触发器可用于

  • 数据确认
  • 实施复杂的安全性检查
  • 做审计，跟踪表上所做的数据操作等
  • 数据的备份和同步

触发器的类型

语句级触发器 ： 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器（FOR EACH ROW） ： 触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态。

语法：

  1. CREATE [or REPLACE] TRIGGER 触发器名
  2.     {BEFORE | AFTER}
  3.     {DELETE | INSERT | UPDATE [OF 列名]}
  4.     ON 表名
  5.     [FOR EACH ROW [WHEN(条件) ] ]
  6. begin
  7.     PLSQL 块
  8. End 触发器名

范例：插入员工后打印一句话“一个新员工插入成功”

  1. create or replace trigger testTrigger
  2.     after insert on person
  3. declare
  4. begin
  5.     dbms_output.put_line('一个员工被插入');
  6. end testTrigger;

java调用Oracle

java连接Oracle数据库

一、加载驱动

  1. try {
  2.     Class.forName("oracle.jdbc.OracleDriver");//  加载Oracle驱动                
  3.         } catch (ClassNotFoundException e) {
  4.             e.printStackTrace();
  5.     }

二、连接数据库

  1. Connection coon = null;
  2.     //创建连接；
  3.     try {
  4.     //@后面的IP根据自己的数据库地址写，这里给出格式。
  5.         coon = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:端口号:数据库名称", "用户名", "密码");
  6.         } catch (SQLException e) {
  7.     // TODO Auto-generated catch block
  8.         e.printStackTrace();
  9.     }

三、创建PrepareStament对象

  1. PreparedStatement p = null;
  3.     try {
  4.         p = coon.prepareStatement("select * from emp where empno=?");
  5.         p.setInt(1, 7839);
  6.         p.execute();//表示执行PreparedStatement 中封装的sql语句
  7.     } catch (SQLException e) {
  8.     // TODO Auto-generated catch block
  9.         e.printStackTrace();
  10.     }

四、关闭资源

  1. if(coon!=null&&p!=null){}
  2.         try {
  3.             coon.close();
  4.             p.close();
  5.         } catch (SQLException e) {
  6.         // TODO Auto-generated catch block
  7.             e.printStackTrace();

java调用Oracle存储过程

  1. @Test
  2. public void testProcedure01(){
  3.     String driver="oracle.jdbc.OracleDriver";
  4.     String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";
  5.     String username="scott";
  6.     String password="tiger";
  7.     try {
  8.         Class.forName(driver);
  9.         Connection con = DriverManager.getConnection(url,
  10.         username, password);
  11.         CallableStatement callSt = con.prepareCall("{call
  12.         proc_countyearsal(?,?)}");
  13.         callSt.setInt(1, 7839);
  14.         callSt.registerOutParameter(2, OracleTypes.NUMBER);
  15.         callSt.execute();
  16.         System.out.println(callSt.getObject(2));
  17.     } catch (Exception e) {
  18.         e.printStackTrace();
  19.     }
  20. }

本博客纯属个人学习笔记，学习资源来自黑马训练营，如有错误，感激指正

发表评论

表情：
评论列表 （有 0 条评论，55人围观）

还没有评论，来说两句吧...

相关阅读