记录自己的Oracle之旅(三)

今天的内容就比较杂乱了，依然是废话不多说直接开始
关于视图的操作

--视图
----创建视图
create view v_flowers as select s_emp.eid as 员工编号,s_emp.ename as 员工姓名,decode(s_emp.sex,'m','男','f','女') as 性别,s_emp.hire as 入职日期,s_emp.sar as 目前薪资,s_dept.dname as 所在部门 from s_emp left join s_dept on s_dept.did = s_emp.did;
----查询当前用户的所有表或视图
select * from tab;
----删除视图
drop view v_flowers;
----查看视图
select * from v_flowers;
/*只有基于单表的视图才可以插入数据，比如说视图是多表联查产生的话，就不能向这个视图插入数据*/
/*插入数据方式与正常的数据表方式相同*/
----插入数据时sar必须大于5000
create or replace view v_eview as select * from s_emp where sar>5000 with check option; 

索引、同义词和序列

--索引
----创建索引
CREATE UNIQUE INDEX idx_emp_ename on s_EMP(vid);
--同义词
----创建同义词(创建私有同义词只需要去掉public，删除同理)
create public synonym sy_e for s_emp;
----查看用户flowers创建的同义词(区分大小写)
select * from dba_synonyms where TABLE_OWNER = 'FLOWERS';
----让scott也可以访问同义词sy_e
grant select on sy_e to scott
--删除同义词sy_e(必须有删除同义词的权限)
drop public synonym sc;
--序列
----创建序列
create sequence seq
increment by 1--每次自增是1
start with 3--从3开始
minvalue 1--最小值
maxvalue 10--最大值
cycle--循环(nocycle是不循环)
nocache;--不存入缓存
----序列的当前值(执行前必须先至少执行一次nextval)
select seq.currval from dual;
----序列的下一个值
select seq.nextval from dual;

表空间的操作

--表空间
----查看当前数据库由哪些表空间构成
select tablespace_name from user_tablespaces;
----查看有哪些数据文件
select * from v$datafile;
----查看所有表空间
select * from v$tablespace;
----新建名为flowers_t的表空间
create tablespace flowers_t
datafile 'A:\Installs\Oracle\tablespce\flowers_t.dbf'
size 50M
autoextend on
next 10M;
----修改数据文件的最大容量
alter database datafile 'A:\Installs\Oracle\tablespce\flowers_t.dbf' resize 200M;
----删除数据文件
alter tablespace flowers_t drop datafile 'A:\Installs\Oracle\tablespce\flowers_t.dbf';
----设置表空间flowers_t为可读写(设置为只读只需要把write换成only)
alter tablespace flowers_t read write;
----表空间脱机(联机是把offline normal换成online)
alter tablespace flowers_t offline normal;

PL/SQL的循环结构

--PLSQL(此处输出的值需要在“输出”选项卡才能看到)
----if...语句
----捕获错误并输出提示信息
set serveroutput on;
declare
    x varchar2(20);
    a int := 1;
    ex1 exception;
begin
    x := 'Hello world';
    dbms_output.put_line('x的值为'||x);
    if a = 1 then raise ex1;
    end if;
exception
    when ex1 then dbms_output.put_line('捕获了错误1');
end;
----把查询到的数据装入指定变量中
declare
    a number;
    b number;
    c number;
begin
    select max(sar),min(sar),avg(sar) into a,b,c from s_emp;
    dbms_output.put_line('最高工资：'||a);
    dbms_output.put_line('最低工资：'||b);
    dbms_output.put_line('平均工资：'||c);
end;
----根据数值分别输出不同的信息
declare
    score number := 80;
begin
    if score < 60 then dbms_output.put_line('你学的不行啊');
    elsif score < 70 then dbms_output.put_line('再加把劲');
    elsif score < 80 then dbms_output.put_line('你有点落后了');
    elsif score < 90 then dbms_output.put_line('成绩不错！');
    else dbms_output.put_line('你很棒！');
    end if;
end;
----根据数值分别输出不同的信息(case有返回值)
declare
    score varchar2(1) := 'A';
    res varchar2(10);
begin
    res := case score
         when 'A' then '优秀'
         when 'B' then '良好'
         when 'C' then '中等'
         when 'D' then '及格'
         when 'E' then '差劲'
         else '没有这样的等级'
         end;
    dbms_output.put_line(res);
end;

--PLSQL循环
----loop循环
declare
    a int := 10;
    b int := 1;
begin
    loop
      b := b * a;
      a := a - 1;
      exit when a=1;
    end loop;
    dbms_output.put_line('10!='||b);
end;
----for循环
declare
    a int;
    b int;
    c int;
begin
    for a in 1..9 loop
    for b in 1..a  loop
        c := a * b;
        dbms_output.put_line(b||'×'||a||'＝'||c||' ');
    end loop;
    dbms_output.put_line('');
    end loop;
end;
----while循环
declare
    a int := 0;
    b int := 0;
begin
    while a<= 100 loop
       b := a + b;
       a := a + 1;
    end loop;
    dbms_output.put_line('1+2+3+4+...+99+100 = '||b);
end;
----goto结构(标签名不能与变量名相同)
declare
    a int := 0;
    b int := 0;
begin
    <<c>>
    b := a + b;
    a := a + 1;
    if a <= 100 then goto c;
    end if;
    dbms_output.put_line('1+2+3+4+...+99+100 = '||b);
end;

PL/SQL记录和表

--PLSQL纪录和表
----字段类型
declare  
      eno  s_EMP.eid%type;
begin
      select  eid into eno from s_EMP where eid=520;
      dbms_output.put_line(eno);
end ;
----记录类型
declare
  type emper is record(
    ename s_emp.ename%type,
    sex s_emp.sex%type,
    dname s_dept.dname%type
  );
  em emper;
begin
  select e.ename,e.sex,d.dname into em
  from s_emp e,s_dept d
  where e.did=d.did and e.eid=7;
  dbms_output.put_line(em.ename||'属于:'||em.dname||',性别为:'||em.sex);
end;
declare
	em s_emp%rowtype;
begin
	select * into em from s_emp where eid=7;
	dbms_output.put_line(em.ename||'的性别为:'||em.sex);
end;
----表
declare
	type tab is table of s_emp%rowtype index by binary_integer;
	em tab;
begin
	select * into em(1) from s_emp where eid=7;
	dbms_output.put_line(em(1).ename||'的性别为:'||em(1).sex);
end;
declare
  i integer;
  type tab is table of s_emp%rowtype index by binary_integer;
  em tab;
begin
  em(1).eid:=12;
  em(1).ename:='orcl';
  em(1).sex:='m';
  em(2).eid:=13;
  em(2).ename:='base';
  em(2).sex:='f';
  for i in 1..2 loop
    dbms_output.put_line(em(i).ename||'的性别为:'||em(i).sex);
  end loop;
end;

游标

--游标
declare
  cursor c is select * from s_emp;--声明游标
  emp s_emp%rowtype;
begin
  open c;--打开游标
  loop
     fetch c into emp;--使用游标
     exit when c%notfound;
  end loop;
  dbms_output.put_line('游标中记录的行数：'||c%rowcount);
  close c;--关闭游标
end;
declare
  cursor c is select * from s_emp where sar<5000;
  e s_emp%rowtype;
begin
  open c;
  loop
    fetch c into e;
    exit when c%notfound;
    if e.sar < 5000 then
      update e set sar=sar+500 where eid=e.eid;
      dbms_output.put_line(ename||'加薪后的工资为：'||(e.sar+500));
    end if;
   end loop;
   close c;
end;

结束