关于Oracle存储过程的问题。调用的时候报语法错误。

0 0

关于Oracle存储过程的问题。调用的时候报语法错误。5

该存储过程，传入一个用户帐号，然后先去SYS_USER 检查这个帐号有没有存在，
如果不存，就根据这个帐号去其他表查询出我要的信息然后分别插入SYS_USER 和SYS_USER_INFO
如果存在，就执行修改操作，也是去其他表将我要修改的字段查询出来，然后再执行UPDATE
存储过程如下：

CREATE OR REPLACE PROCEDURE "CMS"."SYN_SYS_USER"(PARTY_ID IN VARCHAR2(50)) IS
BEGIN
DECLARE
S_COUNT number
ORG_COUNT number
ORG_COUNT2 number
DI_SHI_CODE_TEMP VARCHAR2(50)
ATT1_TEMP VARCHAR2(50)
SELECT COUNT(*) INTO S_COUNT FROM SYS_USER SU1 WHERE SU1.PARTY_ID=PARTY_ID
IF (S_COUNT=0) THEN
SELECT COUNT(*) INTO ORG_COUNT  FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID)
IF (ORG_COUNT>0) THEN
ATT1_TEMP:='C'
SELECT ID INTO DI_SHI_CODE_TEMP FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID)
END IF
SELECT COUNT(*) INTO ORG_COUNT2  FROM LOCATION WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID)
IF (ORG_COUNT2>0) THEN
ATT1_TEMP:='D'
SELECT ID INTO DI_SHI_CODE_TEMP FROM LOCATION WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID)
END IF
INSERT INTO SYS_USER(ID, PARTY_ID, CURRENT_PASSWORD, ENABLED, USER_LOGIN_ID)
SELECT (SELECT MAX(SU.ID)+1 FROM SYS_USER SU) AS SUID,ULA.PARTY_ID,ULA.CURRENT_PASSWORD,ULA.ENABLED,ULA.PARTY_ID FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID
COMMIT
INSERT INTO SYS_USER_INFO(ID, USER_ID,LAST_NAME, MOBILEPHONE, TELEPHONE, GENDER,EMAIL,COMMENTS,IS_SEND_MSM,DI_SHI_CODE, ATT1)
SELECT (SELECT MAX(SUI.ID)+1 FROM SYS_USER_INFO SUI) AS SUIID,(SELECT SU2.ID FROM SYS_USER SU2 WHERE SU2.PARTY_ID=PARTY_ID) AS SU2ID,
PA.LAST_NAME,PA.MOBILEPHONE,PA.TELEPHONE,PA.GENDER,PA.EMAIL,PA.COMMENTS, PA.IS_SEND_MSM,DI_SHI_CODE_TEMP,ATT1_TEMP FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID
COMMIT
ELSE
FOR TEMP_USER IN (SELECT ULA.PARTY_ID, DECODE(ULA.ENABLED,'T',1,0) AS ENABLED,ULA.CURRENT_PASSWORD FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID) LOOP
UPDATE SYS_USER SET ENABLED=TEMP_USER.ENABLED,CURRENT_PASSWORD=TEMP_USER.CURRENT_PASSWORD WHERE PARTY_ID=TEMP_USER.PARTY_ID
END LOOP
FOR TEMP_USER_INFO IN (SELECT PA.PARTY_ID,PA.LAST_NAME,PA.MOBILEPHONE,PA.TELEPHONE,PA.GENDER,PA.EMAIL,PA.COMMENTS,PA.IS_SEND_MSM FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID) LOOP
UPDATE SYS_USER_INFO SET LAST_NAME=TEMP_USER_INFO.LAST_NAME,MOBILEPHONE=TEMP_USER_INFO.MOBILEPHONE,TELEPHONE=TEMP_USER_INFO.TELEPHONE,GENDER=TEMP_USER_INFO.GENDER,EMAIL=TEMP_USER_INFO.EMAIL,COMMENTS=TEMP_USER_INFO.COMMENTS,IS_SEND_MSM=TEMP_USER_INFO.IS_SEND_MSM
WHERE USER_ID=(SELECT SU.ID FROM SYS_USER WHERE SU.PARTY_ID=TEMP_USER_INFO.PARTY_ID)
END LOOP
END IF
Exception
When others then
Rollback
END

存储过程创建的代码在工具里执行没问题
在JAVA中是这样调用的

Session session = getSession();
		Connection connection = session.connection();
		CallableStatement proc = connection.prepareCall("{Call SYN_SYS_USER(?)}");
		proc.setString(1,"lintao");
		proc.execute();

然后报以下错误
java.sql.SQLException: ORA-06550: line 1, column 11:
PLS-00905: object CMS.SYN_SYS_USER is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)