求助，请帮忙看一个Oracle存储过程到底有什么错误，谢谢！

闭目鱼 2013-06-19 05:26:02
该存储过程，传入一个用户帐号，然后先去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;



请各位帮忙看看，到底这存储过程有什么问题？
谢谢！
...全文
363 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
关于 

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
这句循环，我是想根据传入的PARTY_ID去USER_LOGIN_ALL 查询出符合条件的记录，这个记录绝对只有一行 PARTY_ID是唯一的。
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
引用 13 楼 luoyoumou 的回复:
鉴定结果：大蠢猪!
该存储过程是我通过查资料拼凑出来的，有低级错误的地方多谢你帮我指正出来 关于给给变量 DI_SHI_CODE_TEMP 赋值，我的用意就是如此，COMPANY_ID的值在LOCATION和CARD_PROVIDER都有对应的数据，但是绝对不会重复。所以虽然我代码里面赋值2次，但肯定只会存在一种情况
luoyoumou 2013-06-20
  • 打赏
  • 举报
 回复 
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

  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;
/
luoyoumou 2013-06-20
  • 打赏
  • 举报
 回复 
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

  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;
/
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
29行发现一个问题，结尾没加“;”已经补充上去了 创建存储过程依然报语法错误
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
引用 9 楼 linwaterbin 的回复:
[quote=引用 6 楼 li7134551921 的回复:] [quote=引用 1 楼 linwaterbin 的回复:] 1）2-6行是双引号结尾、不是单引号 2）21和24行、select 少了个容器、比如 into
这里是往SYS_USER表插入数据，而数据来源与其他表查询得出的。这个SQL 我单独执行的话是没问题的。 按照你的说法那得怎么改呢？[/quote] 单独执行没问题？不可能吧？ 改、比如： 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; [/quote] 我的意思是，单独执行下面这个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'换成了参数
linwaterbin 2013-06-20
  • 打赏
  • 举报
 回复
引用 6 楼 li7134551921 的回复:
[quote=引用 1 楼 linwaterbin 的回复:] 1）2-6行是双引号结尾、不是单引号 2）21和24行、select 少了个容器、比如 into
这里是往SYS_USER表插入数据，而数据来源与其他表查询得出的。这个SQL 我单独执行的话是没问题的。 按照你的说法那得怎么改呢？[/quote] 单独执行没问题？不可能吧？ 改、比如： 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;
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
附上建表语句 

CREATE TABLE USER_LOGIN_ALL (
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);
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
引用 4 楼 lzd_83 的回复:
2-6行不能逗号结尾的，应该是分号结尾的。
现在改成这样子，执行创建存储过程时还是有错误， 

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
     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;
闭目鱼 2013-06-20
  • 打赏
  • 举报
 回复
引用 1 楼 linwaterbin 的回复:
1）2-6行是双引号结尾、不是单引号 2）21和24行、select 少了个容器、比如 into
这里是往SYS_USER表插入数据，而数据来源与其他表查询得出的。这个SQL 我单独执行的话是没问题的。 按照你的说法那得怎么改呢？
luoyoumou 2013-06-20
  • 打赏
  • 举报
 回复
或者说：当 满足指定条件1，执行赋值1的SQL；当 满足指定条件2，执行赋值2的SQL；
luoyoumou 2013-06-20
  • 打赏
  • 举报
 回复
但你的DI_SHI_CODE_TEMP 变量的两次赋值是个并列关系，很可能第一次赋值本来是正确的，却被第二次赋值覆盖呢，或者说只要第一次赋值成功，就不应该作第二次赋值(不做额外无用功呢) 这个时候，你至少应该：当第一次赋值失败时，再执行第二次赋值操作。起码的逻辑关系得搞清楚。
Rotel-刘志东 2013-06-19
  • 打赏
  • 举报
 回复
21和24行应该select 少into
Rotel-刘志东 2013-06-19
  • 打赏
  • 举报
 回复
2-6行不能逗号结尾的，应该是分号结尾的。
sych888 2013-06-19
  • 打赏
  • 举报
 回复
1、赋值操作有问题 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 连接使用
Wentasy 2013-06-19
  • 打赏
  • 举报
 回复
目测是字符问题，检查下。
linwaterbin 2013-06-19
  • 打赏
  • 举报
 回复
1）2-6行是双引号结尾、不是单引号 2）21和24行、select 少了个容器、比如 into
oracle存储过程到底是什么,，请帮忙看一个Oracle存储过程到底有什么东东，多谢...
求助，请帮忙看一个Oracle存储过程到底有什么错误，谢谢！该存储过程，传入一个用户帐号，然后先去SYS_USER检查这个帐号有没有存在，如果不存，就根据这个帐号去其他表查询出我要的信息然后分别插入SYS_USER和SYS_USER_INFO如果存在，就执行修改操作，也是去其他表将我要修改的字段查询出来，然后再执行UPDATE存储过程如下：CREATEORREPLACEPROCEDURE...
.net oracle 输出参数,求助..NET调用带输出参数的存储过程时老报错.
该楼层疑似违规已被系统折叠隐藏此楼查看此楼.NET 调用带输出参数的存储过程老报错。求大神帮忙.报错：ORA-06550:第一行,第7个栏位,PLS-00306.呼叫 CHECK_PART_ID_MARK时使用的引数数目或类型错误 ORA-06550:第1行,第7个栏位。。。------------------------------------------------------------...
oracle游标耗资源,Oracle游标泄露（open_cursor耗尽）
前言编者注，本文的知识点较多，请补充足够正能量后阅读，看完会让你质疑你的DBA生涯，作者的水平代表着国内Oracle TroubleShooting最高水平。很早就想把ORA-1000的问题，总结成一个话题。机缘巧合，最近恰好遇上几个此类问题，特分享分析过程出来供大家参考。首先，ORA-1000报错是什么意思呢？我们来看看官方的解释：1.png非常简单，单个进程打开的游标数超过了最大值，也就是超过...
oracle px msg pool,ora-04031 ora-12583 ora-12801 ora-12853 求助
本帖最后由 snowermine 于 2013-3-12 13:32 编辑各位大侠，遇到一问题，数据库oracle11.2.0.3在跑一些存储过程的时候会遇到错误：alert.log如下：Thu Mar 07 22:13:11 2013Errors in file /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/trace/rwaprod1_p127_...
Oracle数据库资料收藏
1、UNIX基本培训 - [fjlin]2、AIX入门与提高（1）－中文 - [richard_he]3、PL/SQL上课记录 - [jxdco]4、[分享]2005年IBM原厂的AIX培训资料 - [fsm]5、Oracle入门　让学习更轻松 - [migrator]6、我看得懂的Oracle管理器OEM操作指南 - [tiren]7、一些平时收集的Oracle的资料 - [ll7777]8、O
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章