3.函数和存储过程-CSDN博客

本文链接：https://blog.csdn.net/ruyigongfang/article/details/141712874

知识总结：

1.函数过程

2.子程序的优点

3.子程序的区别及使用场景

4.函数语法及注意点

5.过程语法及注意点

一.函数

可以把函数看成一个处理某些数据并返回结果数据的功能集

create [or replace] function 函数名称(参数1 数据类型,参数2 数据类型.....) return 返回值数据类型 is
     --定义变量
   begin
     --具体业务处理
     return 返回值;
   end;

2.学习案例

2.1创建一个函数返回7788的姓名

create function getname return varchar2 is
       v_name varchar2(20);
     begin
       select ename into v_name from emp where empno = 7788;
       return v_name;
     end;

2.2创建一个函数返回两个数字的乘积

 create function f1(num1 number,num2 number) return number is
     begin
       return num1 * num2;
     end;

2.2创建一个函数返回100-200之间所有整数数字的和

create or replace function f1(n1 number,n2 number) return number is
       v_res number := 0;
     begin
       for v_i in n1..n2 loop
         v_res := v_res + v_i;
       end  loop; 
       return v_res ;
     end;

2.3定义一个函数计算两个数字的最大公约数

create function f2 (n1 number,n2 number) return number is
       v_res number;
     begin
       for v_i in 1..n1 loop
         if mod(n1,v_i)= 0 and mod(n2,v_i)=0 then
           v_res := v_i;
         end if;          
       end loop;
       return v_res;
     end;

5.练习

5.1定义一个函数,根据员工编号返回入职时间

create function dfsdgd(v_empno number) return date is
      v_hiredate date;
    begin
      select hiredate into v_hiredate from emp where empno=v_empno;
      return v_hiredate;
    end;

5.2定义一个函数,计算一个数字的阶乘

create or replace function t1(n1 number) return number is
      v_res number := 1;
    begin
      for v_s in 1 .. n1 loop
        v_res := v_res * v_s;
      end loop;
      return v_res;
    end;

二.过程

create [or replace] procedure 过程名称(参数1 in 数据类型,参数2 out 数类型,参数3 in out 数据类型...) is
    --定义变量
  begin
    --具体业务处理
    参数2 := 返回值;
  end;

2.子程序:

指存储在数据库中供所有的用户程序调用的子程序叫存储过程、存储函数。

3.子程序的优势

3.1.效率高

--子程序编译一次后，就会存到数据库，每次调用时都直接执行。

--而普通的sql语句我们要保存到其他地方（例如：记事本上），都要先分析编译才会执行。

--所以相对而言子程序效率更高。

3.2.降低网络流量

--子程序编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.3.复用性高

--子程序往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该子程序。

3.4.可维护性高

--当功能要求发生小的变化时，修改之前的子程序比较容易，花费精力少。

3.5.安全性高

--完成某个特定功能的子程序一般只有特定的用户可以使用，具有使用身份限制，更安全

4.注意:

A.存储过程和函数的区别:

A1、存储过程帮用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。

A2、存储过程声明用procedure，函数用function。

A3、存储过程不需要写返回值的数据类型，是通过out和in out参数来返回值，函数必须要写返回值的数据类型，使用return返回值。

A4、存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为sql语句（DML或SELECT)表达式中的一部分。

B.适用场合区别：

如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数。

5.学习案例

5.1控制台打印输出7788的姓名

 create procedure p1 is
      v_name varchar2(20);
    begin
      select ename into v_name from emp where empno = 7788;
      dbms_output.put_line(v_name);
    end;

5.2创建一张表a1(tid,tname) 使用过程给表中插入指定数量的数据行

 create procedure p2(num1 number) is
    begin
      for v_i in 1..num1 loop
        insert into a1 values(v_i,v_i||'a');
      end loop;
    end;

5.3创建一个过程返回指定编号对应的姓名

create procedure p3(v_empno number,v_name out varchar2) is
    begin
      select ename into v_name from emp where empno = v_empno;
    end;
    
    declare
      v_ename varchar2(20);
    begin
      p3(7788,v_ename);
      dbms_output.put_line(v_ename);
    end;

5.4查看过程内参数传递过程

create or replace procedure p_3(n1 in number,n2 out number,n3 in out number) is   
    begin
      dbms_output.put_line('n1:'||n1);--10
      dbms_output.put_line('n2:'||n2);--
      dbms_output.put_line('n3:'||n3);--30 
      --n1 := 100;  --会报错  
      n2 := 200;
      n3 := 300;    
    end;
    
    declare
      v_nu1 number := 10;
      v_nu2 number := 20;
      v_nu3 number := 30;
    begin
      P_3(v_nu1,v_nu2,v_nu3);
      dbms_output.put_line('v_nu1:'||v_nu1);--10
      dbms_output.put_line('v_nu2:'||v_nu2);--200
      dbms_output.put_line('v_nu3:'||v_nu3);--300
    end;

三、作业

1.使用动态游标对30号部门的所有员工按照工作时间加薪；81年6月1日及之前的加10%，81年6月1日之后的加5%

declare
    type t1 is ref cursor;
    cur_e   t1;
    v_sql   varchar2(200);
    v_hire  date;
    v_num   number;
    v_empno number;
    v_sal   number;
    v_newsal   number;
  begin
    v_sql := 'select hiredate,sal,empno from emptest where deptno=30';
    open cur_e for v_sql;
    loop
      fetch cur_e
        into v_hire,v_sal, v_empno;
      exit when cur_e%notfound;
      if v_hire < to_date('1981-06-01', 'yyyy-mm-dd') then
        v_num := 1.1;
      else
        v_num := 1.05;
      end if;
      update emptest
         set sal = sal * v_num
       where empno = v_empno
      returning sal into v_newsal;
      dbms_output.put_line(v_empno||' '||v_hire||' '||v_sal||' '||v_newsal);
    end loop;
    close cur_e;
  end;

2.使用函数计算两个数字的最小公倍数

 create or replace function f2(n1 number, n2 number) return number is
     v_res number;
   begin
     for v_i in n1 .. n1 * n2 loop
       if mod(v_i, n1) = 0 and mod(v_i, n2) = 0 then
         v_res := v_i;
         exit;
       end if;
     end loop;
     return v_res;
   end;

3.使用函数传入两个参数 m 和 n ,返回雇员表按工资从小到大排序后的第 m 条到第 n 条记录的工资总和

create or replace function f6(m number, n number) return number is
    v_sal number;
    v_i   number;
    v_j   number;
  begin
    if m > n then
      v_i := n;
      v_j := m;
    else
      v_i := m;
      v_j := n;
    end if;
    select sum(sal)
      into v_sal
      from (select sal, row_number() over(order by sal asc) xh from emp)
     where xh between v_i and v_j;
    return v_sal;
  end;

4.某 c 表数据如下：

c1 c2

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

1 华为

1 p40

1 照亮

2 你

2 的

3 美

--写一个过程打印输出如下内容

1 华为P40照亮

2 你的

3 美

--要求：不能改变表结构及数据内容

 create or replace procedure p1 is    
    begin
      for v_i in (select c1,listagg(c2)within group(order by rownum) c2  
                  from c group by c1)loop
        dbms_output.put_line(v_i.c1||' '||v_i.c2);
      end loop;
    end;

create or replace procedure p2 is 
 begin
  for v_i in (select c1 from c group by c1 order by c1) loop
    dbms_output.put(v_i.c1||' ');
    for v_j in (select c2 from c where c1=v_i.c1) loop
      dbms_output.put(v_j.c2);
    end loop;
    dbms_output.put_line('');
  end loop;
 end;