这个sqlserver的存储过程如何改成ORACLE的？？？

Question

看看oracle基础的pl/sql吧，这个很简单的
begin后的第一段就是一个select into，不过记得框在begin exception end里面，那么为空的情况就是NO_DATA_FOUND异常了，第二段代码就可以放在exception里

Answer 1

你试一下.可能还有些小小的改动CREATE OR REPLACE PROCEDURE DEPARTMENTADMIN(PARENTDEPARTMENTID INT,
                                            DEPARTMENTNAME     VARCHAR2,
                                            TEL1               VARCHAR2,
                                            TEL2               VARCHAR2,
                                            FAX                VARCHAR2,
                                            ISPUBLIC           VARCHAR2,
                                            DEPARTMENTID       INT OUTPUT,
                                            ISSTEPPASS         VARCHAR2) IS
  ID INTEGER := 0;
BEGIN
  SELECT DEPARTMENTID
    INTO ID
    FROM DEPARTMENTS
   WHERE PARENTDEPARTMENTID = PARENTDEPARTMENTID
     AND DEPARTMENTNAME = DEPARTMENTNAME;
  IF ID IS NULL THEN
    INSERT INTO DEPARTMENTS
      (PARENTDEPARTMENTID,
       DEPARTMENTNAME,
       TEL1,
       TEL2,
       FAX,
       ISPUBLIC,
       ISSTEPPASS)
    VALUES
      (PARENTDEPARTMENTID,
       DEPARTMENTNAME,
       TEL1,
       TEL2,
       FAX,
       ISPUBLIC,
       ISSTEPPASS);
    DEPARTMENTID := IDENTITY;
    COMMIT;
  ELSE
    DEPARTMENTID := -1;
  END IF;
  
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
     RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END DEPARTMENTADMIN;

Answer 2

create or replace PROCEDURE departmentadmin(
parentdepartmentID in number,
departmentname in varchar2,
tel1 in varchar2,
tel2 in varchar2,
fax in varchar2,
ispublic in number,
departmentid OUTPUT number,
IsStepPass in number
)
AS
id  number
BEGIN
select departmentid INTO id from departments 
where parentdepartmentid=parentdepartmentID 
and departmentname=departmentname;

if id is null then insert into departments (parentdepartmentid,departmentname,tel1,tel2,fax,ispublic,IsStepPass)
values( parentdepartmentID, departmentname,tel1, tel2 ,fax,ispublic,IsStepPass);

departmentid:=identity; else 
departmentid:=-1
end if;END departmentadmin;