Remove from dual, or not, because the orafce plugin provides dual tables
Sequence transformation: Change to nextval('seq')
Cast type conversion Here, take number as an example, add ::numberic to the character, of course, you can also create a custom type conversion function. Taking the most common varchar to number as an example, use the dba account to execute the following statement, create cast (varchar as numeric) with inout as implicit. The content of custom type conversion is not introduced here, you can use it to Baidu.
If you replace the Nvl function with coalesce, you can also create an nvl function in the database and implement the original nvl function function. Although orafce provides nvl function, it does not cover all types, so you still need to change it yourself.
Subqueries must have an alias
Aliases cannot appear in the set clause of Update
Sysdate is replaced by now(), and sysdate-1 is changed to now()-interval '1 D'. For more information about date calculation, you can use Baidu.
About recursive query, ordinary recursive query
with recursive tmp (staff_id, up_staff_id ) as (
select a.staff_id,a.up_staff_id
from staff a
where a.staff_id = 9527
union all select c.staff_id,c.up_staff_id
from staff c
join tmp t1 on c.staff_id = t1.up_staff_id
) select staff_id from tmp;
If there is a dead loop in recursion, a better idea:
with recursive tmp (staff_id, up_staff_id,visited ) as (
select a.staff_id,a.up_staff_id,array[a.staff_id] as visited
from staff a
where a.staff_id = 9527
union all select c.staff_id,c.up_staff_id,||c.staff_id
from staff c
join tmp t1 on c.staff_id = t1.up_staff_id
where c.staff_id <> all()
) select staff_id from tmp;
Add a traversal list, and if it exists, it will no longer query.
If the query result is a map, the query result is added with an alias enumerated in double quotes. This is because the key values of the result set returned by pg are all lowercase, which is exactly the opposite of oracle being all uppercase. If most of the projects return maps, and do not use double quotes to aliases, and the project uses mybatis, you can use mybatis object processing factory to convert the key values of all map return values into capitals, and the object processing factory objectWrapperFactory can be used to Baidu.
String types can use ='' and is null, but numeric types can only use is null, and the string types ='' and is null are not the same.