一、测试目的
近期研究了原生openGauss和Oracle数据库兼容性，特整理测试了Oracle常用函数和openGauss数据库的对比测试。
二、创建DUAL虚拟表
openGauss 数据库不提供DUAL虚拟表，需要通过如下方式创建视图模拟dual虚拟表：
create or replace view dual as
select NULL::“unknown”
where 1=1;
alter table dual owner to 连接用户名;
grant all on table dual to 连接用户名;
grant select on table dual to public;
三、测试结果
测试语句及结果如下：
| 序号 | 函数名称 | 查询语句 | openGauss查询结果 | Oracle查询结果 | 是否兼容 |
|---|---|---|---|---|---|
| 1 | ASCII | SELECT ASCII(‘A’) FROM DUAL; | openGauss=# SELECT ASCII(‘A’) FROM DUAL; ascii ------- 65 (1 row) | SQL> SELECT ASCII(‘A’) FROM DUAL; ASCII(‘A’) ---------- 65 | 支持 |
| 2 | **CHR** | SELECT CHR(65) FROM DUAL; | openGauss=# SELECT CHR(65) FROM DUAL; chr ----- A (1 row) | SQL> SELECT CHR(65) FROM DUAL; C - A | 支持 |
| 3 | **CONCAT** | openGauss=# SELECT CONCAT(CONCAT(‘Hello’, ’ '), ‘World’) FROM DUAL; concat ------------- Hello World (1 row) | SQL> SELECT CONCAT(CONCAT(‘Hello’, ’ '), ‘World’) FROM DUAL; CONCAT(CONC ----------- Hello World | 支持 | |
| 4 | **INITCAP** | SELECT INITCAP(‘hello world’) FROM DUAL; | openGauss=# SELECT INITCAP(‘hello world’) FROM DUAL; initcap ------------- Hello World (1 row) | SQL> SELECT INITCAP(‘hello world’) FROM DUAL; INITCAP('HE ----------- Hello World | 支持 |
| 5 | **INSTR** | SELECT INSTR(‘Hello World’, ‘o’) FROM DUAL; | openGauss=# SELECT INSTR(‘Hello World’, ‘o’) FROM DUAL; instr ------- 5 (1 row | SQL> SELECT INSTR(‘Hello World’, ‘o’) FROM DUAL; INSTR(‘HELLOWORLD’,‘O’) ----------------------- 5 | 支持 |
| 6 | **LENGTH** | SELECT LENGTH(‘Hello’) FROM DUAL; | openGauss=# SELECT LENGTH(‘Hello’) FROM DUAL; length -------- 5 (1 row) | SQL> SELECT LENGTH(‘Hello’) FROM DUAL; LENGTH(‘HELLO’) --------------- 5 | 支持 |
| 7 | **LOWER** | SELECT LOWER(‘Hello’) FROM DUAL; | openGauss=# SELECT LOWER(‘Hello’) FROM DUAL; lower ------- hello (1 row) | SQL> SELECT LOWER(‘Hello’) FROM DUAL; LOWER ----- hello | 支持 |
| 8 | **UPPER** | SELECT UPPER(‘Hello’) FROM DUAL; | openGauss=# SELECT UPPER(‘Hello’) FROM DUAL; upper ------- HELLO (1 row) | SQL> SELECT UPPER(‘Hello’) FROM DUAL; UPPER ----- HELLO | 支持 |
| 9 | **RPAD and LPAD** | SELECT RPAD(‘Hello’, 10, ’ ') FROM DUAL;SELECT LPAD(‘Hello’, 10, ’ ') FROM DUAL; | openGauss=# SELECT RPAD(‘Hello’, 10, ’ ') FROM DUAL; rpad ------------ Hello (1 row) openGauss=# SELECT LPAD(‘Hello’, 10, ’ ') FROM DUAL; lpad ------------ Hello (1 row) | SQL> SELECT RPAD(‘Hello’, 10, ’ ') FROM DUAL; RPAD('HELL ---------- Hello SQL> SELECT LPAD(‘Hello’, 10, ’ ') FROM DUAL; LPAD('HELL ---------- Hello | 支持 |
| 10 | **LTRIM and RTRIM** | SELECT LTRIM(’ Hello ‘) FROM DUAL;SELECT RTRIM(’ Hello ') FROM DUAL; | openGauss=# SELECT LTRIM(’ Hello ‘) FROM DUAL; ltrim ---------- Hello (1 row) openGauss=# SELECT RTRIM(’ Hello ') FROM DUAL; rtrim ---------- Hello (1 row) | SQL> SELECT LTRIM(’ Hello ') FROM DUAL; LTRIM(‘H -------- Hello SQL> SELECT RTRIM(’ Hello ') FROM DUAL; RTRIM('H -------- Hello | 支持 |
| 11 | **SUBSTR** | SELECT SUBSTR(‘Hello World’, 7, 5) FROM DUAL; | openGauss=# SELECT SUBSTR(‘Hello World’, 7, 5) FROM DUAL; substr -------- World (1 row) | SQL> SELECT SUBSTR(‘Hello World’, 7, 5) FROM DUAL; SUBST ----- World | 支持 |
| 12 | **REPLACE** | openGauss=# SELECT REPLACE(‘Hello World’, ‘o’, ‘a’) FROM DUAL; replace ------------- Hella Warld (1 row) | SQL> SELECT REPLACE(‘Hello World’, ‘o’, ‘a’) FROM DUAL; REPLACE('HE ----------- Hella Warld | 支持 | |
| 13 | **SOUNDEX** | SELECT SOUNDEX(‘Hello’) FROM DUAL; | openGauss=# SELECT SOUNDEX(‘Hello’) FROM DUAL; ERROR: function soundex(unknown) does not exist LINE 1: SELECT SOUNDEX(‘Hello’) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: soundex | SQL> SELECT SOUNDEX(‘Hello’) FROM DUAL; SOUN ---- H400 | 不支持 |
| 14 | **TRIM** | SELECT TRIM(’ Hello ') FROM DUAL; | openGauss=# SELECT TRIM(’ Hello ') FROM DUAL; btrim ------- Hello (1 row) | SQL> SELECT TRIM(’ Hello ') FROM DUAL; TRIM( ----- Hello | 支持 |
| 15 | **ACOS** | SELECT ACOS(0) FROM DUAL; | openGauss=# SELECT ACOS(0) FROM DUAL; acos ----------------- 1.5707963267949 (1 row) | SQL> SELECT ACOS(0) FROM DUAL; ACOS(0) ---------- 1.57079633 | 支持 |
| 16 | **ASIN** | SELECT ASIN(0) FROM DUAL; | openGauss=# SELECT ASIN(0) FROM DUAL; asin ------ 0 (1 row) | SQL> SELECT ASIN(0) FROM DUAL; ASIN(0) ---------- 0 | 支持 |
| 17 | **ATAN** | SELECT ATAN(1) FROM DUAL; | openGauss=# SELECT ATAN(1) FROM DUAL; atan ------------------ .785398163397448 (1 row) | SQL> SELECT ATAN(1) FROM DUAL; ATAN(1) ---------- .785398163 | 支持 |
| 18 | **CEIL** | SELECT CEIL(3.14) FROM DUAL; | openGauss=# SELECT CEIL(3.14) FROM DUAL; ceil ------ 4 (1 row) | SQL> SELECT CEIL(3.14) FROM DUAL; CEIL(3.14) ---------- 4 | 支持 |
| 19 | **COS** | SELECT COS(0) FROM DUAL; | openGauss=# SELECT COS(0) FROM DUAL; cos ----- 1 (1 row) | SQL> SELECT COS(0) FROM DUAL; COS(0) ---------- 1 | 支持 |
| 20 | **COSH** | SELECT COSH(0) FROM DUAL; | openGauss=# SELECT COSH(0) FROM DUAL; ERROR: function cosh(integer) does not exist LINE 1: SELECT COSH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: cosh | SQL> SELECT COSH(0) FROM DUAL; COSH(0) ---------- 1 | 不支持 |
| 21 | **EXP** | SELECT EXP(1) FROM DUAL; | openGauss=# SELECT EXP(1) FROM DUAL; exp ------------------ 2.71828182845905 (1 row) | SQL> SELECT EXP(1) FROM DUAL; EXP(1) ---------- 2.71828183 | 支持 |
| 22 | **FLOOR** | SELECT FLOOR(3.14) FROM DUAL; | openGauss=# SELECT FLOOR(3.14) FROM DUAL; floor ------- 3 (1 row) | SQL> SELECT FLOOR(3.14) FROM DUAL; FLOOR(3.14) ----------- 3 | 支持 |
| 23 | **LN** | SELECT LN(2) FROM DUAL; | openGauss=# SELECT LN(2) FROM DUAL; ln ------------------ .693147180559945 (1 row) | SQL> SELECT LN(2) FROM DUAL; LN(2) ---------- .693147181 | 支持 |
| 24 | **LOG** | SELECT LOG(10, 100) FROM DUAL; | openGauss=# SELECT LOG(10, 100) FROM DUAL; log -------------------- 2.0000000000000000 (1 row) | SQL> SELECT LOG(10, 100) FROM DUAL; LOG(10,100) ----------- 2 | 支持 |
| 25 | **MOD** | SELECT MOD(10, 3) FROM DUAL; | openGauss=# SELECT MOD(10, 3) FROM DUAL; mod ----- 1 (1 row) | SQL> SELECT MOD(10, 3) FROM DUAL; MOD(10,3) ---------- 1 | 支持 |
| 26 | **POWER** | SELECT POWER(2, 3) FROM DUAL; | openGauss=# SELECT POWER(2, 3) FROM DUAL; power ------- 8 (1 row) | SQL> SELECT POWER(2, 3) FROM DUAL; POWER(2,3) ---------- 8 | 支持 |
| 27 | **ROUND and TRUNC** | SELECT ROUND(3.14) FROM DUAL;SELECT TRUNC(3.14) FROM DUAL; | openGauss=# SELECT ROUND(3.14) FROM DUAL; round ------- 3 (1 row) openGauss=# SELECT TRUNC(3.14) FROM DUAL; trunc ------- 3 (1 row) | SQL> SELECT ROUND(3.14) FROM DUAL; ROUND(3.14) ----------- 3 SQL> SELECT TRUNC(3.14) FROM DUAL; TRUNC(3.14) ----------- 3 | 支持 |
| 28 | **SIGN** | SELECT SIGN(-10) FROM DUAL; | openGauss=# SELECT SIGN(-10) FROM DUAL; sign ------ -1 (1 row) | SQL> SELECT SIGN(-10) FROM DUAL; SIGN(-10) ---------- -1 | 支持 |
| 29 | **SIN** | SELECT SIN(0) FROM DUAL; | openGauss=# SELECT SIN(0) FROM DUAL; sin ----- 0 (1 row) | SQL> SELECT SIN(0) FROM DUAL; SIN(0) ---------- 0 | 支持 |
| 30 | **SINH** | SELECT SINH(0) FROM DUAL; | openGauss=# SELECT SINH(0) FROM DUAL; ERROR: function sinh(integer) does not exist LINE 1: SELECT SINH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: sinh openGauss=# | SQL> SELECT SINH(0) FROM DUAL; SINH(0) ---------- 0 | 不支持 |
| 31 | **SQRT** | SELECT SQRT(16) FROM DUAL; | openGauss=# SELECT SQRT(16) FROM DUAL; sqrt ------ 4 (1 row) | SQL> SELECT SQRT(16) FROM DUAL; SQRT(16) ---------- 4 | 支持 |
| 32 | **TAN** | SELECT TAN(0) FROM DUAL; | openGauss=# SELECT TAN(0) FROM DUAL; tan ----- 0 (1 row) | SQL> SELECT TAN(0) FROM DUAL; TAN(0) ---------- 0 | 支持 |
| 33 | **TANH** | SELECT TANH(0) FROM DUAL; | openGauss=# SELECT TANH(0) FROM DUAL; ERROR: function tanh(integer) does not exist LINE 1: SELECT TANH(0) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: tanh | SQL> SELECT TANH(0) FROM DUAL; TANH(0) ---------- 0 | 不支持 |
| 34 | **TRUNC** | SELECT TRUNC(3.14) FROM DUAL; | openGauss=# SELECT TRUNC(3.14) FROM DUAL; trunc ------- 3 (1 row) | SQL> SELECT TRUNC(3.14) FROM DUAL; 3 | 支持 |
| 35 | **ADD_MONTHS** | SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; | openGauss=# SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; add_months --------------------- 2023-09-05 21:02:13 (1 row) | SQL> SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; 2023:09:0521:02:12 | 支持 |
| 36 | **LAST_DAY** | SELECT LAST_DAY(SYSDATE) FROM DUAL; | openGauss=# SELECT LAST_DAY(SYSDATE) FROM DUAL; last_day --------------------- 2023-08-31 21:03:52 (1 row) | SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL; 2023:08:3121:03:51 | 支持 |
| 37 | **MONTHS_BETWEEN** | SELECT MONTHS_BETWEEN(TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’), TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)) FROM DUAL; | openGauss=# SELECT MONTHS_BETWEEN(TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’), TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)) FROM DUAL; ERROR: function months_between(timestamp without time zone, timestamp without time zone) does not exist LINE 1: SELECT MONTHS_BETWEEN(TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’), T… ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: months_between | SQL> SELECT MONTHS_BETWEEN(TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’), TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)) FROM DUAL; 6 | 不支持 |
| 38 | **NEW_TIME** | SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM DUAL; | openGauss=# SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM DUAL; ERROR: function new_time(timestamp without time zone, unknown, unknown) does not exist LINE 1: SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: new_time | SQL> SELECT NEW_TIME(SYSDATE, ‘EST’, ‘PST’) FROM DUAL; 2023:08:0518:05:28 | 不支持 |
| 39 | **NEXT_DAY** | SELECT NEXT_DAY(SYSDATE, ‘SUNDAY’) FROM DUAL; | openGauss=# SELECT NEXT_DAY(SYSDATE, ‘SUNDAY’) FROM DUAL; next_day --------------------- 2023-08-06 21:06:25 (1 row) | SQL> SELECT NEXT_DAY(SYSDATE, ‘SUNDAY’) FROM DUAL; 2023:08:0621:06:24 | 支持 |
| 40 | **SYSDATE** | SELECT SYSDATE FROM DUAL; | openGauss=# SELECT SYSDATE FROM DUAL; sysdate --------------------- 2023-08-05 21:07:08 (1 row) | SQL> SELECT SYSDATE FROM DUAL; 2023:08:0521:07:08 | 支持 |
| 41 | **CHARTOROWID** | SELECT CHARTOROWID(‘AAAVmKAABAAAXFRAAA’) from dual; | openGauss=# SELECT CHARTOROWID(‘AAAVmKAABAAAXFRAAA’) from dual; ERROR: function chartorowid(unknown) does not exist LINE 1: SELECT CHARTOROWID(‘AAAVmKAABAAAXFRAAA’) from dual; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: chartorowid | SQL> SELECT CHARTOROWID(‘AAAVmKAABAAAXFRAAA’) from dual; AAAVmKAABAAAXFRAAA | 不支持 |
| 42 | **CONVERT** | SELECT CONVERT(‘Hello’, ‘WE8ISO8859P1’, ‘US7ASCII’) FROM DUAL; | openGauss=# SELECT CONVERT(‘Hello’, ‘WE8ISO8859P1’, ‘US7ASCII’) FROM DUAL; ERROR: invalid source encoding name “WE8ISO8859P1” CONTEXT: referenced column: convert | SQL> SELECT CONVERT(‘Hello’, ‘WE8ISO8859P1’, ‘US7ASCII’) FROM DUAL; Hello | 不支持 |
| 43 | **HEXTORAW** | SELECT HEXTORAW(‘414243’) FROM DUAL; | openGauss=# SELECT HEXTORAW(‘414243’) FROM DUAL; hextoraw ---------- 414243 (1 row) | SQL> SELECT HEXTORAW(‘414243’) FROM DUAL; 414243 | 支持 |
| 44 | **RAWTOHEX** | SELECT RAWTOHEX(‘ABC’) FROM DUAL; | openGauss=# SELECT RAWTOHEX(‘ABC’) FROM DUAL; rawtohex ---------- 414243 (1 row) | SQL> SELECT RAWTOHEX(‘ABC’) FROM DUAL; 414243 | 支持 |
| 45 | **ROWIDTOCHAR** | SELECT ROWIDTOCHAR(‘AAAVmKAABAAAXFRAAA’) FROM DUAL; | openGauss=# SELECT ROWIDTOCHAR(‘AAAVmKAABAAAXFRAAA’) FROM DUAL; ERROR: function rowidtochar(unknown) does not exist LINE 1: SELECT ROWIDTOCHAR(‘AAAVmKAABAAAXFRAAA’) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: rowidtochar | SQL> SELECT ROWIDTOCHAR(‘AAAVmKAABAAAXFRAAA’) FROM DUAL; AAAVmKAABAAAXFRAAA | 不支持 |
| 46 | **TO_CHAR** | SELECT TO_CHAR(12345) AS converted_value FROM DUAL; | openGauss=# SELECT TO_CHAR(12345) AS converted_value FROM DUAL; converted_value ----------------- 12345 (1 row) | SQL> SELECT TO_CHAR(12345) AS converted_value FROM DUAL; 12345 | 支持 |
| 47 | **TO_DATE** | SELECT TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’) FROM DUAL; | openGauss=# SELECT TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’) FROM DUAL; to_date --------------------- 2022-07-01 00:00:00 (1 row) | SQL> SELECT TO_DATE(‘2022-07-01’, ‘YYYY-MM-DD’) FROM DUAL; 2022:07:0100:00:00 | 支持 |
| 48 | **TO_MULTI_BYTE** | SELECT TO_MULTI_BYTE(‘Hello’) FROM DUAL; | openGauss=# SELECT TO_MULTI_BYTE(‘Hello’) FROM DUAL; ERROR: function to_multi_byte(unknown) does not exist LINE 1: SELECT TO_MULTI_BYTE(‘Hello’) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: to_multi_byte | SQL> SELECT TO_MULTI_BYTE(‘Hello’) FROM DUAL; Ｈｅｌｌｏ | 不支持 |
| 49 | **TO_NUMBER** | SELECT TO_NUMBER(‘123.45’, ‘999.99’) FROM DUAL; | openGauss=# SELECT TO_NUMBER(‘123.45’, ‘999.99’) FROM DUAL; to_number ----------- 123.45 (1 row) | SQL> SELECT TO_NUMBER(‘123.45’, ‘999.99’) FROM DUAL; 123.45 | 支持 |
| 50 | **BFILENAME** | SELECT BFILENAME(‘DIRECTORY’, ‘filename.jpg’) AS file_name FROM DUAL; | openGauss=# SELECT BFILENAME(‘DIRECTORY’, ‘filename.jpg’) AS file_name FROM DUAL; ERROR: function bfilename(unknown, unknown) does not exist LINE 1: SELECT BFILENAME(‘DIRECTORY’, ‘filename.jpg’) AS file_name F… ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: file_name | SQL> SELECT BFILENAME(‘DIRECTORY’, ‘filename.jpg’) AS file_name FROM DUAL; bfilename(‘DIRECTORY’, ‘filename.jpg’) | 不支持 |
| 51 | **DUMP** | SELECT DUMP(‘Hello’) FROM DUAL; | openGauss=# SELECT DUMP(‘Hello’) FROM DUAL; ERROR: function dump(unknown) does not exist LINE 1: SELECT DUMP(‘Hello’) FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: dump | SQL> SELECT DUMP(‘Hello’) FROM DUAL; Typ=96 Len=5: 72,101,108,108,111 | 不支持 |
| 52 | **GREATEST** | SELECT GREATEST(2, 5, 3) FROM DUAL; | openGauss=# SELECT GREATEST(2, 5, 3) FROM DUAL; greatest ---------- 5 (1 row) | SQL> SELECT GREATEST(2, 5, 3) FROM DUAL; 5 | 支持 |
| 53 | **LEAST** | SELECT LEAST(2, 5, 3) FROM DUAL; | openGauss=# SELECT LEAST(2, 5, 3) FROM DUAL; least ------- 2 (1 row) | SQL> SELECT LEAST(2, 5, 3) FROM DUAL; 2 | 支持 |
| 54 | **UID** | SELECT UID FROM DUAL; | openGauss=# SELECT UID FROM DUAL; ERROR: column “uid” does not exist LINE 1: SELECT UID FROM DUAL; ^ CONTEXT: referenced column: uid | SQL> SELECT UID FROM DUAL; 0 | 不支持 |
| 55 | **USER** | SELECT USER FROM DUAL; | openGauss=# SELECT USER FROM DUAL; current_user -------------- omm (1 row) | SQL> SELECT USER FROM DUAL; SYS | 支持 |
| 56 | **AVG** | SELECT AVG(salary) FROM employees; | openGauss=# SELECT AVG(salary) FROM employees; avg -------------------- 65000.000000000000 (1 row) | SQL> SELECT AVG(salary) FROM employees; 65000 | 支持 |
| 57 | **MAX** | SELECT MAX(salary) FROM employees; | openGauss=# SELECT MAX(salary) FROM employees; max ------- 65000 (1 row) | SQL> SELECT MAX(salary) FROM employees; 65000 | 支持 |
| 58 | **MIN** | SELECT MIN(salary) FROM employees; | openGauss=# SELECT MIN(salary) FROM employees; min ------- 65000 (1 row) | SQL> SELECT MIN(salary) FROM employees; 65000 | 支持 |
| 59 | **STDDEV** | SELECT STDDEV(salary) FROM employees; | openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row) | SQL> SELECT STDDEV(salary) FROM employees; 0 | 支持 |
| 60 | **VARIANCE** | SELECT VARIANCE(salary) FROM employees; | openGauss=# SELECT VARIANCE(salary) FROM employees; variance ---------- (1 row) | SQL> SELECT VARIANCE(salary) FROM employees; 0 | 支持 |
| 61 | **GROUP BY** | SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; | openGauss=# openGauss=# SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; department_id | avg ---------------±------------------- 60 | 65000.000000000000 (1 row) | SQL> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; 60 65000 | 支持 |
| 62 | **HAVING** | SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; | openGauss=# SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; department_id | avg ---------------±------------------- 60 | 65000.000000000000 (1 row) | SQL> SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; 60 65000 | 支持 |
| 63 | **ORDER BY** | SELECT * FROM employees ORDER BY salary DESC; | openGauss=# SELECT * FROM employees ORDER BY salary DESC; employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | ma nager_id | department_id -------------±-----------±----------±---------------------±-------------±--------------------±--------±-------±---------------±-- ---------±-------------- 101 | John | Doe | john.doe@example.com | 123-456-7890 | 2023-01-15 00:00:00 | IT_PROG | 65000 | | | 60 (1 row) | SQL> SELECT * FROM employees ORDER BY salary DESC; 101 John Doe john.doe@example.com 123-456-7890 2023:01:1500:00:00 IT_PROG 65000 60 | 支持 |
| 64 | **CASE statement in PL/SQL** | DECLARE salary NUMBER := 5000; BEGIN CASE WHEN salary > 10000 THEN DBMS_OUTPUT.PUT_LINE (‘High Salary’); WHEN salary > 5000 THEN DBMS_OUTPUT.PUT_LINE (‘Medium Salary’); ELSE DBMS_OUTPUT.PUT_LINE (‘Low Salary’); END CASE; END; / | openGauss=# DECLARE salary NUMBER := 5000; openGauss-# openGauss-# BEGIN CASE WHEN salary > 10000 THEN DBMS_OUTPUT.PUT_LINE (‘High Salary’); openGauss# openGauss# openGauss# openGauss# openGauss# WHEN salary > 5000 openGauss# THEN openGauss# DBMS_OUTPUT.PUT_LINE ('Medium Salary'); openGauss# ELSE openGauss# DBMS_OUTPUT.PUT_LINE ('Low Salary'); openGauss# END CASE; openGauss# END; openGauss# / ERROR: schema “dbms_output” does not exist CONTEXT: compilation of PL/pgSQL function “inline_code_block” near line 1 | SQL> DECLARE 2 salary NUMBER := 5000; 3 BEGIN 4 CASE WHEN salary > 10000 5 6 THEN DBMS_OUTPUT.PUT_LINE (‘High Salary’); 7 8 WHEN salary > 5000 9 THEN DBMS_OUTPUT.PUT_LINE (‘Medium Salary’); 10 11 ELSE 12 DBMS_OUTPUT.PUT_LINE (‘Low Salary’); END CASE; 13 14 END; 15 / PL/SQL procedure successfully completed. | 不支持 |
| 65 | **NVL** | SELECT NVL(FIRST_NAME,LAST_NAME) from employees; | openGauss=# SELECT NVL(FIRST_NAME,LAST_NAME) from employees; nvl ------ John (1 row) | SQL> SELECT NVL(FIRST_NAME,LAST_NAME) from employees; John | 支持 |
| 67 | **AVG** | SELECT AVG(salary) FROM employees; | openGauss=# SELECT AVG(salary) FROM employees; avg -------------------- 65000.000000000000 (1 row) | SQL> SELECT AVG(salary) FROM employees; 65000 | 支持 |
| 68 | **COUNT** | SELECT COUNT(salary) FROM employees; | openGauss=# SELECT COUNT(salary) FROM employees; count ------- 1 (1 row) | SQL> SELECT COUNT(salary) FROM employees; 1 | 支持 |
| 69 | **FIRST_VALUE** | SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; | openGauss=# SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; first_value ------------- 65000 (1 row) | SQL> SELECT FIRST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; 65000 | 支持 |
| 70 | **LAG** | SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees; | openGauss=# SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees; lag ----- (1 row) | SQL> SELECT LAG(salary) OVER (ORDER BY DEPARTMENT_ID) FROM employees; SQL> | 支持 |
| 71 | **LAST_VALUE** | SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; | openGauss=# SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; last_value ------------ 65000 (1 row) | SQL> SELECT LAST_VALUE(salary) OVER (PARTITION BY EMPLOYEE_ID ORDER BY DEPARTMENT_ID) FROM employees; 65000 | 支持 |
| 72 | **LEAD** | SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees; | openGauss=# SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees; lead ------ (1 row) | SQL> SELECT LEAD(salary) OVER (ORDER BY EMPLOYEE_ID) FROM employees; SQL> | 支持 |
| 73 | **ROW_NUMBER** | SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; | openGauss=# SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; row_number ------------ 1 (1 row) | SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; 1 | 支持 |
| 74 | **STDDEV** | SELECT STDDEV(salary) FROM employees; | openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row) openGauss=# | SQL> SELECT STDDEV(salary) FROM employees; 0 | 支持 |
| 75 | **STDDEV_POP** | SELECT STDDEV_POP(salary) FROM employees; | openGauss=# SELECT STDDEV_POP(salary) FROM employees; stddev_pop ------------ 0 (1 row) openGauss=# | SQL> SELECT STDDEV_POP(salary) FROM employees; 0 | 支持 |
| 76 | **STDDEV_SAMP** | SELECT STDDEV_SAMP(salary) FROM employees; | openGauss=# SELECT STDDEV_SAMP(salary) FROM employees; stddev_samp ------------- (1 row) openGauss=# | SQL> SELECT STDDEV_SAMP(salary) FROM employees; SQL> | 支持 |
| 77 | **SUM (Aggregate Function)** | SELECT SUM(salary) FROM employees; | openGauss=# SELECT SUM(salary) FROM employees; sum ------- 65000 (1 row) openGauss=# | SQL> SELECT SUM(salary) FROM employees; 65000 | 支持 |
| 78 | **VAR_POP (Aggregate Function)** | SELECT VAR_POP(salary) FROM employees; | openGauss=# SELECT VAR_POP(salary) FROM employees; var_pop --------- 0 (1 row) | SQL> SELECT VAR_POP(salary) FROM employees; 0 SQL> | 支持 |
| 79 | **VAR_SAMP (Aggregate Function)** | SELECT VAR_SAMP(salary) FROM employees; | openGauss=# SELECT VAR_SAMP(salary) FROM employees; var_samp ---------- (1 row) | SQL> SELECT VAR_SAMP(salary) FROM employees; SQL> | 支持 |
| 80 | **VARIANCE (Aggregate Function)** | SELECT VARIANCE(salary) FROM employees; | openGauss=# SELECT VARIANCE(salary) FROM employees; variance ---------- (1 row) openGauss=# | SQL> SELECT VARIANCE(salary) FROM employees; 0 | 支持 |
| 81 | **RANK (Aggregate Function)** | SELECT RANK() OVER (ORDER BY salary) FROM employees; | openGauss=# SELECT RANK() OVER (ORDER BY salary) FROM employees; rank ------ 1 (1 row) openGauss=# | SQL> SELECT RANK() OVER (ORDER BY salary) FROM employees; 1 SQL> | 支持 |
| 82 | **RATIO_TO_REPORT (Aggregate Function)** | SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; | openGauss=# SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; ERROR: function ratio_to_report(numeric) does not exist LINE 1: SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: ratio_to_report | SQL> SELECT RATIO_TO_REPORT(salary) OVER () FROM employees; 1 SQL> | 不支持 |
| 83 | **ROW_NUMBER (Aggregate Function)** | SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; | openGauss=# SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; row_number ------------ 1 (1 row) openGauss=# | SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary) FROM employees; 1 SQL> | 支持 |
| 84 | **STDDEV (Aggregate Function)** | SELECT STDDEV(salary) FROM employees; | openGauss=# SELECT STDDEV(salary) FROM employees; stddev -------- (1 row) | SQL> SELECT STDDEV(salary) FROM employees; 0 | 支持 |
| 85 | **STDDEV_POP (Aggregate Function)** | SELECT STDDEV_POP(salary) FROM employees; | openGauss=# SELECT STDDEV_POP(salary) FROM employees; stddev_pop ------------ 0 (1 row) | SQL> SELECT STDDEV_POP(salary) FROM employees; 0 | 支持 |
| 86 | **STDDEV_SAMP (Aggregate Function)** | SELECT STDDEV_SAMP(salary) FROM employees; | openGauss=# SELECT STDDEV_SAMP(salary) FROM employees; stddev_samp ------------- (1 row) | SQL> SELECT STDDEV_SAMP(salary) FROM employees; SQL> | 支持 |
| 87 | **SUM (Aggregate Function)** | SELECT sum(salary) FROM employees; | openGauss=# SELECT sum(salary) FROM employees; sum ------- 65000 (1 row) | SQL> SELECT sum(salary) FROM employees; 65000 | 支持 |
| 88 | **VAR_POP (Aggregate Function)** | SELECT VAR_POP(salary) FROM employees; | openGauss=# SELECT VAR_POP(salary) FROM employees; var_pop --------- 0 (1 row) openGauss=# | SQL> SELECT VAR_POP(salary) FROM employees; 0 | 支持 |
| 89 | **VAR_SAMP (Aggregate Function)** | SELECT VAR_SAMP(salary) FROM employees; | openGauss=# SELECT VAR_SAMP(salary) FROM employees; var_samp ---------- (1 row) openGauss=# | SQL> SELECT VAR_SAMP(salary) FROM employees; SQL> | 支持 |
| 90 | **VARIANCE (Aggregate Function)** | SELECT VARIANCE(salary) FROM employees; | openGauss=# SELECT VARIANCE(salary) FROM employees; variance ---------- (1 row) openGauss=# | SQL> SELECT VARIANCE(salary) FROM employees; 0 SQL> | 支持 |
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
