oracle-存储过程&函数

存储过程

一、函数

1.1 函数格式

-- 存储函数
create or reeplace function func_name(dept_id number,salary number)
return number
is
       -- 声明变量、记录类型、游标
begin 
       -- 函数执行体
exception
       -- 异常处理
end;

1.2 定义函数

1. helloworld函数

create or replace function hello_world
return varchar2
is 
begin
  return 'hello';
end;

2. 执行函数
   
3. 打印数据

SQL> ed
begin 
  dbms_output.put_line(hello_world);
end;
SQL> /
-- 或者
SQL> select hello_world from dual;

4. 补充

回复： 为什麽dbms_output.put_line('hello')不打印?
sql> show all
serveroutput OFF
sql> set serveroutput on
sql> exec

1.3 函数详细

1. 带参函数

create or replace function hello_world(v_logo varchar2)
return varchar2
is 
begin
  -- v_logo := 'other';
  return 'hello '||v_logo;
end;
SQL> select hello_world('hhhh') from dual;

2. 定义变量函数

create or replace function get_sysdate
return date
is
       v_date date;
begin
       v_date := sysdate;
       return v_date;
end;

3. 多个参数的函数

create or replace function add_param(v_num1 number,v_num2 number)
return number
is
       v_sum number;
begin
       v_sum := v_num1 + v_num2;
       return v_sum;
end;

4. 定义变量多个（使用游标的函数）

-- 获取给定部门工资总和：参数是部门id,返回值是工资总和
create or replcae function get_sal(dept_id number)
return number
is
       v_sumsal number(10) :=0;
       cursor salary_cursor is select salary from employess where department_id = dept_id 
begin
       for c in salary_cursor loop
          v_sumsal := v_sumsal + c.salary;
       end loop;
       return v_sumsal;
end;

--- 调用
declare 
  v_dept_id number(4) := 80; 
begin 
  dbms_output.put_line(get_sal(v_dept_id));
end;

5. 多个返回值

-- 获取给定部门工资总和以及员工总人数 （定义为out类型参数）
-- 部门号是参数，工资总和是返回值
create or replace function get_sql(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(get_sal(80,v_num));
    dbms_output.put_line(v_num);
end;

二、存储过程

2.1 函数格式

create or replcae procedure get_sal(dept_id number,sumsal out number)
is
      -- 声明变量
begin
	   -- 执行体
	   -- 不需要返回
end;

2.2 存储过程

1. 多个变量存储过程

-- 获取制定部门的工资总和
create or replcae procedure get_sal(dept_id number,sumsal out number)
is
       v_sumsal number(10) :=0;
       cursor salary_cursor is select salary from employess where department_id = dept_id 
begin
	   sumsal := 0;
       for c in salary_cursor loop
          sumsal := sumsal  + c.salary;
       end loop;
       dbms_output.put(sumsal);
end;

--- 调用
declare 
  v_sal number(4) := 0; 
begin 
  get_sal(80,v_sal);
end;

2. 复杂执行体

-- 对给定部门（作为输入参数）的员工进行加薪操作，若其到公司时间在(7,95) {95,98) [98,?) 45,43,41
-- 得到以下返回结构：为此加薪公司需要每月额外付出多少成本（定义一个out型输出参数）
create or replace procedure add_sal(dept_sal number,temp_sql out number)
is
	cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;
	v_i number(4,2) := 0;
begin
	-- 赋值
	temp_sql := 0;
	for c in sal_cursor loop
		-- 如果字符串是纯数字存在一个隐形转换
		if to_char(c.hire_date,'yyyy') < '1999' then v_i := 0.05;
		elseif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;
		else v_i := 0.01;
		end if;
		--- 更新工资
		update employees set salary = salary * (1+v_i) where employee_id = c.employee_id;
		--- 付出成本
		temp_sql := temp_sql + c.salary * v_i;
	end loop;
end;
--- 调错
--- show errors;

3 循环

create or replace procedure genoid 
as
       n_count number := 0;
begin
  while n_count < 10 loop
    dbms_output.put_line(n_count);
    n_count := n_count + 1;
  end loop;
commit;
end;

2.3 删除

删除存储过程 DROP PROCEDURE 存储过程名字
删除函数 DROP FUNCTION 函数名字
删除触发器 DROP TRIGGER 触发器名字