文章目录
视图
给操作可视化，动态
可以跨用户查询
查询其他用户的表，将信息存入当前用户下
---视图
---视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表。
---查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
视图根本没有数据，操作会作用到原表上，所以视图一般只读，防止混乱
---查询语句创建表
create table emp as select * from scott.emp;
select * from emp;
---创建视图【必须有dba权限】
create view v_emp as select ename, job from emp;
---查询视图
select * from v_emp;
---修改视图[不推荐]
update v_emp set job='CLERK' where ename='ALLEN';
commit;
---创建只读视图
create view v_emp1 as select ename, job from emp with read only;
视图的作用
—第一：视图可以屏蔽掉一些敏感字段。
—第二：保证总部和分部数据及时统一。
视图为多表查询提供了上层封装，可以屏蔽某些字段的查询，可以统一操作各表，防止各表查询等操作不及时，信息不统一
索引
—索引
–索引的概念：索引就是在表的列上构建一个二叉树，相当于一本书的目录
----达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。每次更新都会重构二叉树
单列索引
构建在一列上就是单列，构建在多列上就是多列
创建单列索引
create index idx_ename on emp(ename);
---单列索引触发规则，条件必须是索引列中的原始值。
---单行函数，模糊查询，都会影响索引的触发。
select * from emp where ename='SCOTT'--这个是必定触发的，但是其他的就不太现实
复合索引
创建复合索引
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';---不触发索引，or相当于两个查询，一个触发，一个不触发，结果就是不触发
select * from emp where ename='SCOTT';---触发单列索引。
pl/sql编程语言
pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
—pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
—pl/sql编程语言主要用来编写存储过程和存储函数等。
—声明方法
---赋值操作可以使用:=也可以使用into查询语句赋值
declare
i number(2) := 10;--用：来赋值
s varchar2(10) := '小明';
ena emp.ename%type;---引用型变量，相当于找到这一列，获得列属性，声明一个变量
emprow emp%rowtype;---记录型变量，查询的是一行数据
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select ename 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);--连接字符串为：||
end;
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;
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;
/**/
游标
可以存放多个对象，多行记录。
—输出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;
/**/
存储过程
如果创建出错，会有红色
业务需求
java需要连接池实现，但是这个本来就是数据库内部的可以不借助连接池直接实现，优势。但是维护麻烦，也不能太复杂，一般简单固定的写
存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
即使数据库关闭依旧可用
--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
----给指定员工涨100块钱
create or replace procedure p1(eno emp in.empno%type)--此处应该有in或者out，不写默认
is--或者as都行
begin
update emp set sal=sal+100 where empno = eno;
commit;
end;
select * from emp where empno = 7788;
----测试p1
declare
begin
p1(7788);
end;
存储函数的使用
和存储过程关键字不一样，且有返回值
----通过存储函数实现计算指定员工的年薪
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度，因此这里就是number
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;
/**/
out类型参数如何使用
使用存储过程来算年薪,number不必规定长度，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;
----in和out类型参数的区别是什么？
---凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。
如上图的yearsal
/**/
存储过程和存储函数的区别
---存储过程和存储函数的区别
---语法区别：关键字不一样，
------------存储函数比存储过程多了两个return。
---本质区别：存储函数有返回值，而存储过程没有返回值。
----------如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
----------即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
----------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。
----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。
create table dept as select * from scott.dept;
----使用传统方式来实现案例需求
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;
/**/
触发器
，就是制定一个规则，在我们做增删改操作的时候触发，查询不会
----只要满足该规则，自动触发，无需调用。
----语句级触发器：不包含有for each row的触发器。
----行级触发器：包含有for each row的就是行级触发器。
-----------加for each row是为了使用:old或者:new对象或者一行记录。
---语句级触发器
----插入一条记录，输出一个新员工入职，就是插入数据以后会返回插入的数据
create or replace trigger t1
after--插入之前触发还是之后
insert--操作的类型
on person--要操作的表
declare
begin
dbms_output.put_line('一个新员工入职');
end;
---触发t1
insert into person values (1, '小红');
commit;
select * from person;
---行级别触发器
---不能给员工降薪
---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
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;
----触发器实现主键自增。【行级触发器】
---分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create or replace trigger auid
before
insert
on person
for each row
declare
begin
select s_person.nextval into :new.pid from dual;--从虚表中得到下一次的id,使得id自增
end;
--查询person表数据
select * from person;
---使用auid实现主键自增
insert into person (pname) values ('a');
commit;
insert into person values (1, 'b');--即使写了1，最后id还是自增的，不会覆盖之前的，id白写了
commit;
使用java调用oracle
导入对应驱动包
连接数据库
调用需要的功能对象接口
语句
接受结果
----oracle10g ojdbc14.jar
----oracle11g ojdbc6.jar
java\
package com.learnCCC.oracle;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.*;
public class OracleDemo {
//进行简单查询测试
@Test
public void javaCallOracle() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl",
"learnCCC", "learnCCC");//类似界面登录的信息
//得到预编译的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调用存储过程
* {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用，有返回值
* {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用，无发回执
* @throws Exception
*/
@Test
public void javaCallProcedure() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl",
"learnCCC", "learnCCC");
//得到预编译的对象,执行存储过程的接口
CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");//规定格式，call 名字，参数
//给参数赋值
pstm.setObject(1, 7788);
pstm.registerOutParameter(2, OracleTypes.NUMBER);
//执行数据库查询操作
pstm.execute();
//输出结果[第二个参数]，存储过程，第二个参数已经内部赋值了，直接输出第二个参数即可，之前输入的是个空参
System.out.println(pstm.getObject(2));
//释放资源
pstm.close();
connection.close();
}
/**
* java调用存储函数
* {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用
* @throws Exception
*/
@Test
public void javaCallFunction() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl",
"learnCCC", "learnCCC");
//得到预编译的对象，对应接口
CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
//给参数赋值
pstm.setObject(2, 7788);
pstm.registerOutParameter(1, OracleTypes.NUMBER);//传进去的也是空参，等待执行完存储函数内部处理赋值
//执行数据库查询操作
pstm.execute();
//输出结果[第一个参数]
System.out.println(pstm.getObject(1));
//释放资源
pstm.close();
connection.close();
}
}