oracle数据库操作进阶,oracle数据库进阶语句

--视图

--视图就是一个提供查询的窗口，所有数据来自于原表

--查询语句创建表

create table emp as select * from scot

select * from emp;t.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;

select * from v_emp1;

--视图的作用

--1，视图可以屏蔽敏感字段

--2，保证总部和分部数据及时统一

--索引

--索引的概念：索引在表列上面构建一个二叉树

--达到最大幅度提高查询效率的目的，但是索引会影响增删改的效率

--单页索引

--创建单例索引

--单列索引触发规则：条件必须是索引列中的原始值

--单行函数，模糊查询会影响触发索引

create index idx_ename on emp(ename);

--复合索引

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变编程语言主要用来编写存储过程

--申明方法

declare

i number(2) := 10;

s varchar2(10) := ‘小明‘;

ena emp.ename%type; --引用型变量

emprow emp%rowtype; -- 记录型变量

begin

dbms_output.put_line(s);

dbms_output.put_line(i);

select ename into ena from emp where empno = 7788;

dbms_output.put_line(ena);

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中的循环

--用三种方式输出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>11;

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;

--游标：类似于java中的集合

--输出emp表中所有员工的姓名

declare

cursor cl is select * from emp;

emprow emp%rowtype;

begin

open cl;

loop

fetch cl into emprow;

exit when cl%notfound;

dbms_output.put_line(emprow.ename);

end loop;

close cl;

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一般是固定步骤

--给指定员工涨100块钱

create or replace procedure p1(eno emp.empno%type)

is

begin

update emp set sal = sal+100 where empno = eno;

commit;

end;

---测试p1

declare

begin

p1(7788);

end;

--使用存储过程 查询

select * from emp where empno=7788;

--通过存储函数计算指定员工的年薪

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;

--测试

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俩修饰

-------------44

--存储过程和存储函数的区别

--语法区别：关键字不一样，

--------------存储函数比存储过程多了两个return

--------------本质区别：存储函数有返回值，存储过程没有返回值

--------------如果存储过程想实现有返回值的业务，必须使用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;

--测试存储函数

select e.ename,fdna(e.deptno)

from emp e;

--------------------

--触发器：指定一个规则，在我们做crud操作的时候自动触发，无需调用

--语句触发器 ，不包含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;

--行级触发器

--不能给员工降薪

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;

--测试触发器

update emp set sal = sal -1 where empno=7788;

--查询

select * from emp where empno=7788;

--触发器实现主键自增

--分析：在用户插入之前，拿到即将插入的数据

--给该数据的主键赋值

create or replace trigger suid

before

insert on person

for each row

declare

begin

select s_person.nextval into :new.pid from dual;

end;

select * from person;

--使用触发器实现主键自增

insert into person(pname) values (‘a‘);

commit;

--oracle 10g ojdbc14.jar---oracle 11g ojdbc