oracle学习总结------procedure

--子程序（存储过程，函数）


--存储过程
--语法：
 --create or replace procedure 存储过程名字【（参数列）】
 --is|as
 --声明区
  --begin
  --执行区
  --exception
  --异常处理
  --end;
  
  --存储过程的参数分为三类 in, out, in out 参数类型不能带长度
     --in是将实际参数传给形参，默认类型
     --out是将形式参数传递给实参，可以实现多个值得返回
     -- in out 两个兼备
     
  
  --实例：创建一个存储过程，打印所有部门的编号和部门的平均工资


  create or replace procedure pro_get_sal is
  v_deptno emp.deptno%type;
  v_avg_sal emp.sal%type;
  cursor cur is select deptno,avg(nvl(sal,0)) from emp group by deptno; 
  begin
    open cur;
    fetch cur into v_deptno,v_avg_sal;
    while cur%found loop
      dbms_output.put_line('deptno=' || v_deptno ||'v_avg_sal=' || v_avg_sal);
       fetch cur into v_deptno,v_avg_sal;
      end loop;
      
   close cur;   
      
  end;
  
   --实例：创建一个存储过程，打印所有部门的编号和部门的平均工资
  create or replace procedure pro_get_sal2 is
  begin
    for emp in(select deptno,avg(nvl(sal,0)) avg_sal from emp group by deptno) loop
       dbms_output.put_line('deptno=' || emp.deptno ||'v_avg_sal=' || emp.avg_sal); 
    
      end loop;
    end;
  
  
  
 declare 
  begin
    pro_get_sal2();
    end;
      
  
  
--创建一个存储过程，参数传入一个员工编号，打印该员工所在的职位的员工姓名和薪资


create or replace procedure get_jon_empty(eno in emp.empno%type)
is 
 
begin
     for e in ( select * from emp where job like(select job from emp where empno=eno) )loop 
    dbms_output.put_line(e.ename || ' ' || e.sal || '  ' || e.job);
     end loop;
  end;
      
   --调用存储过程
   
   declare
   begin
     --pro_get_sal;
     get_jon_empty('&input');
   end;
   
  --删除存储过程
   drop procedure pro_get_sal;
   
   


   --查询CLERK的所有员工信息
   select * from(select * from emp where job='&input');
   
   --创建一个存储过程，参数传入一个员工编号，打印该员工所在的职位的员工姓名和薪资
   declare
   begin  
   for e in ( select * from emp where job like(select job from emp where empno='&input') )loop 
    dbms_output.put_line(e.ename || ' ' || e.sal);
     end loop;
     end;
     
   --创建一个存储过程，参数传入部门编号，输出该部门的平均工资。
   
   create or replace procedure get_dept_sal(dno in emp.deptno%type,avg_sal out emp.sal%type)
   is
   begin  
     select avg(nvl(sal,0)) into avg_sal from emp where deptno = dno;
     end;
     
     
     declare 
      s emp.sal %type;
     begin 
       get_dept_sal('&input',s);
       dbms_output.put_line(s);
       end;
   
   
   
   
   --创建一个存储过程，参数传入一个员工编号，输出该员工的姓名，
     --薪资，职位，用该职位作为参数创建存储过程，用于查询该职位的员工信息
     
     
     --创建一个存储过程，参数传入一个员工编号，输出该员工的姓名，薪资，职位，
     create or replace procedure get_emp_info(eno in emp.empno%type,
     v_name out emp.ename%type,v_sal out emp.sal%type,v_job out emp.job%type)
     as 
     begin
        select ename,sal,job into v_name,v_sal,v_job from emp where empno = eno;
       end;
   
   
    --创建一个存储过程，用于参数传入则职位，显示该职位的所有员工信息
    
     create or replace procedure prin_dept_info(v_job in emp.job%type)
     
     as
     
     begin 
  
          for temp in( select * from emp where job = v_job) loop
            dbms_output.put_line(temp.empno || '  ' || temp.ename || '  ' ||temp.job ||'  ' 
            ||to_char(temp.hiredate,'YYYY-MM-DD'));
            end loop;
      
       end;
       
     --创建一个存储过程，用于参数传入员工编号，和修改的薪资，修改emp的对应参数的员工的薪资
     
     create or replace procedure pro_setSal(in_empno in emp.empno%type,in_sal in emp.sal%type)
      is
      begin
     
      update emp set sal = in_sal  where empno = in_empno;
      exception
        when no_data_found then
          dbms_output.put_line('没有该员工信息');
        end; 
        
        --创建匿名块，调用存储过程
        declare
        begin
          pro_setSal(7369,1000);
          end;
      
      update emp set sal = 500 where empno =7369;  
      s+


    rollback;
   
 
   
   declare 
   v_name emp.ename%type;
   v_sal emp.sal%type;
   v_job emp.job%type;
   begin
     
   get_emp_info('&input',v_name ,v_sal,v_job);
    dbms_output.put_line(v_name || '  ' || v_sal || '  ' ||v_job);
    dbms_output.put_line('-----------------');
    prin_dept_info(v_job);
     end;
     
   
   
   --函数
   --基本语法
   --create or replace function 函数名【(参数列)】
   -- return 返回值类型 //返回值类型不能带长度
   -- is|as
   --声明区
   --begin
   --执行区，一定要有return语句
   --exceprion
   --异常区
   --end;
   
    
   -- in out 参数
   
   
 
   
   --CASE 条件表达式
  --WHEN 条件表达式结果1 THEN 
    -- 语句段1
  --WHEN 条件表达式结果2 THEN
    -- 语句段2
  --......
  --WHEN 条件表达式结果n THEN
    -- 语句段n
  --[ELSE 条件表达式结果]
--END;
---------格式二---------
--CASE 
  --WHEN 条件表达式1 THEN
    -- 语句段1
  --WHEN 条件表达式2 THEN
    -- 语句段2
  --......
  --WHEN 条件表达式n THEN 
    -- 语句段n
  --[ELSE 语句段]
--END;
   
  --实例：参数传入两个数，参数输出他们的四则运算结构
   create or replace function operation(num1 number ,num2 number ,c char)
   return number
   is
   re number;
   begin
     case c 
       when '+' then
         re := num1 + num2;
       when  '-' then
         re := num1 - num2;
        when '*' then
         re := num1 * num2;
         when  '/' then
           re := num1/num2;
         dbms_output.put_line('re-->' + re);
         end case ;
     return re;
     end;
     
   declare
   
   begin
     
      dbms_output.put_line(operation(3 ,5 ,'*'));
     end;
     
     
     
     --案例：编写一个函数，用于接收用户名，和返回用户的年薪
     
     create or replace function fun_annual_salary(v_in_empno number)
     return number is
     annual_sal number;
     begin
       select (sal + nvl(comm,0))*13 as an_sal into annual_sal  from emp where empno = v_in_empno;
       
       return annual_sal;
       exception
         when no_data_found then


        dbms_output.put_line('没有该员工！');     
     end;
  




declare
an_sal emp.sal%type;
begin
  an_sal := fun_annual_salary(7369);
  dbms_output.put_line('年薪' || an_sal);
end;








--综合实例，创建一个包，用于封装存储过程和函数，存储过程用于修改员工的工资，函数用于获得该员工的工资。


--定义包头，用于声明存储过程和函数


create or replace package pak is
procedure pro(v_in_ename in varchar2,v_in_sal in number);
function fun(v_in_ename in varchar2) return number;
end;




--定义包体，用于定义存储过程和函数


create or replace package body pak is


--定义储存过程
procedure pro(v_in_ename in varchar2,v_in_sal in number) 
 is
begin 
  update emp set sal = v_in_sal where ename = v_in_ename;
  end;
    
--定义函数
function fun(v_in_ename in varchar2) return number
  is 
v_sal number;
 begin 
    select sal into v_sal from emp where ename = v_in_ename;
  return v_sal;
  end;  


end;




declare
v_sal number;
begin
  select pak.fun('KING')  into v_sal FROM dual;
  dbms_output.put_line(v_sal);
  end;
  
  
  
  
 --实例：创建一个存储过程，用于参数输入员工编号，参数输出员工姓名和薪资，
 
 create or replace procedure proNameSal(v_in_empno in number , v_out_name out varchar2,v_out_sal out number)
 is 
 begin
   select ename,sal into v_out_name,v_out_sal from emp where empno = v_in_empno;
   end;
   
--创建一个匿名块，用于调用存储过程


declare
v_ename emp.ename%type;
v_sal number;
begin


  proNameSal(7839,v_ename,v_sal);
  dbms_output.put_line('name-->' ||v_ename || 'sal--->' || v_sal );
end;




--创建一个存储过程，用于参数输如部门编号，参数输出该部门的所有员工信息。


--1,定义一个包，用于定义一个游标类型
create or replace package pack_cursor is
--定义一个动态游标数据类型
 type dept_corsor is ref cursor;
 end;


--2.定义一个存储，用于参数输如部门编号，参数输出该部门的所有员工信息。
create or replace procedure pro_cursor(v_in_deptno in number,v_out_cursor out pack_cursor.dept_corsor)
 is
 --自定义异常
 nodata exception;
 begin
   --打开动态游标
   open v_out_cursor for select empno,ename,sal,deptno from emp where deptno = v_in_deptno;
   if v_out_cursor is null then 
     raise nodata;
     end if;
   --异常处理
   exception
     when nodata  then
        dbms_output.put_line('没有该部门！');   
 end;
   
 
 --3.创建一个块，用于测试存储过程
 declare
  v_cursor pack_cursor.dept_corsor;
 v_deptno emp.deptno%type :=&input; 
 v_name emp.ename%type;
 v_empno emp.empno%type;
 v_sal emp.sal%type;
 begin
   pro_cursor(v_deptno,v_cursor);
   
   --执行游标
   fetch v_cursor into v_empno,v_name,v_sal,v_deptno;
   while v_cursor%found loop
   dbms_output.put_line('员工编号：' || v_empno  || ' 员工姓名：' || v_name || ' 薪资：' ||v_sal || ' 所属部门：' || v_deptno );
    fetch v_cursor into v_empno,v_name,v_sal,v_deptno;
   end loop; 
    --关闭游标
   close v_cursor;
   end;
   
  
   




--sql语句实现分页：
select t2.* from (select t1.*,rownum as rn from (select * from emp) t1 where rownum <=10) t2 where rn >= 5;




--实例：创建一个存储过程，实现分页功能：
create or replace procedure paging(v_in_table in varchar2 , v_in_currPage in number,v_in_pageSize in number ,v_out_count out number
 ,v_out_pageCount out number, v_resultCursor out pack_cursor.dept_corsor ) is
 v_start number; --开始的记录
 v_end number;--结束的记录
  v_sql_count varchar(50);
  v_sql_page varchar2(2000);
  v_count number ;-- 存储总记录数  
  v_pageSize number;--分页显示的记录数
  v_currPage number;--当前页数
  v_table varchar2(2000); --需要查询的表明
 begin 
   v_sql_count := 'select count(*) from ' || v_in_table;--获得记录数目
  execute immediate  v_sql_count into v_count ;  
    v_out_count := v_count;
    v_currPage := v_in_currPage;
    v_pageSize :=v_in_pageSize;
    v_table := v_in_table;
   --获得页面总数  
   if mod(v_count,v_in_pageSize) = 0 then
     v_out_pageCount := v_count / v_pageSize;
   
     else
        v_out_pageCount := v_count / v_pageSize + 1;
       end if;
   --获得当前页面的记录 
   v_start := v_pageSize *(v_currPage - 1) + 1;
   v_end := v_pageSize * v_currPage;  
   v_sql_page := 'select t2.* from (select t1.*,rownum as rn from (select empno,ename,sal,deptno from ' ||  v_table || ') t1 where rownum <= ' ||  v_end || ') t2 where rn >= '  || v_start;
   open v_resultCursor for v_sql_page;
   end;
   
   
   declare
   v_table varchar2(20):= '&input';
   v_out_count number;
   begin
    -- execute immediate  'select count(*) from ' || v_table into v_out_count ;
     dbms_output.put_line(v_out_count);
     end;