Kingbase简单存储过程

1.存储过程查询

SELECT *
FROM pg_proc
JOIN pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid)
WHERE prokind = 'p' AND nspname NOT LIKE 'pg_%' AND nspname NOT LIKE 'information_schema%' AND nspname NOT LIKE 'sys%' AND proname LIKE 'getDivisionSys%';

2.存储过程创建

-- 根据参数查询并返回一行一列
CREATE OR REPLACE PROCEDURE getDivisionSysName(p_division_code varchar(100)) AS
DECLARE p_name varchar(100);
BEGIN 
	SELECT 
		tb2.SYS_NAME INTO p_name
	FROM
		base_division_sys_view tb1
		LEFT JOIN base_sys tb2 ON tb1.SYS_CODE = tb2.SYS_CODE
	WHERE tb1.DIVISION_CODE = p_division_code LIMIT 1;
	SELECT p_name;
END;

CALL getDivisionSysName('530000000000');

-- 根据参数查询并返回一行多列
CREATE OR REPLACE PROCEDURE getDivisionSysNameAndCode(p_division_code varchar(100)) AS
DECLARE 
	p_name varchar(100);
	p_code varchar(100);
BEGIN 
	SELECT 
		tb2.SYS_CODE,tb2.SYS_NAME INTO p_code,p_name
	FROM
		base_division_sys_view tb1
		LEFT JOIN base_sys tb2 ON tb1.SYS_CODE = tb2.SYS_CODE
	WHERE tb1.DIVISION_CODE = p_division_code LIMIT 1;
	SELECT p_code,p_name;
END;

CALL getDivisionSysNameAndCode('530000000000');

-- 根据参数查询并返回多行一列
CREATE OR REPLACE PROCEDURE getDivisionSysNameList(p_division_code varchar(100)) AS
BEGIN 
	SELECT DISTINCT 
		tb2.SYS_NAME AS p_name
	FROM
		base_division_sys_view tb1
		LEFT JOIN base_sys tb2 ON tb1.SYS_CODE = tb2.SYS_CODE
	WHERE tb1.DIVISION_CODE = p_division_code AND tb2.SYS_NAME IS NOT NULL;
END;

CALL getDivisionSysNameList('530000000000');

-- 将多条结果遍历单个输出
CREATE OR REPLACE PROCEDURE getDivisionSysNameListConcat(p_division_code varchar(100)) AS
DECLARE 
	-- 游标
  	cursor c_result is 
  	SELECT DISTINCT 
		tb2.SYS_NAME AS sys_name
	FROM
		base_division_sys_view tb1
		LEFT JOIN base_sys tb2 ON tb1.SYS_CODE = tb2.SYS_CODE
	WHERE tb2.SYS_NAME IS NOT NULL AND tb1.DIVISION_CODE = p_division_code;
 
  	-- 定义一个与游标一样的变更
  	v_result c_result%rowtype;
BEGIN 
	-- 打开游标
	OPEN c_result;
		-- 开始循环
		LOOP 
			-- 获取游标记录
			FETCH c_result INTO v_result;  
		    EXIT WHEN c_result%notfound;
		    	SELECT v_result.sys_name;
		-- 结束循环
		END LOOP;
	-- 关闭游标
	CLOSE c_result;
	
	-- 异常处理
	EXCEPTION
	WHEN OTHERS THEN 
    BEGIN 
      dbms_output.put_line('报异常啦~~');
    END; 
END;

CALL getDivisionSysNameListConcat('530000000000');

3.存储过程删除

DROP PROCEDURE getDivisionSysName;
DROP PROCEDURE getDivisionSysName(p_division_code varchar(100));