oracle练习二

--------exists/not exists,存在或者不存在--------
--语法:select ... from ... where exists (sql查询语句)  
--exists返回的是boolean类型，即exists (sql查询语句)是true或者false,如果查询有结果，返回true，结果全部展示，
--如果查询是null,返回fasle,结果不展示
select * from emp where exists (select * from dept); --结果全部展示
select * from emp where exists (select * from dept where sal = '9999'); --结果不展示

  --查询部门中有员工的部门信息 exists为什么比in效率高？
select * from dept d where exists (select * from emp e where e.deptno=d.deptno);

-------------------第二天内容-------------------
---------------------视图-----------------------
select * from session_privs;--查询当前用户的权限
select * from session_roles;--查询当前用户的角色
grant dba to scott;--给当前用户授予dba的角色（dba有创建视图的权限，现在scott用户没有授予dba角色）

  --视图：一张虚拟表，视图本身不存放数据，数据来源于原始表，视图和表是映射关系。
  --语法：create [or repalce] view 视图名称 as 查询语句;
  
  --应用：1.封装一条复杂查询语句
create or replace view v_emp20 as select * from emp where deptno = 20; 
  --使用视图跟使用表一样，以下是查询视图
select * from v_emp20;

  --2.隐藏一些敏感数据
create or replace view emp20 as select empno,ename,job from emp;  
select * from emp20;

update v_emp20 set ename='JACK' WHERE empno=7369;--修改视图的数据会把映射关系中表的数据一并改掉
select * from v_emp20;
select * from emp;

  --3.设置只读
create or replace view v_emp30 as select * from emp where deptno = 30 with read only;--v_emp30视图只能读取，不能修改

------------------索引------------------
--索引：为了提高检索效率。 
--语法:create index 索引名称 on 表名（列名1,列名2）

create table student(
  id number(10) primary key,
  name varchar2(100) not null
)
  --添加百万级别的数据
select sys_guid() from dual;--sys_guid()是Oracle提供的函数,产生并返回唯一的标识符

declare
 vname varchar2(100);
begin
  for i in 1..1000000 loop
    select sys_guid() into vname from dual;
    insert into student values(i,vname);
  end loop;
  commit;    
end;
  --验证索引效率
select * from student;
select * from student where name='267CE34AA7E64A309014A969B2EC4AF0';
create index idx_student_name on student(name);
select * from student where name='59765230FC694D3EB1C0EB1057F8B8A7';
  --索引的使用说明：1.在数据量比较大的表上使用索引
                  --2.在表中经常用于查询的列上使用索引
                  --3.给多列创建索引叫做复合索引，创建索引的列最好不要超过四个
                  
----------------pl/sql在Oracle数据库中进行编程-------------
  --pl/sql:过程化语言/结构化查询语言
  --在pl/sql中进行编程的语法：
[declare]
  --定义部分
begin
  --函数体
[exception]
  --处理异常
end;

  --练习：
declare 
  i number(8) :=1;  --定义变量并赋值，:=为赋值符号
  n constant  number(8) :=2;  --加上constant，为定义常量
  pname1 varchar2(30);  --定义变量
  pname2 emp.ename%type;  --定义引用型变量，引用某张表的某一列类型，%type为指定类型
  pemp emp%rowtype;  --定义记录型变量，保存某一张表的一条记录，%rowtype为指定行类型
begin 
 dbms_output.put_line('i的值：'||i);  --||为连接符号
 dbms_output.put_line('n的值：'||n);  
 select ename into pname1 from emp where empno=7369;
 dbms_output.put_line('pname1的值为'||pname1);
 select ename into pname2 from emp where empno=7654;
 dbms_output.put_line('pname2的值为'||pname2);
 select * into pemp from emp where empno=7782;
 dbms_output.put_line('pemp中的ename为：'||pemp.ename||',pemp中的job为:'||pemp.job);
end;  

-----------------------if判断--------------------
--语法：
if 条件 then
  逻辑
  elsif 条件 then 
    逻辑
  else
    逻辑
end if;
   --练习1：
declare 
 i number(8) :=&p;  --&代表后边的值需要输入，&后面可以任意写（数字和字符都可以）。
begin
  if  i=1 then
   dbms_output.put_line('i的值为'||i);
   else
   dbms_output.put_line('i为其他值');     
  end if;
end;  
  ----练习2：
declare
 score number(10) :=&score;
begin
  if  score<30 then
  dbms_output.put_line('初级');
  elsif score>=30 and score<50 then
  dbms_output.put_line('中级');
  else
  dbms_output.put_line('高级');
  end if;
end;  

----------------------loop循环--------------------
1.while循环 语法：
while 循环条件 loop
  循环体
end loop;
2.loop循环 语法：
loop
  循环体
  exit when 退出条件；
end loop；
3. for循环 语法：
for i in 1..100 loop
  循环体
end loop;

练习:三种循环方式输出1到10；
1.
declare 
  i number(8) :=1;
begin
 while i<=10 loop
 dbms_output.put_line(i);
 i:=i+1;
 end loop;
end;
2.
declare
 i number(3) :=1;
begin 
  loop
    dbms_output.put_line('i值为'||i);
    i:=i+1;
    exit when i>10;
  end loop;
end;
3.
declare 
 i number(3) :=1;
begin
  for i in 1..10 loop
    dbms_output.put_line('i的值为'||i);
  end loop;
end;
     
-----------------游标cursor----------------
游标：存放结果集的，返回多条记录存放到游标中。
游标定义在declare部分，语法：
cursor 游标名称 is 查询语句;
  --练习：将20号部门员工存放到游标中，打印每个员工的信息。
declare 
  pemp emp%rowtype;
  cursor pc is select * from emp where deptno=20;
begin 
  open pc;
    loop 
      fetch pc into pemp;
      exit when pc%notfound;
      dbms_output.put_line('姓名:'||pemp.ename||',工作:'||pemp.job);
    end loop;
  close pc;
end;  
  --练习：给员工涨工资，销售涨500，经理涨300，总裁涨100
declare
 addsal number(3):=0;
 cursor pc is select * from emp;
 pemp emp%rowtype;
begin
  open pc;
   loop
     fetch pc into pemp;
     exit when pc%notfound;
     if pemp.job='SALESMAN' then 
       addsal:=500;
     elsif pemp.job='MANAGER' then
       addsal:=300;
     elsif pemp.job='PRESIDENT' then
       addsal:=100;
     else
       addsal:=0;
     end if;
     update emp set sal=sal+addsal where empno=pemp.empno;
   end loop;
   commit;  close pc;
end;

------------------ 存储过程-----------------
--存储过程：事先编译好的一组sql语句集，为了完成某一个特定功能的业务逻辑。
--存储过程是事先编译好的，存放在数据库服务器端的，将来应用程序通过存储过程的名称来进行调用进而完成某个业务功能。  
--存储过程的好处：效率高（提前编译好的），弊端：维护成本高

--存储过程语法：
create [or replace] procedure 存储过程名称（参数1 in|out 类型,参数2 in|out 类型,...）
as|is
  定义变量  
begin
  程序体
end;

--使用存储过程实现helloworld,
--存储过程执行的时候就是编译的过程。
--存储过程执行的时候，如果有语法错误，不会提示。
create or replace procedure pro_helloworld
is 
 a varchar2(20):='helloworld';
begin
  dbms_output.put_line(a);
end;

--调用存储过程
--第一种方式
call pro_helloworld();
--第二种方式(这种方式用的多，因为可以定义变量存放存储过程返回的结果)
declare
begin
  pro_helloworld();
end;

--使用存储过程给某个员工涨工资，输出涨前和涨后的工资。
create or replace procedure pro_add_sal(pno in number,money in number)
is
 oldsal emp.sal%type;
 newsal emp.sal%type;
begin
  select sal into oldsal from emp where empno=pno;
  dbms_output.put_line(oldsal);
  update emp set sal=sal+money where empno=pno;
  commit;
  select sal into newsal from emp where empno=pno;
  dbms_output.put_line(newsal);
end;                    
call pro_add_sal(7369,200);

--使用存储过程查询某个员工的年薪_方式1
create or replace procedure pro_total_sal1(pno in number)
is
 totalsal number(20);
begin
  select sal*12+nvl(comm,0) into totalsal from emp where empno=pno;
  dbms_output.put_line(totalsal);
end;
--调用存储过程
call pro_total_sal1(7499);

--使用存储过程查询某个员工的年薪_方式2
create or replace procedure pro_total_sal(pno in number,totalsal out number)
is

begin
  select sal*12+nvl(comm,0) into totalsal from emp where empno=pno;
  dbms_output.put_line(totalsal);
end;
--调用存储过程
declare
 totalsal varchar(20);
begin
  pro_total_sal(7499,totalsal);
end;

--删除存储过程
drop procedure 存储过程名称;

-----------------存储函数---------------
--存储函数：事先编译好的一组sql语句集，为了完成某一个特定功能的业务逻辑。
--存储函数是事先编译好的，存放在数据库服务器端的，将来应用程序通过存储函数的名称来进行调用进而完成某个业务功能。
--语法：
create or replace function 存储函数名称（参数1 in|out 类型,参数2 in|out 类型,...） return 类型
is

begin
  return 变量；--该变量类型一定要跟存储函数指定的返回值类型一致。
end;

--查询某个员工的年薪
create or replace function fun_total_sal(pno in number) return number
is
 totalsal number(20);
begin
  select sal*12+nvl(comm,0) into totalsal from emp where empno=pno;
  return totalsal;
end;
--调用存储函数
declare 
 totalsal number(20);
begin
 totalsal:=fun_total_sal(7499);
 dbms_output.put_line(totalsal);
end;

--存储过程和存储函数的区别：
--相同点：从完成特定功能来说，存储过程能实现，存储函数也能实现。
--不同点：语法不一样，存储函数必须有返回值，存储函数可以用在select语句中

--用存储函数实现：根据部门编号返回部门名称
create or replace function fun_dname(dno in number) return varchar2
is
 dname dept.dname%type;
begin
  select dname into dname from dept where deptno=dno;
  return dname;
end;  
--调用存储函数
declare
 dname varchar2(20);
begin
  dname:=fun_dname(30);
  dbms_output.put_line(dname);
end;
--把存储函数用在select语句中
select ename,fun_dname(deptno) dname from emp;

--------------触发器trigger--------------
触发器老师的个人理解：
给某张表安装了监视器，监控表中所有的数据，一旦外部对表中记录做操作（增删改）,就会触发触发器中的逻辑。
语法：
create or replace trigger 触发器名称
before|after
insert|delete|update
on 表名
declare

begin
  
end;

--触发器练习：新增用户后，在控制台输出'新员工入职'
create or replace trigger tri_after_emp
after
insert
on emp
declare
begin
  dbms_output.put_line('新员工入职了呀');
end;

insert into emp(empno,ename) values(03,'WANGWU');
commit;

--触发器练习2：周六日不能办理入职
create or replace trigger tri_before_emp
before
insert
on emp
declare 
 weekend varchar2(20);
begin
  select trim(to_char(sysdate,'day')) into weekend from dual;--trim()函数：删除文本中除单词间的单个空格之外的所有空格
  if weekend in ('thursday','sunday') then
    raise_application_error(-20001,'周末不能办理入职');
  end if;
end;
insert into emp(empno,ename) values(05,'QIANQI');
commit;

--触发器练习3：不能给员工降薪
--tri_before_sal_emp此触发器为行级触发器，因为加了for each row
--行级触发器：对每行记录做出操作，都会触发，一般用:old，:new获取旧记录和新纪录会用行级触发器。 
create or replace trigger tri_before_sal_emp
before 
update 
on emp
for each row
declare

begin
  if :old.sal>:new.sal then  --:old代表操作（增删改）之前的行，:new代表操作（增删改）之后的行，代表的行都是伪记录。
    raise_application_error(-20002,'不能给员工降薪');
  end if;
end; 

update emp set sal=sal+1 where empno=7369; 
commit;