Oracle SQL 函数与语法精讲-优快云博客

一：oracle语法（20130906）

1.语法：语法NULLIF ( expression1, expression2 )

语法NULLIF ( expression1 , expression2 )

参数expression1,expression2

如果两个表达式不相等，NULLIF 返回第一个 expression1 的值。

如果两个表达式相等，NULLIF 返回空值NULL。

例：

SELECT NULLIF('222','222') FROM DUAL; -- return

SELECT NULLIF('222','') FROM DUAL; -- return

2.语法：语法NVL2(expr1,expr2,expr3)

NVL2(表达式,不为空设值,为空设值)

3. inner join on 、left join on、 right join on 的区别？

2.操作实例

表A记录如下：

aID aNum

1 a20050111

2 a20050112

3 a20050113

4 a20050114

5 a20050115

表B记录如下:

bID bName

1 2006032401

2 2006032402

3 2006032403

4 2006032404

8 2006032408

实验如下:

1.left join

sql语句如下:

select * from A

left join B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

5 a20050115 NULL NULL

（所影响的行数为 5 行）

结果说明:

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

2.right join

sql语句如下:

select * from A

right join B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

NULL NULL 8 2006032408

（所影响的行数为 5 行）

结果说明:

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join

sql语句如下:

select * from A

innerjoin B

on A.aID = B.bID

结果如下:

aID aNum bID bName

1 a20050111 1 2006032401

2 a20050112 2 2006032402

3 a20050113 3 2006032403

4 a20050114 4 2006032404

结果说明:

很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录. 还有就是inner join 可以结合where语句来使用如： select * from A innerjoin B on A.aID = B.bID where b.bname='2006032401' 这样的话就只会放回一条数据了

4.with 的用法

有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。

with

sql1 as (select to_char(a) s_name from test_tempa),

sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))

select * from sql1

union all -- union含有distinct的功能

select * from sql2

union all -- UNION ALL只是简单的将两个结果合并后就返回

select 'no records' from dual

where not exists (select s_name from sql1 where rownum=1)

and not exists (select s_name from sql2 where rownum=1);

WITH Clause方法的优点：

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；

更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

5.over 的用法分析函数

连续求和分析函数。

二：oracle 创建临时表（20130909）

1. Oracle 中创建临时表语句

create global temporary table TMP_TKZC_RETURN_APPRAISE_DAY

(

CALC_PHASE VARCHAR2(50),

BEG_DATE CHAR(10),

END_DATE CHAR(10)

)

on commit delete rows; -- 定义了建立事务级临时表的方法.

--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）

--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。

详细介绍：

Oracle临时表分为会话级临时表和事务级临时表。

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。

临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，临时表不需要DML锁。

2. Case when … then .. else ….

pl/sql中的case语句
select (case when DUMMY='X' then 0 else 1 end) as flag from dual;
case的第1种用法：
case col when 'a' then 1
when 'b' then 2
else 0 end
这种用法跟decode一样没什么区别
case的第2种用法：
case when score <60 then 'd'
when score >=60 and score <70 then 'c'
when score >=70 and score <80 then 'b'
else 'a' end

3. oralce抛异常

a) PROCEDURERAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);

RAISE_APPLICATION_ERROR(-20000, P_PIPE_NAME || ' 管道名称已经存在');

RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言).

error_number_in 之容许从 -20000 到 -20999 之间，这样就不会与 ORACLE 的任何错误代码发生冲突。error_msg_in 的长度不能超过 2k，否则截取 2k。

DECLARE

v_empno employees.employee_id％TYPE :=&empno;

no_result EXCEPTION; --1、定义

BEGIN

UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;

IF SQL％NOTFOUND THEN

raise_application_error

（-20000， ""NEW_EMP::hiredate cannot be in the future""）; --3、抛出自定义异常

END IF;

EXCEPTION

when dup_val_on_index then

raise_application_error

（-20001， ""NEW_EMP::employee called ""||p_name||"" already exists""， true）; --3、包装Oracle异常

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE（SQLCODE||""---""||SQLERRM）;

END;

b) RAISE 自定义异常

DECLARE

v_empno employees.employee_id％TYPE :=&empno;

no_result EXCEPTION; --1、定义

BEGIN

UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;

IF SQL％NOTFOUND THEN

RAISE no_result; --2、抛出

END IF;

EXCEPTION

WHEN no_result THEN --3、处理惩罚

DBMS_OUTPUT.PUT_LINE（""你的数据更新语句失败了!""）;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE（SQLCODE||""---""||SQLERRM）;

END;

4．SQL%ROWCOUNT 影响的行数 SQL%BULK_ROWCOUNT 属性SQL%BULK_ROWCOUNT(i)表示FORALL 语句第i 元素所作用的行数

-- SQL%ROWCOUNT

CREATE OR REPLACE PROCEDURE test

BEGIN

update MY_TIME_TEST1 set discript = '6' where discript = '1' ;

If sql%rowcount>1 then --上面update影响行数>1就插入到my_time_test1

insert into MY_TIME_TEST1 values(sysdate,'7');

end if;

END;

-- SQL%BULK_ROWCOUNT

DECLARE

TYPE deptno_table_type IS TABLE OF number(2);

v_deptno_table deptno_table_type:=deptno_table_type(10,20,30);

BEGIN

FORALL i IN 1.. v_deptno_table.COUNT UPDATE emp2 SET sal=sal*0.5 WHERE deptno=v_deptno_table(i);

dbms_output.put_line('DEPARTMENT_10:'||SQL%BULK_ROWCOUNT(1)||' rows');

dbms_output.put_line('DEPARTMENT_20:'||SQL%BULK_ROWCOUNT(2)||' rows');

dbms_output.put_line('DEPARTMENT_30:'||SQL%BULK_ROWCOUNT(3)||' rows');

END;

参考：http://blog.youkuaiyun.com/inyutal/article/details/5819161

5. SQLCODE和SQLERRM

对于内部异常来说，SQLCODE会返回Oracle错误编号。SQLCODE 返回的总是一个负数，除非发生的Oracle错误是没有找到数据，这时返回的是+100。

SQLERRM会返回对应的错误消息。消息是以Oracle错误编号开头的。

如果我们没有使用编译指令EXCEPTION_INIT把异常与编号关联，SQLCODE和SQLERRM就会分别返回+1和消息"User-Defined Exception"。

Oracle错误消息最大长度是512个字符，其中包括错误编号、嵌套消息和具体表和字段的名称。

如果没有异常抛出，SQLCODE返回0，SQLERRM 返回消息"ORA-0000: normal, successful completion"。

我们可以把错误编号传递给SQLERRM，让它返回对应的错误消息。但是，一定要保证我们传递给SQLERRM的错误编号是负数。

6．For 循环、BULK COLLECT INTO 、returninginto

通过bulk collect减少loop处理的开销

采用bulk collect可以将查询结果一次性地加载到collections中。

而不是通过cursor一条一条地处理。

可以在select into,fetch into,returning into语句使用bulk collect。

注意在使用bulk collect时，所有的into变量都必须是collections.

举几个简单的例子：

--在select into语句中使用bulk collect

DECLARE

TYPE SalList IS TABLE OF emp.sal%TYPE;

sals SalList;

BEGIN

-- Limit the number of rows to 100.

SELECT sal BULK COLLECT INTO sals FROM emp

WHERE ROWNUM <= 100;

-- Retrieve 10% (approximately) of the rows in the table.

SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;

END;

--在fetch into中使用bulk collect

DECLARE

TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;

dept_recs DeptRecTab;

CURSOR c1 IS

SELECT deptno, dname, loc FROM dept WHERE deptno > 10;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO dept_recs;

END;

--在returning into中使用bulk collect

CREATE TABLE emp2 AS SELECT * FROM employees;

DECLARE

TYPE NumList IS TABLE OF employees.employee_id%TYPE;

enums NumList;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

names NameList;

BEGIN

DELETE FROM emp2 WHERE department_id = 30

RETURNING employee_id, last_name BULK COLLECT INTO enums, names;

dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');

FOR i IN enums.FIRST .. enums.LAST

LOOP

dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));

END LOOP;

END;

DROP TABLE emp2;

RETURNING into语句

DELETE操作：RETURNING返回的是DELETE之前的结果；

　　INSERT操作：RETURNING返回的是INSERT之后的结果；

　　UPDATE操作：的RETURNING语句是返回UPDATE操作之后的结果。

www.2cto.com

　　INSERT INTO SELECT语句不支持。

　　MERGE语句不支持RETURNING语句。

三：UNION 、UNION ALL(20130910)

1、UNION 、UNION ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

2、PL/SQL 开发工具美化器

总和：∑，连乘：∏，从n个元素中取出r个元素所有不同的组合数：C(n,r) ，幂（A，Ac，Aq，x^n）等。

3、oracle里的extend详解

扩展已知的数组空间，例：

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(10);

courses CourseList;

BEGIN

-- 初始化数组元素，大小为3

courses := CourseList( 'Biol 4412 ', 'Psyc 3112 ', 'Anth 3001 ');

-- 为数组增加一个元素，数组大小为4，末尾的元素为NULL

courses.EXTEND; -- append one null element

-- 为增加的元素赋值，如果没用EXTEND，这里会出错

courses(4) := 'Engl 2005 ';

end

Oracle 在逻辑上是由各个表空间(tablespace)构成的，

tablespace由segments(段)构成

段是由extends构成中文叫作区或者数据区

区是由一个一个的数据块构成数据块的大小由操作系统决定。

4. 动态SQl中用using传递参数

动态SQl中用using传递参数下边这种写法对吗？
execute immediate 'select * from 表 where 字段1 = :1 and 字段2 = :1'
using '1'; -- 如果前面已经定义了变量则可以这样使用
还是说必须写成：
execute immediate 'select * from 表 where 字段1 = :1 and 字段2 = :2'
using '1'，'1'-- 如果前面已经定义了变量则可以这样使用

我的动态SQL里边很多地方都用到了同一个参数，用一次就得在using后边加一个吗？

动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的，而不是名称关联。所以，如果在SQL语句中同样的占位符出现两次或多次，那么，它的每次出现都必须与一个USING子句中的绑定参数相关联。

http://bbs.youkuaiyun.com/topics/390439524?page=1

5. SYS_GUID

SYS_GUID (),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。在大多数平台,生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)组成。

6. SYSTIMESTAMP SYSDATE

SELECT SYSTIMESTAMP , Sysdate FROM dual ;

Systimestamp 函数返回本机数据库上当前系统日期和时间(包括微秒和时区)。

Systimestamp : 10-9月 -13 02.49.23.281000 下午 +08:00 , sysdate: 2013-9-10 14:49:23

7. EXTRACT 扩展

CREATE OR REPLACE TYPE "VAR_LIST" -- 自己定义变量

V_TABLE VAR_LIST := VAR_LIST(); -- 给此变量开辟空间

V_TABLE.EXTEND; -- 像一个数组，扩展下空间