Oracle视图、索引

视图

建视图

select * from teacher;

创建不看密码的视图

create or replace view t_view
    as select id,name from teacher;--不看密码的视图
select * from t_view;

创建名字以Y开头的，密码为321456的老师的视图

create or replace view yang_teacher
    as
    select * from teacher where name like 'Y%' and p ='321456';#pwd

select * from yang_teacher;

操作scott需要system用户

create or replace view dept_emp_view
as
select e.EMPNO,e.ENAME,e.SAL,d.DEPTNO,d.DNAME,d.LOC
from scott.EMP e left join scott.DEPT d on e.DEPTNO = d.DEPTNO;

select * from dept_emp_view;

删除视图

drop view dept_emp_view;

删除视图中的字段

delete from T_VIEW where id = 21;

with check option 带检查约束视图

根据 T_ADDRESS（地址表），创建视图 VIEW_ADDRESS。
内容为 区域ID 为2 的记录。

create or replace view yang_teacher
    as
    select * from teacher where name like 'Y%' and p ='321456' #pwd
	with check option;

with read only 只读视图P

create or replace view yang_teacher
    as
    select * from teacher where name like 'Y%' and p ='321456' #pwd
	with read only;

带错误视图

-- 错误视图，创建不成功
create or replace view `view_TEMP`
as
-- `T_TEMP`表不存在
select * from 'T_TEMP'; 

索引

创建索引

create index t_index on teacher(name);

创建唯一索引

create unique index only_name on DEMO(name);

插入zs

insert into demo(id,name) values (TEACHER_SEQ.nextval,'zs');

再次执行会违反唯一原则

创建函数

create or replace function myFunc(f_id number) 
return varchar2
as
    result_name varchar2(32);
    begin
        select name from DEMO where id = f_id;
        return result_name;
    end ;

select myFunc(25) from dual;

创建判断段奇偶数的函数

create or replace function checkNumer(num number) return varchar2
as
    resultStr varchar2(32);
    temp number;
        begin
            temp := mod(num,2);
            if temp = 1 then
                resultStr := '奇数';
            else
                resultStr := '偶数';
            end if;
            return resultStr;
        end;

select checkNumer(10) from dual;

将两个数相加再判断奇偶

create or replace function checkNumer1(num1 number,num2 number) return varchar2
as
    resultStr varchar2(32);
    sumtemp number;
    temp number;
        begin
            sumtemp := num1 + num2;
            temp := mod(sumtemp,2);
            if temp = 1 then
                resultStr := '奇数';
            else
                resultStr := '偶数';
            end if;
            return resultStr;
        end;

select checkNumer1(10,11) from dual;

练习：
（操作scott需要system用户）

--找出scott.emp中工资大于等于3000的人的数量
--如果只有一个人，则输出'有一位员工薪资大于3000'
--如果大于一个人，则输出'有？员工薪资大于3000'
--否则输出‘没有员工薪资大于3000’
create or replace function myfunct2 return varchar2
as
    resultStr varchar2(32);
    countNum number;
    begin
        select count(sal) into countNum from scott.EMP e where sal >= 3000;
        if  countNum = 1 then
            resultStr := '有一位员工薪资大于3000' ;
        else if countNum > 1 then
            resultStr := '有'||countNum|| '位员工薪资大于3000';
        else
            resultStr := '没有员工薪资大于3000';
        end if;
        end if;
        return resultStr;
    end;


select myfunct2() from dual;

创建函数—输入部门名显示此部门的人数

create or replace function myfunc3(name varchar2) return number
as
    resultNum number;
    begin
        select count(ENAME) into resultNum from scott.EMP e right join scott.DEPT d on e.DEPTNO = d.DEPTNO
 where d.DNAME = name;
        return resultNum;
    end;


select myfunc3('ACCOUNTING') from dual;