---视图---视图的概念:视图就是提供一个查询的窗口,所有数据来自于原表。---查询语句创建表createtable emp asselect*from scott.emp;select*from emp;---创建视图【必须有dba权限】createview v_emp asselect ename, job from emp;---查询视图select*from v_emp;---修改视图[不推荐]update v_emp set job='CLERK'where ename='ALLEN';commit;---创建只读视图createview v_emp1 asselect ename, job from emp withread only;---视图的作用?---第一:视图可以屏蔽掉一些敏感字段。---第二:保证总部和分部数据及时统一。---索引--索引的概念:索引就是在表的列上构建一个二叉树----达到大幅度提高查询效率的目的,但是索引会影响增删改的效率。---单列索引---创建单列索引createindex idx_ename on emp(ename);---单列索引触发规则,条件必须是索引列中的原始值。---单行函数,模糊查询,都会影响索引的触发。select*from emp where ename='SCOTT'---复合索引---创建复合索引createindex 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) :=ⅈbeginif i<18then
dbms_output.put_line('未成年');
elsif i<40then
dbms_output.put_line('中年人');else
dbms_output.put_line('老年人');endif;end;---pl/sql中的loop循环---用三种方式输出1到10是个数字---while循环declare
i number(2) :=1;beginwhile i<11loop
dbms_output.put_line(i);
i := i+1;endloop;end;---exit循环declare
i number(2) :=1;beginloopexitwhen i>10;
dbms_output.put_line(i);
i := i+1;endloop;end;---for循环declarebeginfor i in1..10loop
dbms_output.put_line(i);endloop;end;---游标:可以存放多个对象,多行记录。---输出emp表中所有员工的姓名declarecursor c1 isselect*from emp;
emprow emp%rowtype;beginopen c1;loopfetch c1 into emprow;exitwhen c1%notfound;
dbms_output.put_line(emprow.ename);endloop;close c1;end;-----给指定部门员工涨工资declarecursor c2(eno emp.deptno%type)isselect empno from emp where deptno = eno;
en emp.empno%type;beginopen c2(10);loopfetch c2 into en;exitwhen c2%notfound;update emp set sal=sal+100where empno=en;commit;endloop;close c2;end;----查询10号部门员工信息select*from emp where deptno =10;---存储过程--存储过程:存储过程就是提前已经编译好的一段pl/sql语言,放置在数据库端--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。----给指定员工涨100块钱createorreplaceprocedure p1(eno emp.empno%type)isbeginupdate emp set sal=sal+100where empno = eno;commit;end;select*from emp where empno =7788;----测试p1declarebegin
p1(7788);end;----通过存储函数实现计算指定员工的年薪----存储过程和存储函数的参数都不能带长度----存储函数的返回值类型不能带长度createorreplacefunction f_yearsal(eno emp.empno%type)return number
is
s number(10);beginselect 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类型参数如何使用---使用存储过程来算年薪createorreplaceprocedure p_yearsal(eno emp.empno%type, yearsal out number)is
s number(10);
c emp.comm%type;beginselect sal*12, nvl(comm,0)into s, c from emp where empno = eno;
yearsal := s+c;end;---测试p_yearsaldeclare
yearsal number(10);begin
p_yearsal(7788, yearsal);
dbms_output.put_line(yearsal);end;----in和out类型参数的区别是什么?---凡是涉及到into查询语句赋值或者:=赋值操作的参数,都必须使用out来修饰。---存储过程和存储函数的区别---语法区别:关键字不一样,------------存储函数比存储过程多了两个return。---本质区别:存储函数有返回值,而存储过程没有返回值。----------如果存储过程想实现有返回值的业务,我们就必须使用out类型的参数。----------即便是存储过程使用了out类型的参数,起本质也不是真的有了返回值,----------而是在存储过程内部给out类型参数赋值,在执行完毕后,我们直接拿到输出类型参数的值。----我们可以使用存储函数有返回值的特性,来自定义函数。----而存储过程不能用来自定义函数。----案例需求:查询出员工姓名,员工所在部门名称。----案例准备工作:把scott用户下的dept表复制到当前用户下。createtable dept asselect*from scott.dept;----使用传统方式来实现案例需求select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;----使用存储函数来实现提供一个部门编号,输出一个部门名称。createorreplacefunction fdna(dno dept.deptno%type)return dept.dname%typeis
dna dept.dname%type;beginselect 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对象或者一行记录。---语句级触发器----插入一条记录,输出一个新员工入职createorreplacetrigger t1
afterinserton person
declarebegin
dbms_output.put_line('一个新员工入职');end;---触发t1insertinto person values(1,'小红');commit;select*from person;---行级别触发器---不能给员工降薪---raise_application_error(-20001~-20999之间, '错误提示信息');createorreplacetrigger t2
before
updateon emp
for each rowdeclarebeginif :old.sal>:new.sal then
raise_application_error(-20001,'不能给员工降薪');endif;end;----触发t2select*from emp where empno =7788;update emp set sal=sal-1where empno =7788;commit;----触发器实现主键自增。【行级触发器】---分析:在用户做插入操作的之前,拿到即将插入的数据,------给该数据中的主键列赋值。createorreplacetrigger auid
before
inserton person
for each rowdeclarebeginselect s_person.nextval into :new.pid from dual;end;--查询person表数据select*from person;---使用auid实现主键自增insertinto person (pname)values('a');commit;insertinto person values(1,'b');commit;