openGauss的个人空间 / openGauss /
正文

【我和openGauss的故事】原生openGauss与Oracle数据库函数兼容性对比验证测试

openGauss
openGauss
2023/08/08 18:00
阅读数 116
AI总结
本文被收录于专区
数据库
进入专区参与更多专题讨论

一、测试目的

近期研究了原生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> 

支持


本文分享自微信公众号 - openGauss（openGauss）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

展开阅读全文

本文转载自网络

举报
加载中
点击引领话题📣 发布并加入讨论🔥

关于作者

openGauss
Lv8
openGauss
私信
提问
文章
943
经验值
1.4W
粉丝
94
关注
63

作者的专辑

全部
openGauss(943)

作者的其它热门文章

▪ {{formatHtml(o.title)}}

热门资讯

1
Spring Boot 3.4 正式发布
2
Ant Design X 1.0 发布，Ant Design 全新 AGI 组件库
3
人人影视将二十年字幕文件及数据开源分享
4
OpenWrt 项目组发布 OpenWrt One 路由器
5
这些热门 APP 竟都采用了 uni-app 框架开发
6
PostgreSQL 将不再支持 MD5 密码
7
“1 亿人/天”产品计划：下 app，上 idea
8
开源日报 | 人人网停止服务；首款专为OpenWrt设计的路由器；我们这个时代最疯狂的程序员；编程高薪神话末日？
9
马斯克申请禁令，阻止 OpenAI 转型为营利性公司
10
Qt Creator 15 发布

推荐关注

换一批
匠心码农
匠心码农
文章 53
访问 1.3W
爱编程的浪子
爱编程的浪子
文章 77
访问 1.6W
ericSM
ericSM
文章 144
访问 7.3W
邓草
邓草
文章 46
访问 1W
wlnmp
wlnmp
文章 3
访问 19

热门软件

GeSHi - HTML代码高亮库
date4j - Java日期时间处理类
Spring Insight
Siena - 简易Java数据持久层框架
Luke - Lucene 索引查看工具
MRTG - 系统监控工具
JSwiff - Java 的 Flash 类库
Tokyo Cabinet - 哈希表数据库
Jazz - IBM软件开发协作平台
QtJambi - 基于JVM平台的Qt移植
Gambas - VB开发工具
EasyImage - 图片效果制作的Jar包
Abduction! - FireFox抓屏插件
Yii - PHP开发框架
Kismet - 无线网络探测器
MySQL Cluster - MySQL集群
OpenKM - 文档管理系统
MyDNS - DNS服务器软件
MonoDroid - 微软.NET for Android
Subversion Edge
0 评论
0 收藏
0 赞
分享
AI总结
返回顶部
顶部