Oracle操作2

一、视图

视图的概念：视图就是提供一个查询的窗口，所有的数据来自原表。

0、将emp表创建到当前用户中，方便后面的操作

---查询语句创建表
create table emp as select * from scott.emp;
select * from emp;

1、创建视图【必须有dba权限】

create view v_emp as select ename, job from emp;

2、查询视图

select * from v_emp;

3、修改视图【不推荐修改视图，大部分公司创建视图后不会去修改它】

update v_emp set job='CLERK' where ename='ALLEN';
commit;

4、创建只读视图

create view v_emp1 as select ename, job from emp with read only;

5、视图的作用

---第一：视图可以屏蔽掉一些敏感字段。
---第二：保证总部和分部数据及时统一。

二、索引

索引的概念：索引就是在表的列上构建二叉树

1、创建单列索引

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

2、创建复合索引

create index idx_enamejob on emp(ename, job);
---复合索引中第一列为优先检索列
---如果要触发复合索引，必须包含有优先检索列的原始值。
select * from emp where ename='SCOTT' and job='xx';---触发复合索引
select * from emp where ename='SCOTT' or job='xx';---不触发索引
select * from emp where ename='SCOTT';---触发的是单列索引。

三、pl/sql编程语言

0、plsql的概念

---pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
---pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
---pl/sql编程语言主要用来编写存储过程和存储函数等。

1、声明方法

---赋值操作可以使用:=也可以使用into查询语句赋值
---declare与begin之间是定义变量，begin和end之间写一些编程逻辑结构
declare
   i number(2) := 10; --类似 int i = 10
   s varchar2(10) := '小明';
   ena emp.ename%type; --引用型变量  定义ena类型是emp表ename字段相同类型
   emprow emp%rowtype;--记录型变量 emprow为emp表中一行的数据类型
begin
  dbms_output.put_line(i); --类似 System.out.println(i)
  dbms_output.put_line(s);
  select ename into ena from emp where empno = 7788;--将查询语句赋给ena
  dbms_output.put_line(ena);
  select * into emprow from emp where empno = 7788;
  dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);-- ||类似+
end;

2、pl/sql中的if判断

---输入小于18的数字，输出未成年
---输入大于18小于40的数字，输出中年人
---输入大于40的数字，输出老年人
declare
  i number(3) := ⅈ--&加变量名 代表输入一个值
begin
  if i<18 then
     dbms_output.put_line('未成年');
  elsif i<40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

3、pl/sql中的loop循环【用三种方式输出数字1到10】

---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;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
---for循环
declare
 
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

4、游标【可以存放多个对象，多行记录】

---输出emp表中所有员工的姓名
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;
----给指定部门员工涨工资
declare
   cursor c2(eno emp.deptno%type) 
                 is select empno from emp where deptno = eno;
                 en emp.empno%type;
begin
   open c2(10);
      loop
        fetch c2 into en;
        exit when c2%notfound;
        update emp set sal=sal+100 where empno=en;
        commit; 
      end loop;
   close c2;
end;
----查询10号部门员工信息
select * from emp where deptno = 10;

四、存储过程和存储函数

概念：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端。

       可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

1、存储过程

----给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
 
begin
  update emp set sal = sal+100 where empno = eno;
  commit;
end;
 
 
select * from emp where empno = 7788;
----测试p1
declare 
 
begin
  p1(7788);
end;

2、存储函数

----通过存储函数实现计算指定员工的年薪
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;
 
----测试f_yearsal
----存储函数在调用的时候，返回值需要接受
declare
  s number(10);
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

3、out类型参数如何使用【存储过程】

---使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
  s number(10);
  c emp.comm%type;
begin
  select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
  yearsal := s+c;
end;
 
---测试p_yearsal
declare
  yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

4、into和out类型参数的区别是什么

---凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰

5、存储过程和存储函数的区别

---语法区别：关键字不一样，
------------存储函数比存储过程多了两个return。
---本质区别：存储函数有返回值，而存储过程没有返回值。
------------如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数
------------即使是存储过程使用了out类型的参数，其本质也不是真的有了返回值
------------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值
 
----我们可以使用存储函数有返回值的特性来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
 
----使用传统方式来实现需求
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
----使用存储函数来实现提供一个部门编号，输出一个部门名称
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  return dna;
end;
---使用fdna存储函数来实现案例需求:查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno)
from emp e;

五、触发器

概念：触发器，就是制定一个规则，在我们做增删改操作时，只要满足该规则，自动触发，无需调用。

1、语句级触发器：

【不包含for each row的触发器。在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。】

----插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare
 
begin
  dbms_output.put_line('一个新员工入职');
end;
---触发t1
insert into person values('王五', 19, '男');
commit;
select * from person;

2、行级触发器：

【包含for each row的就是行级触发器。触发语句作用的每一条记录都被触发。】

【加for each row是为了使用：old或者：new对象或者一行记录。】

---不能给员工降薪
---raise_application_error(-20001~-20999之间, '错误提示地信息');
create or replace trigger t2
before
update
on emp
for each row
declare
 
begin
  if :old.sal>:new.sal then
     raise_application_error(-20001, '不能给员工降薪');
  end if;
end;
 
----触发t2
update emp set sal=sal-1 where empno = 7788;
commit;

3、触发器实现主键自增。【行级触发器】

---分析：在用户插入操作之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create or replace trigger auid
before
insert
on person
for each row
declare
 
begin
  select s_person.nextval into :new.pid from dual;
end;
--查询person表数据
select * from person;
---使用auid实现主键自增
insert into person (pname) values('a');
commit;

六、java调用oracle

	/**
    * java调用oracle普通查询
    * @throws Exception
    */
   @Test
   public void javaCallOracle() throws Exception {
       //加载数据库驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //得到Connection连接
       Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.154.128:1521:orcl",
               "itheima", "itheima");
       //得到预编译的Statement对象
       PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
       //给参数赋值
       pstm.setObject(1, 7788);
       //执行数据库查询操作
       ResultSet rs = pstm.executeQuery();
       //输出结果
       while(rs.next()) {
           System.out.println(rs.getString("ename"));
       }
       //释放资源
       rs.close();
       pstm.close();
       connection.close();
   }

   /**
    * java调用存储过程
    * @throws Exception
    */
   @Test
   public void javaCallProcedure() throws Exception {
       //加载数据库驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //得到Connection连接
       Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1187.129:1521:orcl",
               "crazer", "crazer");
       //得到预编译的Statement对象
       CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");

       //给参数赋值
       pstm.setObject(1, 7788);
       pstm.registerOutParameter(2, OracleTypes.NUMBER);
       //执行数据库查询操作
       pstm.execute();
       //输出结果[第二个参数]
       System.out.println(pstm.getObject(2));
       //释放资源
       pstm.close();
       connection.close();
   }

   /**
    * java调用存储函数
    * @throws Exception
    */
   @Test
   public void javaCallFunction() throws Exception {
       //加载数据库驱动
       Class.forName("oracle.jdbc.driver.OracleDriver");
       //得到Connection连接
       Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.187.129:1521:orcl",
               "crazer", "crazer");
       //得到预编译的Statement对象
       CallableStatement pstm = connection.prepareCall("{?=call f_f1(?)}");

       //给参数赋值
       pstm.setObject(2, 20);
       pstm.registerOutParameter(1, OracleTypes.VARCHAR);
       //执行数据库查询操作
       pstm.execute();
       //输出结果[第二个参数]
       System.out.println(pstm.getObject(1));
       //释放资源
       pstm.close();
       connection.close();
   }
   
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc14</artifactId>
    <version>10.2.0.4.0</version>
    <scope>compile</scope>
</dependency>