--- 视图
--- 视图的概念: 视图就是提供一个查询的窗口, 来操作数据库中的数据, 不存储数据, 数据在表中.
--- 一个由查询语句定义的虚拟表.
--- 查询语句创建表
- 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;
-- 删除视图
drop view v_emp;
--- 创建只读视图
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';--- 不触发索引
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;
- declare
- i number(2) := 28;
- begin
- if i <18 then
- dbms_output.put_line('child');
- elsif i < 25 then
- dbms_output.put_line('teenager');
- else
- dbms_output.put_line('old');
- end if;
- end;
---pl/sql 中的 loop 循环
--- 用三种方式循环打印 1 到 10
---while 循环
- declare
- i number(2) := 0;
- begin
- while i < 10 loop
- dbms_output.put_line(i + 1);
- 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 c is select * from emp;
- emprow emp%rowtype;
- begin
- open c;
- loop
- fetch c into emprow;
- exit when c%notfound;
- dbms_output.put_line(emprow.empno || ' ' || emprow.ename);
- end loop;
- close c;
- end;
----- 给指定部门员工涨工资
- declare
- cursor c(dno emp.deptno%type)
- is select * from emp where deptno=dno;
- r emp%rowtype;
- begin
- open c(10);
- loop
- fetch c into r;
- exit when c%notfound;
- update emp set sal = sal + 100 where deptno=r.deptno and ename=r.ename;
- commit;
- end loop;
- close c;
- end;
---- 查询 10 号部门员工信息
select * from emp where deptno = 10;
--- 存储过程
-- 存储过程: 存储过程就是提前已经编译好的一段 pl/sql 语言, 放置在数据库端
-------- 可以直接被调用. 这一段 pl/sql 一般都是固定步骤的业务.
---- 给指定员工涨 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;
---- 通过存储函数实现计算指定员工的年薪
---- 存储过程和存储函数的参数都不能带长度
---- 存储函数的返回值类型不能带长度
- 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 类型参数如何使用
--- 使用存储过程来算年薪
- 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 来修饰.
--- 存储过程和存储函数的区别
--- 语法区别: 关键字不一样,
------------ 存储函数比存储过程多了两个 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 对象或者一行记录.
--- 语句级触发器
---- 插入一条记录, 输出一个新员工入职
-- 应用场景: 1. 下订单用触发器修改库存 2. 帖子评论增删时, 修改帖子的评论数.
- 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;
- end;
-- 查询 person 表数据
select * from person;
--- 使用 auid 实现主键自增
- insert into person (pname) values ('a');
- commit;
- insert into person values (1, 'b');
- commit;
- ----oracle10g ojdbc14.jar
- ----oracle11g ojdbc6.jar
视图 索引 sql 编程 游标 存储过程 存储函数 触发器
来源: http://www.bubuko.com/infodetail-3092666.html