时间戳

首页 > 解决方案 > 在函数pl sql oracle中返回两个varchar

sql - 在函数pl sql oracle中返回两个varchar

问题描述

所以我想在oracle中创建一个函数。用户将输入部门名称和雇用日期的年份。然后我想返回员工姓名列表。但是我怎样才能返回函数中员工的名字和姓氏？这是我到目前为止所得到的。我收到一个错误“查询必须以 SELECT 或 WITH 开头”。任何想法？谢谢！

type emp_type is record
(v_name1 employees.first_name%type, v_name2 employees.last_name%type);
create or replace function get_employee
 (P_IN_DEPT_NAME IN departments.department_name%type, P_IN_YEAR IN NUMBER)
return emp_type as emp_record emp_type;
BEGIN
   For D in (SELECT e.first_name, e.last_name into emp_record.v_name1,emp_record.v_name2
               FROM employees e
               join departments d
                 on e.department_id = d.department_id 
              where d.department_name = P_IN_DEPT_NAME 
                and extract(year from e.hire_date) = P_IN_YEAR)
                loop
        return emp_record;
        end loop;
END;
end;
/

标签： sqloracleplsql

解决方案

不知道将在哪里使用此功能，但这是您问题的另一个答案：

-- First creating the function. It will return SYS_REFCURSOR.
create or replace function get_employee
 (P_IN_DEPT_NAME IN departments.department_name%type, P_IN_YEAR IN NUMBER)
return sys_refcursor
is
    v_cursor sys_refcursor;
BEGIN
open v_cursor for 
   SELECT e.first_name, e.last_name
               FROM employees e
               join departments d
                 on e.department_id = d.department_id 
              where d.department_name = P_IN_DEPT_NAME 
                and extract(year from e.hire_date) = P_IN_YEAR;
        return v_cursor;
END;


-- Then main code that will call function and outpur result:

declare
    v_first_name employees.firsT_name%type;
    v_last_name employees.last_name%type;
    v_res sys_refcursor;
begin
    v_res := get_employee('Purchasing', 2005); -- 'Purchasing' and 2005 are just examples
    loop
        fetch v_res into v_first_name, v_last_name;
        exit when v_res%Notfound;
        dbms_output.put_line(v_first_name || ' ' ||v_last_name);
    end loop;
end;

谢谢。

推荐阅读