Oracle vs PostgreSQL Develop (25)-plsql vs plpgsql (grammatical rigor)
Oracle's SQL and PL/SQL syntax is relatively loose, while PostgreSQL is relatively strict.
In PL/SQL, some statements can be followed without a semicolon, while PG requires that a semicolon be added.
Oracle
To create a stored procedure, loop can be created successfully, regardless of whether you add a semicolon or not.
TEST-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE PROCEDURE proc_test (a NUMBER) as 2 v_id number; 3 begin 4 for i in (select 1 from dual) loop 5 select 2 into v_id from dual; 6 end loop 7 return; 8 end; 9 / Procedure created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE PROCEDURE proc_test (a NUMBER) as 2 v_id number; 3 begin 4 for i in (select 1 from dual) loop 5 select 2 into v_id from dual 6 end loop; 7 return; 8 end; 9 / Procedure created.TEST-orcl@DESKTOP-V430TU3 >
PostgreSQL
To create a stored procedure, you must add a semicolon after the loop before it can be created successfully.
[local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE PROCEDURE proc_test (pi_in int) pg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_id int;pg12@testdb$# I record;pg12@testdb$# begin pg12@testdb$# for i in (select 1 from dual) looppg12@testdb$# select 2 into v_id from dual; pg12@testdb$# end looppg12@testdb$# return; pg12@testdb$# end;pg12@testdb$# $$LANGUAGE 'plpgsql' T (pi_in int) as$$declare v_id int; I record;begin for i in (select 1 from dual) loop select 2 into v_id from dual; end loop; return; end;$$ LANGUAGE 'plpgsql';ERROR: end label "return" specified for unlabelled blockLINE 11: return ^ [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# CREATE OR REPLACE PROCEDURE proc_test (pi_in int) pg12@testdb-# aspg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_id int;pg12@testdb$# I record Pg12@testdb$# begin pg12@testdb$# for i in (select 1 from dual) loop pg12@testdb$# select 2 into v_id from dual; pg12@testdb$# end loop;pg12@testdb$# return; pg12@testdb$# end;pg12@testdb$# $$LANGUAGE 'plpgsql';CREATE PROCEDURE [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=#
references
N/A
