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

[fleamboy]

REATE PROCEDURE departmentadmin
@parentdepartmentID int,
@departmentname varchar(40),
@tel1 varchar(30),
@tel2 varchar(30),
@fax varchar(30),
@ispublic bit,
@departmentid int OUTPUT,
@IsStepPass bit
AS
DECLARE @id int
select @id=departmentid from departments where parentdepartmentid=@parentdepartmentID and
departmentname=@departmentname
if @id is null

begin

insert into departments (parentdepartmentid,departmentname,tel1,tel2,fax,ispublic,IsStepPass)
values( @parentdepartmentID, @departmentname,@tel1, @tel2 ,@fax,@ispublic,@IsStepPass)
set @departmentid=@@identity

end

else
begin
set @departmentid=-1
end
GO

[ern]

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

[liuyi8903]

你试一下.可能还有些小小的改动

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;

[dragonwzw]

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;