函数和过程

--===================================================================
--创建函数get_date，返回一个固定格式(yyyy-mm-dd)的当前日期时间字符串
--===================================================================
create or replace function get_date
return varchar2
is
  v_date varchar2(50);
begin
  v_date := to_char(sysdate,'yyyy-mm-dd');
  return v_date;
end;

--测试
begin
  dbms_output.put_line(get_date);
end;

--=======================================================
/*
创建函数get_name，传入一个员工编号，返回此员工的姓和名
例如Steven.King，如果员工不存在，返回“查无此人”
*/
--=======================================================
create or replace function get_name(p_empid number)
return varchar2
is
  v_name varchar2(100);
begin
  select first_name||'.'||last_name into v_name
  from employees
  where employee_id=p_empid;
  
  return v_name;
exception
  when no_data_found then
    return '查无此人';
end;

--测试
declare
  v_name varchar2(100);
begin
  v_name := get_name(100);
  dbms_output.put_line(v_name);
end;

--=====================================================
--创建函数get_names，传入一个部门编号，返回这个部门所有员工的last-name
--形成一个字符串，名字用逗号格开，例如tom,jack,rose
--=====================================================
create or replace function get_names
    (p_deptid employees.department_id%type)
return varchar2
is
  v_names varchar2(4000);
  cursor c_names is 
     select last_name from employees where department_id=p_deptid;
begin
  for e in c_names loop
      v_names := v_names || e.last_name || ',';
  end loop;
  
  return trim(',' from v_names);
end;

--测试
begin
  dbms_output.put_line(get_names(90));
end;

--============================================================
--创建函数add_salary，传入员工编号，金额，给某员工加工资
--返回加完工资，这个员工现在的工资是多少？
--============================================================
create or replace function add_salary
  (p_empid number,p_money number)
return number
is
  v_salary number;
begin
  update employees set salary=salary+p_money
  where employee_id=p_empid;
  
  select salary into v_salary from employees
  where employee_id=p_empid;
  
  commit;
  
  return v_salary;
end;
--测试
begin
  dbms_output.put_line(add_salary(100,2));
end;

--===============================================================
--创建函数get_empcount，传入一个员工编号，返回员工的手下个数
--===============================================================
create or replace function get_empcount(p_empid number)
return number
is
  v_count number := 0;--总人数
  
  cursor c_emp is select * from employees where manager_id=p_empid;
begin
  for e in c_emp loop
    v_count := v_count + 1 + get_empcount(e.employee_id);
  end loop;
  
  return v_count;
end;
--测试
declare
  v_count number;
begin
  v_count := get_empcount(101);
  dbms_output.put_line(v_count);
end;

--=============================================================
--创建函数get_result，传入两个参数，返回相加结果和相减结果
--=============================================================
create or replace function get_result
(a number,b number,result2 out number,result3 out number)
return number
is
  v_add number;
begin
  v_add := a + b; 
  result2 := a - b;
  result3 := a * b;
  return v_add;
end;

--测试
declare
  v_result2 number;
  v_result3 number;
begin
  dbms_output.put_line(get_result(5,3,v_result2,v_result3));
  dbms_output.put_line(v_result2);
  dbms_output.put_line(v_result3);
end;

--=====================================================================
--创建函数get_result，传入两个参数，返回相加结果和相减结果和相乘结果
--=====================================================================
create or replace function get_result
(a in out number,b in out number)
return number
is
  v_add number;
  v_a number := a;
  v_b number := b;
begin  
  v_add := v_a + v_b;
  a := v_a - v_b;
  b := v_a * v_b;
  
  return v_add;
end;

--测试
declare
  v_result1 number := 5;
  v_result2 number := 3;
begin
  dbms_output.put_line(get_result(v_result1,v_result2)); 
  dbms_output.put_line(v_result1); 
  dbms_output.put_line(v_result2); 
end;

--=====================================================================
--创建函数get_deptinfo，传入部门编号，返回人数，工资总额，人均工资
--=====================================================================
create or replace function get_deptinfo
(p_deptid number default 90,p_sum out number,p_avg out number)
return number
is
  v_count number;
begin
  select count(*),sum(salary),avg(salary) into
         v_count,p_sum,p_avg
  from employees
  where department_id=p_deptid;
  
  return v_count;
end;

--测试
declare
  v_sum number;
  v_avg number;
begin
  dbms_output.put_line(get_deptinfo(50,v_sum,v_avg));
  dbms_output.put_line(v_sum||','||v_avg);
end;

--测试2
declare
  v_sum number;
  v_avg number;
begin
  dbms_output.put_line
      (get_deptinfo(p_sum=>v_sum,p_deptid=>50,p_avg=>v_avg));
  dbms_output.put_line(v_sum||','||v_avg);
end;