• 全部
  • Oracle 基础和管理
  • Oracle 高级技术
  • Oracle 认证与考试
  • 职位交流
  • 问答

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

fleamboy 2005-04-10 07:35:12
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
...全文
73 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dragonwzw 2005-04-11
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;
回复
liuyi8903 2005-04-11
你试一下.可能还有些小小的改动

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;
回复
ern 2005-04-10
看看oracle基础的pl/sql吧,这个很简单的
begin后的第一段就是一个select into,不过记得框在begin exception end里面,那么为空的情况就是NO_DATA_FOUND异常了,第二段代码就可以放在exception里
回复
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2005-04-10 07:35
社区公告
暂无公告