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;
--------------------

（编辑：开发网_开封站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!