Shulou(Shulou.com)06/01 Report--

1. Premise

First of all, you need to be familiar with both Oracle and PostgreSQL's SQL. The more detailed you understand it, the more advantageous it is. This article helps readers quickly understand what is the difference between these two types of SQL.

If you need to migrate Oracle to PG because of ACS/pg, you need to be familiar with AOLserver Tcl, especially SOLserver's API. This article mainly discusses:

Oracle 10g to 11g (most can be applied to 8i)

Oracle 12c will be different in some ways, but migration is easier

PostgreSQL 8.4 and even earlier versions.

2. Transaction

The Oracle database uses transactions, so PostgreSQL also needs to activate transactions. Multiple DML statements form a code snippet, and these statements are not immediately committed, so you need to start a transaction with the begin statement and then include these statements in the BEGIN block. The semantics of ROLLBACK, COMMIT and SAVEPOINT in Oracle and PG are the same. The isolation level of Oracle is also available in PostgreSQL. In most cases, the isolation level of PG (read submitted) meets the requirements.

3. Grammatical differences

There are a few SQL in PG with different syntax but the same function. ACS/pg converts automatically, only most of the functions are different and need to be converted manually. The work is done by db_sql_prep.

Function

Oracle has more than 250 built-in one-line functions and more than 50 aggregate functions. For more information, see https://wiki.postgresql.org/wiki/Oracle_Functions.

Sysdate

Oracle uses the sysdate function to get the current date and time, depending on the time zone of the server. Postgres uses' now'::timestamp as the date and time when the current transaction starts. ACS/pg wraps this as a sysdate () function.

ACS/pg also includes the Tcl process, or db_sysdate. Therefore:

Set now [database_to_tcl_string $db "select sysdate from dual"]

It should be:

Set now [database_to_tcl_string $db "select [db_sysdate] from dual"]

Dual table

Table DUAL can be used where table names are not actually needed in Oracle's SELECT, because the FROM clause in Oracle is required. FROM clauses can be discarded in Postgsql. You can eliminate the above problem by creating a view in postgres as the table. This makes it compatible with Postgres's SQL without interfering with Oracle's parser. During the migration, remove the "FROM DUAL" clause as much as possible. Because it's weird to join with jual.

ROWNUM and ROWID

Oracle's virtual column ROWNUM: assigns a numeric value when reading data before executing ORDER BY. You can use ROW_NUMBER () OVER (ORDER BY...) in many scenarios. Replace. However, using sequences for simulation may slow performance.

Oracle's virtual column ROWID: the physical address of the table row, encoded in base64. This column can be used in the application to temporarily cache the row address to make the second access more convenient. Postgres's ctid plays the same role.

Sequence

The sequence syntax of Oracle is sequence_name.nextval.

The sequence syntax of Postgres is nextval ('sequence_name').

In Tcl, getting and writing a sequence value can be abstracted as a call to [db_sequence_nextval $db sequence_name]. You can use [db_sequence_nextval_sql sequence_name] if you need to use sequence values in a complex SQL statement.

Decode

How to use the decoding function of Oracle: decode (expr, search, result [, search, result...] [, default])

To evaluate this expression, Oracle compares the expr and search values one by one. Returns the corresponding result if expr equals search,Oracle. If no match is found, either default or null is returned.

Postgres does not have such a structure, but you can use the following format instead:

CASE WHEN expr THEN expr [...] ELSE expr END

For example: CASE WHEN C1 = 1 THEN 'match' ELSE' no match' END, which returns the expression corresponding to the first true predicate.

DECODE and CASE are simulated in a way that is a little different: DECODE (x < NULL) returns NULL; if x is null, while CASE x WHEN NULL THEN 'null' ELSE' else' END returns the result of 'else'. Oracle is the same.

NVL

Oracle also has other convenience functions: NVL. If the first parameter is not returned for NULL,NVL, otherwise the second parameter is returned: start_date: = NVL (hire_date, SYSDATE);. If hire_date is NULL, the previous statement returns SYSDATE. Postgres and Oracle have a function that performs the same behavior in a more general way: coalesce (expr1, expr2, expr3,....), which returns the first non-NULL expression.

FROM subquery

Postgresql subqueries need to be enclosed in parentheses and provide an alias. Aliases are not required in Oracle:

Oracle: SELECT FROM (SELECT FROM table_a)

Postgresql: SELECT FROM (SELECT FROM table_a) AS foo

4. Functional differences

Postgresql does not have all the features of Oracle. ACS/pg addresses these limitations through the specified scheme. Although postgres has most of its features, some features have yet to be released.

Outer joins

Before the previous version of Oracle 9i, outer join:

SELECT a.field1, b.field2

FROM a, b

WHERE a.item_id = b.item_id (+)

(+) indicates that if there is no matching item_ id value in table b, the match will continue and will be matched as a blank line. Postgresql and Oracle 9i and previous versions:

SELECT a.field1, b.field2

FROM a

LEFT OUTER JOIN b

ON a.item_id = b.item_id

Join may not be used when only aggregate values are extracted from the outer joined table. If the original query:

SELECT a.field1, sum (b.field2)

FROM a, b

WHERE a.item_id = b.item_id (+)

GROUP BY a.field1

Postgres query: SELECT a.field1, b_sum_field2_by_item_id (a.item_id) FROM a. You can define the function at this time:

CREATE FUNCTION b_sum_field2_by_item_id (integer)

RETURNS integer

AS'

DECLARE

V_item_id alias for $1

BEGIN

RETURN sum (field2) FROM b WHERE item_id = v_item_id

END

'language 'plpgsql'

Oracle 9i will begin to support outer join syntax for SQL 99. But some programmers still use the old syntax, so this article makes sense.

CONNECT BY

Postgres does not support connect by statements. You can use WITH RECURSIVE instead. Because WITH RECURSIVE is completed by Turing, it is easy to convert CONNECT BY statements into WITH RECURSIVE. Sometimes you can think of CONNECT BY as a simple iterator:

SELECT... FROM DUAL CONNECT BY rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.