ORACLE语句

with as

WITH
        TMP_1 AS (SELECT CITY FROM BBBBBBBBBBBBBB B),
        TMP_2 AS (SELECT BSFWR FROM TMP_1 A INNER JOIN AREAS B ON A.CITY = B.AREA_NAME)
    SELECT BSFWR
      FROM TMP_2

CASE WHEN

case when相当于if

select id,
CASE WHEN AGE>0 OR AGE<10 THEN '大于0小于10' else '不大于0不小于10' end as age from demp
这个case when相当于java的if，多个条件就case when ---  case when --- end就表示结束了
if(age>0||age<10){
	就输出大于0小于10
}else{
	输出不大于0不小于10
}

一次执行多条sql

mybaits也可以直接执行

begin
	delete from employee where name='zhangsan';
	delete from dept where name='caiwu';
end;

查指定表的字段和注释

Employee为表名

select 
      ut.COLUMN_NAME,--字段名称
      uc.comments,--字段注释
      ut.DATA_TYPE,--字典类型
      ut.DATA_LENGTH,--字典长度
      ut.NULLABLE--是否为空
from user_tab_columns  ut
inner JOIN user_col_comments uc
on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.Table_Name='Employee' 
order by ut.column_name

查所有表的字段和注释

SELECT * FROM ALL_COL_COMMENTS;

批量修改表字段注释

SELECT 'COMMENT ON COLUMN '||TABLE_NAME||'.'||COLUMN_NAME||' IS ''无注释'';' from ALL_COL_COMMENTS where owner='DDFRAME' AND table_name like 'T_COST%' AND COMMENTS IS NULL;

通过条件筛选，拼接成sql语句
以下就是结果，拿去执行就可以修改了

grouping sets

相当于使用union all多条语句
grouping sets(a,(a,b))相当于第一条语句的group by是a，第二条语句的group by是a,b，前提是他们查询参数的数量要一样，可以使用Null代替

select a,null b from temp1 where nf=2020 group by a
union all 
select a,b from temp1 where nf=2020 group by a,b

grouping sets改造后

select a,b from temp1 where nf=2020 group by grouping sets (a,(a,b)) order by nf

来源https://www.cnblogs.com/shiliye/p/10518151.html

SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
UNION ALL
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;
等价于
SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);


GROUP BY GROUPING SETS (A,B,C)  等价与  GROUP BY A  
                                        UNION ALL  
                                        GROUP BY B  
                                        UNION ALL  
                                        GROUP BY C
                                        
                                        
GROUP BY GROUPING SETS ((A,B,C))  等价与  GROUP BY A,B,C  
   
   
GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A  
                                          UNION ALL  
                                          GROUP BY B,C

                                        
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
        ,GROUPING SETS (B)  
        ,GROUPING SETS (C)  
   
   
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
        ,GROUPING SETS ((B,C))   
   
   
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B  
        ,GROUPING SETS (B,C)        UNION ALL  
                                    GROUP BY A,C
                                    
                                    
GROUP BY A                     等价于  GROUP BY A  
        ,B                                     ,B  
        ,GROUPING SETS ((B,C))                 ,C  
   
   
GROUP BY A                    等价于  GROUP BY A,B,C  
        ,B                            UNION ALL  
        ,GROUPING SETS (B,C)          GROUP BY A,B  
   
   
GROUP BY A                    等价于 GROUP BY A,B,C  
        ,B                           UNION ALL  
        ,C                           GROUP BY A,B,C  
        ,GROUPING SETS (B,C)

行转列

查询出的数据

SELECT F_CONTENT_TYPE type, F_CONTENT_PATH path, F_CL_CODE code FROM TEST_CONTENT

把type转到横排作为表头，要使用子查询，先查出需要的数据，然后通过pivot转换，type是要转的字段，作为表头，sum(path)并没有计算，是要展示的数据，code这一列实在最左边，这里type是写死的，如果要动态转换，需要用到存储过程

SELECT
	* 
FROM
	( SELECT F_CONTENT_TYPE type, F_CONTENT_PATH path, F_CL_CODE code FROM TEST_CONTENT ) pivot ( sum( path ) FOR type IN ( 2, 3, 4 ) )

PL/SQL语句块

方括号部分是可选的

[DECLARE]
/*声明部分：在这个PL/SQL用到的变量、类型及游标等*/
BEGIN
/*执行部分：过程及SQL语句，SQL主体*/
[EXCEPTION]
/*异常处理：错误处理*/
END;

DECLARE 	-- 声明变量
name VARCHAR2 ( 30 );
BEGIN	-- 语句块开始

	SELECT '张三' INTO newName FROM dual;	--into赋值
	
	DBMS_OUTPUT.PUT_LINE ( name );		--输出name
	
	EXCEPTION 		-- 异常
		WHEN no_data_found THEN		
	DBMS_OUTPUT.PUT_LINE ( '错误' );	
	
END;	--语句块结束

begin中可嵌套begin

DECLARE 		
/* 
	声明变量，
	
	字段类型可以自己指定name VARCHAR2 ( 30 )，也可以使用 表名.字段%type 和 表名%rowtypede 的方式
	
	表名.字段%type ，意思是字段类型和这个表中的某个字段类型一致，
		例如 name employee.name%type,name的类型和employee表中的name字段类型一致
	表名%rowtype，返回的就是一整行数据，例如： emp employee%rowtype,使用的时候就是emp.name或emp.age这种
*/
name VARCHAR2 ( 30 );
newName VARCHAR2 ( 30 );
BEGIN	-- 语句块开始
	
	BEGIN		-- 子块
		SELECT '李四' INTO name FROM dual;	--into赋值
	END;

	SELECT '张三' INTO newName FROM dual;	--into赋值
	
	DBMS_OUTPUT.PUT_LINE ( name );
	DBMS_OUTPUT.PUT_LINE ( newName );
	
	EXCEPTION 
		WHEN no_data_found THEN
	DBMS_OUTPUT.PUT_LINE ( '错误' );	
	
END;	-- 语句块结束

赋值

两种方式,一种:=，一种into

DECLARE 
name VARCHAR2 ( 30 );
newName VARCHAR2 ( 30 );
BEGIN

  name:='你好';		-- := 赋值
	DBMS_OUTPUT.PUT_LINE ( name );

  SELECT '张三','李四' INTO name,newName FROM dual;		-- into赋值，可以按照顺序赋值多个
  
  DBMS_OUTPUT.PUT_LINE ( name );
  DBMS_OUTPUT.PUT_LINE ( newName );

  
END;