首页 > 其他分享 >DWS(GAUSSDB)函数返回结果集(表)

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

时间:2024-08-25 17:28:01浏览次数:14  
标签:salary DWS 函数 GAUSSDB employee employees first id name

-----------建表------------
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

相关文章

  • Linux中的exec族函数
    exec系列函数用于替换当前进程的用户空间代码和数据,从而执行一个新的程序。调用exec系列函数不会创建新的进程,但会用新程序的代码和数据替换当前进程,因此调用exec后,进程的ID保持不变,但进程的行为变为执行新的程序exec系列函数有六个,分别是:execlintexecl(constcha......
  • [vue3] vue3 setup函数
    从语法上看,CompositionAPI提供了一个setup启动函数作为逻辑组织的入口,提供了响应式API,提供了生命周期函数以及依赖注入的接口,通过调用函数来声明一个组件。OptionsAPI选项式API在props、data、methods、computed等选项中定义变量;在组件初始化阶段,Vue.js内部处理这......
  • C++函数调用栈从何而来
    竹杖芒鞋轻胜马,谁怕?一蓑烟雨任平生~个人主页:rainInSunny | 个人专栏:C++那些事儿、Qt那些事儿目录写在前面原理综述x86架构函数调用栈分析如何获取rbp寄存器的值总结写在前面  程序员对函数调用栈是再熟悉不过了,无论是使用IDE调试还是GDB等工具进行调试,都离......
  • 最全!万字长文总结opencv-python常用函数(一)
    文章目录一,简介:二,图像的基础操作:2.1,图像的读取显示与保存2.1.1图像的读取cv2.imread:2.1.2图像的显示cv2.imshow与等待cv2.waitKey:2.1.3图像保存cv2.imwrite:2.2,图像属性获取:2.3,图像裁剪cv2.selectROI:2.4,图像通道的拆分cv2.split:2.5,图像通道的合并cv2.merge:三,图像的数值......
  • C++函数调用栈从何而来
    竹杖芒鞋轻胜马,谁怕?一蓑烟雨任平生~个人主页:rainInSunny | 个人专栏:C++那些事儿、Qt那些事儿文章目录写在前面原理综述x86架构函数调用栈分析如何获取rbp寄存器的值总结写在前面  程序员对函数调用栈是再熟悉不过了,无论是使用IDE调试还是GDB......
  • C语言函数介绍(上)
    函数概念库函数标准库和头文件库函数的使用方法头文件包含库函数文档的一般格式自定义函数函数的语法形式函数例子形参和实参实参形参实参和形参的关系return语句数组做函数参数函数概念数学中我们其实就见过函数的概念,比如:一次函数y=kx+b,k和b都是常数,给⼀个......
  • C++多态——虚函数
    其实在学习的时候,一直没有搞懂为什么要用虚函数,为什么需要传递基类的引用或者指针,要用谁的时候写谁不就好了。其实这时候我的思维还局限在面向过程编程,不是面向对象编程。现在搞明白了,因为多态,利用继承的思想,减少代码复用。我们来看下面的例子。#include<iostream>usingnamesp......
  • 算法的学习笔记—包含 min 函数的栈(牛客JZ30)
    ......
  • 每天五分钟深度学习:从数学角度分析逻辑回归算法损失函数的来源
    本文重点前面的课程中,我们只得到了逻辑回归算法模型的损失函数(如下所示),但是你是否知道它为什么是逻辑回归算法的损失函数?本文对其进行拆分,从0到1构建该损失函数,从数学角度分析为什么它是逻辑回归的损失函数.逻辑回归在逻辑回归算法中,我们需要预测y^,也就是说目标函数如下所......
  • HTMLTestRunner_cn测试报告不显示案例的函数注释解决方法
    找到生成函数注释的代码如下:def_generate_report_test(self,rows,cid,tid,n,t,o,e):#e.g.'pt1.1','ft1.1',etchas_output=bool(oore)#ID修改点为下划线,支持Bootstrap折叠展开特效-Findyoutid=(n==0and'p�......