建立存储语法
CREATE [OR REPLACE] PROCEDURE [模式名.]存储过程 ([参数模式 [参数名] 数据类型 [default 默认值] [,…]]) AS[标签][DECLARE--变量定义]BEGIN--注释/*注释*/--语句执行END;[标签]LANGUAGE PLPGSQL;
[OR REPLACE] 更新存储介绍
带 OR REPLACE 的作用,在建立存储过程时若存在则替换,建立存储时不带 OR REPLACE 关键字,则遇到函数已经存系统则会报错,如下所示。
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$$beginraise notice 'Hello,tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# 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_pgCALLpostgres=#
[模式名.]存储过程名介绍
建立存储过程,模式名可以指定,也可以不指定,不指定则存放在当前模式下,如上面例子就没有指定模式名,则就存放在当前模式下,如下所示。
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 wherepg_proc.pronamespace=pg_namespace.oid and pg_proc.proname='proc_1';nspname | prosrc---------+---------------------------------public | +| begin +| raise notice 'Hello,tdsql_pg';+| end; +|(1 row)
因为 $user 模式不存在,所以存在 public 模式下。
存储过程与函数不能同名
如创建一个与函数同名的存储过程会提示 function xxx already exists with same argument types。
postgres=# CREATE OR REPLACE FUNCTION proc_1() RETURNS void AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE FUNCTIONpostgres=# CREATE PROCEDURE proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;ERROR: function "proc_1" already exists with same argument typespostgres=#
如果要替换,则提示。
postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;ERROR: cannot change routine kindDETAIL: "proc_1" is a function.postgres=#
删除存储过程
删除不带参数的存储过程
postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# drop procedure proc_1 ( );DROP PROCEDUREpostgres=#
删除带参数的存储过程
postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS$$beginraise notice '%',a_int;end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# drop procedure proc_1 ( a_int int);DROP PROCEDUREpostgres=#
也可以只指定参数的类型即可。
postgres=# drop procedure proc_1 (int);DROP PROCEDUREpostgres=#
存储过程修改名称
修改不带参数的存储过程名称
postgres=# CREATE OR REPLACE PROCEDURE proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# alter procedure proc_1() rename to proc_1_1;ALTER PROCEDUREpostgres=#
修改带参数的存储过程名称
postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS$$beginraise notice '%',a_int;end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# alter procedure proc_1 (a_int int) rename to proc_1_1;ALTER PROCEDURE
修改存储过程所属 schema
修改不带参数的存储过程 schema
postgres=# CREATE OR REPLACE PROCEDURE public.proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=#postgres=# alter procedure public.proc_1() set schema myche;ALTER PROCEDUREpostgres=#
修改带参数的存储过程 schema
postgres=# CREATE OR REPLACE PROCEDURE public.proc_1(a_int int) AS$$beginraise notice '%',a_int;end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# alter procedure public.proc_1 (int) set schema myche;ALTER PROCEDUREpostgres=#
修改存储过程所属用户
修改不带参数的存储过程所属用户
postgres=# CREATE OR REPLACE PROCEDURE public.proc_1() AS$$beginraise notice 'Hello tdsql_pg';end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# alter procedure proc_1() owner to tdsql_pg_01_admin;ALTER PROCEDUREpostgres=#
修改不带参数的存储过程所属用户
postgres=# CREATE OR REPLACE PROCEDURE proc_1(a_int int) AS$$beginraise notice '%',a_int;end;$$LANGUAGE PLPGSQL;CREATE PROCEDUREpostgres=# alter procedure proc_1 (int) owner to tdsql_pg_01_admin;ALTER PROCEDUREpostgres=#
存储过程执行
不带参数
postgres=# create or replace procedure p_no_para() as$$beginraise notice 'no_para procedure';end;$$language plpgsql;CREATE PROCEDUREpostgres=# call p_no_para();NOTICE: no_para procedureCALLpostgres=#
带参数
postgres=# create or replace procedure p_para(a_int integer) as$$beginraise notice 'a_int = %',a_int;end;$$language plpgsql;CREATE PROCEDUREpostgres=# call p_para(1);NOTICE: a_int = 1CALLpostgres=#