ORACLE疑难点

归零

一、oracle没有create or replace table

Oracle数据库和其他数据库（比如MySQL）在新建数据表的时候有以下区别：

SQL> create or replace table testTb;  
create or replace table testTb

ORA-00922: 选项缺失或无效只能使用先drop再create来代替

drop table testTb;  
create teble testTb(  
    fid   varchar2(4),  
    fname   varchar2(10)  
);

可以用create or replace的对象有： functions , procedures , packages , types , synonyms , trigger and views ，就是没有table，也没有sequence。

drop掉一个并不存在的表报错：

SQL> drop table non_exists;  
drop table non_exists  
ORA-00942:

表或视图不存在 drop table容错的方法是：

BEGIN  
  DROP TABLE non_exists_table;  
EXCEPTION  
  WHEN OTHERS THEN  
    IF sqlcode != -0942 THEN RAISE; END IF;  
END;

drop sequence容错的方法是：

BEGIN  
  DROP SEQUENCE non_exists_sequence;  
EXCEPTION  
  WHEN OTHERS THEN  
    IF sqlcode != -2289 THEN RAISE; END IF;  
/ 错误代码：-2289

二、Oracle 的drop table if exists功能

Mysql 创建表之前判断表是否存在，如果存在则删除已有表

DROP TABLE IF EXISTS SH_PLACARD_INFO;

Oracle 创建表之前判断表是否存在，如果存在则删除已有表

declare
      num number;
begin
    select count(1) into num from user_tables where table_name = upper('SH_PLACARD_INFO') ;
    if num > 0 then
        execute immediate 'drop table SH_PLACARD_INFO' ;
    end if;
end;

三、Oracle 批量插入数据 insert all into 用法

将多行插入到一个或 多个表 中。在这种情况下，就可以使用Oracle INSERT ALL语句，该语句也被称为多项式插入语句。

3.1 无条件的Oracle INSERT ALL语句

要将多行插入到表中，请使用以下Oracle INSERT ALL语句：

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

在这个语句中，每个值表达式值：val1，val2或val3必须引用由 子查询的选择列表返回的列对应的值 。

如果要使用文字值而不是子查询返回的值，请使用以下子查询：

SELECT * FROM dual;

以下示例演示如何将多行插入到表中。

首先，创建一个名为fruits的新表：

CREATE TABLE fruits (
    fruit_name VARCHAR(100) PRIMARY KEY,
    color VARCHAR(100) NOT NULL
);

其次，使用Oracle INSERT ALL语句将行插入到fruits表中：

INSERT ALL 
    INTO fruits(fruit_name, color)
    VALUES ('苹果','红色') 
    INTO fruits(fruit_name, color)
    VALUES ('橙子','橙色') 
    INTO fruits(fruit_name, color)
    VALUES ('香蕉','黄色')
SELECT 1 FROM dual;

第三，查询fruits表数据来验证插入结果：

SELECT 
    fruits;

执行上面查询语句，得到以下结果 -

将多行插入到多个表中

还可以使用INSERT ALL语句将行插入到多个表中，如下所示。

INSERT ALL
    INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

3.2 有条件的Oracle INSERT ALL语句

条件多项插入语句允许 根据指定的条件将行插入到表中 。

以下显示了条件多项插入语句的语法：

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
        INTO table_3(column_list ) VALUES (value_list)
Subquery

如果指定了ALL关键字，则Oracle将在WHEN子句中评估每个条件。如果条件评估/计算为true，则Oracle执行相应的INTO子句。

但是，当指定FIRST关键字时，对于由子查询返回的每一行，Oracle都会从WHEN子句的上下方向评估每个条件。如果Oracle发现条件的计算结果为true，则执行相应的INTO子句并跳过给定行的后续WHEN子句。

请注意，单条件多项式插入语句最多可以有127个WHEN子句。

有条件的Oracle INSERT ALL示例

以下CREATE TABLE语句创建三个表：small_orders，medium_orders和big_orders，它们具有相同的结构：

CREATE TABLE small_orders (
    order_id NUMBER(12) NOT NULL,
    customer_id NUMBER(6) NOT NULL,
    amount NUMBER(8,2) 
CREATE TABLE medium_orders AS
SELECT *
FROM small_orders;
CREATE TABLE big_orders AS
SELECT *
FROM small_orders;

以下条件Oracle INSERT ALL语句根据订单金额将订单数据插入到三个表：small_orders，medium_orders和big_orders之中：

INSERT ALL
   WHEN amount < 10000 THEN
      INTO small_orders
   WHEN amount >= 10000 and amount <= 30000 THEN
      INTO medium_orders
   WHEN amount > 30000 THEN
      INTO big_orders
 SELECT order_id,
        customer_id,
        (quantity * unit_price) amount
 FROM orders
 INNER JOIN order_items USING(order_id);

通过使用ELSE子句插入到big_orders表中，这样也可以达到相同的结果，如下所示：

INSERT ALL
   WHEN amount < 10000 THEN
      INTO small_orders
   WHEN amount >= 10000 and amount <= 30000 THEN
      INTO medium_orders
      INTO big_orders
 SELECT order_id,
        customer_id,
        (quantity * unit_price) amount
 FROM orders
 INNER JOIN order_items USING(order_id);

有条件的Oracle INSERT FIRST示例

考虑下面的例子。

INSERT FISRT
   WHEN amount < 10000 THEN
      INTO small_orders
   WHEN amount >= 10000 and amount <= 30000 THEN
      INTO medium_orders