该存储过程,传入一个用户帐号,然后先去SYS_USER 检查这个帐号有没有存在,
如果不存,就根据这个帐号去其他表查询出我要的信息然后分别插入SYS_USER 和SYS_USER_INFO
如果存在,就执行修改操作,也是去其他表将我要修改的字段查询出来,然后再执行UPDATE
存储过程如下:CREATE OR REPLACE PROCEDURE SYN_SYS_USER(PARTY_ID IN VARCHAR2) IS
S_COUNT NUMBER ,
ORG_COUNT NUMBER,
ORG_COUNT2 NUMBER,
DI_SHI_CODE_TEMP VARCHAR2(50),
ATT1_TEMP VARCHAR2(50)
BEGIN
S_COUNT:=SELECT COUNT(*) FROM SYS_USER SU1 WHERE SU1.PRTY_ID=PARTY_ID;
IF (S_COUNT=0) THEN
ORG_COUNT:=SELECT 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';
DI_SHI_CODE_TEMP:=SELECT ID FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID);
END IF;
ORG_COUNT2:= SELECT COUNT(*) 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';
DI_SHI_CODE_TEMP:=SELECT ID 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;
RAISE;
END;该存储过程,我分别在两个工具里面执行过
在 DbVisualizer 中 创建存储过程时报语法错误在 Oracle SQL Developer中 创建存储过程没问题,调用的时候报错DECLARE
party_id VARCHAR2;
BEGIN
set party_id='lintao'
exec SYN_SYS_USER(party_id in);
END;请各位帮忙看看,到底这存储过程有什么问题?
谢谢!存储过程OracleSQL
如果不存,就根据这个帐号去其他表查询出我要的信息然后分别插入SYS_USER 和SYS_USER_INFO
如果存在,就执行修改操作,也是去其他表将我要修改的字段查询出来,然后再执行UPDATE
存储过程如下:CREATE OR REPLACE PROCEDURE SYN_SYS_USER(PARTY_ID IN VARCHAR2) IS
S_COUNT NUMBER ,
ORG_COUNT NUMBER,
ORG_COUNT2 NUMBER,
DI_SHI_CODE_TEMP VARCHAR2(50),
ATT1_TEMP VARCHAR2(50)
BEGIN
S_COUNT:=SELECT COUNT(*) FROM SYS_USER SU1 WHERE SU1.PRTY_ID=PARTY_ID;
IF (S_COUNT=0) THEN
ORG_COUNT:=SELECT 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';
DI_SHI_CODE_TEMP:=SELECT ID FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID);
END IF;
ORG_COUNT2:= SELECT COUNT(*) 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';
DI_SHI_CODE_TEMP:=SELECT ID 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;
RAISE;
END;该存储过程,我分别在两个工具里面执行过
在 DbVisualizer 中 创建存储过程时报语法错误在 Oracle SQL Developer中 创建存储过程没问题,调用的时候报错DECLARE
party_id VARCHAR2;
BEGIN
set party_id='lintao'
exec SYN_SYS_USER(party_id in);
END;请各位帮忙看看,到底这存储过程有什么问题?
谢谢!存储过程OracleSQL
2)21和24行、select 少了个容器、比如 into
DI_SHI_CODE_TEMP:=SELECT ID FROM CARD_PROVIDER WHERE PROVIDER .......
应该用SELECT ID into DI_SHI_CODE_TEMP FROM CARD_PROVIDER WHERE PROVIDER .......
2、存储过程内不能直接出现select ..... FROM ...
应该和INTO 连接使用
这里是往SYS_USER表插入数据,而数据来源与其他表查询得出的。这个SQL
我单独执行的话是没问题的。
按照你的说法那得怎么改呢?
S_COUNT number;ORG_COUNT number;ORG_COUNT2 number;DI_SHI_CODE_TEMP VARCHAR2(50);ATT1_TEMP VARCHAR2(50);
BEGIN
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;
RAISE;
END;
USER_LOGIN_ID VARCHAR2(60) NOT NULL,
PARTY_ID VARCHAR2(60),
CURRENT_PASSWORD VARCHAR2(60),
PASSWORD_HINT VARCHAR2(255),
ENABLED VARCHAR2(60),
IS_ENCODE_M_D5 VARCHAR2(60),
LAST_CHANGE_PWD_DATE DATE,
DISABLED_DATE_TIME TIMESTAMP(6),
SUCCESSIVE_FAILED_LOGINS NUMBER(18),
SOURCE VARCHAR2(60),
LAST_UPDATED_STAMP TIMESTAMP(6),
LAST_UPDATED_TX_STAMP TIMESTAMP(6),
CONSTRAINT PK_USER_LOGIN_ALL PRIMARY KEY (USER_LOGIN_ID)
);CREATE TABLE PERSON_ALL (
PARTY_ID VARCHAR2(60) NOT NULL,
COMPANY_ID VARCHAR2(60),
LAST_NAME VARCHAR2(60),
FIRST_NAME VARCHAR2(60),
GENDER VARCHAR2(60),
TELEPHONE VARCHAR2(60),
IS_SEND_MSM VARCHAR2(60),
MOBILEPHONE VARCHAR2(60),
EMAIL VARCHAR2(255),
COMMENTS VARCHAR2(255),
LAST_UPDATED_STAMP TIMESTAMP(6),
LAST_UPDATED_TX_STAMP TIMESTAMP(6)
);CREATE TABLE SYS_USER (
ID NUMBER NOT NULL,
PARTY_ID VARCHAR2(60),
CURRENT_PASSWORD VARCHAR2(60),
PASSWORD_HINT VARCHAR2(255),
ENABLED NUMBER(2),
IS_ENCODE_M_D5 VARCHAR2(60),
LAST_CHANGE_PWD_DATE DATE,
DISABLED_DATE_TIME TIMESTAMP(6),
SUCCESSIVE_FAILED_LOGINS NUMBER(18),
SOURCE VARCHAR2(60),
LAST_UPDATED_STAMP TIMESTAMP(6),
LAST_UPDATED_TX_STAMP TIMESTAMP(6),
USER_LOGIN_ID VARCHAR2(60),
CONSTRAINT SYS_USER_BAK_PK PRIMARY KEY (ID)
);CREATE TABLE SYS_USER_INFO (
USER_ID NUMBER NOT NULL,
LAST_NAME VARCHAR2(200),
MOBILEPHONE VARCHAR2(200),
TELEPHONE VARCHAR2(200),
GENDER NUMBER,
EMAIL VARCHAR2(200),
COMMENTS VARCHAR2(200),
ID NUMBER(22) NOT NULL,
DI_SHI_CODE VARCHAR2(200),
ATT1 VARCHAR2(200),
IS_SEND_MSM VARCHAR2(20),
CONSTRAINT SYS_USER_INFO_BAK_PK PRIMARY KEY (ID)
);ALTER TABLE SYS_USER_INFO ADD CONSTRAINT SYS_USER_INFO_BAK_SYS_USE_FK1 FOREIGN KEY (USER_ID) REFERENCES SYS_USER (ID);
这里是往SYS_USER表插入数据,而数据来源与其他表查询得出的。这个SQL
我单独执行的话是没问题的。
按照你的说法那得怎么改呢?单独执行没问题?不可能吧?
改、比如:
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;改成:SELECT (SELECT MAX(SU.ID)+1 FROM SYS_USER SU) AS SUID,ULA.PARTY_ID,ULA.CURRENT_PASSWORD,ULA.ENABLED,ULA.PARTY_ID INTO 变量 FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID;
这里是往SYS_USER表插入数据,而数据来源与其他表查询得出的。这个SQL
我单独执行的话是没问题的。
按照你的说法那得怎么改呢?单独执行没问题?不可能吧?
改、比如:
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;改成:SELECT (SELECT MAX(SU.ID)+1 FROM SYS_USER SU) AS SUID,ULA.PARTY_ID,ULA.CURRENT_PASSWORD,ULA.ENABLED,ULA.PARTY_ID INTO 变量 FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID;
我的意思是,单独执行下面这个SQL是可以成功的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='lintao';我把这句放在存储过程里,仅仅是把具体的帐号'lintao'换成了参数
IS
S_COUNT NUMBER;
ORG_COUNT NUMBER;
ORG_COUNT2 NUMBER;
DI_SHI_CODE_TEMP VARCHAR2(50);
ATT1_TEMP VARCHAR2(50);
BEGIN SELECT COUNT(*)
INTO S_COUNT
FROM SYS_USER SU1
WHERE SU1.PRTY_ID=SYN_SYS_USER.PARTY_ID; IF (S_COUNT=0) THEN
BEGIN
SELECT COUNT(*)
INTO ORG_COUNT
FROM CARD_PROVIDER
WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.PARTY_ID); -- 要引用存储过程传过来的变量,但这个变量又与字段重名,这个时候应该加“对象限定符加以确定!
-- 猪01次! IF (ORG_COUNT>0) THEN
ATT1_TEMP:='C';
SELECT ID
INTO DI_SHI_CODE_TEMP -- 第一次给变量 DI_SHI_CODE_TEMP 赋值
FROM CARD_PROVIDER
WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID); IF (ORG_COUNT2>0) THEN
ATT1_TEMP:='D';
SELECT ID
INTO DI_SHI_CODE_TEMP -- 第二次给变量 DI_SHI_CODE_TEMP 赋值,将覆盖第一次的赋值。
-- -- 猪02次!
FROM LOCATION
WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID; 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=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID;
COMMIT;
END;
ELSE -- 一个关联更新就搞定了,你就慢慢循环去吧!
-- 猪03次!
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=SYN_SYS_USER.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; -- 一个关联更新就搞定了,你就慢慢循环去吧!
-- 猪04次!
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=SYN_SYS_USER.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;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- ROLLBACK; -- 出错会自动回滚!
-- 猪05次!
RAISE;
END;
/
IS
S_COUNT NUMBER;
ORG_COUNT NUMBER;
ORG_COUNT2 NUMBER;
DI_SHI_CODE_TEMP VARCHAR2(50);
ATT1_TEMP VARCHAR2(50);
BEGIN SELECT COUNT(*)
INTO S_COUNT
FROM SYS_USER SU1
WHERE SU1.PARTY_ID=SYN_SYS_USER.PARTY_ID; IF (S_COUNT=0) THEN
BEGIN
SELECT COUNT(*)
INTO ORG_COUNT
FROM CARD_PROVIDER
WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.PARTY_ID); -- 要引用存储过程传过来的变量,但这个变量又与字段重名,这个时候应该加“对象限定符加以确定!
-- 猪01次! IF (ORG_COUNT>0) THEN
ATT1_TEMP:='C';
SELECT ID
INTO DI_SHI_CODE_TEMP -- 第一次给变量 DI_SHI_CODE_TEMP 赋值
FROM CARD_PROVIDER
WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID); IF (ORG_COUNT2>0) THEN
ATT1_TEMP:='D';
SELECT ID
INTO DI_SHI_CODE_TEMP -- 第二次给变量 DI_SHI_CODE_TEMP 赋值,将覆盖第一次的赋值。
-- -- 猪02次!
FROM LOCATION
WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID
FROM PERSON_ALL PA
WHERE PA.PARTY_ID=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID; 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=SYN_SYS_USER.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=SYN_SYS_USER.PARTY_ID;
COMMIT;
END;
ELSE -- 一个关联更新就搞定了,你就慢慢循环去吧!
-- 猪03次!
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=SYN_SYS_USER.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; -- 一个关联更新就搞定了,你就慢慢循环去吧!
-- 猪04次!
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=SYN_SYS_USER.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 SU
WHERE SU.PARTY_ID=TEMP_USER_INFO.PARTY_ID);
END LOOP;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- ROLLBACK; -- 出错会自动回滚!
-- 猪05次!
RAISE;
END;
/
DECODE(ULA.ENABLED,'T',1,0) AS ENABLED,
ULA.CURRENT_PASSWORD
FROM USER_LOGIN_ALL ULA
WHERE ULA.PARTY_ID=SYN_SYS_USER.PARTY_ID)
LOOP这句循环,我是想根据传入的PARTY_ID去USER_LOGIN_ALL 查询出符合条件的记录,这个记录绝对只有一行
PARTY_ID是唯一的。