1.3.2.3.10. GoldenDB-MySQL

1.3.2.3.10.1. select … into …

select ... into ... 语句的作用是将查询结果存储到一个变量、文件或新表中,是一种非标准的SQL,可在PL/SQL中使用。

语法

SELECT select_expr_list INTO variable FROM from_list

示例

-- 转换前Oracle SQL:
select col1,col2 into @a1,@a2 from test_unisql_into_table;
select col1,col2 into b1,b2 from test_unisql_into_table;

-- 转换后GoldenDB-MySQL:
SELECT `col1`,`col2` INTO @a1, @a2 FROM `test_unisql_into_table`
SELECT `col1`,`col2` INTO `b1`, `b2` FROM `test_unisql_into_table`

1.3.2.3.10.2. 层次查询

层次查询（Hierarchical Query）是一种具有特殊功能的查询语句，通过它能够将分层数据按照层次关系展示出来。

语法

SELECT [level,] [column,] expression...
FROM from_list
[where_clause]
[hierarchical_query_clause]
[group_by_clause]

hierarchical_query_clause::=
  start_with_clause connect_by_clause
| connect_by_clause start_with_clause

start_with_clause::=
    START WITH expression

connect_by_clause::=
    CONNECT BY [NOCYCLE] expression

警告

不支持CONNECT_BY_ISCYCLE、CONNECT_BY_ROOT、CONNECT_BY_ISLEAF
不支持和oracle(+)一起使用
不支持和pivot, unpivot函数一起使用
不支持SIBLINGS
不支持和with cte一起使用
不支持窗口函数和分析函数 OVER语法
不支持rownum

1.3.2.3.10.2.1. connect by

DROP TABLE unisql_connect_by_emp;
CREATE TABLE unisql_connect_by_emp (empno int NOT NULL,ename VARCHAR2(10),job VARCHAR2(9),mgr int,sal int,comm int,deptno int);

INSERT INTO unisql_connect_by_emp VALUES (7369,'SMITH','CLERK',7902,800,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7499,'ALLEN','SALESMAN',7698,1600,300,30);
INSERT INTO unisql_connect_by_emp VALUES (7521,'WARD','SALESMAN',7698,1250,500,30);
INSERT INTO unisql_connect_by_emp VALUES (7566,'JONES','MANAGER',7839,2975,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7654,'MARTIN','SALESMAN',7698,1250,1400,30);
INSERT INTO unisql_connect_by_emp VALUES (7698,'BLAKE','MANAGER',7839,2850,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7782,'CLARK','MANAGER',7839,2450,NULL,10);
INSERT INTO unisql_connect_by_emp VALUES (7788,'SCOTT','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7839,'KING','PRESIDENT',NULL,5000,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7844,'TURNER','SALESMAN',7698,1500,0,30);
INSERT INTO unisql_connect_by_emp VALUES (7876,'ADAMS','CLERK',7788,1100,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7900,'JAMES','CLERK',7698,950,NULL,30);
INSERT INTO unisql_connect_by_emp VALUES (7902,'FORD','ANALYST',7566,3000,NULL,20);
INSERT INTO unisql_connect_by_emp VALUES (7934,'MILLER','CLERK',7782,1300,NULL,10);

-- 转换前Oracle SQL:
-- 单表
SELECT LEVEL,empno,ename,mgr,sal,connect_by_root(DEPTNO) AS root,SYS_CONNECT_BY_PATH(LEVEL, '/') FROM unisql_connect_by_emp START WITH ename='BLAKE' CONNECT BY PRIOR empno=mgr

LEVEL|EMPNO|ENAME |MGR |SAL |ROOT|SYS_CONNECT_BY_PATH(LEVEL,'/')|
-----+-----+------+----+----+----+------------------------------+
    1| 7698|BLAKE |7839|2850|  30|/1                            |
    2| 7499|ALLEN |7698|1600|  30|/1/2                          |
    2| 7521|WARD  |7698|1250|  30|/1/2                          |
    2| 7654|MARTIN|7698|1250|  30|/1/2                          |
    2| 7844|TURNER|7698|1500|  30|/1/2                          |
    2| 7900|JAMES |7698| 950|  30|/1/2                          |

-- 转换后GoldenDB-MySQL SQL:
SELECT LEVEL,empno,ename,mgr,sal,connect_by_root(DEPTNO) AS root,SYS_CONNECT_BY_PATH(LEVEL, '/') FROM unisql_connect_by_emp START WITH ename='BLAKE' CONNECT BY PRIOR empno=mgr

level|empno|ename |mgr |sal |root|sysconnectpath|
-----+-----+------+----+----+----+--------------+
    1| 7698|BLAKE |7839|2850|  30|/1            |
    2| 7499|ALLEN |7698|1600|  30|/1/2          |
    2| 7521|WARD  |7698|1250|  30|/1/2          |
    2| 7654|MARTIN|7698|1250|  30|/1/2          |
    2| 7844|TURNER|7698|1500|  30|/1/2          |
    2| 7900|JAMES |7698| 950|  30|/1/2          |