DWS(GAUSSDB)函数返回结果集(表)

-----------建表------------
drop table if exists employees;

CREATE TABLE employees (

employee_id NUMBER(10) PRIMARY KEY, -- Employee ID, primary key

first_name VARCHAR2(50), -- Employee's first name

last_name VARCHAR2(50), -- Employee's last name

email VARCHAR2(100) NOT NULL, -- Employee's email, required

phone_number VARCHAR2(20), -- Employee's phone number

hire_date DATE NOT NULL, -- Date of hire, required

job_id VARCHAR2(10) NOT NULL, -- Job ID, required

salary NUMBER(8, 2), -- Employee's salary

commission_pct NUMBER(2, 2), -- Commission percentage

manager_id NUMBER(10), -- Manager's ID, foreign key to employee_id

department_id NUMBER(10)

) WITH (orientation = column);

-----------初始化数据------------

INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1001, 'John', 'Doe', 'jdoe@example.com', '555-1234', TO_DATE('2022-01-15', 'YYYY-MM-DD'), 'IT_PROG', 60000, NULL, 1003, 10); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1002, 'Jane', 'Smith', 'jsmith@example.com', '555-5678', TO_DATE('2021-03-22', 'YYYY-MM-DD'), 'HR_REP', 45000, NULL, 1003, 20); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1003, 'Michael', 'Johnson', 'mjohnson@example.com', '555-8765', TO_DATE('2019-11-05', 'YYYY-MM-DD'), 'FIN_MGR', 85000, NULL, NULL, 10); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1004, 'Emily', 'Davis', 'edavis@example.com', '555-4321', TO_DATE('2020-07-10', 'YYYY-MM-DD'), 'IT_PROG', 62000, NULL, 1003, 10);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1005, 'William', 'Brown', 'wbrown@example.com', '555-6543', TO_DATE('2018-05-30', 'YYYY-MM-DD'), 'SA_REP', 50000, 0.10, 1006, 30); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1006, 'Sophia', 'Wilson', 'swilson@example.com', '555-3456', TO_DATE('2017-08-21', 'YYYY-MM-DD'), 'SA_MGR', 90000, NULL, NULL, 30);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1007, 'James', 'Taylor', 'jtaylor@example.com', '555-9876', TO_DATE('2021-10-12', 'YYYY-MM-DD'), 'HR_REP', 47000, NULL, 1003, 20); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1008, 'Olivia', 'Martinez', 'omartinez@example.com', '555-2345', TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'IT_PROG', 61000, NULL, 1003, 10);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1009, 'tom', 'Taylor', 'jtaylor@example.com', '555-9876', TO_DATE('2021-10-12', 'YYYY-MM-DD'), 'HR_REP', 98000, NULL, 1003, 20); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1010, 'hank', 'Martinez', 'omartinez@example.com', '555-2345', TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'IT_PROG', 10000, NULL, 1003, 10); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1011, 'hank', 'Martinez', 'omartinez@example.com', '555-2345', TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'IT_PROG', 10000.01, NULL, 1003, 10); 
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (1012, 'hank', 'Martinez', 'omartinez@example.com', '555-2345', TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'IT_PROG', 100000.01, NULL, 1003, 10); 

------------创建函数，两种方式均可------------

drop function if exists get_employees_by_salary_collection;

CREATE OR REPLACE FUNCTION get_employees_by_salary_collection(

p_min_salary NUMERIC,

p_max_salary NUMERIC

) RETURNS SETOF employees AS $$

SELECT *

FROM employees

WHERE salary BETWEEN p_min_salary AND p_max_salary;

$$ LANGUAGE sql;

 

CREATE OR REPLACE FUNCTION get_employees_by_salary_collection4pg(

p_min_salary NUMERIC,

p_max_salary NUMERIC

) RETURNS SETOF employees AS $$

BEGIN

RETURN QUERY

SELECT *

FROM employees

WHERE salary BETWEEN p_min_salary AND p_max_salary;

END;

$$ LANGUAGE plpgsql;

-----------------使用函数-----------------

SELECT t1.*, t2.first_name as xxx FROM get_employees_by_salary_collection(0, 100000) t1 left join employees t2 on t1.employee_id = t2.employee_id and t2.first_name = 'John';

SELECT t1.*, t2.first_name as xxx FROM get_employees_by_salary_collection4pg(0, 100000) t1 left join employees t2 on t1.employee_id = t2.employee_id;