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

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

### Oracle第二天 ###

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

### 视图 ###

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

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

**创建视图的三种方式**

**1.普通创建**

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

**2.使用replace创建**

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

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

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

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

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

**修改视图**

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

**修改后**

![在这里插入图片描述][20200803165249610.png]

### 索引 ###

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

**单列索引**

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

create index ind1 on emp(ename);

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

**复合索引**

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

--以下索引可以同时存在，因为它们的优先检索列不同，是两个不同的索引
    Create index emp_idx1 on emp(ename,job);
    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变量声明示例 ####

--pl/SQL语句
    --引用变量
    declare 
      emprc emp.ename%type;
    begin 
      select emp.ename into emprc from emp where emp.empno=7782;
      dbms_output.put_line(emprc);
    end;
    
    --记录型变量
    declare 
      emprow emp%rowtype;
    begin
      select * into emprow from emp where emp.empno=7782;
      dbms_output.put_line(emprow.ename||' '||emprow.sal);
    end;

#### pl/sql中if循环语句 ####

--用if语句判断成绩等级，90-100以上优秀，70-90为良好，60-70为及格，0-60为不及格
    declare
     score number:=&score;
    begin
      if score>=90 and score<=100 then 
        dbms_output.put_line('您的分数为'||score||'   恭喜您，您的等级为优秀！');
      elsif score>=70 then 
        dbms_output.put_line('您的分数为'||score||'   您的等级为良好,请继续加油！');
    
      elsif score>=60 then 
         dbms_output.put_line('您的分数为'||score||'   等级达到及格，还可以更棒哦！'); 
      elsif score>=0 then 
         dbms_output.put_line('您的分数为'||score||'   等级为不及格，要加把劲哦！');
      else
         dbms_output.put_line('您的分数有误');
         end if;
    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**

--方式一
    declare
     pnum number :=1;
    begin
      while pnum<=10 loop
            dbms_output.put_line(pnum);
            pnum:=pnum+1;
      end loop;
    end;
    --方式二
    declare
    	step number := 1;
    begin
    	loop
    	exit when step > 10;
    	dbms_output.put_line(step);
    	step := step + 1;
    	end loop;
    end;
    --方式三
    declare
    	step number := 1;
    begin
    	for step in 1 .. 10 loop
    		dbms_output.put_line(step);
    	end loop;
    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 表中的员工编号和姓名**

declare
    	cursor pc is
    	select * from emp;
    	pemp emp%rowtype;
    begin
    	open pc;
    		loop
    			fetch pc
    			into pemp;
    			exit when pc%notfound;
    			dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
    		end loop;
    	close pc;
    end;

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

declare
    	cursor pc is select * from myemp;
    	addsal myemp.sal%type;
    	pemp myemp%rowtype;
    begin
    	open pc;
    		loop
    			fetch pc into pemp;
    			exit when pc%notfound;
    			if pemp.job = 'PRESIDENT' then
    				addsal := 1000;
    			elsif pemp.job = 'MANAGER' then
    				addsal := 800;
    			else
    				addsal := 400;
    			end if;
    			update myemp t set t.sal = t.sal + addsal where t.empno =pemp.empno;
    		end loop;
    	close pc;
    end;

### 存储过程 ###

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

创建存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
    AS
    begin
    	PLSQL 子程序体；
    End;
    
    -------------------------------
    create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
    is
    begin
    	PLSQL 子程序体；
    End 过程名;

**示例1**

create or replace procedure helloworld is
    begin
    	dbms_output.put_line('helloworld');
    end helloworld;
    
    -----调用存储过程--------
    begin
    	helloworld;
    end;

**示例2**

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

### 存储函数 ###

**语法**

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

**示例**

create or replace function empincome(eno in emp.empno%type) return
    number is 
    	psal emp.sal%type;
    	pcomm emp.comm%type;
    begin
    	select t.sal into psal from emp t where t.empno = eno;
    	return psal * 12 + nvl(pcomm, 0);
    end;
    
    --调用
    declare
    	income number;
    begin
    	empincomep(7369, income);
    	dbms_output.put_line(income);
    end;

### 触发器 ###

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

**触发器可用于**

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

**触发器的类型**

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

**语法：**

CREATE [or REPLACE] TRIGGER 触发器名
    	{BEFORE | AFTER}
    	{DELETE | INSERT | UPDATE [OF 列名]}
    	ON 表名
    	[FOR EACH ROW [WHEN(条件) ] ]
    begin
    	PLSQL 块
    End 触发器名

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

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

### java调用Oracle ###

#### java连接Oracle数据库 ####

**一、加载驱动**

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

**二、连接数据库**

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

**三、创建PrepareStament对象**

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

**四、关闭资源**

if(coon!=null&&p!=null){}
    		try {
    			coon.close();
    			p.close();
    		} catch (SQLException e) {
    		// TODO Auto-generated catch block
    			e.printStackTrace();

#### java调用Oracle存储过程 ####

@Test
    public void testProcedure01(){
    	String driver="oracle.jdbc.OracleDriver";
    	String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";
    	String username="scott";
    	String password="tiger";
    	try {
    		Class.forName(driver);
    		Connection con = DriverManager.getConnection(url,
    		username, password);
    		CallableStatement callSt = con.prepareCall("{call
    		proc_countyearsal(?,?)}");
    		callSt.setInt(1, 7839);
    		callSt.registerOutParameter(2, OracleTypes.NUMBER);
    		callSt.execute();
    		System.out.println(callSt.getObject(2));
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
    }

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

[20200803165210984.png]: /images/20230208/bbff91bb8a254a0b863ccd5cd4e43737.png
[20200803165249610.png]: /images/20230208/c265155fc4824773bc6c60ff5962f9c1.png