oracle 10g 学习之函数和存储过程(12)

时间:2021-06-14 08:14:59

一、函数

1. 函数的 helloworld: 返回一个 "helloworld--!" 的字符串

 

create or replace function helloworld

return varchar2

is

begin

       return 'helloworld--!';

end;

 

执行函数

 

begin

    dbms_output.put_line(helloworld());

end;

 

2. 定义带参数的函数: 两个数相加

 

create or replace function add_func(a number, b number)

return number

is

begin

       return (a + b);

end;

 

执行函数

 

begin

    dbms_output.put_line(add_func(12, 13));

end;

 

3. 定义一个函数: 获取给定部门的工资总和, 要求, 部门号定义为参数, 工资总额定义为返回值.

 

create or replace function sum_sal(dept_id number)

       return number

       is

      

       cursor sal_cursor is select salary from employees where department_id = dept_id;

       v_sum_sal number(8) := 0;  

begin

       for c in sal_cursor loop

           v_sum_sal := v_sum_sal + c.salary;

       end loop;      

 

       dbms_output.put_line('sum salary: ' || v_sum_sal);

       return v_sum_sal;

end;

 

执行函数

 

begin

    dbms_output.put_line(sum_sal(80));

end;

 

4. 关于 OUT 型的参数: 因为函数只能有一个返回值, PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值

要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数, 要求: 部门号定义为参数, 工资总额定义为返回值.

 

create or replace function sum_sal(dept_id number, total_count out number)

       return number

       is

      

       cursor sal_cursor is select salary from employees where department_id = dept_id;

       v_sum_sal number(8) := 0;  

begin

       total_count := 0;

 

       for c in sal_cursor loop

           v_sum_sal := v_sum_sal + c.salary;

           total_count := total_count + 1;

       end loop;      

 

       --dbms_output.put_line('sum salary: ' || v_sum_sal);

       return v_sum_sal;

end;  

 

执行函数:

 

delare

  v_total number(3) := 0;

 

begin

    dbms_output.put_line(sum_sal(80, v_total));

    dbms_output.put_line(v_total);

end;

 

二、存储过程

基本上和函数一样

1. 定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求, 部门号和工资总额定义为参数

 

create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)

       is

      

       cursor sal_cursor is select salary from employees where department_id = dept_id;

begin

       v_sum_sal := 0;

      

       for c in sal_cursor loop

           --dbms_output.put_line(c.salary);

           v_sum_sal := v_sum_sal + c.salary;

       end loop;      

 

       dbms_output.put_line('sum salary: ' || v_sum_sal);

end;

 

2. 自定义一个存储过程完成以下操作:

对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 ?  -- 95 期间, 为其加薪 %5 95 – 98 %3  98 --  ?  %1

得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).

 

create or replace procedure add_sal_procedure(dept_id number, temp out number)

is

 

       cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;

       a number(4, 2) := 0;

begin

       temp := 0;      

 

       for c in sal_cursor loop

           a := 0;   

      

           if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then

              a := 0.05;

           elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then

              a := 0.03;

           else

              a := 0.01;

           end if;

          

           temp := temp + c.sal * a;

           update employees set salary = salary * (1 + a) where employee_id = c.id;

       end loop;      

end;