oracle使用with关键字定义变量保存结果集

定义变量保存全部小程序的门店Id，定义变量名称为applet_depot_id

WITH applet_depot_id AS (SELECT  depot_id  FROM  "X6_ARCHIVES_GLS"."A_PARTNER_DEPOT"  WHERE extand_property_three='1')

使用变量名称

SELECT depot_id FROM applet_depot_id

with同时创建多个变量

WITH 
   cte1 AS (
      SELECT last_name FROM employees WHERE department_id = 20
   ),
   cte2 AS (
      SELECT salary FROM employees WHERE department_id = 30
   )
SELECT COUNT(*) INTO v_count FROM employees WHERE last_name IN (SELECT last_name FROM cte1) AND salary IN (SELECT salary FROM cte2);