oracle常用笔记（二）

oracle常用笔记（二）

1. 视图

--创建视图
create view v1 as select ename,job from emp
--创建或覆盖原视图的只读视图
create or replace view v1 as select ename from emo with read only;
--修改视图与更新sql语言一样

2. 索引

• 索引：在表的列上构建一个二叉树，以达到大幅提高查询效率的目的，但是会影响增删改的效率。

1. 创建单列索引

create index idx_ename on emp(ename);
--单列索引触发规则：条件必须是索引列中的原始值。
--单行函数，模糊查询，都会影响索引的触发

2. 复合索引

create index idx_enamejob on emp(ename,job);
--复合索引中第一列时优先检索列
--要触发复合索引，必须包含优先检索列中的原始值

索引的使用原则：

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

3. pl/sql 基本语法

1. 变量声明

declare --说明部分 （变量说明，游标申明，例外说明 〕
i number(2) : =10;
s varchar2(10) :='小明';
ena emp.ename&type; -- 引用型变量
emprow emp%rowtype; -- 记录型变量

begin  -- 语句序列 （DML 语句〕… 
dbms_output.put_line(i);

select * into ena from emp where empno = 7788;
dbms_output.put_line(ena);
select * into emprow from emp where empno = 7788;
dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);

exception 例外处理语句 
end;

2. if分支

declare
	i number(3) := &11; --定义 &和任意一个变量 与控制台交互，
begin
	if i<18 then
	dbms_output.put_line('未成年');
	elsif i<40 then
	dbms_output.put_line('中年人');
	--。。。
	end if;
end;

3. LOOP 循环

--while循环
declare 
	i number(2) :=1;
begin
	while i<11 loop
	dbms_output.put_line(i);
	i :=i+1;
	end loop;
end;

--exit循环
declare 
	i number(2) :=1;
begin
	loop
	exit when i>10 loop
	dbms_output.put_line(i);
	i :=i+1;
	end loop;
end;

--for循环
declare 
	i number(2) :=1;
begin
	for i in 1..10 loop
	dbms_output.put_line(i);
	end loop;
end;

4. 游标Cursor

• 游标：可以存放多条记录，相当于集合的概念

declare
	cursor c1 is select  * from emp;  -- 创建游标
	emprow emp%rowtype;
begin
	open c1;
	 loop
	  fetch c1 into emprow;
	  exit when c1%notfound;
	  dbms_output.put_line(emprow.ename);
	  end loop;
	 close c1; 
end;

4. 存储过程

create or replace procedure p1 (eno emp.empno%type)  --参数有in/out类型，不写默认in
is --或者as
begin 
	update emp set sal = sal+100 where empno = eno;
	commit;
end;

--调用
declare 

begin 
  p1(7788);
 end;

• in和out类型参数的类别：
  • 凡是涉及到into，查询语句或者：= 赋值操作的参数都必须使用out
  • 其余都用in

5. 存储函数

create or replace function f_yearsal(eno emp.empno%type) return number 
--存储过程和存储函数的参数都不能带长度
is 
  s number(10);
begin 
	select sal*12+nvl(comm,0) into s from emp where empno = eno;
	return s;  --存储函数的返回值类型不能带长度
end;

6. 触发器

触发器：制定一个规则，在执行增删改操作时，只要满足该条件，就会自动触发，无需调用

• 语句级触发器：不包含 for each row 的触发器

--语句级触发器
create or replace trigger t1
after
insert
on person
declare
begin
	dbms_output.put_line('1个新员工入职了');
end;

• 行级触发器：包含for each row 的触发器 ，触发语句作用的每一条记录都被触发。在行级触

  发器中使用 :old 和 :new 为了记录变量, 识别值的状态

--行级触发器
create or replace trigger t2
before 
update on emp
for each row
declare 
begin
	if :old.sal>:new.sal then
	raise_application_error(-20001,'不能员工降薪');
	--raise_application_error(-20001~20999之前不能重复,'不能员工降薪');
	end if;
end;

7.java调用存储过程/存储函数

  @Test
    public void javaCallProcedure() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Coonection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "itheima", "itheima");
        //得到预编译statement对象
    CallableStatement ps = connection.prepareCall("{call p_yearsal(?,?)}");
        //给对象赋值
        ps.setObject(1, 7788);//1=index下标  7788 第一个？参数
        ps.registerOutParameter(2, OracleTypes.NUMBER);
        //执行查询操作
        ps.execute();
        System.out.println(ps.getObject(2));

        //释放资源
        ps.close();
        connection.close();
    }


--调用存储函数
     CallableStatement ps = connection.prepareCall("{?= call p_yearsal(?)}");
        //给对象赋值
        ps.setObject(2, 7788);//1=index下标  7788 第一个？参数
        ps.registerOutParameter(1, OracleTypes.NUMBER);
        //执行查询操作
        ps.execute();
        System.out.println(ps.getObject(1));