MYSQL的,一个ERP项目CREATE DEFINER=`root`@`localhost` PROCEDURE `S_DelSellMaster`(OutMasterID integer) BEGIN DECLARE done INT DEFAULT 0; DECLARE DetialID INTEGER; DECLARE cur CURSOR FOR SELECT ID FROM SellDetialTab WHERE SellMasterID=OutMasterID; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur; REPEAT FETCH cur INTO DetialID; IF NOT done THEN CALL S_DelSellDetial(DetialID);#另一个存储过程 END IF; UNTIL done END REPEAT; CLOSE cur;delete from SellMasterTab where id=OutMasterID; END
oracle存储过程就跟你在delphi上用pascal写过程基本相同,写写就知道了
用sqlplus编译,再查看错误,就可以知道你写的过程哪些不正确
CREATE PROCEDURE PR_X_XSLYQKTJ (VAR_TJTJ IN VARCHAR2, VAR_TJRMC IN VARCHAR2, VAR_TJSJ IN VARCHAR2, VAR_RXRQ IN VARCHAR2, VAR_XYMC IN VARCHAR2, VAR_FX IN VARCHAR2, VAR_BJMC IN VARCHAR2, VAR_ZYMC IN VARCHAR2, VAR_STATE OUT VARCHAR2) is BEGIN VAR_STATE:=NULL; DELETE FROM TA_X_XSLYQK WHERE TJTJ =VAR_TJTJ ; INSERT INTO TA_X_XSLYQK SELECT VAR_TJTJ,B.SFMC,B.SFHM,0,0,0,0,0,0,0,VAR_TJRMC,SYSDATE FROM TA_P_SF B; UPDATE TA_X_XSLYQK SET BSRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='博士生' ); UPDATE TA_X_XSLYQK SET SSRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='硕士生' ); UPDATE TA_X_XSLYQK SET YJRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='研究生在读' ); UPDATE TA_X_XSLYQK SET SSRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='硕士生' ); UPDATE TA_X_XSLYQK SET BKRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='普通本科' ); UPDATE TA_X_XSLYQK SET CBRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='专升本' ); UPDATE TA_X_XSLYQK SET WLRS=(select count( JG) FROM TA_P_XSJBXX WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='网络招生' ); UPDATE TA_X_XSLYQK SET HJRS=BSRS+SSRS+YJRS+BKRS+CBRS+WLRS; INSERT INTO TA_X_XSLYQK SELECT VAR_TJTJ,'总计',COUNT(SFHM)+1,SUM(BSRS),SUM(SSRS), SUM(YJRS),SUM(BKRS),SUM(CBRS),SUM(WLRS),SUM(HJRS),VAR_TJRMC,VAR_TJSJ FROM TA_X_XSLYQK WHERE TJTJ=VAR_TJTJ; INSERT INTO TA_X_XSLYQK VALUES(VAR_TJTJ,'甲','00',1,2,3,4,5,6,7,VAR_TJRMC,VAR_TJSJ); VAR_STATE:='1'; END;给你看看~~~ ORACLE上的 嘿嘿
不过存储过程代码写在ORACLE上,DELPHI中代码就简单的调用,不知道你会不会PL/SQL.
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE DetialID INTEGER;
DECLARE cur CURSOR FOR SELECT ID FROM SellDetialTab
WHERE SellMasterID=OutMasterID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO DetialID;
IF NOT done THEN
CALL S_DelSellDetial(DetialID);#另一个存储过程
END IF;
UNTIL done END REPEAT;
CLOSE cur;delete from SellMasterTab where id=OutMasterID;
END
(VAR_TJTJ IN VARCHAR2,
VAR_TJRMC IN VARCHAR2,
VAR_TJSJ IN VARCHAR2,
VAR_RXRQ IN VARCHAR2,
VAR_XYMC IN VARCHAR2,
VAR_FX IN VARCHAR2,
VAR_BJMC IN VARCHAR2,
VAR_ZYMC IN VARCHAR2,
VAR_STATE OUT VARCHAR2)
is
BEGIN
VAR_STATE:=NULL; DELETE FROM TA_X_XSLYQK WHERE TJTJ =VAR_TJTJ ;
INSERT INTO TA_X_XSLYQK SELECT VAR_TJTJ,B.SFMC,B.SFHM,0,0,0,0,0,0,0,VAR_TJRMC,SYSDATE FROM
TA_P_SF B;
UPDATE TA_X_XSLYQK SET BSRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='博士生' ); UPDATE TA_X_XSLYQK SET SSRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='硕士生' );
UPDATE TA_X_XSLYQK SET YJRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='研究生在读' ); UPDATE TA_X_XSLYQK SET SSRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='硕士生' ); UPDATE TA_X_XSLYQK SET BKRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='普通本科' ); UPDATE TA_X_XSLYQK SET CBRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='专升本' ); UPDATE TA_X_XSLYQK SET WLRS=(select count( JG) FROM TA_P_XSJBXX
WHERE SFMC=SUBSTR(JG,0,2) AND JG is not null AND XYMC like VAR_XYMC AND BJMC like VAR_BJMC
AND ZYMC like VAR_ZYMC AND SZNJ like VAR_RXRQ AND FY like VAR_FX AND PYCC ='网络招生' );
UPDATE TA_X_XSLYQK SET HJRS=BSRS+SSRS+YJRS+BKRS+CBRS+WLRS; INSERT INTO TA_X_XSLYQK SELECT VAR_TJTJ,'总计',COUNT(SFHM)+1,SUM(BSRS),SUM(SSRS),
SUM(YJRS),SUM(BKRS),SUM(CBRS),SUM(WLRS),SUM(HJRS),VAR_TJRMC,VAR_TJSJ
FROM TA_X_XSLYQK WHERE TJTJ=VAR_TJTJ; INSERT INTO TA_X_XSLYQK VALUES(VAR_TJTJ,'甲','00',1,2,3,4,5,6,7,VAR_TJRMC,VAR_TJSJ); VAR_STATE:='1';
END;给你看看~~~ ORACLE上的 嘿嘿