Migration from Oracle PL/SQL to Postgres plpgsql

On Thursday, this week, PL/SQL compiled in OracleCodeMigration to PostgreSQL plpgsql.

When I started to talk about the problem, the database ipvs probably felt rough after a week. Summary of the reasons: 1. In the past three years, apart from Oracle, almost no other databases have been used. I have been familiar with the elegant and elegant manners of the famous girl. I suddenly felt immature when I saw a girl at the cold door. 2. Postgres does not have a mature ide tool. I prefer database programming. I am used to Toad and Oracle developper, a very mature ide. When I first came into contact with ipvs, I tried my best to find a tool comparable to them, however, whether it is the official pgadmin or navicat, it makes me want to die. Later, it was hard to find a recent free dreamcoder to use or die! There is no way to make people open-source without free and delicious lunch.

To put it bluntly, PL/SQL and plpgsql are both standard SQL statements combined with some control statements. The syntax is mostly the same. I will only list the differences found during the migration process as follows:

1. varchar2 --> varchar (varchar2 is a data format defined by Oracle. It is originated from the standard SQL data format defined by varchar)

2. There is no procedure in Postgres, there is only a function, and there is a set of annoying definitions: ($ body $ what is this ?)

Create or replace function function_name ()

Returns void as $ body $

Declare

V_parament int;

Begin

End;

$ Body $ language plpgsql;

3. The variable must be defined under the declare keyword, as shown in the red letter above.

4. The cursor mode has changed and no cursor variable exists. Previously, the SELECT statement defined by Oracle in the cursor variable must be directly written after the for statement as follows:

ForV_cursor_paramentIn select .......

Loop

End loop;

The variable marked red must also be declared as: v_cursor_parament record;

5. The row variable rowtype is the same as that in Oracle. However, if you use rowtype for insert, you must write the following statement:

Insert into table_name values (rowtype_parament .*)

6. The Oracle table join operator (+) is available. Use the standard SQL left (right) Outer Join.

7. If you do not have the instr function, you can implement it yourself.

8. The GOTO statement does not exist, but you can use the continue statement directly. This is a good news.

9. Minus does not have it. Instead, minus does not have it. The usage and effect are the same.

10. I cannot understand this. I want to write this statement when calling another function in a function:

Select function_name ();

Ele. Me! What is this!

11. The rownum used in Oracle to obtain the first record is gone, but we have a more powerful limit... offset

12. nvl () does not exist. coalesct () is also used ()

13. Exception Capture: Postgres also has a wide range of exception definitions. The capture method is similar to that of Oracle:

Begin

Exception when Condition

Then operate

End;

However, if there is no smooth sailing, there is always a problem. ipvs does not regard it as a warning exception, and there is a large number of exceptions applied in Oracle: no_data_found.

If you want to force ipvs to run out of this error, you need to do this:Select into strict...

14. Oracle's to_char () does not exist. functions with the same name are strange.

15. There is no dual, but dual is not required in ipvs.

The above are the differences I encountered during the migration process. I hope you can add them. Thank you.