存储过程的编辑

1、dm

oracle的名称不可重复，参数不同也不可以重复

CREATE OR REPLACE PROCEDURE dm_p1(a VARCHAR2, b VARCHAR2) IS BEGIN SELECT a+b; END;

注释不可修改

drop procedure dm_p1；

2、oracle

oracle的名称不可重复，参数不同也不可以重复

CREATE OR REPLACE PROCEDURE oracle_pro(num1 IN NUMBER, num2 IN NUMBER) AS result NUMBER;
BEGIN result := num1 + num2; END;

注释不可修改

drop procedure oracle_pro；

3、mysql

mysql名称不可重复，参数不同也不可以重复

CREATE OR REPLACE mysql_pro(IN `country` char(3),OUT `cities` INT) begin select 1; END;

drop procedure mysql_pro；

alter procedure mysql_pro comment '视图注释'；

4、pg

pg的名称在参数不一样时可重复
CREATE OR REPLACE PROCEDURE pg_pro() LANGUAGE sql AS $$ select 1; $$;
CREATE OR REPLACE PROCEDURE pg_pro(IN a integer, IN b integer)
LANGUAGE sql AS $$ select a+b; $$;

drop procedure pg_pro(IN a integer, IN b integer)

comment on procedure pg_pro(IN a integer, IN b integer) is '存储过程注释'

1、oracle

--数量
SELECT COUNT(*) FROM ALL_OBJECTS 
WHERE OBJECT_TYPE='PROCEDURE' AND OWNER='LDS' AND OBJECT_NAME in('ORACLE_PROCEDURE_1');

--名称、定义、注释
SELECT
CASE WHEN C.LINE = 1 THEN B.OBJECT_NAME ELSE '' END AS PROCEDURE_NAME,
CASE WHEN C.LINE = 1 THEN TO_CHAR(B.CREATED, 'YYYY-MM-DD hh24:mi:ss') ELSE '' END AS PROCEDUR_CREATE,
C.TEXT PROCEDUR_CONTENT
FROM
( SELECT A.*, ROWNUM AS ROWNO FROM
 (SELECT * FROM ALL_OBJECTS WHERE OWNER='LDS' AND OBJECT_TYPE='PROCEDURE' AND OBJECT_NAME in('ORACLE_PROCEDURE_1') ORDER BY OBJECT_NAME ASC) A
  WHERE ROWNUM <= 10
) B
LEFT JOIN ALL_SOURCE C ON B.OWNER=C.OWNER AND B.OBJECT_TYPE=C.TYPE AND B.OBJECT_NAME=C.NAME
WHERE B.ROWNO > 0 ORDER BY B.OBJECT_NAME, C.LINE;

2、mysql

--数量
SELECT COUNT(*)
FROM `information_schema`.`ROUTINES`
WHERE `ROUTINE_TYPE`='PROCEDURE' and `ROUTINE_SCHEMA`='rbc_test';

--名称、注释
SELECT `ROUTINE_NAME`, `CREATED`, `ROUTINE_COMMENT`
FROM `information_schema`.`ROUTINES`
WHERE ROUTINE_TYPE='PROCEDURE' and ROUTINE_SCHEMA='rbc_test' 
ORDER BY ROUTINE_NAME LIMIT 0,10;

--定义
SHOW CREATE PROCEDURE `db_name`.`mysql_procedure_1`;

3、DM

--数量
SELECT COUNT(*) FROM ALL_OBJECTS WHERE OWNER='public' AND OBJECT_TYPE='PROCEDURE';

--名称、定义、注释
SELECT B.OBJECT_NAME AS PROCEDURE_NAME, 
B.CREATED AS PROCEDUR_CREATE, C.TEXT PROCEDUR_CONTENT
FROM
( SELECT A.*, ROWNUM AS ROWNO FROM
  (SELECT * FROM ALL_OBJECTS WHERE OWNER='public' AND OBJECT_TYPE='PROCEDURE' ORDER BY OBJECT_NAME ASC) A
  WHERE ROWNUM <= 10
) B
LEFT JOIN ALL_SOURCE C ON B.OWNER=C.OWNER AND C.TYPE='PROC' AND B.OBJECT_NAME=C.NAME
WHERE B.ROWNO > 0;

4、PG

--版本在11以上才支持存储过程
select version()；

--数量
SELECT COUNT(*) TOTAL_NUM
FROM pg_proc pr
JOIN pg_namespace pn ON (pr.pronamespace = pn.oid)
left JOIN pg_description pd ON (pd.objoid=pr.oid and pd.objsubid=0)
where pn.nspname = 'public' AND pr.prokind='p'；

--名称、定义、注释
SELECT
pr.proname || '(' || pg_get_function_identity_arguments(pr.oid) || ')' as function_name,
pg_get_functiondef(pr.oid) as function_content, pd.description
FROM pg_proc pr
JOIN pg_namespace pn ON (pr.pronamespace = pn.oid)
left JOIN pg_description pd ON (pd.objoid=pr.oid and pd.objsubid=0)
where pn.nspname = 'public' AND pr.prokind='p'
order by function_name
LIMIT 10 OFFSET 0；