/*表结构*/

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))