Oracle由ID生成父ID的函数

 

/*表结构*/
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))