8 兼容的Oracle语法
外连接符（+）
外连接包括：左外连接（左表不加限制），右外连接（右表不加限制
），全外连接（左右两表都不加限制）。在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。+ 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。反之同理。
例如：
halo0root=# select * from t_a;
id | name
----+------
1 | A
2 | B
3 | C
4 | D
5 | E
(5 行记录)
halo0root=# select * from t_b;
id | name
----+------
1 | AA
1 | BB
2 | CC
1 | DD
(4 行记录)
halo0root=# select * from t_a a,t_b b where a.id=b.id(+);
id | name | id | name
----+------+----+------
1 | A | 1 | DD
1 | A | 1 | BB
1 | A | 1 | AA
2 | B | 2 | CC
3 | C | |
4 | D | |
5 | E | |
(7 行记录)
halo0root=# select * from t_a a,t_b b where a.id(+)=b.id;
id | name | id | name
----+------+----+------
1 | A | 1 | AA
1 | A | 1 | BB
2 | B | 2 | CC
1 | A | 1 | DD
(4 行记录)
序列
序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
创建一个序列：
halo0root=# CREATE SEQUENCE a_seq;
CREATE SEQUENCE
初始化序列：
halo0root=# SELECT a_seq.nextval FROM dual;
nextval
---------
1
(1 行记录)
查询当前序列：
halo0root=# SELECT a_seq.currval FROM dual;
currval
---------
1
(1 行记录)
SYSDATE
查看系统日期：
halo0root=# select sysdate from dual;
sysdate
---------------------
2022-03-10 10:56:32
(1 行记录)
别名update
通过表别名可以大大缩减代码，同时表别名也是解决同表多次引用的手段之一。
例如：
halo0root=# select * from ta;
id | name | location
----+------+----------
1 | A | CN
2 | B | CN
3 | C | US
4 | D | JP
(4 行记录)
halo0root=# UPDATE ta a SET a.location = 'CN' WHERE id = 4;
UPDATE 1
halo0root=# select * from ta;
id | name | location
----+------+----------
1 | A | CN
2 | B | CN
3 | C | US
4 | D | CN
(4 行记录)
无别名子查询
兼容的无别名子查询。
例如：
halo0root=# SELECT * FROM (SELECT * FROM ta WHERE id IN (1, 2)) WHERE location = 'CN';
id | name | location
----+------+----------
1 | A | CN
2 | B | CN
(2 行记录)
MINUS
MINUS在Oracle中也是用来做减法操作的，只不过它不是传统意义上对数字的减法，而是对查询结果集的减法。A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。Oracle的minus是按列进行比较的，所以A能够minus B的前提条件是结果集A和结果集B需要有相同的列数，且相同列索引的列具有相同的数据类型。
Oracle会对minus后的结果集进行去重，即如果A中原本多条相同的记录数在进行A minus B后将会只剩一条对应的记录。
例如：
halo0root=# select * from td;
id
----
1
2
3
(3 行记录)
halo0root=# select * from te;
id
----
1
2
0
(3 行记录)
halo0root=# select * from td MINUS SELECT * FROM te;
id
----
3
(1 行记录)
NULL与空串
在我们不知道具体有什么数据的时候，也即未知，可以用NULL，我们称它为空，含有空值的表列长度为零。
支持空串与NULL等价，例如：
halo0root=# SELECT 'Y' FROM dual WHERE '' IS NULL;
?column?
----------
Y
(1 行记录)
DECODE
decode函数在QL查询语句中的使用非常广泛，也经常应用到PL/SQL语句块中。
decode()函数语句的基本表达式是：
decode(expr1,expr2,expr3,[expr4])
如果expr1 = expr2,decode函数返回expr3表达式的值；如果pr1 != expr2,函数返回expr4表达式的值,如果expr4未指定，返回null。
例如：
halo0root=# SELECT location, decode(location, 'CN', 'Y', 'N') FROM ta;
location | case
----------+------
CN | Y
CN | Y
US | N
CN | Y
CN | Y
CN | Y
US | N
JP | N
(8 行记录)
ROWNUM
ROWNUM是一个伪列，根据从查询中检索行的顺序，为每一行分配一个增量的、唯一的整数值。因此，检索到的第一行ROWNUM为1；二行有
ROWNUM为2，以此类推。
例如：
halo0root=# SELECT * FROM (SELECT rownum rn, id, name, location FROM ta) WHERE rn <= 3 AND rn >= 1;
rn | id | name | location
----+----+------+----------
1 | 1 | A | CN
2 | 2 | B | CN
3 | 3 | C | US
(3 行记录)
DBLINK
DBLink的作用是通过一台服务器上面的数据库访问另外一台服务器上面的数据库。Halo支持@方式的语法
halo0root=# SELECT * FROM a_tab@a_remote a;
数值格式化
支持Oracle方式的数值输出，例如：
halo0root=# SELECT cast(100.00100 AS VARCHAR2(30)) FROM dual;
varchar
---------
100.001
(1 行记录)
输出字段名大写
数据库支持字段名输出大写，例如：
halo0root=# SET enable_col_output_upcase = TRUE;
SET
halo0root=# SELECT * FROM halo_test;
A
---------------
Hello
Halo Database
(2 行记录)
日期类型
支持Oracle方式的日期类型，例如：
halo0root=# CREATE TABLE halo_test1(
halo0root(# a DATE
halo0root(# );
CREATE TABLE
halo0root=# \dS+ halo_test1
数据表 "public.halo_test1"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 统计目标 | 描述
------+----------+----------+--------+------+-------+----------+------
a | datetime | | | | plain | |
访问方法 heap
DUAL伪表
dual是Oracle中的一个伪表，利用这个伪表可以设置或查看序列，或者是调用一些内置的函数，方便操作。简单来说，dual表就是oracle与数据字典自动创建的一张表，这张表是一个单行单列的表，这个表只有1列：DUMMY，数据类型为VERCHAR2(1)，dual表中只有一个数据'X', Oracle有内部逻辑保证dual表中永远只有一条数据。dual表主要是用来选择系统变量或是求一个表达式的值。
例如：
halo0root=# select 'ww' from dual;
?COLUMN?
----------
ww
(1 行记录)
数据导入
Halo数据库支持特殊分隔符的数据导入。
IN特别语法
IN 运算符可以用来确定值是否与列表或子查询中的任何值相匹配。Halo数据库同样支持，例如：
halo0root=# SELECT 'Y' FROM dual WHERE 1 IN 1;
?COLUMN?
----------
Y
(1 行记录)
COUNT (DISTINCT..) OVER(PARTITION BY..)语法
Count (distinct.. ) over (partition by)功能为求分组去重后目标的结果，支持常用的聚合函数，如count, sum, listagg等。例如：
halo0root=# select name, count(distinct id) over(partition by name) from cvt;
name | count
------+-------
a | 2
a | 2
a | 2
b | 1
c | 1
d | 1
(6 rows)
匿名块功能语法
以DECLARE或BEGIN开始，每次提交都被编译。匿名块没有名称，不能在数据库中存储并且不能直接从其他PL/SQL块中调用，能够动态地创建和执行过程代码的PL/SQL结构，不需要以持久化的方式将代码作为数据库对象储存在系统目录中。例如：
- DECLARE
halo0root=# DECLARE
halo0root$# a INTEGER;
halo0root$# BEGIN
halo0root$# a:=12;
halo0root$# RAISE NOTICE 'a : %',a;
halo0root$# END;
halo0root$# /
Oracle动态sql语法
例如：支持动态SQL中的OPEN FOR语句。
OPEN FOR 语句可以关联游标变量和动态的SQL语句。
halo0root=# DECLARE
halo0root$# TYPE zip_cur_type IS REF CURSOR;
halo0root$# zip_cur zip_cur_type;
halo0root$# v_table_name VARCHAR2(20) := 'student';
halo0root$# sql_stmt VARCHAR2(500);
halo0root$# v_zip VARCHAR2(5);
halo0root$# v_total NUMBER;
halo0root$#
halo0root$# v_count NUMBER;
halo0root$# BEGIN
halo0root$# DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
halo0root$# ' table');
halo0root$#
halo0root$# sql_stmt := 'SELECT zip, COUNT(*) total'||
halo0root$# ' FROM '||v_table_name||' '||
halo0root$# 'GROUP BY zip';
halo0root$#
halo0root$# v_count := 0;
halo0root$# OPEN zip_cur FOR sql_stmt;
halo0root$# LOOP
halo0root$# FETCH zip_cur INTO v_zip, v_total;
halo0root$# EXIT WHEN zip_cur%NOTFOUND;
halo0root$#
halo0root$# -- Limit the number of lines printed on the
halo0root$# -- screen to 10
halo0root$# v_count := v_count + 1;
halo0root$# IF v_count <= 10 THEN
halo0root$# DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
halo0root$# ' Total: '||v_total);
halo0root$# END IF;
halo0root$# END LOOP;
halo0root$# CLOSE zip_cur;
halo0root$#
halo0root$# EXCEPTION
halo0root$# WHEN OTHERS THEN
halo0root$# DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
halo0root$#
halo0root$# END;
halo0root$# /
Totals from student table
Zip code: 11203 Total: 1
Zip code: 11415 Total: 1
Zip code: 11216 Total: 1
Zip code: 11422 Total: 1
Zip code: 07096 Total: 1
Zip code: 11412 Total: 3
Zip code: 07304 Total: 2
Zip code: 07029 Total: 1
Zip code: 10048 Total: 1
Zip code: 11419 Total: 2
Procedure语法
procedure存储过程是通过参数的，没有返回值。
CONTEXT: PL/oraSQL function pro_test() line 3 at CALL
halo0root=# Create or replace procedure pro_test()
halo0root-# As
halo0root$# begin
halo0root$# dbms_output.put_line('aaaa ');
halo0root$# end;
halo0root$# /
CREATE PROCEDURE
halo0root=# exec pro_test();
aaaa
CALL
Function语法
function函数是有返回值，一般情况返回值就只有一个。
Function：
halo0root=# CREATE OR REPLACE FUNCTION add_test(a IN INTEGER, b IN INTEGER)
halo0root-# RETURN INTEGER
halo0root-# AS
halo0root$# c INTEGER;
halo0root$# BEGIN
halo0root$# c:=a+b;
halo0root$# RETURN c;
halo0root$# END;
halo0root$# /
CREATE FUNCTION
支持错误码ZERO_DIVIDE、VALUE_ERROR、raise_application_error
raise_application_error抛出用户自定义的错误信息：
过程 RAISE_APPLICATION_ERROR 能帮助我们从存储子程序中抛出用户自定义的错误消息。这样，我们就能把错误消息报告给应用程序而避免返回未捕获异常。
halo0root=# declare
halo0root$# v_student_id integer := -100;
halo0root$# v_total_courses number;
halo0root$# begin
halo0root$# if v_student_id < 0 then
halo0root$# raise_application_error (-20000, 'an id cannot be negative');
halo0root$# else
halo0root$# select count(*)
halo0root$# into v_total_courses
halo0root$# from enrollment
halo0root$# where student_id = v_student_id;
halo0root$# dbms_output.put_line ('the student is registered for '||
halo0root$# v_total_courses||' courses');
halo0root$# end if;
halo0root$# end;
halo0root$# /
ERROR: HERR-20000: an id cannot be negative
CONTEXT: PL/oraSQL function raise_application_error(integer,text) line 12 at RAISE
SQL statement "CALL raise_application_error(-20000, 'an id cannot be negative')"
PL/oraSQL function inline_code_block line 6 at CALL
ZERO_DIVIDE异常是预定义的异常，表示算数表达式中有除以0的异常：
halo0root=# DECLARE
halo0root$# v_num1 integer := 201;
halo0root$# v_num2 integer := 0;
halo0root$# v_result number;
halo0root$# BEGIN
halo0root$# v_result := v_num1 / v_num2;
halo0root$# DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
halo0root$# EXCEPTION
halo0root$# WHEN ZERO_DIVIDE THEN
halo0root$# DBMS_OUTPUT.PUT_LINE ('A number cannot be divided by zero.');
halo0root$# END;
halo0root$# /
A number cannot be divided by zero.
DO
VALUE_ERROR异常：
VALUE_ERROR异常表示数字或值错误，一般在字符串和数字类型的转换时或字符串缓冲区太小时报错。
halo0root=# declare
halo0root$# pnum number;
halo0root$# begin
halo0root$# pnum:='abc';
halo0root$# exception
halo0root$# when VALUE_ERROR then dbms_output.put_line('算术或转化错误');
halo0root$# when others then dbms_output.put_line('其他例外');
halo0root$# end;
halo0root$# /
算术或转化错误
DO
游标for循环直接引用子查询
可以在游标for循环中直接引用子查询进行简化。目前record不需要提前声明。
halo0root=# BEGIN
halo0root$# FOR rec IN (SELECT *
halo0root$# FROM dual)
halo0root$# LOOP
halo0root$# DBMS_OUTPUT.put_line (rec.dummy);
halo0root$# END LOOP;
halo0root$# END;
halo0root$# /
X
DO
