Oralce 开发包 和 游标

--________________________________开发包______________________________________________________
 
 --包 ：组合逻辑相关的PL/SQL 类型（table 类型，record 类型，游标和游标变量， 过程和函数） 提高性能，隐藏信息（私有），子程序重载
 
 --1.创建包 （包:由包规范 和包体 组成， 创建包是要首先创建包规范 ，然后 包体）  实际上包规范是包与应用程序之间的接口
  
   --A 首先 创建包规范 create package

  --语法：
   create [or replace] package package_name   --package_name 指定包名
   is|AS                                           --用于定义共有组件
   public type end item declareations
   subprogram specifications 
   end package_name;
   
   --示例
   create or replace package emp_package   --定义emp_package
   As
       g_deptno number(3):=30;    --定义公有变量
       procedure pro_add_employee   --定义公有的 过程
       (
         param_empno number,
         param_ename varchar2,
         param_sal number,
         param_deptno number:=g_deptno
       )
        --定义公有的函数fun_get_sal;
       function fun_get_sal(param_empnno number)return number;
       
       --***过程和函数 都没有给出具体的实现  所以 公有的函数和过程 只有在创建了包体后才能调用
   END emp_package;
 
 
   --B: 创包体； create package body （实现包规范中定义的过程和函数） 可以定义私有的组件（变量，常量，过程， 函数）只能在包体中使用
   
      --语法
        create [or replace] package body package_name  --package_name用于指定包名 和包规范名称一样
        iS|AS                                          --定义自由组件的开始 并实现公有的函数和过程
            private type and item declarations subprogram bodies
        end package_name;
        
      --示例 1
       create or replace package body emp_package  --创建包体名
       As
       
       --定义私有的 函数
         function fun_vallidate_dept(param_deptno number)
         return boolean
         as
            v_temp number;
         begin
           select 1 into v_temp from dept where deptno=param_deptno;
           return true;
         Exception
           when no_data_found then
           return false;
         END;
         
         --实现 包规范中的 pro_add_employee 过程
         procedure pro_add_employee
         (
            param_name number,
            param_ename varchar2,
            param_sal number,
            param_deptno number:=g_deptno
            
         )
         AS
         begin
           if fun_vallidate_dept(param_deptno) then   --调用了同一包中的 组件  fun_vallidate_dept
              insert into emp(empno,ename,sal,deptno) values(param_empno,param_ename,param_sal,param_deptno);
           else
              raise_application_error(-2000,'不存在该部门！');
           end if;
          Exception
           when DUP_VAL_On_index then
              raise_application_error(-2002,'该员工编号已经存在！');
          end;
          
          --实现公有函数fun_get_sal
          function fun_get_sal(param_empno number) return number
          as
            v_sal emp.sal%type;
          begin
            select sal into v_sal from emp where empno=param_empno;
            return v_sal;
          Exception
             wher no_data_found then
                raise_application_error(-2000,'该员工不存在！');
          end;
        end emp_package;

--在同一包中调用
    直接调用（如上示例 的 pro_pro_add_employee）： fun_vallidate_dept

--调用包的公用变量
   begin 
       emp_package.g_deptno:=20;
   end;
--调用包的公有过程
   begin
       emp_package.g_deptno:=20;
       emp_package.proc_add_employee(8888,'chen',3000);
       emp_package.proc_add_employee(8889,'yaun',3000,30);
   end;
--调用包的公用函数  （要定义变量来接受返回值）
  declare
    v_sal emp.sal%type;
  begin
    v_sal:=emp_package.fun_get_sal(7788);
    DBMS_OUTPUT.put_Line('员工7788的工资是：'||V-sal);
  end;
--不同用户调用包的公用组件  (要有权限)
  grant execute on emp_package to hr;
  conn hr/hr@oracl;
  
  begin
     scott.emp_package.pro_add_employee(2010,'tiger',2000);
  end;
  
--查看包的源代码
  conn scott/tiger@orcl
  
  select text from user_source where name='emp_package' and type='package';

--删除包
  drop package emp_package;

--__________________________________游标___________________________________________---

--游标分为   1.隐含游标：（处理 select into 和DML语句）
           --2.显示游标： (处理select 语句返回多行数据)
  
  
--显示游标 （定义游标declare，打开游标open，提取数据fethch，关闭游标close）
   
    --1.定有游标 （在使用显示游标的时候，必须首先在定义部分定义游标，用于定义游标所对应的select语句）
       --语法
        cursor cursor_name is select_statement;  --cursor_name 游标的名称，select_statement：指定游标对应的select 语句
        
   --2.打开游标  （打开游标的时候，oracle 会执行游标所对应的select 语句，将select语句的结果暂时放在结果集中）
         open cursor_name； --cursor_name :必须在定义部分定义已经定义了的游标
         
   --3.提取数据  （oracle 9i之前 fetch 每次只能提取一行数据 在oracle 9i 之后 通过fetch ....bulk collect into 语句一次可提取多行数据）
          fetch cusor_name into variable1,variable2,....;    --variavle：用于指定接受游标数据的变量
          --或者
          fetch cusor_name bulk collect into colllec1,collect2,.....;   --collect:用于指定接受游标结果的集合变量
   --4.关闭游标
         close cusor_name;

--显示游标的属性  （返回显示游标的执行信息 包括 %isopen,%found,%notfound,%rowcount）
   --1.isopen  (判断游标是否已经打开)
       if cusor_name %isopen then
            执行语句
       else 
          open cusor_name;
       end if;
   --2.%found (检测是否从数据集中提取到数据,提取到 返回true)
      LOOP
         fetch cusor_name into var1,var2;
             if cusro_name%fount then
                  执行语句；
             else 
                exit;
             end if;
      ENd LOOP;
   --3.%notfound (检测是否从数据集中提取到数据,没有提取到 返回true)
   
      LOOP
          fetch cusor_name into var1,var2;
          exit when cusor_name%notfound;
          ....
      END LOOP;
   --4.%rowcount  (返回到当前行为止，已经提取到的世纪行数)
      LOOP
         fetch cusor_name into var1,var2;
         if cusor_name%rowcount>n then   --执行提取数据的行数大于n时候，要执行的语句
               .....
         END if；
         exit 
           when cusor_name%notfound;
      END LOOP;
 
--显示游标的使用示例
   --1.在显示游标中使用 fetch  ....into ..语句  （fetch ...into.. 每次只能提取一行，要处理结果集中多行数据，必须使用循环语句）;
      declare
         cursor emp_cursor is select ename,sal from emp where deptno=30;
         v_name emp.ename%type;
         v_sal emp.sal%type;
       begin
         open emp_cursor;  
         Loop 
            fetch emp_cursor into v_name,v_sal;
            exit when emp_cursor%notfound;
            DBMS_OUTPUT.put_line(v_name||' '||v_sal);
         end LOOP;
         close emp_cursor;
       end;
       
    --2.在显示游标中，使用fetch...bulk collect into提取所有的数据
      declare
        cursor emp_cursor is select ename,sal from emp where deptno=10;
        type emp_record is record
        (
           ename emp.ename%type,
           sal emp.sal%type
        );
        type ename_table_type is table of emp_record;
        v_name_table ename_table_type;
        begin
          open emp_cursor;
          fetch emp_cursor bulk collect into v_ename_table;
          close emp_cursor;
          for i in v_ename_table.first..v_emp_table.last llllllllllllllllllll.
             LOOP  
                   DBMS_OUTPUT.put_line(v_ename_table(i).ename||'    '||v_ename_table(i).sal);
             END loop;
        END;
     --3.在游标中使用属性
       cursor emp_cursor is select ename from emp where deptno=10;
       type ename_table_type is table  of varchar2(10);
       v_ename_table ename_table_type;
       begin
         if not emp_cursor%isopen then
            open emp_cursor;
         end if;
         fetch emp_cursor bulk collect into v_name_table;
           DBMS_OUTPUT.put_line('提取的总行数'||emp_cursor%rowcount);
           close emp_corsor;
       end; 
       
       --4.基于游标定义记录变量 （%rowtype可以基于表和视图定义记录变量，还可以基于游标定义记录变量）
       declare
         cursor emp_cursor is select ename,sal from emp where deptno=10;
         emp_record emp_cursor%rowtype;
         begin
             open emp_cursor;
             LOOP
              fetch emp_cursor into emp_record;
              exit when emp_cursor&notfound;
              DBMS_OUTPUT.put_line(emp_record.ename||'  '||emp_record_sal);
             end LOOP;
         end;
       
       --5.参数游标 （使用不同参数多次打开游标可以生成不同的结果集）
          cursor cursor_name(parameter_name datatype) is select_statement;
          --示例
            declare            --下面 定义了带参数的游标  指定数据类型不能指定长度
              cursor emp_cursor(param_dept number) is select ename,sal from emp where deptno=param_dept;
              emp_record emp_cursor%rowtype;
            begin
               open emp_cursor(10);    --打开游标时候 传入参数 （10） 表示查询 部门编号为10 的所有员工的姓名和工资   
               loop
                fetch emp_cursor into emp_record;
                exit when emp_cursor%notfound;
                DBMS_OUTPUT.put_line(emp_cursor.ename||'     '||emp_cursor.sal);
               end loop;
               close emp_cursor;
            end;
            
         --6.是有游标更新或删除数据  （在定义游标时候 带有for update）
            --语法
              cursor cursor_name(param_naem datatype) is select_statement for update [nowalt];
            --更新数据
              declare
                cursor emp_cursor is select ename,sal from emp for update;
                v_emp_row emp_cursor%rowtype;
                v_update_emp_count number(2):=0;
              begin
                open emp_cursor;
                LOOP
                  fetch emp_cursor into v_emp_row;
                  exit when emp_cursor%notfound;
                  if v_emp_row.sal<2000 then
                    update emp set sal=sal+100 where current of emp_cursor;
                    v_update_emp_count:=v_update_emp_count+1;
                  end if;
                 end LOOP;
                 dbms_output.put_line('共有'||v_update_emp_count||'名员工被更新了！');
                 close  emp_cursor;
               end;
               
             --使用游标删除数据
               declare
                 cursor emp_cursor is select deptno form emp for update;
                 v_emp_row emp_cursor%rowtype;
                 v_update_emp_count mumber(2):=0;
                 begin
                    open emp_cursor;
                    loop
                      fetch emp_cursor into v_emp_row;
                      exit when emp_cursor%notfound;
                      if v_emp_row.deptno=30 then
                         delete emp where current of emp_currsor;
                         v_update_emp_count:=v_update_emp_count+1;
                      end if;
                    end loop;
                   DBMS_OUTPUT.put_Line('共有'||v_update_emp_count||'名雇员删除了！');
                   close emp_cursor;
                 end;
                  

    declare  
     cursor emp_cursor is 
    select ename,sal from emp form update nowalt;
    v_emp_row emp_sursor%RowType;
     u_upate_emp_count number(2):=0;
    begin
     open emp_cursor;
     loop
        fetch emp_cursor into v_emp_row;
        exit when emp_cursor%notfound;
       if v_emp_row.sal<2000 then
          update emp set sal=sal+100 where current of emp_cursor;
          v_updae_emp_count:=v_update_emp_count+1;
          end if
     end loop;
       dbms_output.put_line('共有'|| _update_emp_count||‘员工被更新了 ！’);
    close emp_cursor;
end 



--游标for 循环


declare 

    --定义游标
    cursor emp_cursor is select ename，sal form emp;
   begin
        for emp_row in emp_cursor loop
          dbms_output.put_line('第'||emp_cursor%RowCount ||'个员工：'||emp_row.ename);
        end loop;

  end;


在for 游标中直接使用 子查询
     begin   
         for   emp_row int (select ename,sal form emp) loop
                ebms_output.put_line(emp_row.ename);
          end loop;

     end;   

--使用游标变量
   定义ref cursor 类型 和游标变量
        
    type ref_type_name is ref cursor；
    cursor_variable  ref_type_name;

--游标变量使用
    依次显示部门编号为30 的所有员工的 说明使用游标变量的方法 
     declare
        TYPE emp_cursor_type is REF CURSOR;
         
        emp_cursor emp_cursor_type;
        emp_row emp%ROWTYPE;
     begin
       open emp_cursor for
         select * from em where deptno=30;
         loop
           fetch emp_cursor into emp_row;
           exit when emp_cursor%notfound;
           dbms_output.put_line('第'||emp_cursor%RowCount||'个员工：'||emp_row.ename);
         end loop;
        close emp_cursor;
     end;


---使用游标变量开发返回结果集的子程序
   create or replace procedure proc_getEmpsByDeptNO
   （
       param_deptno number,
       param_resultset out  SYS_REFCURSOR

    ）
      as
     begin
        open param_resultset for 
             select ename,sal from emp where deptno=param_deptNo;
     end;



    declare
       TYPE emp_record_type is record
	(
		ename varchar2(20),
  		sal number(7,2)	
	) ;   
     v_emp_rows SYS_REFCURSOR;
     v_deptno number(2):=30;
     v_emp_row emp_record_type;

  begin
     pro_getEmpByDeptno(v_deptno,v_emp_rows);
  LOOP
    fetch v_emp_Row into v_emp_row;
    exit when v_emp_rows%NOTFOUND;
     DBMS.output.put_line('第'||v_emp_rows%rowcount||‘员工 名称’||v_emp_row.ename||'工资'||v_emp.row.sal);
  end loop;
  close v_emp_rows;

end;


--开发返回结果集的函数
  create or replace function fun_getEmpByDateYear
(
	param_hireDareYear number

)
return sys_REFCURSOR
as
  param_resultset sys_REFCUSOR;
begin
    open param_resultset for
      select ename,sal from emp  where EXTRACT(Year from hrdare )=param_hirdDateYear;
   return param_resultset;
end


declare
    TYPE emp_record_Type is record
	(
		ename varchar2(20);
    		sal number(7,1)
	);
       v_emp_rows SYS_REFCURSOR;
       v_hireDateYear number(4):=1981;
       v_emp_row emp_record_type;
       begin
          v_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);
         loop
           fetch v_emp_rows into v_emp_row;
           exit when v_emp_rows%NOTFOUND；
          DBMS.output.put_line('第'||v_emp_rows%rowcount ||'个员工的名称：'||v_emp_row.name||'工资'||v_emp_row.sal);
        end loop;
      close v_emp_rows;
 end;


--隐含游标的属性 （SQL%FOUND　　　SQL％NOTFOUND　　　SQL％rowcount　SQL％isopen）
    
     1 ：SQL%FOUND
       decare 
          v_empno number(4):=7788;
       begin  update emp set empno=v_empno where empno=v_empno;
       if  sql%fount then
              dbms_out.put.put_line('存在该员工');
       else
             dbms_output.put_line('不存在该员工');
     end if;
  end ;

   2:　SQL％NOTFOUND  与  SQL%FOUND　 相反


   3：  declare
            v_deptno number(2):=20;
            v_rows_count number;
            begin updare emp set sal =sal+100 where deptno=v_deptno;
             v_row_count:=Sql%rowCount;
       if v_rows_count =0 then
            dbms_output.put_line('没有员工被更新！');
       else
            dbms_output.put_line('共有：'||_rows_count||'个员工被更新了！');
       end  if;
end;