Oralce 存储过程 和 函数

--____________________________________子程序（过程和函数）____________________________________________

--1.创建过程  ：无参数
   create or replace procedure pro_del_dup_rec
   as [is]
   begin
      delete teb_test a where a.rowid=(select max(rowid) from tb_test b where a.a=b.a and a.b=b.b);
   end;


--2.带有IN的参数的过程  (输入参数)
   create or replace procedure pro_transit_station   
   (
     v_start_station tb_station.station_name%type,  --定义一个变量用于保存起点站
     v_end_station tb_station.station_name%type;   --定义一个变量用于保存终点站
     V_line_name tb_station.line_name%type:='536'  --定义变量把保存公交的车次
   )
   as
      v_start_forder tb_station.forder%type;    --定义变量用户保存起点站的序号
      v_end_forder tb_station.forder%type;   --定义变量用于保存终点站的序号
      v_station_line varchar2(100);    --定义变量用于保存起点到终点的线路
      type Station_name_table_type is table of tb_station.station_name%type;    --自定义一个索引表 用户保存一组车站的名字
      v_station_name_table Station_name_table_type;  --自定义类型的变量
   begin

--查询出起点和终点站的的序号  分别保存到变量中
      select fouder into v_start_forder from tb_station where line_name=v_line_name and station_name=v_start_station;
      select forder into v_end_forder from tb_station where line_name=v_line_name and station_name=v_end_station;

 --判断 如果终点站的序号必起点站的序号大  就说明是 去的路上
      if v_start_forder<=v_end_forder
         select staion_name bulk collect into v_station_name_table from tb_station 
            where line_name=v_line_name and forder>=v_start_forder and forder<=v_end_forder order by forder ;
      --否则 说明是回来的路上
      else
         select station_name bulk collect into v_station_name_table from tb_sataion 
            where line_name=v_line_name and forder>=v_end_forder and forder<=v_start_forder order by forder desc;
      END if;
      --输出 起点站和终点站的 开头 部分；
      DBMS_OUTPUT.put_line(v_line_name||'公交车【'||v_start_name||'->'||v_end_station||'】站的公交路线：');
      --循环遍历表中的数据
      for  i in v_station_name_table.first..v_station_name_table.last 
          LOOP  
             -- 累加 途中车站的名称
             v_station_line:=v_station_line||v_station_name_table(i)||'->';
          end LOOP;
          --去掉最后多的一个 '->'
          v_station_line=sbustr(v_station_line,0,length(v_station_line)-2);
          --输出途中的车站名称
          DBMS_output.put_line(v_station_line);
   Exception
      when no_data_found then
        DBMS_OUTPUT.put_line('请输入正确的公交车次路线！');
   END;   

   --调用此过程 （除了具有默认值的参数外，其他参数必须提供数值）
      Call pro_transit_station('常青路','武胜路','536');  --没有使用默认值
      --或者  
      Call pro_transit_station('常青路','武胜路');  --使用了默认值


--3.带有Out的过程 （有输出参数）
   --示例1
    create or replace procedure proc_query_em
    (
      param_empno numner,
      param_ename out varchar2(20),
      param_salary out number
    )
    as
    begin
      select ename,salary into param_ename,param_salary from emp where empno=param_empno;
    Exception
       when no_data_found then
         raise_application_error(-2000,'该雇员不存在!');
    END;

   --调用带有out 参数的的过程 必须定义变量接受输出参数的数据
    declare 
     v_empno emp.empno%type:=7788;
     v_ename emp.ename%type;
     v-salary emp.salary%type;
    Begin
     proc_query_emp(v_empno,v_ename,v_salary);
     DBMS_output.put_Line(v_ename||' '||v_salary);


--4.创建带有 in out 的参数 （in out 是值输入输出参数）
  --示例1
    create or replace procedure pro_eompute
    (
     param_num1 in out number,
     param_num2 in out number
    )
    as
     v1 number;
     v2 number;
    begin
      v1:=param_num1/param_num2;
      v2:=MOD(param_num1,param_num2);
      param_num1:=v1;
      param_num2:=v2;
    END;
   --调用 in out 过程 必须提供两个变量临时赋值
    declare 
       v_num1 number(2):=10;
       v_num2 number(2):=3
     begin
       proc_compute(v_num1,v_num2);
       DBMS_output.put_line(v_num1);
       DBMS_outPut.put_line(v_num2);
     end;

  --5.过程中的参数 使用 “按值传递” 和 “位置传递 ” 和 “组合传递”
      --1 定义过程
        delcare or replace procedure proc_add_dept
        (
          param_deptno number,
          param_dname varchar2(20),
          param_loc varchar2(20):=null
        )
        as
        begin
           insert into dept values(param_deptno,param_dname,param_loc);
        Exception
          when Dup_val_on_index then
            raise_application_error(-2000,'部门编号不能重复');
        END;
      --按值传递
           call proc_add_dept(60,'manager','beijing');  --没有使用默认值
           --或者
           call proc_add_dept(70,'product');  --使用默认值

      --按名称传递
           call proc_add_dept(param_dept=>80;param_dname=>'purchase',param_loc=>'wuhan');

      --按组合传递 （第一个参数要按位置传递）
           call proc_add_dept(90,'admin',param_loc=>'wuhan');

--__________________________________________函数(用于返回指定的值)_____________________________________

  --语法
      create [or replace] function function_name   --function_name ：函数的名称
      (
           argument1 [model1] datatype1;  -- 函数的参数： 在指定函数参数类型时候，不能指定长度
           argument2 [model2] datatype2; 
           ....

      )  
      return datatype  --指定函数的返回类型   函数必须要有return  
      is|AS    --is|As 用于开始 一个 PL/SQL 语句块   
           声明部分
      begin
           执行部分  --在函数体内至少包含一条return 语句
      exception
           异常部分
      END;

   --示例1
      create or replace function fun_get_user
      return varchar2
      as
        v_user varchar2(100);
      begin 
        select username into v_user from tb_users;
        return v_user;
      end;
      --调用
       declare
         v_user varchar2(100);
       begin
         v_user:=fun_get_user;
         DBMS_outpur.put_line('当前用户是'||v_user);
       end;
          --或者 直接调用
       select fun_get_user 当前用户 from dual;

--1.创建带有 in 参数（输入）
    create or replace function fun_get_sal 
    (parm_name varchar2)  -- 定义了一个输入参数
    return number
    as
      v_sal emp%type;
    begin
      select sal into v_sal from emp where upper(param_name)=upper(sname);

    Exception
      when no_data_found then
       raise_application_error(-2000,'该雇员不存在！');
    END;
      --调用
    declare
           v_ename emp.ename%type:=&ename;
    begin
           DBMS_output.put_line(fun_get_sal(v_ename));
    end; 


--2.带有 out 参数的 函数
   declare or replace function fun_get_emp_info
   (  
      param_ename varchar2,  --输入参数 
      param_dname out varchar2   --输出参数
   )
   return varchar2
   as
     v_ejob emp.job%type;  --返回的变量
   begin
     select a.job,b.dname into v_job,param_dname from emp a,dept b where a.deptno=b.deptno and upper(a.ename) =upper(param_ename);
  return  --异常
     when no_data_found then
       raise_application_erroe(-2000,'该员工不存在！');
  END;

  --调用
    declare
        v_ename varchar2(20):=&v_ename;
        v_dname varchar(20);
        v_ejob varchar2(20);
     begin
        v_ejob:=fun_get_emp_info(v_ename,v_dname);
        DBMS_output.put_line('员工名称'||v_ename);
        DBMS_output.put_line('部门名称'||v_dname);
        DBMS_output.put_line('员工岗位'||v_ejob);
     End;

--3.带有in out 参数的函数
   create or replace function fun_compute
   (
     param_num1 number,
     param_num2 in out number ---定义 in out 参数
   )
   return number
   as
      v1 number;   --定义返回两个数相除的 结果
   begin
      v1:=param_num1/param_num2;
      param_num2:=MOD(param_num1,param_num2);   --两数相余 结果 重新赋值给param_num2;
      return v1;
    ENd;

  --调用的时候
    declare
        v_num1 number(2):=10;
        v_num2 number(2):=3;
        v_result number(2);
     begin
        v_resultL:=fun_compute(v_num1,v_num2);
        DBMS_output.put_line('余数'||v_num2);
        DBMS_output.put_line('商是'||v_result);
    End;


--4。过程和函数的 选用  
      过程： 返回多个值 或不返回值时候      
      函数： 返回一个值


--5.查看子程序的源码 
    --查看  函数 fun_computer 的源码
  select text from user_source where name=upper('fun_compute');


--6.查看当前用户所包含的所有过程和函数 (User_objects)
  col object_name format a20
  select object_name 对象名称,created 创建时间,status 状态（valid 有效，invalid 没效） from user_objects where object_type in ('function','procedure');

--7.列出子程序的编译错误 (show error)
   create or replace procedure  raise_salary  -- raise_salary : 对象依赖 
   (
     param_empno number,
     param_increase number
   )
   as
   begin
       -- emp  是这个 raise_salary 过程的 :引用对象
      update emp set sal=sal+param_increase where empno=param_empno  -- (故意少了一个结束 " ；" 符号)
   end;

   --使用 show error 确定错误的位置和原因
   show error procedure raise_salary;  --  raise_salary 是一个过程的名称

--8.列出对象的依赖关系（user_dependencies）  （创建的过程名和函数 ：叫 对象依赖，  过程和函数中涉及的表名 ：引用对象）

     --查询引用对象为 'emp' 的所有 对象依赖   包括 函数 过程  视图  
   select name,type from user_dependencies a where a.referenced_name='emp' ;


--9.重新编译子程序 （当我们修改了  引用对象（emp..）的结果后 所有的函数 过程 都会无效）
   --当函数中没有引用被修改的列时    ：经过 PL/SQL 的自动重新编译（即惰性编译）后，就可以正常使用  否则 失败
   --此时就要手动重新编译(compile) （函数和过程）
    alter procedure  pro_query_emp comPile;  --这样就可以 正常使用了


--10.删除子程序  （如果不在需要  子程序 （函数 和过程））
    --删除 过程
      drop procedure pro_query_emp;
    --删除 函数
      drop function fun_get_sal;