oracle05--视图、序列、存储过程

视图

什么是视图？    
视图(VIEW)也被称作虚表，也就是虚拟的表，是一组数据的逻辑表示。
视图对应一个select语句，结果集被赋予一个名字，就是视图的名字。
视图本身不包含任何数据，它只是包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

视图创建后可以向操作表一样的操作数据库，主要是查询。

根据视图所对应的子查询种类可以分为几种类型：
select语句是基于单表建立的，并且不包含任何函数运算、表达式或者分组函数，叫做简单视图，此时视图是基表的子集。
select语句是基于单表建立的，但是包含单行函数、表达式、分组函数或者group by 子句，叫做复杂视图。
select语句是基于多个表，叫做连接视图

视图的作用
如果需要经常执行某种复杂查询，可以基于这个复杂查询建立视图，以后查询此视图即可，可以简化复杂查询
视图本质上就是一条select语句，所以当访问视图是，只能访问到对应的select语句中涉及到的列，对基表中的其他列起到安全和保密的作用，限制数据的访问

在创建视图的用户必须要获得创建视图的角色
在sysdba角色下为指定用户授权
conn sys/11111 as sysdba;
show user;
grant create view to scott;
conn scott/11111;
show user;

创建视图
create or replace view v_emp
as
select empno,ename,sal from emp;

desc v_emp;

drop view v_emp;

select * from v_emp;

create or replace view v_emp2
as
select empno no,ename name,job job,sal sal from emp;

SQL> select no,name from v_emp2 where no=7369;

        NO NAME
---------- --------------------
      7369 SMITH

SQL> select no,name from v_emp2 where empno=7369;
select no,name from v_emp2 where empno=7369
                                 *
第 1 行出现错误:
ORA-00904: "EMPNO": 标识符无效

------------------------------------------------------------------------------------------------------------------
序列(sequence)
用在哪里？
在mysql数据库中，可以设置id字段以自动增长的方式，实现数据的插入
create table mysql_tbl(
    id int primary key auto_increment,
    name varchar(100)
);

在oracle中如何实现mysql表中的id字段自动增长。
使用序列    序列是oracle中的一种对象
create sequence seq_user     --创建序列的关键字和序列名称
increment by 1                     --自动增长的步长    默认1
start with 1                         --开始的大小值        默认1
maxvalue | minvalue num     --最大值和最小值   默认nomaxvalue 10的26次方
nomaxvalue(默认)                --默认的    不限制
cycle | nocycle                    --是否轮回            默认nocycle
cache num | nocache            --缓存区大小            默认20
;

使用序列
select seq_user.nextval from dual;

select seq_user.currval from dual;

建表
create table t_user (id number , username varchar2(100) , passowrd varchar2(48) , regtime date , constraint pk_user primary key(id));

insert into t_user values (seq_user.nextval , 'jack' , 123123 , sysdate);

drop sequence seq_user;

需要一张表t_product
编号 主键 自动增长
产品名称 非空
产品销量 非空 默认0
产品库存 非空 默认0
产品初始化销量

创建表
插入5条数据
建立视图 （视图中不显示产品初始化销量）
查询视图
从视图插入一条数据

create sequence seq_product;

create table t_product (
    p_id number ,
    p_name varchar2(100)  not null,
    p_sales_num number default 0 not null ,
    p_has_sum number default 0 not null ,
    p_init_sales number ,
    constraint pk_product_id primary key(p_id)
);

insert into t_product values (seq_product.nextval , '怡宝' , 800 , 1000 , 100);

create or replace view v_product
as
select p_id , p_name , p_sales_num , p_has_sum from t_product;

select * from v_product;

insert into v_product values (seq_product.nextval , '怡宝' , 800 , 1000);

-------------------------------------------------------------------------------------------------------------
存储过程
存储过程适合做更新操作，特别是大数据量的更新

创建存储过程
create or replace procedure proc1
as | is    相当于 declare 声明的意思
    abc varchar2(100) ;    --定义该存储过程的变量    作用域就是本存储过程中
begin
    update t_user set username = 'lucy' where id = 3 ;
end ;

创建存储过程
create or replace procedure proc1
as
begin
    update t_user set username = 'hello' where id = 2 ;
    commit ;
end ;

调用存储过程
exec proc1 ;
call proc1 ;

--------------------------------------------------------------------------
带输入参数的存储过程
创建存储过程
create or replace procedure proc2 (
    parama1 varchar2 , parama2 varchar2     default '888888'    --存储过程的参数    类型不需要指定宽度（范围）
)
as    
begin
    insert into t_user values (seq_user.nextval , parama1,  parama2 , sysdate) ;
    commit ;
end ;

调用存储过程
exec proc2('tom');
call proc2('tom');

------------------------------------------------------------------
带输出参数的存储过程
create or replace procedure proc3(
    parama1 in varchar2 , parama2 out varchar2    --in 代表输入参数    out代表输出参数    parama0 in out number ,
)
as
begin
    select passowrd into parama2 from t_user where username = parama1 ;
    dbms_output.put_line(parama2) ;
end ;

set serverout on;
var pp varchar2(100) ;
exec proc3('tom', :pp) ;

----------------------------------------------------
查询结果返回多个字段的值的存储过程
create or replace procedure proc4(
    parama1 in varchar2 , parama2 out varchar2, parama3 out varchar2    --in 代表输入参数    out代表输出参数    parama0 in out number ,
)
as
    parama varchar2(100) ;
begin    --注意：这里只能处理返回的是一条记录的结果集
            --如果结果集含有多条记录，oracle没有提供直接处理的方式，必须间接的借助游标，但是游标本身的效率很差，实际开发中基本不适用
    select id, passowrd into parama2 , parama3 from t_user where username = parama1 ;
    parama := 'who am I ?' ;
    dbms_output.put_line('查询的结果数据是：id='||parama2||', password='||parama3) ;
    dbms_output.put_line(parama) ;
end ;

set serverout on;
var pp1 varchar2(100) ;
var pp2 varchar2(100) ;
exec proc4('tom', :pp1 , :pp2) ;

------------------------------------------------------------------------
定义变量
parama1 varchar2(100) ;    --变量的 类型可以是oracle系统所有合法的数据类型
parama2 number ;
给变量赋值
parama1 := 'who am i ?' ;
parama2 := 123 ;

判断
if t_value = 1 then
    begin
        do ...
    end ;
end if ;
-----------------
create or replace procedure proc_if(pp in number)
as
    total number ;
begin
    total := pp ;
    if total > 4 then
        begin
            insert into t_user values (seq_user.nextval , '苏乞儿',  '666666' , sysdate) ;
            commit ;
        end ;
    end if ;
end ;

call proc_if(3);

------------------------------------------------

while循环
while t_value = 1 loop
    begin
        do...
    end ;
end loop ;
-------------------------
create or replace procedure proc_while(pp in number)
as
    total number ;
begin
    total := pp ;
    while total > 4 loop
        begin
            insert into t_user values (seq_user.nextval , '梅长苏') ;
            commit ;
            total := total - 1;
        end ;
    end loop ;
end ;

call proc_while(5);
------------------------------------------------------------------------
for y in 1..100 loop    
    i := x*y ;
    exit when i = 300;
end loop ;

 create or replace procedure proc_for
 as
 begin
    for i in 1..10 loop
        dbms_output.put_line(i) ;
    end loop;
 end ;