存储过程语法介绍

CREATE [OR REPLACE] PROCEDURE [模式名.]存储过程 ([参数模式 [参数名] 数据类型 [default 默认值] [,…]])  AS
[标签]
[DECLARE
    --变量定义]
BEGIN
--注释
/*注释*/
    --语句执行
END;
[标签]
LANGUAGE PLPGSQL;

postgres=#  select prosrc from pg_proc where proname='proc_1';
             prosrc             
--------------------------------
                               +
 begin                         +
    raise notice 'Hello tdsql_pg';+
 end;                          +

(1 row)

postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS
$$
begin
   raise notice 'Hello，tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
postgres=# select prosrc from pg_proc where proname='proc_1';
             prosrc              
---------------------------------
                                +
 begin                          +
    raise notice 'Hello，tdsql_pg';+
 end;                           +

(1 row)

postgres=#

postgres=# call proc_1();
NOTICE:  Hello，tdsql_pg
CALL
postgres=#

postgres=# select * from pg_namespace;
      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
 pg_toast           |       10 |
 pg_temp_1          |       10 |
 pg_toast_temp_1    |       10 |
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 行记录)

postgres=# show search_path;
  search_path
----------------
 "$user",public
(1 行记录)

postgres=# select pg_namespace.nspname,pg_proc.prosrc from pg_proc,pg_namespace where 
pg_proc.pronamespace=pg_namespace.oid and pg_proc.proname='proc_1';
 nspname |             prosrc              
---------+---------------------------------
 public  |                                +
         | begin                          +
         |    raise notice 'Hello，tdsql_pg';+
         | end;                           +
         | 
(1 row)

postgres=# CREATE OR REPLACE FUNCTION proc_1() RETURNS void AS
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE FUNCTION

postgres=# CREATE  PROCEDURE proc_1() AS                      
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
ERROR:  function "proc_1" already exists with same argument types
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS                      
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
ERROR:  cannot change routine kind
DETAIL:  "proc_1" is a function.
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# drop procedure proc_1 ( );
DROP PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS
$$
begin
   raise notice '%',a_int;
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# drop procedure proc_1 ( a_int int);
DROP PROCEDURE
postgres=#

postgres=# drop procedure proc_1 (int);   
DROP PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS         
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# alter procedure proc_1() rename to proc_1_1;
ALTER PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS        
$$
begin
   raise notice '%',a_int;    
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# alter procedure proc_1 (a_int int) rename to proc_1_1;
ALTER PROCEDURE

postgres=# CREATE OR REPLACE PROCEDURE public.proc_1() AS
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
postgres=# 

postgres=# alter procedure public.proc_1() set schema myche;
ALTER PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE public.proc_1(a_int int) AS                           
$$
begin
   raise notice '%',a_int;
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# alter procedure public.proc_1 (int) set schema myche; 
ALTER PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE public.proc_1() AS
$$
begin
   raise notice 'Hello tdsql_pg';
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# alter procedure proc_1() owner to tdsql_pg_01_admin;
ALTER PROCEDURE
postgres=#

postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS
$$
begin
   raise notice '%',a_int;
end;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE

postgres=# alter procedure proc_1 (int) owner to tdsql_pg_01_admin;
ALTER PROCEDURE
postgres=#

postgres=# create or replace procedure p_no_para() as
$$
begin
    raise notice 'no_para procedure';
end;
$$
language plpgsql;
CREATE PROCEDURE
postgres=# call p_no_para();                         
NOTICE:  no_para procedure
CALL
postgres=#

postgres=# create or replace procedure p_para(a_int integer) as
$$
begin
    raise notice 'a_int = %',a_int;
end;
$$
language plpgsql;
CREATE PROCEDURE
postgres=# call p_para(1);
NOTICE:  a_int = 1
CALL
postgres=#