Oracle由ID生成父ID的函数

最新推荐文章于 2024-01-13 16:09:06 发布

weixin_34072857

最新推荐文章于 2024-01-13 16:09:06 发布

阅读量220

点赞数

文章标签：数据库

/*表结构*/
CREATE TABLE ly_md
(
  bh    VARCHAR2( 20 BYTE),
  mc    VARCHAR2( 20 BYTE),
  pym   VARCHAR2( 10 BYTE),
  f_bh  VARCHAR2( 20 BYTE),
  ch    NUMBER,
  ID    NUMBER
);

INSERT INTO ly_md SELECT "bh" ,"mc", "pym","f_bh" ,"ch", SEQ_LY_MD.nextval as ID FROM BDLLY. "ly_md"

--生成PID的函数--
CREATE OR REPLACE FUNCTION BDLLY.fun_GetPID(v_id IN NUMBER(10,0)) RETURN NUMBER IS 
v_pid NUMBER(10,0);
BEGIN 
SELECT id INTO v_pid FROM ly_md WHERE bh =(SELECT f_bh FROM ly_md WHERE id=v_id);
RETURN(v_pid);  
exception
  when others then RETURN(0); 
END fun_GetPID;
/

--调用函数--
SELECT fun_GetPID (1444) as pid FROM dual;

--更新PID--
UPDATE ly_md T1 SET PID=(SELECT id FROM ly_md WHERE bh =(SELECT f_bh FROM ly_md WHERE id=T1.ID))