创建存储过程:
CREATE OR REPLACE
PROCEDURE "TEST_PROC"(NAME IN VARCHAR2,DM IN VARCHAR2) IS
ZJ VARCHAR2(40);
JH VARCHAR2(10);
VERRINFO VARCHAR2(200);
USER_COUNT NUMBER;
BEGIN
ZJ:= 'YH0' || TRUNC(100000000000+900000000000*DBMS_RANDOM.VALUE);
SELECT XZQH INTO JH FROM ZF_T_SYS_DWDM WHERE DWDM = DM;
INSERT INTO ZF_T_SYS_YH(ID,XM,JH) VALUES(ZJ,NAME,JH);
FOR I IN (SELECT * FROM ZF_T_SYS_YH) LOOP
SELECT COUNT(1) INTO USER_COUNT FROM APP_USER WHERE JYID = I.ID;
IF USER_COUNT > 0 THEN
UPDATE APP_USER SET USERNAME = I.XM WHERE JYID=I.ID;
ELSE
INSERT INTO APP_USER(ID,JYID,USERNAME) VALUES(TRUNC(100000000000+900000000000*DBMS_RANDOM.VALUE),I.ID,I.XM);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE));
VERRINFO := SUBSTR(SQLERRM, 1, 200);
INSERT INTO ERR(ERRINFO,CREATETIME) VALUES(VERRINFO,SYSDATE);
COMMIT;
END;
END TEST_PROC;
调用存储过程:
DECLARE
NAME VARCHAR2(20);
DM VARCHAR2(40);
BEGIN
NAME:='姓名';
DM:='000000000000';
TEST_PROC(NAME,DM);
END;
备注:Oracle的存储过程创建建议采用PL/SQL工具,Navicat Premium创建存储过程容易出错。(同样的过程在PL/SQL上执行成功,在Navicat Premium上执行报错......)