oracle 创建简单的函数
CREATE OR REPLACE FUNCTION hello/**********************************************************
Author : fuqiang WX155924
Version : v1.00
Date : 2013-01-06
Description : 目录上下移动
******************************************************/
(i_source_objid IN VARCHAR2, -- 原目录objectid
i_tag_objid IN VARCHAR2 -- 要移动到得目标objectid
-- o_resulet out number
) RETURN INT AS-- 迁移是否成功 1:失败 0：成功
v_sourcePid VARCHAR2(30); -- 源目录的Pid
v_tagpid VARCHAR2(30); --目标目录的Pid
BEGIN
-- 入口参数判断
IF i_source_objid IS NULL OR i_tag_objid IS NULL THEN
RETURN 1;
END IF;
-- 存储他们各自的pid
select category.parentid
into v_sourcePid
from T_CMP_TYPE_CATEGORY category
where category.objectid = i_source_objid;
select category.parentid
into v_tagpid
from T_CMP_TYPE_CATEGORY category
where category.objectid = i_tag_objid;
if v_sourcePid = v_tagpid then
RETURN (1);
end if;
--上移的时候i_source_objid > i_tag_objid
if i_source_objid > i_tag_objid then
------------------------ 更新 T_CMP_TYPE_CATEGORY ----------------------
--(源目录)
update T_CMP_TYPE_CATEGORY t
set t.objectid = i_tag_objid
where t.parentid = v_sourcePid
and t.objectid = i_source_objid;
--(目标目录)
update T_CMP_TYPE_CATEGORY t
set t.objectid = i_source_objid
where t.parentid = v_sourcePid
and t.objectid = i_tag_objid;
------------------------ 更新 T_CMP_EXT_MEDIACATEGORY ----------------------
--(源目录)
update T_CMP_EXT_MEDIACATEGORY t
set t.objectid = i_tag_objid
where t.objectid = i_source_objid;
--更新(目标目录)
update T_CMP_EXT_MEDIACATEGORY t
set t.objectid = i_source_objid
where t.objectid = i_tag_objid;
------------------------ 更新 T_CMP_TYPE_MEDIACATEGORY ----------------------
-- (源目录)
update T_CMP_TYPE_MEDIACATEGORY t
set t.objectid = i_tag_objid
where t.objectid = i_source_objid;
--(目标目录)
update T_CMP_TYPE_MEDIACATEGORY t
set t.objectid = i_source_objid
where t.objectid = i_tag_objid;
------------------------ 更新 mdsp_t_categoryinfo ------------------------
-- (源目录)
update mdsp_t_categoryinfo t
set t.objectid = i_tag_objid
where t.objectid = i_source_objid;
--(目标目录)
update mdsp_t_categoryinfo t
set t.objectid = i_source_objid
where t.objectid = i_tag_objid;
RETURN 0;
end if;
EXCEPTION
WHEN OTHERS THEN
RETURN 2;
END hello;
页:
[1]