游标_表分区

--使用存储过程，从部门表中取出所有部门名称并返回

-------------------------通用性不高 ---只是在数据库输出，用户看不到
 create or replace procedure pro_dept
  as
  v_count number;
  v_dname dept.dname%type;
  begin
    select count(dname) into v_count from dept;
    for i in 1 .. v_count loop
    select dname into v_dname from (select rownum as rn,dname from dept) where rn = i;
    dbms_output.put_line(v_dname);
    end loop;
    commit;
  end;
  
  ---------测试
  begin
    dbms_output.put_line('部门表中所有的部门名称：');
    pro_dept;
    end;
    
    -----------------------------在外部执行循环
    
    create or replace procedure pro_dept1( v_i in number,v_dname out dept.dname%type)
  as
  begin
    
    select dname into v_dname from (select rownum as rn,dname from dept) where rn =v_i;
   
    commit;
  end;
  
  ---测试
    declare
     v_count number;
     v_dname dept.dname%type; 
    begin
    dbms_output.put_line('部门表中所有的部门名称：');
    select count(dname) into v_count from dept;
    for i in 1 .. v_count loop
    pro_dept1(i,v_dname);
    dbms_output.put_line(v_dname);
    end loop;
    end;
    

  -------------------------------------游标-------------------------

  
  /*
    游标：用户定义的内存工作区，用来存储查询结果集，使用游标来提取每一条记录进行操作
          使用游标来提取每一条记录进行操作
          
    分类：
          1、静态游标：sql语句提前编译
             a、显示游标：声明、打开、访问、关闭四个步骤
             b、隐式游标
          2、动态游标
             游标可以直接在PL/SQL中使用
  */
  
  ------使用游标：从部门表中取出所有部门名称
  
  -------非游标
     declare
     v_count number;
     v_dname dept.dname%type; 
    begin
    dbms_output.put_line('部门表中所有的部门名称：');
    select count(dname) into v_count from dept;
    for i in 1 .. v_count loop
     select dname into v_dname from (select rownum as rn,dname from dept) where rn =i;
    dbms_output.put_line(v_dname);
    end loop;
    end;

    
  -----------游标

  --1、声明游标（cursor），指向一个查询的结果集

  declare
    cursor cs_dept is select * from dept;
    --行变量用于存储表中一条元组，通过行变量还可以访问元组中的每个属性，
    --语法： 变量名.属性名

    v_dp dept%rowtype;  --变量用于和表中的行保持一致

  --2、在执行体中打开游标（open 游标名）

  begin

    open cs_dept;

    --3、提取游标中的记录（fetch 游标名 into）

    fetch cs_dept into v_dp;
      while cs_dept%found loop
      dbms_output.put_line('部门编号'||v_dp.deptno||'    '||'部门名称'||v_dp.dname);
       fetch cs_dept into v_dp;

      end loop;

      --4、关闭游标名（ close 游标名）

      close cs_dept;
    end;
  

  ---------------------------------------静态游标

  /*
    静态游标：
      显示游标：声明、打开、提取、关闭
      隐式游标：oracle预定义的sql隐式游标和cursor
      for  loop 游标
        
      sql游标常用来访问DML操作影响的记录数
      
      cursor for loop 常用来遍历结果集  
  */
  begin
    update dept set loc = '二楼';
    dbms_output.put_line('影响行数： '||sql%rowcount);
    commit;
  end;
  
  --cursor for loop(隐式游标) 查询部门表中所有部门编号和名称
  declare
  v_dept dept%rowtype;  --行变量
  begin
     for v_dept in (select * from dept) loop
       dbms_output.put_line(v_dept.dname);
     end loop;
  
    end;
  
  
  
  

  -----------------------需求：要求使用存储过程来返回一个结果集

  create or replace procedure pro_cursor(v_dept out dept%rowtype, v_cs out (select * from dept))
  is
     cursor v_cs is select * from dept;
     begin
       open v_cs;
       fetch v_cs into v_dept;
       while v_cs%found loop
         fetch v_cs into v_dept;
             --dbms_output.put_line(v_dept.dname);
       end loop;
     end;
  
  
  declare
  v_dp dept%rowtype;
  begin
          pro_cursor(v_dp);
          dbms_output.put_line(v_dp.dname);
    end;
  
  
  
  

  ----动态游标

  DECLARE
   --定义一个游标数据类型
   TYPE dept_cursor_type IS REF CURSOR;
   --声明一个游标变量
   c1 dept_CURSOR_TYPE;
   dp dept%rowtype;
begin
 open c1 for SELECT * FROM dept;
 fetch c1 into dp;
 while c1%found loop
   dbms_output.put_line('部门编号：'||dp.deptno||'部门名称：'||dp.dname );
   fetch c1 into dp;
  end loop;
  close c1;
end;


--需求：要求使用存储过程返回一个结果集
--在包的规范中声明一个动态游标类型的变量


create or replace package pk_cursors   --创建一个包规范，为了游标类型的声明
as type yscursor is ref cursor;
end pk_cursors;


create or replace procedure pro_yscr(v_dept out pk_cursors.yscursor)
is
begin
  --在程序运行期间打开游标
  open v_dept for select * from dept;
end;

 --其他程序中来调用过程得到结果集
  
  declare
  --定义游标变量接收过程返回的游标
  v_dept pk_cursors.yscursor;
  v_dp dept%rowtype;  --定义行变量
  begin
    pro_yscr(v_dept);
    fetch v_dept into v_dp;
    while v_dept%found loop
   dbms_output.put_line('部门编号：'||v_dp.deptno||'部门名称：'||v_dp.dname );
   fetch v_dept into v_dp;
  end loop;
close v_dept;---关闭游标
  end;
  
  
  /*

  表分区：

将表中的数据按照某种条件存储到不同的物理空间，以改善查询性能，被分区的表在逻辑上任然是一张完整的表

  常用的表分区技术：
    1、范围分区：by range(字段)
    2、散列分区：oracle数据库把数据平均分配到物理文件中
    3、list分区：根据确定的值进行分区
*/


--创建3个表空间
create tablespace tbl1 datafile 'E:\app\wodediannao\oradata\orcl\tb1.dbf'
       size 5m autoextend on next 1m maxsize 50m;
       
create tablespace tbl2 datafile 'E:\app\wodediannao\oradata\orcl\tb2.dbf'
       size 5m autoextend on next 1m maxsize 50m;
       
create tablespace tb3 datafile 'E:\app\wodediannao\oradata\orcl\tb3.dbf'
       size 5m autoextend on next 1m maxsize 50m;
       
--使用范围分区（日期）创建订单表（属性：订单编号，订单人，联系方式，下单日期）
create table order_sale (
       tid varchar2(10),
       tname varchar2(10),
       telphone number,
       tdate date
)


partition by range(tdate)
(
     partition o1 values less than(to_date('2016-12-31', 'yyyy-mm-dd')) tablespace tbl1,
      partition o2 values less than(to_date('2017-12-31', 'yyyy-mm-dd')) tablespace tbl2,
       partition o3 values less than(to_date('2018-12-31', 'yyyy-mm-dd')) tablespace tb3
)

--测试
insert into order_sale values('515611','啊啊',151668116,sysdate);
insert into order_sale values('111111','haha',164868116,to_date('2017-02-13','yyyy-mm-dd'));


select * from order_sale partition(o2);


--使用散列分区创建 人员表（人员编号，人员姓名），分区条件使用人员编号将不同的数据‘分散’到不同的分区中
create table people(
       pid number,
       pname varchar2(50)
)
partition by hash(pid)
(
          partition h1 tablespace tbl1,
           partition h2 tablespace tbl2
)


insert into people values(1,'sss');
insert into people values(2,'sss');
insert into people values(3,'sss');
insert into people values(4,'sss');
insert into people values(5,'sss');
insert into people values(6,'sss');


select * from people partition (h1);
select * from people partition (h2);


--=-----使用list分区 创建 学生表（学生编号、学生姓名、地址）
create table student(
       sid number,
       sname varchar2(50),
       address varchar2(50)
)
partition by list(address)
(
          partition s1 values('西安') tablespace tbl1,
           partition s2 values('南京') tablespace tbl2，
                     partition s3 values(default) tablespace tb3
)


insert into student values(1,'啊啊啊','西安');
insert into student values(2,'hahaha','南京');
insert into student values(3,'凄凄切切','北京');



--查询
select * from student partition (s1);
select * from student partition (s2);
select * from student partition (s3)；