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/,如需转载,请注明出处,否则将追究法律责任。