Oracle数据库常用sql语法

创建表

 create table table_name(
      column_name dataTpe primary key not null, 
      column_name dataType,
      ...
      );

-- 创建自增序列
CREATE SEQUENCE SEQUENCE_NAME
MINVALUE 1       --最小值
NOMAXVALUE       --不设置最大值
START WITH 1     --从1开始计数
INCREMENT BY 1   --每次加1
NOCYCLE          --一直累加，不循环
NOCACHE;         --不建缓冲区


-- 创建触发器实现数据插入时触发主键自动增长
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE INSERT ON TABLE_NAME FOR EACH ROW WHEN(NEW.PRIMARYKEY_COLUMN IS NULL)
BEGIN 
        SELECT SEQUENCE_NAME.NEXTVAL INTO:NEW.PRIMARYKEY_COLUMN FROM dual;
END;

修改表

 alter table table_name add (column_name dataType);   -- 增加新列
 alter table table_name modify (column_name dataType);    --修改原有列
 alter table table_name drop column column_name;   --删除列
 alter table table_name rename column column_name to new_column_name; -- 修改列名

删除表

drop table table_name;    -- 删除表

记录的增删改查

insert into table_name(column_name1,column_name2,......) values(value1,value2,......);  -- 插入数据

select column_name1,column_name2,...... from table_name [where 条件  group by column_name1 having 条件 order by desc];    -- 查询数据

select column_name1,...... from table_name where column_name like '......'； -- 模糊查询

delete from table_name where 条件;  -- 删除语句

update table_name 
set 
   column_name1 = value1,
   column_name2 = value2,
   ......
where 条件;   -- 修改语句

case when的用法

# 第一种用法(判断列值是否为null，只能用这种方式)
case  
when  列名= 条件值1   then  选项1
when  列名=条件值2    then  选项2.......
......
else    默认值 end

# 第二种用法
case 列名
when   条件值1   then  选项1
when   条件值2    then  选项2.......
......
else     默认值      end

exists关键字和in关键字

Exists只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。

In和exists对比：
若子查询结果集比较小，优先使用in(用in，oracle会优先查询子查询，然后匹配外层查询)，若外层查询比子查询小，优先使用exists(用exists，oracle会优先查询外层表，然后再与内层表匹配。)。因为根据最优化匹配原则，拿最小记录匹配大记录。

-- 关于Exist使用请注意
SELECT * FROM tableName A WHERE EXISTS (
    SELECT 1 FROM tableName B,tableName C WHERE A.NAME = B.NAME AND B.ID = C.ID);

-- 切勿写成以下方式
SELECT * FROM tableName A WHERE A.NAME EXISTS (
    SELECT A.NAME FROM tableName B,tableName C WHERE B.ID = C.ID); 

or

oracle中的or关键字具有"短路"原则,即使在where条件后面也成立.

结果如下:

只要mode的值为’01’那么就查询这条记录,不会再执行management是否为null的判断

oracle语句执行顺序

select 语法的处理顺序

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
      这些步骤执行时, 每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。

select各个阶级分别干了什么：
（1）FROM 阶段

FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中（各种join），主要有以下几个步骤：

  a.求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接（cross join），求笛卡儿积，生成虚拟表VT1-J1。

  b.ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。

  c.添加外部行。如果指定了outer join(内连接没有这步)，还需要将VT1-J2中没有找到匹配的行(外连接会有一个主表，这一步需要将被ON筛选器中筛选掉的主表中的数据插入进来)，作为外部行添加到VT1-J2中，生成VT1-J3。

经过以上步骤，FROM阶段就完成了。概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理（除了join，还有apply，pivot，unpivot）

（2）WHERE阶段

 WHERE阶段是根据<where_predicate>中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。

（3）GROUP BY阶段

  GROUP阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。最后每个分组只有一行。

（4）HAVING阶段

该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选，并将符合条件的组插入到VT4中。

（5）SELECT阶段

这个阶段是投影的过程，处理SELECT子句提到的元素，产生VT5。这个步骤一般按下列顺序进行

    a.计算SELECT列表中的表达式，生成VT5-1。

    b.若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2

    c.若有TOP，则根据ORDER BY子句定义的逻辑顺序，从VT5-2中选择签名指定数量或者百分比的行，生成VT5-3

（6）ORDER BY阶段

根据ORDER BY子句中指定的列明列表，对VT5-3中的行，进行排序，生成游标VC6.

example

表A：

表B：

查询语句：SELECT * FROM A LEFT JOIN ORACLE.B ON A.PERSON_ID=B.PERSON_ID AND A.PERSON_ID=1;
结果：

过程解释：

1. 先求笛卡儿积，产生5 * 5 = 25条数据，生成虚拟表VT1-J1如下：
   
2. ON筛选器：ON A.PERSON_ID=B.PERSON_ID AND A.PERSON_ID=1，生成虚拟表VT1-J2结果如下：
   
3. 添加外部行：A表为主表,将外部行添加到虚拟表VT1-J2中，生成虚拟表VT1-J3，如下：
   
   自此，from阶段的过程已完毕，最终的VT1-J3作为where阶段中的所说的VT1表。

即，oracle会先将所有表关联起来，然后再利用where条件筛选掉不满足要求的数据。而不是先用where条件筛选掉主表中不满足要求的数据。

oracle创建函数

CREATE OR REPLACE FUNCTION functionName(
    -- 传入参数
    param1 VARCHAR2,
    param2 VARCHAR2,
    ...
) RETURN VARCHAR2 AS
-- 函数内使用临时变量
temp1  VARCHAR2(50);
temp2 NUMBER(38,0);
BEGIN
  -- 函数体
  ...
  RETURN temp;
END;

-- 实际例子
CREATE OR REPLACE FUNCTION oracleFunction(
    -- 传入参数
    param1 VARCHAR2,
    param2 VARCHAR2
) RETURN VARCHAR2 AS
-- 函数内使用临时变量
temp1  VARCHAR2(50);
temp2 NUMBER(38,0);
BEGIN
  IF param1 IS NULL THEN
      RETURN '';
  ELSIF param2 = 'xxx' THEN
      temp1 := 'TO_CHAR( case when X.column1 = 1 then trunc(:1,X.column2))'
  END IF;
  someSql := 'select' || temp1 || 'RESULTS FROM (
      SELECT column1,column2, column3 FROM tableNAME
      WHERE column4 in (:2, :3)
      ) X
      WHERE column3 = 1';
  EXECUTE IMMEDIATE someSql INTO temp2 USING param1,'default',param2;
  -- USING 关键字后面的参数是替换上面的 “ :1, :2, :3 ”这些地方的值
  
  RETURN temp;
END;

oracle格式化数字

形式：TO_CHAR(NUM, ‘FM9990.0000’)

• 其中9代表：如果存在数字则显示数字，不存在则显示空格。
• 其中0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
• 其中FM代表：删除如果是因9带来的空格，则删除之。

SELECT TO_CHAR(123.45678, 'FM99990.0000') FROM DUAL    -- 返回结果为"123.4568"

SELECT TO_CHAR(123.45678, '99990.0000') FROM DUAL -- 返回结果为"空格空格123.4568"