请教两个关于存储过程的问题。

Question

0 CREATE PROCEDURE [dbo].[up_SLK_ProgramsDao_InsertPrograms]
1  @ProgramID INT = NULL
2 , @ProgramName NVARCHAR(120) = NULL
3 , @MenuChk BIT = NULL
4 , @ParentProgramID INT = NULL
5 , @ClassName VARCHAR(100) = NULL
6 , @Path VARCHAR(50) = NULL
7 , @Url NVARCHAR(400) = NULL
8 , @ProgramTypeCode CHAR(1) = NULL
9 , @UserID VARCHAR(30) = NULL
10 , @Key1 VARCHAR(200) = NULL
11 , @Key2 VARCHAR(6) = NULL
12 AS
13 BEGIN
14  IF @ProgramID IS NULL RAISERROR('ProgramID不能为空，不能执行存储操作！',16,1)
15  IF @ProgramName IS NULL RAISERROR('ProgramName不能为空，不能执行存储操作！',16,1)
16
17  SET @ProgramID=ISNULL((SELECT MAX(ProgramID) FROM Programs),0)+1
18 
19  IF EXISTS(SELECT 1 FROM Programs WHERE [ProgramName]=@ProgramName) RAISERROR('ProgramName存在重复值，不能进行添加！',16,1)
20 
21  INSERT INTO Programs
22  ( [ProgramID]
23  , [ProgramName]
24  , [MenuChk]
25  , [ParentProgramID]
26  , [ClassName]
27  , [Path]
28  , [Url]
29  , [ProgramTypeCode])
30  VALUES
31  ( @ProgramID
32  , @ProgramName
33  , @MenuChk
34  , @ParentProgramID
35  , @ClassName
36  , @Path
37  , @Url
38  , @ProgramTypeCode)
39 END以上是一段SQL SERVER的存储过程，现在需要在ORACLE中实现同样的功能，目前遇到一下问题，请教高手：
1、第17行中，需要取得ProgramID的最大值加1，再赋值给@ProgramID,这样好实现字段值的自增，但在ORACLE中输入型参数的值不能被修改，难道只能重新定义一个变量吗？有没有别的简便的方法？另外MAX(ProgramID)的值也不知道如何赋值给@ProgramID了，这个只能先填到游标，再从游标中取吗？有没有不需要定义游标的方法？
2、第19行会提示EXISTING函数只能在SELECT语句中使用，有没有什么其它替代方法？我改成IF (SELECT COUNT(1) FROM Programs WHERE [ProgramName]=@ProgramName)>0 THEN ...也不行，在线等待...

Answer 1

1、【输入型参数的值不能被修改】
把 【@ProgramID】对应的oracle参数 显示定义成 【in out】 型。2、【MAX(ProgramID)的值也不知道如何赋值给@ProgramID了】
SELECT nvl(MAX(ProgramID),0)+1 INTO ProgramID FROM Programs;3、【第19行会提示EXISTING函数只能在SELECT语句中使用，有没有什么其它替代方法？】
定义一个 number 型的变量【cnt number;】
SELECT COUNT(1) INTO cntF ROM Programs WHERE ProgramName=@ProgramName;
IF cnt > 0 THEN
 ...
END IF;---
以上，希望对你有所帮助。

Answer 2

可以了。放在这MARK一下。CREATE OR REPLACE PROCEDURE Programs_Insert1(I_ProgramID   IN OUT NUMBER,
                                             I_ClassName   IN VARCHAR2,
                                             I_BaseRoleChk IN NUMBER,
                                             I_MenuChk     IN NUMBER,
                                             I_UseChk      IN NUMBER,
                                             I_PageName    IN NVARCHAR2,
                                             I_Path        IN VARCHAR2,
                                             I_ProgramName IN NVARCHAR2,
                                             I_UpProgramID IN NUMBER) AS
  M_Count INTEGER;
BEGIN
  IF I_ProgramID IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramID不能为空，不能执行存储操作！');
    RETURN;
  END IF;
  
  IF I_ProgramName IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramName不能为空，不能执行存储操作！');
    RETURN;
  END IF;
  
  SELECT NVL(MAX(ProgramID), 0)
    INTO I_ProgramID
    FROM Programs
   WHERE ProgramID = I_ProgramID;  SELECT NVL(COUNT(*), 0)
    INTO M_COUNT
    FROM Programs
   WHERE ProgramID = I_ProgramID
     AND ProgramName = I_ProgramName;
  IF M_COUNT > 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramName存在重复值，不能进行添加！');
    RETURN;
  END IF;  INSERT INTO Programs
    (ProgramID,
     ClassName,
     BaseRoleChk,
     MenuChk,
     UseChk,
     PageName,
     Path,
     ProgramName,
     UpProgramID)
  VALUES
    (I_ProgramID,
     I_ClassName,
     I_BaseRoleChk,
     I_MenuChk,
     I_UseChk,
     I_PageName,
     I_Path,
     I_ProgramName,
     I_UpProgramID);
END;

Answer 3

但在ORACLE中输入型参数的值不能被修改，难道只能重新定义一个变量吗？
输入参数的数值，没有必要修改。只有既作为输出又作为输入的参数(in out 类型的参数)可以被赋值

Answer 4

max(id)最好用序列来实现，效率较高
create sequence seqname
也不需要赋给programid,直接写到values后面就行了
CREATE PROCEDURE up_SLK_ProgramsDao_InsertPrograms(
    P_ProgramID in INT default NULL 
    P_ProgramName in NVARCHAR2 default NULL 
    P_MenuChk in number default NULL 
    P_ParentProgramID in INT default NULL 
    P_ClassName in VARCHAR2 default NULL 
    P_Path in VARCHAR2 default NULL 
    P_Url in NVARCHAR2 default NULL 
    P_ProgramTypeCode in CHAR default NULL 
    P_UserID in VARCHAR2 default NULL 
    P_Key1 in VARCHAR2 default NULL 
    P_Key2 in VARCHAR2 default NULL )
AS 
BEGIN 
IF P_ProgramID IS NULL then RAISE_APPLICATION_ERROR(-20000,'ProgramID不能为空，不能执行存储操作！') ; end if;
IF P_ProgramName IS NULL then RAISE_APPLICATION_ERROR(-20001,'ProgramName不能为空，不能执行存储操作！') ; end if;
 
 --P_ProgramID:=ISNULL((SELECT MAX(ProgramID) FROM Programs),0)+1 ;
 
 SELECT count(1) into v_count FROM Programs WHERE ProgramName=P_ProgramName;
 if v_count>0 then RAISE_APPLICATION_ERROR(-20003,'ProgramName存在重复值，不能进行添加！') ;end if;
 
 INSERT INTO Programs 
 ( ProgramID 
 , ProgramName 
 , MenuChk 
 , ParentProgramID 
 , ClassName 
 , Path 
 , Url 
 , ProgramTypeCode )
VALUES 
 ( SEQNAME.NEXTVAL 
 , P_ProgramName 
 , P_MenuChk 
 , P_ParentProgramID 
 , P_ClassName 
 , P_Path 
 , P_Url 
 , P_ProgramTypeCode) ;
 END 

Answer 5

如果不用序列，则需要再定义一个参数，假设为v_programid
SET @ProgramID=ISNULL((SELECT MAX(ProgramID) FROM Programs),0)+1 改成
select max(programid)+1 into v_programid from programs;建议用序列

Answer 6

传入参数间的逗号和最后一个end后的分号忘记加了，自己加上
如果需要过程中提交事务的话，end前加个commit;

Answer 7

没看到四楼的回复..
连续回复3次，只好换小号来说下I_ProgramID  IN OUT NUMBER这句最好不要这样
宁可多加一个临时变量。用out话，传入参数必须为一个变量，若为常量会出错SELECT NVL(MAX(ProgramID), 0) 
    INTO I_ProgramID 
    FROM Programs 
  WHERE ProgramID = I_ProgramID; 
这句nvl没有意义，如果没有符合条件的记录的话，nvl没用，会产生异常。应该用count来处理
select count(1) into v_count from ..where..
if v_count = 0 then i_programid:=0;SELECT NVL(COUNT(*), 0) 
    INTO M_COUNT 
    FROM Programs 
  WHERE ProgramID = I_ProgramID 
    AND ProgramName = I_ProgramName; 
这个nvl也没必要。count返回的是记录条数的值。必然是个正整数

Answer 8

好好看好再说话，不要....SELECT NVL(MAX(ProgramID), 0)
    INTO I_ProgramID
    FROM Programs
  WHERE ProgramID = I_ProgramID;
这句nvl没有意义，如果没有符合条件的记录的话，nvl没用，会产生异常。应该用count来处理

Answer 9

看来这位老兄看得比我仔细
何不把理由说来听听
批评完人就不吱声可不能服人哦我的理由已经说了，如果没有符合条件的记录，会发生异常，而不是交给nvl处理。没有特殊需求的话，这段可以不要

Answer 10

虽然问题暂时已经解决，不过还是非常感谢jiaruimin11的热心回答。
如果用序列的话如果是符合主键应该怎么用？比如主键有：
ProjectID和ProgramID
ProjectID是自增的，ProgramID则根据ProjectID为参考自增，那么ProgramID用序列可以解决吗？另外之所以将ProgramID定义为IN OUT型而不定义一个变量是因为这段代码是用源代码生成器（我自己写的）生成的。如果定义一个变量的话则处理起来比较复杂，而且如果出现两个或更多的自增字段则更麻烦了。关于NVL(COUNT(*),0)是因为我在资料上看到说ORACLE里头如果COUNT的数据为零会返回NULL，我也不知道是作者弄错了还是怎么，我再试一试。 NVL(MAX(ProgramID), 0)好像确实多余了，如果结果为NULL的话赋值的操作根本不会执行了。
这样的话我是否需要在后面加这样一句才行：I_ProgramID := NVL(I_ProgramID ,1)?再次感谢jiaruimin11，我需要的就是这样的指点啊。

Answer 11

1楼的代码由于生成的时候参数设置有误，因此有逻辑错误，为避免误导大家，现在重新贴出正确的代码：CREATE OR REPLACE PROCEDURE Programs_Insert1(I_ProgramID   IN OUT NUMBER,
                                             I_ClassName   IN VARCHAR2,
                                             I_BaseRoleChk IN NUMBER,
                                             I_MenuChk     IN NUMBER,
                                             I_UseChk      IN NUMBER,
                                             I_PageName    IN NVARCHAR2,
                                             I_Path        IN VARCHAR2,
                                             I_ProgramName IN NVARCHAR2,
                                             I_UpProgramID IN NUMBER) AS
  M_Count INTEGER;
BEGIN  IF I_ProgramID IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramID不能为空，不能执行存储操作！');
    RETURN;
  END IF;
  
  IF I_ProgramName IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramName不能为空，不能执行存储操作！');
    RETURN;
  END IF;
  
  SELECT MAX(ProgramID) INTO I_ProgramID FROM Programs;
  I_ProgramID := NVL(I_ProgramID,0) + 1;  SELECT COUNT(*)
    INTO M_COUNT
    FROM Programs
   WHERE ProgramName = I_ProgramName;  IF M_COUNT > 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
                            'ProgramName存在重复值，不能进行添加！');
    RETURN;
  END IF;  INSERT INTO Programs
    (ProgramID,
     ClassName,
     BaseRoleChk,
     MenuChk,
     UseChk,
     PageName,
     Path,
     ProgramName,
     UpProgramID)
  VALUES
    (I_ProgramID,
     I_ClassName,
     I_BaseRoleChk,
     I_MenuChk,
     I_UseChk,
     I_PageName,
     I_Path,
     I_ProgramName,
     I_UpProgramID);
END;这样大家看是否合理？

Answer 12

如果担心I_ProgramID可能为空的情况，用exception控制处理一下就可以了begin
      SELECT MAX(ProgramID) INTO I_ProgramID FROM Programs; 
   exception
      when no_data_found then
         I_ProgramID := 0;
   end;
   
   I_ProgramID := I_ProgramID + 1; 

Answer 13

NVL(MAX(ProgramID), 0)好像确实多余了，如果结果为NULL的话赋值的操作根本不会执行了。 
这样的话我是否需要在后面加这样一句才行：I_ProgramID := NVL(I_ProgramID ,1)? ---------------
这里虽然最初I_ProgramID传入时不为空，但是有可能在执行select max(programid) into后变为空。为了验证是否存在这种情况，可以做个测试：
清空Programs表中的所有数据，这样max(programid)实际上取不到值的，这种情况下可能会有几种情况：
1.I_ProgramID变为0；
2.I_ProgramID变为null；
3.报no_data_found错误；根据测试的情况写出具体的pl/sql处理、

Answer 14

楼主, 你是不是看错了, jiaruimin11貌似没说出这么多来啊, 不太公平哦...

Answer 15

因为有max函数，所以....[TEST@ora10gr1] SQL>select nvl(max(1),0) from dual where 1=2;NVL(MAX(1),0)
-------------
            0[TEST@ora10gr1] SQL>select max(1) from dual where 1=2;    MAX(1)
----------

Answer 16

试了一下，在没有分组的情况下聚合，确实能返回一条记录，学习了但传入参数设为in out有潜在危险，要保证传入的i_programid是个变量，而且执行完过程后这个变量值会发生改变。还有，既然programid的值要由max来决定，那么传入这个参数也变得没有必要，因为不管传入什么值都会被覆盖，如果为空还会出错
我的建议是，将这个传入参数去掉，既然不是由用户来决定的，就不要让用户来填写这个值
如果要用max来写，就将i_programid设为out型，或者增加一个临时变量
最好是用序列，创建时将初始值设为表中原有programid的最大值+1
用seq.nextval来insert就可以不要i_programid这个传入参数和临时变量，而且性能会高得多，特别在表数据量大的时候。
oracle一般使用序列来实现自增