oracle存储过程入参为xml,oracle 存储过程 输入XML

CREATE OR REPLACE

PROCEDURE proc_sys_parameter(

pi_xml  IN  CLOB,     --收入参数(XML报文格式)

po_cod  OUT NUMBER,   --执行返回值(0=成功,非0代表失败)

po_msg  OUT VARCHAR2  --执行返回信息(成功或失败信息)

) IS

--系统参数设置

--收入参数报文格式

/*

100  

0  

AUTOMATIC_RECEIPT  

7  

自动收货  

1  

*/

--局部变量声明

vi_count          NUMBER(2);

vi_cod            NUMBER(6);

vc_msg            VARCHAR2(4000);

vc_body           CLOB;

vx_param_config   sys.xmlType;

--输入参数值存储变量

vi_opt_type      NUMBER(2);

vc_param_key     t_sys_parameter_config.parameter_key%TYPE;

vc_param_value   t_sys_parameter_config.parameter_value%TYPE;

vc_param_name    t_sys_parameter_config.parameter_name%TYPE;

vi_valid_flag    t_sys_parameter_config.valid_flag%TYPE;

--输入参数值提取路径

vc_opt_type_path       VARCHAR2(50) := '/XSF/HEAD/OPERATE_TYPE';

vc_param_key_path      VARCHAR2(50) := '/XSF/BODY/ROWS/ROW/PARAMETER_KEY';

vc_param_value_path    VARCHAR2(50) := '/XSF/BODY/ROWS/ROW/PARAMETER_VALUE';

vc_param_name_path     VARCHAR2(50) := '/XSF/BODY/ROWS/ROW/PARAMETER_NAME';

vc_valid_flag_path     VARCHAR2(50) := '/XSF/BODY/ROWS/ROW/VALID_FLAG';

--异常对象变量

EXP_XML_PARSE   EXCEPTION;

EXP_PARM_GET    EXCEPTION;

EXP_PARM_CHECK  EXCEPTION;

EXP_NOT_EXISTS  EXCEPTION;

EXP_EXISTS      EXCEPTION;

BEGIN

--变量初始化

vi_cod  := 1;

vc_msg  := '';

--输出参数初始化

dbms_lob.createTemporary(vc_body, TRUE);

--输入参数类型转换

BEGIN

vx_param_config := sys.xmlType.createXML(pi_xml);

EXCEPTION

WHEN OTHERS THEN

vi_cod := 1;

vc_msg := '输入参数[CLOB]类型转换为[XMLTYPE]类型失败';

RAISE EXP_XML_PARSE;

END;

--提取输入参数值

BEGIN

SELECT TRIM(ExtractValue(vx_param_config, vc_opt_type_path)),     --记录操作类型

TRIM(ExtractValue(vx_param_config, vc_param_key_path)),    --参数关键字

TRIM(ExtractValue(vx_param_config, vc_param_value_path)),  --参数值

TRIM(ExtractValue(vx_param_config, vc_param_name_path)),   --参数名称

TRIM(ExtractValue(vx_param_config, vc_valid_flag_path))    --参数有效标志

INTO vi_opt_type, vc_param_key, vc_param_value, vc_param_name, vi_valid_flag

FROM dual;

EXCEPTION

WHEN OTHERS THEN

vi_cod := 2;

vc_msg := '提取输入参数值失败:' || SQLERRM;

RAISE EXP_PARM_GET;

END;

--输入参数值验证

IF vi_opt_type NOT IN (-1,0,1) THEN

vi_cod := 3;

vc_msg := '操作类型参数值[' || vi_opt_type || ']无效!';

RAISE EXP_PARM_CHECK;

ELSIF vi_opt_type = 0 AND (vc_param_key IS NULL OR LENGTH(vc_param_key) = 0 OR vc_param_value IS NULL OR LENGTH(vc_param_value) = 0 OR vc_param_name IS NULL OR LENGTH(vc_param_name) = 0) THEN

vi_cod := 3;

vc_msg := '新增记录操作: 系统参数关键字、参数值、参数名称等参数值均不能为空!';

RAISE EXP_PARM_CHECK;

ELSIF vi_opt_type IN(-1, 1) AND (vc_param_key IS NULL OR LENGTH(vc_param_key) = 0) THEN

vi_cod := 3;

vc_msg := CASE WHEN vi_opt_type = -1 THEN '删除' ELSE '更新' END || '记录操作: 系统参数关键字参数值不能为空!';

RAISE EXP_PARM_CHECK;

END IF;

--记录存在性验证

SELECT COUNT(t.parameter_key) INTO vi_count

FROM t_sys_parameter_config t

WHERE t.parameter_key = vc_param_key;

IF vi_opt_type = 0 AND vi_count > 0 THEN

vi_cod := 101;

vc_msg := '系统参数配置[KEY=' || vc_param_key || ']记录已经存在,不能执行新增操作!';

RAISE EXP_EXISTS;

ELSIF vi_opt_type IN(-1,1) AND vi_count = 0 THEN

vi_cod := 100;

vc_msg := '系统参数配置[KEY=' || vc_param_key || ']记录不存在,不能执行更新或删除操作!';

RAISE EXP_NOT_EXISTS;

END IF;

--记录新增、修改和删除处理

IF vi_opt_type = -1 THEN

DELETE FROM t_sys_parameter_config t1 WHERE t1.parameter_key = vc_param_key;

ELSE

MERGE INTO t_sys_parameter_config ta

USING (

SELECT vc_param_key parameter_key, vc_param_value parameter_value, vc_param_name parameter_name, vi_valid_flag valid_flag

FROM dual

) tb

ON (ta.parameter_key = tb.parameter_key)

WHEN MATCHED THEN

UPDATE

SET ta.parameter_name  = NVL(tb.parameter_name, ta.parameter_name),

ta.parameter_value = NVL(tb.parameter_value, ta.parameter_value),

ta.valid_flag      = NVL(tb.valid_flag, ta.valid_flag),

ta.update_time     = SYSDATE

WHEN NOT MATCHED THEN

INSERT (parameter_key, parameter_value, parameter_name, valid_flag, create_time)

VALUES (tb.parameter_key, tb.parameter_value, tb.parameter_name, NVL(tb.valid_flag,1), SYSDATE);

END IF;

--执行成功

COMMIT;

po_cod := 0;

po_msg := CASE vi_opt_type

WHEN -1 THEN '删除'

WHEN 0  THEN '新增'

WHEN 1  THEN '更新'

END || '系统参数配置[KEY=' || vc_param_key || ']记录成功!';

--异常处理

EXCEPTION

WHEN EXP_XML_PARSE THEN

po_cod := vi_cod;

po_msg := vc_msg;

WHEN EXP_PARM_GET THEN

po_cod := vi_cod;

po_msg := vc_msg;

WHEN EXP_PARM_CHECK THEN

po_cod := vi_cod;

po_msg := vc_msg;

WHEN EXP_NOT_EXISTS THEN

po_cod := vi_cod;

po_msg := vc_msg;

WHEN EXP_EXISTS THEN

po_cod := vi_cod;

po_msg := vc_msg;

WHEN OTHERS THEN

ROLLBACK;

po_cod := SQLCODE;

po_msg := CASE vi_opt_type

WHEN -1 THEN '删除'

WHEN 0  THEN '新增'

WHEN 1  THEN '更新'

END || '系统参数配置[KEY=' || vc_param_key || ']记录失败:' || SQLERRM;

END proc_sys_parameter;