oracle_day02

视图view和索引index

视图概念:虚表,封装的一条sql语句,简化开发,显示指定的列,视图就是提供一个查询的窗口,所有数据来自于原表。
	create view 视图名 as sql语句 
		with read only;  --只读视图
   eg: create table emp as select * from scott.emp; --使用查询语句创建表,相当于复制一张表
	   create view v_emp as select * from emp ; --创建视图,必须是dba权限
	对视图中的数据进行DML,本质上是还是对原表(基表)数据进行DML。

索引的概念:索引就是在表的列上构建一个二叉树
    优点:提高查询效率,但是降低了增删改的效率
    create index 索引名 on 表名(字段1,字段2)  --1个字段代表单列索引,多个字段代表复合索引
    eg:create index i_ename on emp(ename);
1.单列索引触发规则，条件必须是索引列中的原始值。单行函数,模糊查询,都会影响索引的触发。
2.复合索引中第一列为优先检索列;如果要触发复合索引，必须包含有优先检索列中的原始值。
create index idx_enamejob on emp(ename, job); --SCOTT是原始值,xx不是
select * from emp where ename='SCOTT' and job='xx';---触发复合索引
select * from emp where ename='SCOTT' or job='xx';---不触发索引
select * from emp where ename='SCOTT';---触发单列索引。

LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。
而CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。

plsql

1.概念: procedure language sql,过程化处理sql,pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
语法:
	declare 
	--定义变量
	  begin
	  --业务逻辑
	    end;
2.两种特殊数据类型的定义:
	--引用型: 变量名 表名.字段名%type  引用某张表中某个字段的数据类型
	--记录型: 变量名 表名%rowtype	  记录某张表中所有字段的数据类型,相当于private Emp emp;pojo类型
eg:
	declare
	  v_abc number(10);
	  v_ename emp.ename%type;
	  v_emp emp%rowtype;
	begin
	  v_abc := 10;		--赋值使用冒号等号":="
	  select ename into v_ename from emp e where e.empno=7788; --赋值使用into 
	  select * into v_emp from emp e where e.empno=7788;
	  dbms_output.put_line(v_abc);
	  dbms_output.put_line(v_ename);
	  dbms_output.put_line(v_emp.ename || ',工作是:' || v_emp.job);
	 end;
	 	通常引用型和记录型赋值使用into关键字,oracle数据类型使用":="赋值,但是不必须

3.plsql中的if条件判断
语法:
	if 条件1 then
	  业务逻辑1;
	  elsif 条件2 then	--注意:此处没有e
	   业务逻辑2;
	   else 
	   业务逻辑3;
	   end if;
eg:	declare
		i number(5) := #	--"&"与符号代表oracle的键盘录入,后边的变量随意命名
	begin
		if i > 50 then
		  dbms_output.put_line('老年人');
		elsif i > 30 then
		  dbms_output.put_line('中年人');
		else
		  dbms_output.put_line('年轻人');
		end if;
	end;

	   
4.plsql中的loop循环
  1.while循环,跟java一样
	while 循环条件
	 loop
	  end loop;
eg: declare
		i number(2) := 1 ; --初始化条件语句
	begin
		while i < 11 loop  --条件判断语句,这里没有";"分号
		   dbms_output.put_line('第'||i||'次循环');  --循环体语句
		   i := i + 1;        --条件控制语句
		end loop;
	end;
	
  2.exit循环,跟java一样
	loop
	  exit when 退出条件;
	  end loop;
eg: declare
		i number(2) := 1; --初始化条件语句
	begin
		loop
		  exit when i > 10; --条件判断语句,这里有";"分号
		  dbms_output.put_line('第'||i||'次循环'); --循环体语句
		  i := i + 1 ;   --条件控制语句
		end loop;
	end;
	  
  3.for循环
	 for 变量 in 循环条件
	   loop
	    end loop;
eg: declare						--不需要初始化条件语句
	begin
		for i in 1..10 loop  --这里没有";"分号
			dbms_output.put_line('第'||i||'次循环');  --不需要步进表达式
		end loop;
	end;
	    
5.plsql中的cursor游标:
可以存放多个对象，多行记录。相当于List集合
	declare		--注意:声明游标变量不需要create,关键字使用is
		cursor 游标名(参数名 数据类型) is sql语句; --声明游标,并注入值,数据类型不能有长度
		rowemp emp%rowtype ; --声明一个变量,用来接收游标中的每一条记录
	begin 
		open 游标名(参数值); --cursor循环遍历
		  loop
		  	fetch 游标名 into 变量;
		  	exit when 游标名%notfound;
		  	--业务逻辑
		  end loop;
		close  游标名;
	end;
eg:  declare
		cursor c_emp is select * from emp; --声明游标并赋值
		rowemp emp%rowtype;    --声明变量,用来获取游标中的每一条记录
	 begin
	 	open c_emp;
	 		loop
	 			fetch c_emp into rowemp;
	 			dbms_output.put_line(rowemp.ename);
	 			exit when c_emp%notfound;
	 		end loop;
	 	close c_emp;
	 end;
总结:
1.while循环和exit循环需要初始化条件语句和步进表达式,for循环不需要
2.exit需要";",while和for不需要

存储过程procedure和存储函数function

存储过程procedure:
存储过程的概念：存储过程就是提前已经编译好的一段pl/sql语言,放置在数据库端可以直接被调用。这一段pl/sql一般都是固定步骤的业务。调用方式:plsql
定义格式:
	create or replace procedure 存储过程名(参数名 参数类型)
    is
    --声明变量
    begin
    --业务逻辑
    end;
eg: --根据emono给员工涨工资	
	create or replace procedure p_sal(eno emp.empno%type)
     is
     begin
         update emp set sal = sal + 1000 where empno = eno;
         commit;
         end;
         
存储函数function:
跟存储过程一样,只是多了返回值,调用方式:plsql,select 
定义格式:
	 create or replace function 存储函数名(参数名 参数类型) return 返回值数据类型
	 is
	 --声明变量
	 begin
	 --业务逻辑
	 return 返回值;
	 end;
eg: --根据empno计算员工年薪
	create or replace function f_yearsal(eno emp.empno%type) return number
	is
		money number(20);
	begin
	     select sal*12+nvl(comm,0) into money from emp where empno = eno;
	return money;
	end;
	
注意:
	1.存储过程和存储函数的参数都不能带长度,存储函数的返回值类型不能带长度
	2.存储函数在调用的时候，返回值必须接收。
	3.凡是涉及到into查询语句赋值给参数或者:=赋值给参数的操作,都必须使用out来修饰。
	4.存储中传入参数其实本质就是参数名 in 数据类型,不过in可以省略
eg: --根据emono计算员工年薪加强版
	声明plsql
	create or replace procedure p_yearsal(eno in emp.empno%type,money out number)
	is
	begin
		select sal*12+nvl(comm,0) into money from emp where empno = eno;
	end;
	测试plsql
	declare
		money number(20);
	begin
		p_yearsal(7788,money);
		dbms_output.put_line(money);
	end;
	
存储函数和存储过程的区别
1.存储过程没有返回值,存储函数有返回值
2.存储过程使用procedure,存储函数使用function
3.存储过程只能用plsql调用,存储函数可以使用plsql/select调用
4.存储过程和存储函数都可以在参数列表中,使用out来完成对某个变量的赋值,也相当于又返回值

触发器trigger

触发器概念:检测表的增删改,只要满足规则,自动触发,无需调用。
行级触发器:影响多少行,触发多少次,有for each row
语句级触发器:执行一条语句触发一次,没有for each row
定义规则:
	create or replace trigger 触发器名称
	before/after  --表示语句执行前触发或者语句执行后触发,二选一
	insert or update or delete  --表示触发器触发的语句,可以共存
	on 表名
	--for each row 有就是行级触发器,没有就是语句级触发器
	declare
	begin
	end;
	
模拟主键自增触发器:
	--创建序列
	create sequence s_id start with 1 increment by 1;
	--创建触发器
	create or replace trigger t_id
	before  --在插入数据前,将主键封装到:new对象中
	insert
	on person
	for each row --只有行级触发器才有伪类,:new/:old
	declare
	begin
		select s_id.nextval into :new.id from dual;
	end;

伪类:
	:new代表操作后的对象
	:old代表操作前的对象

java操作oracle

//注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取连接
DriverManager("jdbc:oracle:thin:@192.168.80.88:1521:orcl","baidu","baidu");
{call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用

调用存储过程:
//获取执行sql的CallableStatement对象
CallableStatement call = connection.prepareCall("{call p_sal(?,?)}");
//对占位符进行赋值
call.setInt(1,7788);
call.registerOutParameter(2,OracleTypes.NUMBER); --注册out参数
//执行sql
call.execute();
//处理结果
int money = call.getInt(2);
//释放资源
...
调用存储方法:
CallableStatement call = connection.prepareCall("{ ?=call p_sal(?)}");