Oracle语句、函数汇总

（一）删除表中所有数据：

 方法一： delete from 表名;

 方法二： truncate table 表名；  //  速度更快

 

（二）A表数据复制给B表（表复制）：                                

1、创建表时复制：

create table c as select * from b;    // as 是关键字不能省略

create table d(username) as select username from a;

或 create table d(username) as （select username from a）;

2、表已经存在，仅记录复制

insert into b(select * from a);    // 全复制

insert into b(username)  select username from a;   // 有选择的复制

或 insert into b(username)(select username from a);

 

(三) top n 问题(orcale没有该关键字)

SELECT ... FROM ... WHERE ... AND rownum <= 10 

//  rownum 是隐式游标，rownum <= 10   类似于 top 10

如果有 order by 的处理方法（可以使用临时表）：

SELECT * FROM  (
     SELECT A.*, rownum r FROM
          (
          SELECT *
          FROM Articles
          ORDER BY PubTime DESC        //蓝色部分可以改为任意的select
          ) A 
     WHERE rownum <= PageUpperBound
     ) B
WHERE r > PageLowerBound;         //  r 是rownum的别名

 

（四）oracle同时向多表插入数据

All表示非短路运算，即满足了第一个条件也得向下执行查看是否满足其它条件，而First是短路运算找到合适条件就不向下进行。

INSERT ALL
WHEN prod_category=’B’ THEN
INTO book_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category=’V’ THEN
INTO video_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category=’A’ THEN
INTO audio_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
SELECT prod_category ,product_id ,customer_id ,sale_qty,sale_price
FROM sales_detail;

 

（五）Merge into用法:

如果条件匹配就更新，不存在就插入 。

语法为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause      //  匹配则更新
WHEN NOT MATCHED THEN insert_clause;  // 不匹配则插入记录
举例：
MERGE INTO course c
USING ( SELECT course_name, period, course_hours
        FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE  SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period, c.course_hours)

VALUES (cu.course_name, cu.period, cu.course_hours);

备注：on后面的条件语句查询出的结果集必须大于1。

 

（六）oracle多表关联更新

如果要将test1表与test2表no字段相等的记录的name字段更新为与test2表中的name字段的值.

update test1 a set name=(select name from test2 b where a.no=b.no) where

exists(select name from test2 b where a.no=b.no);

 

（七）Oracle A表向B表导入数据（存储过程）：

  存储过程如下:

create or replace procedure TestProc is

begin

  for c in (select id, name from test2)  loop

    insert into test(id,name) values(c.id, c.name);

  end loop;

end TestProc;

执行存储过程：

Exec TestProc;

 

（八）Oracle多表级联删除方法

Oracle可以实现级联删除，不可以级联更新； 级联更新可以通过触发器实现。

方法1：创建约束时设定级联删除

（但一般由于各种原因或出于各种考虑在创建数据库时没有设定级联删除）

方法二：创建约束时没有使用级联删除，在需要使用级联删除时，删除原来的外键约束，重建带级联删除的约束（相当于把已有的表修改为可以级联的表） 

方法三：使用触发器（创建时没有级联删除）

（比较灵活，可以根据自己编写的程序进行，引用的不是唯一主键也可以）

SQL：

CREATE OR REPLACE TRIGGER "FG123"."TER_OV"

BEFORE

DELETE ON "ORDERCOMBINE" FOR EACH ROW   // 删除

BEGIN

DELETE   FROM   VIPForm  WHERE   VIPForm.V_Id=:OLD.FormerId;

END;

或

SQL：

CREATE OR REPLACE TRIGGER ID_TRG

AFTER

UPDATE ON AAA FOR EACH ROW    // 更新

BEGIN

UPDATE BBB SET a=:NEW.a WHERE a=:OLD.a;

END;

 

（九）Concat() 函数：

update tab1 set user_email = concat(name,'@byzh.com') where user_id = '2713'

注：CONCAT　连接字符，相当于 || ；两个或者多个字符串连接 用 || 比较好。

例如：select concat('aa'||' ','bb') from tab1;   // 字符aa 与 空格、bb 连接

 

(十)DBMS_OUTPUT.put()与DBMS_OUTPUT.put_line()区别：

语句：
declare
begin
  DBMS_OUTPUT.put( "--put--");   //不换行输出
  DBMS_OUTPUT.put_line( "--putline-- ");  //换行输出
end;

在SQL Plus中：
SQL>set serveroutput on     // 设置客户端显示输出
SQL>exec dbms_output.put_line('***'); 

 

（十一）Oracle常用的系统函数：

字符：length ,  replace, substr , ltrim ,rtrim , trim

日期：Sysdate , current_date , next_day

转换：to_char, to_date, to_number

聚集函数：sum,avg,max, min,count

其他：user,decode,nvl