PL/SQL函数和过程示例

函数尽量不用in,out,in out

根据三角形的三边长返回面积

create or replace function calcTriangleArea(a number, b number, c number)
  return number is
  p number := (a + b + c) / 2;
  s number;
begin
  s := round(sqrt(p * (p - a) * (p - b) * (p - c)), 2);
  return s;
end;

根据雇员ID返回其部门名称

create or replace function getDeptName(v_empno emp.empno%type)
  return varchar2 is
  v_deptname dept.dname%type;
begin
  select dname
    into v_deptname
    from dept d
   where exists (select 0
            from emp e
           where e.deptno = d.deptno
             and e.empno = v_empno);
  return v_deptname;
end;

返回一个由大小写字母组成的，长度为6-10位的随机字符串

create or replace function grs return varchar2 is
  l number := trunc(dbms_random.value(6, 11));
begin
  return dbms_random.string('A', l); --A表示生成大小写字母，l表示长度
end;

根据指定参数输出斐波那契数列对应项上的数值

create or replace function fibonacii(n pls_integer) return pls_integer is
  fib_1 pls_integer := 0;
  fib_2 pls_integer := 1;
  rlt   pls_integer;
begin
  case
    when n = 1 then
      rlt := fib_1;
    when n = 2 then
      rlt := fib_2;
    else
      rlt := fibonacii(n - 2) + fibonacii(n - 1);
  end case;
  return rlt;
end;

过程

根据三角形的两边及其夹角计算出周长和面积

create or replace procedure calcTriangleAreaGirth(a   number,
                                                  b   number,
                                                  ang number) is
  rad number;
  c   number;
  s   number;
  g   number;
begin
  rad := ang / 180 * (asin(1) * 2);
  c   := sqrt(a ** 2 + b ** 2 - 2 * a * b * cos(rad));
  g   := round(a + b + c, 2);
  s   := round(a * b * sin(rad) / 2, 2);
  dbms_output.put_line('Area:' || to_char(s) || '  Girth:' || to_char(g));
end;

begin
calcTriangleAreaGirth(3,4,90);
end;

为指定dept_no的部门的雇员增加20%薪水

create or replace procedure raiseSal(v_deptno emp.deptno%type) is
begin
  update emp set sal = sal * (1 + 0.2) where emp.deptno = v_deptno;
  dbms_output.put_line(to_char(SQL%ROWCOUNT) || '名雇员的薪水已上涨20%');
end;

begin
raiseSal(10);
end;

列出指定日期（默认当前日期）所在月份的所有日期

create or replace procedure listDay(d date default sysdate) is
  v_first_day date := trunc(d, 'mm');
  v_last_day  date := trunc(last_day(d));
begin
  for i in 0 .. (v_last_day - v_first_day) loop
    dbms_output.put_line(v_first_day + i);
  end loop;
end;

begin
listDay;
end;

begin
listDay(date'2014-02-01');
end;

in 接收，不可改变

out 输出，可改变，不可接受

in out 可接受，可改变，可输出

链接中有in,out,in out的用法说明

版权声明：下面链接为xldmx原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。