PLSQL存储过程及存储函数

关于Struts2 + spring + hibernate进Q群: 130529143交流。
有偿技术支持Q群：398162181 

1.存储过程与存储函数的区别：

	存储过程无返回值，存储函数有返回值。

2.存储过程：
demo1:

declare 
  --声明一个记录类型
  type emp_record is record(
    v_sal employees.salary%type,--动态的获取变量的类型
    v_email varchar(20),
    v_hire_date date
    );
  --定义一个记录类型的成员变量
  v_emp_record emp_record;

begin 
--程序的执行部分
select salary, email, hire_date into v_emp_record from employees where employee_id = 100;

dbms_output.put_line(v_emp_record.v_sal || ',' || v_emp_record.v_email || ',' || v_emp_record.v_hire_date);

--exception  
end;

demo2:
 
 

  
  declare 
  type salary_record is record(
       v_name varchar2(20),
       v_salary number(10, 2)
  );
  v_sal_record salary_record;
 
 
 
 

  
  
begin 
  v_sal_record.v_name := '老王';
  v_sal_record.v_salary := 20000;
  dbms_output.put_line(v_sal_record.v_name || ',' || v_sal_record.v_salary);
--exception 
end;

 
 

demo3:

declare 
  type salary_record is record(
       v_name varchar2(20),
       v_salary employees.salary%type
  );
  
  v_sal_record salary_record;


begin 
  v_sal_record.v_name := '老王';
  v_sal_record.v_salary := 20000;
  dbms_output.put_line(v_sal_record.v_name || ',' || v_sal_record.v_salary);
--exception 
end;

demo4:

declare 
  v_sal employees.salary%type;
  v_temp varchar2(30);


begin 
  select salary into v_sal from employees where employee_id = 150;
  v_temp := 
  case trunc(v_sal/5000) when 0 then 'salary<5000'
                         when 1 then '5000<=salary<10000'
                         else 'salary>=15000'
  end;
  dbms_output.put_line( v_temp );
--exception 
end;

demo5:

--流程控制

declare 
  v_sal employees.salary%type;
  v_temp varchar2(30);


begin 
  select salary into v_sal from employees where employee_id = 150;
  v_temp := 
  case trunc(v_sal/5000) when 0 then 'salary<5000'
                         when 1 then '5000<=salary<10000'
                         else 'salary>=15000'
  end;
  dbms_output.put_line( v_temp );
--exception 
end;

demo6:

--存储过程开启
set serveroutput on
--存储过程无返回值
create or replace procedure salary_sum1(dept_id number, v_sumsal out number) 
is 
       cursor salary_cursor is select salary from employees where department_id = dept_id;
begin 
      v_sumsal := 0;
      for c in salary_cursor loop 
        v_sumsal := v_sumsal + c.salary; 
      end loop;
      dbms_output.put_line(v_sumsal);
end;

--调用存储过程
declare 
  v_sumsal number(10) := 0;
begin
  salary_sum1(80, v_sumsal); 
end;

demo7:

--给指定部门的，按入职时间加薪，公司每月多支出多少
create or replace procedure add_sal(dept_id number, temp out number)
is 
       cursor sal_cursor is select employee_id, salary, hire_date  
              from employees where department_id = dept_id;
       v_i number(5,2) := 0;
begin 
       temp := 0;
       for c in sal_cursor loop
          if to_char(c.hire_date, 'yyyy') < '1995' then v_i := 0.05;
          elsif to_char(c.hire_date, 'yyyy') < '1998' then v_i := 0.03;
          else  v_i := 0.01;
          end if;
          
          --1.更新工资
          update employees set salary = salary + v_i * salary where employee_id = c.employee_id;
          
          --2.付出的成本
          temp :=  temp + c.salary * v_i;
        end loop;
end;

--调用存储过程
declare 
  v_temp number(10) := 0;
begin 
  add_sal(80, v_temp);
  dbms_output.put_line(v_temp);
end;

2.存储函数：
demo1:
--存储函数：有返回值。
create or replace function func_name(dept_id number, salary number)
return number 
is 
       --函数使用过程中，需要声明的变量、记录类型、cursor
begin
       --函数的执行体    
exception 
       --处理函数执行过程中的异常   
end;  

demo2:
   
   

    
    
   
   
create or replace function hello_world 
return varchar2 
is 
   --函数使用过程中，需要声明的变量、记录类型、cursor
begin
       --函数的执行体
       dbms_output.put_line('hello world!');
exception 
       --处理函数执行过程中的异常
end;  
--调用上面存储函数
begin 
  dbms_output.put_line(hello_world);
end;

--调用上面存储函数
select hello_world from dual;
demo3:
--带参函数
create or replace function hello_world1(v_name varchar2) 
return varchar2 
is 
begin 
  return ('hello! ' || v_name);
end;
--
--调用带参函数
select hello_world1('lixuan') from dual；

demo4:
--创建存储函数返回当前系统时间
create or replace function get_sysdate 
return date 
is 
       v_date date;
begin 
  v_date := sysdate;
  return v_date;
end;

--调用上面这个函数
begin 
  dbms_output.put_line(get_sysdate);
end; 


demo5:
--指定部门的工资总和
create or replace function salary_sum(dept_id number) 
return number 
is 
       v_sumsal number(10) := 0;
       cursor salary_cursor is select salary from employees where department_id = dept_id;
begin 
  for c in salary_cursor loop 
    v_sumsal := v_sumsal + c.salary;  
  end loop;
  return v_sumsal;
end;

--调用
select salary_sum(80) from dual；

demo6:

--含有out 参数的函数:传入的out参数值会被改变，in参数不能改变
create or replace function salary_sum(dept_id number, total_count out number) 
return number 
is 
       v_sumsal number(10) := 0;
       cursor salary_cursor is select salary from employees where department_id = dept_id;
begin 
  total_count := 0;
  for c in salary_cursor loop 
    v_sumsal := v_sumsal + c.salary;
    total_count := total_count + 1;  
  end loop;
  return v_sumsal;
end;

--调用函数
declare 
  v_num number(5) := 0;
begin 
  dbms_output.put_line(salary_sum(80, v_num)); 
  dbms_output.put_line(v_num); 
end;

关于Struts2 + spring + hibernate进Q群: 130529143交流。
有偿技术支持Q群：398162181