Oracle第二天---视图+索引+pl/sql基本语法+存储过程+存储函数+触发器+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;

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

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

修改视图

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

修改后

索引

索引的实质就是在表的列上构建一个二叉树,是用于加速数据存取的数据对象。 合理的使用索引可以大大降低 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();
	}
}			

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