-----------建表------------
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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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', '[email protected]', '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;
标签:salary,DWS,函数,GAUSSDB,employee,employees,first,id,name From: https://www.cnblogs.com/shengkai126126/p/18379167