原生openGauss与Oracle数据库函数兼容性对比验证测试

SELECT ASCII('A') FROM DUAL;

openGauss=# SELECT ASCII('A') FROM DUAL;
ascii
-------
65
(1 row)

SQL> SELECT ASCII('A') FROM DUAL;

ASCII('A')
----------
65

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> 

支持