嗨,各位朋友这是我在SQL2000中的存储过程
CREATE PROCEDURE dbo.s_User_Update
(
@return varchar(50) output,
@uid varchar(15),
@uname varchar(50),
@upwd varchar(50),
@urid char(2),
@udid char(3)
)
AS
BEGIN TRAN
IF EXISTS (SELECT * FROM tblUser WHERE UID=@uid)
BEGIN
SET @return='User Exist'
END
ELSE
BEGIN
INSERT INTO tblUser (UID,UName,UPWD,
Dept_DeptID,Right_RID) VALUES (@uid,@uname,@upwd,@udid,@urid)
SET @return='True'
END
COMMIT TRAN
RETURN GO
在Oracle9i中要如何写这个存储过程,如果不用存储过程用函数又如何写.谢谢
CREATE PROCEDURE dbo.s_User_Update
(
@return varchar(50) output,
@uid varchar(15),
@uname varchar(50),
@upwd varchar(50),
@urid char(2),
@udid char(3)
)
AS
BEGIN TRAN
IF EXISTS (SELECT * FROM tblUser WHERE UID=@uid)
BEGIN
SET @return='User Exist'
END
ELSE
BEGIN
INSERT INTO tblUser (UID,UName,UPWD,
Dept_DeptID,Right_RID) VALUES (@uid,@uname,@upwd,@udid,@urid)
SET @return='True'
END
COMMIT TRAN
RETURN GO
在Oracle9i中要如何写这个存储过程,如果不用存储过程用函数又如何写.谢谢
Sreturn out varchar2(50),
suid in varchar2(15),
uname in varchar2(50),
upwd varchar2(50),
urid in char(2),
udid in char(3)
)
is
bolexist number;
begin
SELECT count(1) into bolexist FROM tblUser WHERE UID = Suid;
if bolexist > 0 then
Sreturn := 'User Exist';
else
INSERT INTO tblUser (UID,UName,UPWD,Dept_DeptID,Right_RID) VALUES (Suid,uname,upwd,udid,urid);
Sreturn :='True';
end if;
commit;
end s_User_Update;
(
v_uid varchar(15),
v_uname varchar(50),
v_upwd varchar(50),
v_urid char(2),
v_udid char(3),
v_return varchar(50) out
)ISl_count NUMBER;
BEGIN
SELECT count(*) into l_count FROM tblUser WHERE UID=v_uid;
IF l_count>0 THEN
v_return='User Exist';
RETURN;
ELSE
INSERT INTO tblUser (UID,UName,UPWD,
Dept_DeptID,Right_RID) VALUES (v_uid,v_uname,v_upwd,v_udidv_urid)
v_return='True';
END IF;
COMMIT;
RETURN;
END P_compile;
/
CREATE PROCEDURE dbo.s_User_Update
(
"@uid"in VARCHAR,
"@uname" in VARCHAR,
"@upwd" in VARCHAR,
"@urid" in VARCHAR,
"@udid" in VARCHAR,
"@return" out varchar
)ISl_count NUMBER;
BEGIN
SELECT count(*) into l_count FROM tblUser WHERE UID="@uid";
IF l_count>0 THEN
v_return='User Exist';
ELSE
INSERT INTO tblUser (UID,UName,UPWD,
Dept_DeptID,Right_RID) VALUES ("@uid","@uname","@upwd","@udid","@urid")
v_return='True';
END IF;
END P_compile;
/