PL/SQL

--基础概念：数据库，实例同用户和表空间为一对多，用户和表空间的关系为多对一
--中文乱码解决，
select userenv('language') from dual;--查询服务器编码
select * from V$NLS_PARAMETERS  --查询plsql developer编码

--创建表空间
create tablespace myspace
datafile 'D:\Oracle\mydata\myfiles.dbf'
size 100m
autoextend on
next 10m;


--删除表空间
drop tablespace myspcae;


--创建用户
create user cai
identified by cai --cai的密码
default tablespace myspace;--指定用户出生的表空间

--给用户授权
--数据库中常用角色
connect --连接角色
resource --开发者角色
dba  --超级管理员角色
grant dba to cai;


--常用数据类型
varchar2 --可变字符串，可以自动截取
number(4) --四位数
NUMBER(4,2) --11.11
data --日期
clob --Character Large Object文本数据类型，可存4g
blob -- Binary Large Object二进制数据类型，4g


--创建表
create table person(
       pid number(30),
       pname varchar2(30)
);


--修改表结构
--添加表结构
alter table person add (geder number(1));
--修改列类型
alter table person modify gender char(1); --char为定长
--修改列名称
alter table person rename column gender to sex;
--删除列名称
alter table person drop column sex;


--数据增删改
--添加一条记录
insert into person(pid,pname)values(1,'小明');
commit;--增删改需要手动提交
--修改一条记录
update person set pname = '小马' where pid = 1;
commit;

--删除
--删除表中全部记录
delete from person；
--删除表结构
drop table person;
--先删除表，再创建表
--在数据量大或表中带有索引的情况下，该操作会先删索引，效率高
truncate table person;

--序列不属于表，但可以和表做逻辑绑定
--dual:虚表，只为补全语法
create sequence s_person;
select s_person.nextval from dual;
select s_person.currval from dual;
--修改一条记录
insert into person(pid,pname)values(s_person.currval,'小明');


--scott用户，密码tiger
--解锁scott用户
alter user scott account unlock;
--解锁密码【也可以重置密码】
alter user scott identified by tiger;
--切换到scott


--查询数据

--单行函数；作用于一行，返回一个值
--字符函数
select upper('yes') from dual;
select lower('Yes') from dual;
--数组函数
select round(56.16, -2) from dual; --四舍五入
select trunc(56.16, -2) from dual; --直接截取
--日期函数
select sysdate-e.hiredate from emp e;
select months_between(sysdate,e.hiredate) from emp e;
--转换函数
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;--日期转字符串
select to_date('2020-11-16 01:22:09','yyyy-mm-dd hh:mi:ss')from dual;--日期转字符串
--通用函数
select e.sal*12+nvl(e.comm,0)from emp e; --如果不为null，则为第二个参数值


--条件表达式（通用）
select e.ename,--等值判断
       case e.ename
            when 'SMITH' then 'aa'
            when 'ALLEN' then 'bb'
            else 'cc'
        end
from emp e;

--范围判断
select e.sal,--等值判断
       case
            when e.sal>3000 then 'aa'
            when e.sal>1500 then 'bb'
            else 'cc'
        end
from emp e; 

--orcale专用条件表达式
select e.ename,--等值判断
       decode(e.ename,
            'SMITH' , 'aa',
            'ALLEN' , 'bb',
            'cc') 中文名
from emp e;


--多行函数【聚合函数】，作用于多行，返回 一个值、
select sum(1) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;


--分组查询:group by后面的原始列或者聚合函数才能出现在select后面
--查询每个部门工资高于800的平均工资再查询平均工资高于2000的部门
select e.deptno,avg(e.sal)a --,e.ename
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;--条件不能使用别名having where条件优先级大于select
--where过滤分组前数据，having过滤分组后的数据


--多表查询
--笛卡尔积
select * 
from emp e,dept d
--等值连接
select * 
from emp e,dept d
where e.deptno=d.deptno; 
--内连接
select *
from emp e inner join dept d
on e.deptno=d.deptno;
--左右外连接
select *
from emp e right dept d
on e.deptno=d.deptno;
--oracle专用
select * 
from emp e,dept d
where e.deptno=d.deptno(+);--左连接


--自连接
select e1.ename, e2.ename--查询出员工姓名，员工领导姓名
from emp e1,emp e2
where e1.mgr= e2.empno


--子查询
--返回一个值,集合
select * from emp where sal in
(select *sal from emp where ename = 'SCOTT')
--返回一张表
--三表连查询
select t.deptno,t.msal,e.ename,d.dname
from (select deptno ,min(sal) msal
      from emp
      group by deptno)t,emp e,dept d
where t.deptno=e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;


--分页
--rownum：select之后会在结果上加行号order优先级低于select
--涉及排序可以嵌套查询
select rownum,t.* from(
select rownum,e.* from emp e order by e.sal desc) t;

--rownum行号不能写大于一个正数
select * from(
       select rownum rn,e.* from(
              select * from emp order by sal desc
       ) e where rownum<11
)where rn >5;


--视图：提供一个查询的窗口，所有数据来自原表
--创建表
create table emp as select * from scott.emp;
--创建只读视图【必须有dba权限】
create view v_emp as select ename,job from emp with read only;
select * from v_emp;
--视图的作用：1.屏蔽敏感字段。2.保证总部和分部数据的统一


--索引：在表的列上构建二叉树，提高查询效率，但会影响增删改的效率
--单列索引,必须是原始值才能触发，模糊查询，单行函数
create index idx_ename on emp(ename);
--复核索引，第一列为优先检索列，优先检索列中必须包含原始值
create index idx_enamejob on emp(ename,job);

select * from emp where enmae='SCOTT' and job='xxx';--触发复合索引
select * from emp where enmae='SCOTT' or job='xxx';--不触发索引
select * from emp where enmae='SCOTT' --触发单列索引

--qlsql编程
--变量声明
declare 
     i number(2) :=10;
     s varchar2(10) :='小妹';
     ena emp.ename%type;--引用型变量
     enprow emp%rowtype; --记录型变量
begin 
     dbms_output.put_line(i);
     select ename into ena from emp where empno = 7788;--对ena初始化
     select * into emprow from emp where empno = 7788;
     dbms_output.put_line('你好'||'小妹');
end;

--if分支
declare
     i number(3) :=&ii;
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;

--loop循环
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>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;


--游标：存放多行记录
declare 
     cursor c1 is select * from emp;
     emprow emp%rowtype;
begin 
     open c1;
         loop
           fetch c1 into emprow;
           exit when c1%notfound;
           dbms_output.put_line(emprow.ename);
         end loop;
     close c1;
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;



--存储过程：提前编译好的sql语句集
create or replace procedure p1(eno emp.empno%type)
is
begin
  update emp set sal = sal + 100 where empno = eno;
  commit;
end; 


--存储函数：
create or replace function f_yersal(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;

--out类型参数,涉及到赋值操作的参数必须用out，没有涉及的必须用in
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;

declare
  yearsal number(10);
begin
  p_yearsal(7788,yearsal);
  dbms_output.put_line(yearsal);
end;
--存储过程和存储函数的区别：函数可以有一个返回值；而过程没有返回值。
--但过程和函数都可以通过 out 指定一个或多个输出参数。在过程和函数中实现返回多个值。


--触发器：制定一个规则，在增删改时如果符合规则则触发
--语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行
create or replace trigger t1
after 
insert 
on emp
declare

begin
  dbms_output.put_line('入职');
end; 

--行级触发器：触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态
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; 


--触发器实现主键自增
create or replace trigger auid
before 
insert 
on person
for each row
declare

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

insert into person (pname) values('a');
commit;
insert into person  values(1,'b');--有也不用这个id
commit;