oracle plsql develop,Oracle vs PostgreSQL Develop（25） - plsql vs plpgsql（语法严谨性）

Oracle的SQL和PL/SQL语法相对宽松，而PostgreSQL则相对严格一些。

在PL/SQL中，某些语句后可以不加分号，而PG则要求必须加分号。

**Oracle**

创建存储过程，loop不管加不加分号，均可创建成功。

```

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**

创建存储过程，loop后必须加分号，才可创建成功。

```

[local:/data/run/pg12]:5120 pg12@testdb=# CREATE OR REPLACE PROCEDURE proc_test(pi_in int)

pg12@testdb-# as

pg12@testdb-# $$

pg12@testdb$# declare

pg12@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';

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 block

LINE 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-# as

pg12@testdb-# $$

pg12@testdb$# declare

pg12@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=#

```

**参考资料**

N/A

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-2670335/，如需转载，请注明出处，否则将追究法律责任。