首页 > 其他分享 >通用表表达式

通用表表达式

时间：2023-03-08 21:15:27浏览次数：41  
标签：NOTICE END name select 通用 id 表达式 RAISE

通用表表达式

通用表表达式（Common Table Expression、CTE）是一个临时的查询结果或者临时表，可以 在其他 SELECT、INSERT、UPDATE 以及 DELETE 语句中使用。通用表表达式只在当前语句中 有效，类似于子查询。

使用 CTE 的主要好处包括：

  • 提高复杂查询的可读性。CTE 可以将复杂查询模块化，组织成容易理解的结构。

  • 支持递归查询。CTE 通过引用自身实现递归，可以方便地处理层次结构数据和图数据。

--临时表t 一个字段为n 括号里是表的内容
with t(n) as(
	select 1
)
select * from  t;

-- 也可以定义多个
with t(n) as(
	select 1
),
t2(m) as(
	select 1
)
select * from  t cross join t2;

相当于一个变量，可以重复使用, 后面的临时表可以应用前面临时表的变量

with t(n) as(
	select 1
),
t2(m) as(
	select n+1 from  t
)
select * from  t cross join t2;

with子句

--查询每个部门的平均薪资
select d.department_name ,ds.avg_sal
from departments d 
join (select department_id,avg(salary) avg_sal from employees  group by department_id) ds 
on d.department_id =ds.department_id



with department_avg(department_id,avg_sal) as (
		select department_id,avg(salary) avg_sal from employees  group by department_id
)
select d.department_name ,department_avg.avg_sal
from departments d
join department_avg
on d.department_id =department_avg.department_id;

递归

递归 CTE 允许在它的定义中进行自引用，理论上来说可以实现任何复杂的计算功能，最常 用的场景就是遍历层次结构的数据和图结构数据。

WITH RECURSIVE cte_name AS(
 cte_query_initial -- 初始化部分
 UNION [ALL]
 cte_query_iterative -- 递归部分
) SELECT * FROM cte_name;
  • RECURSIVE 表示递归；
  • cte_query_initial 是初始化查询，用于创建初始结果集；
  • cte_query_iterative 是递归部分，可以引用 cte_name；
  • 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归并返回结果。

一个经典的递归 CTE 案例就是生成数字序列：

with recursive t(n) as (
	select 1   -- 初始化
	union all     
	select n+1 from t  where n<10 -- 递归
)
select * from t

-- 组织递归举例
select * from employees;

with recursive emp_path(employee_id,emp_name,path_name) as (
	select employee_id ,first_name||last_name ,first_name||last_name from employees where manager_id isnull 
	union all 
	select e.employee_id ,e.first_name||e.last_name ,path_name||'-->'||e.first_name||e.last_name from employees e join emp_path p on (e.manager_id=p.employee_id)
)
select * from emp_path

PL/pgSQL 存储过程

除了标准 SQL 语句之外，PostgreSQL 还支持使用各种过程语言（例如 PL/pgSQL、C、PL/Tcl、 PL/Python、PL/Perl、PL/Java 等 ）创建复杂的过程和函数，称为存储过程（Stored Procedure） 和自定义函数（User-Defined Function）。存储过程支持许多过程元素，例如控制结构、循环和 复杂的计算。

代码块结构

[ <<label>> ]
[ DECLARE
 declarations ]
BEGIN
 statements;
 ...
END [ label ];

其中，label 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定 变量的名称；DECLARE 是一个可选的声明部分，用于定义变量；BEGIN 和 END 之间是代码主 体，也就是主要的功能代码；所有的语句都使用分号（;）结束，END 之后的分号表示代码块结 束。

-- $$ 避免引号拼接 $$用于替换单引号（'），因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。

DO $$ 
DECLARE
 name text;
BEGIN 
 name := 'PL/pgSQL';
 RAISE NOTICE 'Hello %!', name;
END $$;

代码子块

DO $$ 
<<outer_block>>
DECLARE
 name text;
BEGIN 
 name := 'outer_block';
 RAISE NOTICE 'This is %', name;

	 DECLARE 
	 name text := 'sub_block';
	 BEGIN 
	 RAISE NOTICE 'This is %', name;
	 RAISE NOTICE 'The name FROM the outer block is %', outer_block.name;
	 END;
	
  RAISE NOTICE 'This is %', name;
 
END outer_block $$;

声明与赋值

1.变量

在使用变量之前，需要在代码的声明部分进行声明：

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';

do $$
declare 
	id integer;
	price numeric(5,2) default 0.6;
	name text;
begin
	id :=1;
	name :='Postgresql';
	raise notice 'id : %' ,id;
	raise notice 'price : %' ,price;
	raise notice 'name : %' ,name;
end;
$$

除了基本的 SQL 数据类型之外，PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量：

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;

常量

DO $$ 
DECLARE
 PI CONSTANT NUMERIC := 3.14159265;
 radius NUMERIC;
BEGIN 
 radius := 1.0;
 RAISE NOTICE 'The area is %', PI * radius * radius;
END $$;
NOTICE: The area is 3.1415926500

if语句

IF 语句可以基于条件选择性执行操作， PL/pgSQL 提供了三种形式的 IF 语句。

  • IF … THEN … END IF
  • IF … THEN … ELSE … END IF
  • IF … THEN … ELSIF … THEN … ELSE … END IF
DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大于 3';
 END IF;
 IF 2 < 3 THEN
 RAISE NOTICE '2 小于 3';
 END IF;
END $$;
NOTICE: 2 小于 3



DO $$
BEGIN 
 IF 2 > 3 THEN
 RAISE NOTICE '2 大于 3';
 ELSE
 RAISE NOTICE '2 小于 3';
 END IF;
END $$;
NOTICE: 2 小于 3


DO $$
DECLARE
 i integer := 3;
 j integer := 3;
BEGIN 
 IF i > j THEN
 RAISE NOTICE 'i 大于 j';
 ELSIF i < j THEN
 RAISE NOTICE 'i 小于 j';
 ELSE
 RAISE NOTICE 'i 等于 j';
 END IF;
END $$;
NOTICE: i 等于 j
DO

case语句

DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE i
 WHEN 1, 2 THEN
 RAISE NOTICE 'one or two';
 WHEN 3, 4 THEN
 RAISE NOTICE 'three or four';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;



DO $$
DECLARE
 i integer := 3;
BEGIN 
 CASE 
 WHEN i BETWEEN 0 AND 10 THEN
 RAISE NOTICE 'value is between zero and ten';
 WHEN i BETWEEN 11 AND 20 THEN
 RAISE NOTICE 'value is between eleven and twenty';
 ELSE
 RAISE NOTICE 'other value';
 END CASE;
END $$;

循环语句

PostgreSQL 提供 4 种循环执行命令的语句：LOOP、WHILE、FOR 和 FOREACH 循环，以 及循环控制的 EXIT 和 CONTINUE 语句。

loop

  • EXIT 语句用于退出循环。

  • CONTINUE 表示忽略后面的语句

DO $$
DECLARE
 i integer := 0;
BEGIN 
 LOOP
 EXIT WHEN i = 5;
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

while

DO $$
DECLARE
 i integer := 0;
BEGIN 
 WHILE i < 5 LOOP
 i := i + 1;
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

FOR 循环

DO $$
BEGIN 
 FOR i IN 1..5 BY 2 LOOP
 RAISE NOTICE 'Loop: %', i;
 END LOOP;
END $$;

NOTICE: Loop: 1
NOTICE: Loop: 3
NOTICE: Loop: 5


FOR 循环默认从小到大进行遍历
REVERSE 表示从大到小遍历；BY 用于指定每次的增量，默认为 1。

遍历查询结果集的 FOR 循环如下

[ <<label>> ]
FOR target IN query LOOP
 statements
END LOOP [ label ];

DO $$
DECLARE
 emp record;
BEGIN 
 FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP
 	RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name;
 END LOOP;
END $$;

NOTICE: Loop: Steven,King
NOTICE: Loop: Neena,Kochhar
NOTICE: Loop: Lex,De Haan
NOTICE: Loop: Alexander,Hunold
NOTICE: Loop: Bruce,Ernst

FOREACH 循环 ( 数组 )

DO $$
DECLARE
 x int;
BEGIN
 FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'x = %', x;
 END LOOP;
END $$;
NOTICE: x = 1
NOTICE: x = 2
NOTICE: x = 3
NOTICE: x = 4
NOTICE: x = 5
NOTICE: x = 6

DO $$
DECLARE
 x int[];
BEGIN
 FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
 LOOP
 RAISE NOTICE 'row = %', x;
 END LOOP;
END $$;
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}

标签：NOTICE,END,name,select,通用,id,表达式,RAISE
From： https://www.cnblogs.com/liurui12138/p/17196263.html

相关文章

  • python FastAPI sqlalchemy 数据库模型基类通用模型
    作用用于所有表都需要使用的字段或者方法实现代码base.py#!/usr/bin/python#-*-coding:utf-8-*-#@time:2023/2/1317:43#@author:pugongying#@de......
  • 正则表达式
    正则表达式正则表达式解释：正则表达式（英语：regularExpression,常简写为regex、regexp或RE）,又称正则表达式、正则表示法、规则表达式、常规表示法，是计算机的一个概念。......
  • 实验1 C语言开发环境使用和数据类型、运算符、表达式
    //打印一个字符小人#include<stdio.h>intmain(){printf("o\n");printf("<H>\n");printf("II\n");return0;}#include<stdio.h>......
  • 记录一下C#中使用正则表达式
    privatestaticvoidMain(string[]arg){vartext="";//文本读取器using(TextReaderreader=newStreamR......
  • for循环原理补充、生成器对象、yield冷门用法、生成器表达式的面试题、常见内置函数
    目录一、for循环原理补充二、生成器对象(1)、自定义生成器对标range功能(一个参数两个参数三个参数迭代器对象)三、yield冷门用法(1)、yield与return的对比四、生成器表......
  • 递归表达式系列
    递归函数：什么是递归函数：就是直接或者间接的调用自己"""递归:1.递推逐层寻找答案2.回溯根据最终的答案推导出最原始的答案3.递归函......
  • 通用缓存存储方案设计
    目录介绍01.整体概述说明1.1项目背景介绍1.2遇到问题记录1.3基础概念介绍1.4设计目标1.5产生收益分析02.市面存储方案2.1缓存存储有哪些2.2缓存策略有哪些2.3常见......
  • 常用正则表达式
     1、数字1、数字：^[0-9]*$2、n位的数字：^\d{n}$3、至少n位的数字：^\d{n,}$4、m-n位的数字：^\d{m,n}$5、零和非零开头的数字：^(0|[1-9][0-9]*)$6、非零开头的最多......
  • 未定义符号的链接问题通用解决方法
    在linux下做c/c++开发时，经常遇到一个问题，就是如果代码引用了大量的第三方库，链接的时候常忘记或者根本就不知道具体要链接哪个库，导致链接的时候报未定义的引用，特别是一些库......
  • C# lambda 表达式
    C#lambda表达式lambda函数是一个小的匿名函数。lambda函数可以接受任意数量的参数，但只能具有一个表达式。本文主要介绍C#lambda表达式。 1、C#lambda表......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99