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;