CREATE OR REPLACE PACKAGE BODY PKG_USERS_CUD IS
PROCEDURE Sys_Users_CreateUpdateDelete(
V_ID NUMBER, -- 用户编号
V_LoginID varchar:='', -- 登录ID
V_Password varchar:='', -- 用户密码
V_Status varchar:='', -- 用户状态
V_PhotoPath varchar:='', -- 用户照片
Action NUMBER, -- 执行的操作类型
stateCount out NUMBER
)
AS
BEGIN
DECLARE
strSQL VARCHAR2(1000);
v_count number;
BEGIN
--- 创建用户
stateCount:=1;
IF Action = 0 THEN
BEGIN
-- 检查用户名是否存在
strSQL:='SELECT COUNT(login_id) FROM sys_users WHERE login_id ='''|| V_LoginID ||'''';
EXECUTE IMMEDIATE strSQL into v_count;
IF v_count>0 THEN
stateCount:= 2;
ELSE
BEGIN
-- 插入新用户记录
INSERT INTO sys_users(
id,
login_id,
password,
status,
photo_path)
VALUES(
SEQ_USERS.NEXTVAL,
V_LoginID,
V_Password,
V_Status,
V_PhotoPath);
END;
END IF;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
-- 更新用户
ELSIF Action = 1 THEN
BEGIN
UPDATE
sys_users
SET
password = V_Password,
status = V_Status,
photo_path = V_PhotoPath
WHERE
id = V_ID;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
-- 删除用户
ELSIF Action = 2 THEN
BEGIN
DELETE FROM sys_users WHERE id = V_ID;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
END IF;
EXCEPTION WHEN OTHERS THEN
stateCount:= -1;
END;
END;
END;
-------------------------------------------------这是我写的对一个表增删改的存储过程,设置了一个输出参数是stateCount,如果有异常的话就给他赋值为-1,没有异常则返回1,但是我发现有错误虽然进入了EXCEPTION处理块但是却没有给这个参数赋值,就是返回出去的总是1,我想问下有没有哪位高手知道是啥问题啊。。谢谢!
stateCount:= -1;
你没忘记先set serveroutput on吧?大家来帮下MM啊~~~~~~~~~~~
才能看得到语句dbms_output.put_line(stateCount);
输出的值
ELSIF Action = 2 THEN
这两句话是什么意思呢?
PROCEDURE Sys_Users_CreateUpdateDelete(
V_ID NUMBER, -- 用户编号
V_LoginID varchar:='', -- 登录ID
V_Password varchar:='', -- 用户密码
V_Status varchar:='', -- 用户状态
V_PhotoPath varchar:='', -- 用户照片
Action NUMBER, -- 执行的操作类型
stateCount out NUMBER
)
AS strSQL VARCHAR2(1000);
v_count number;
BEGIN
--- 创建用户
stateCount:=1;
IF Action = 0 THEN
BEGIN
-- 检查用户名是否存在
strSQL:='SELECT COUNT(login_id) FROM sys_users WHERE login_id ='''|| V_LoginID ||'''';
EXECUTE IMMEDIATE strSQL into v_count;
IF v_count>0 THEN
stateCount:= 2;
ELSE
BEGIN
-- 插入新用户记录
INSERT INTO sys_users(
id,
login_id,
password,
status,
photo_path)
VALUES(
SEQ_USERS.NEXTVAL,
V_LoginID,
V_Password,
V_Status,
V_PhotoPath);
END;
END IF;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
-- 更新用户
ELSIF Action = 1 THEN
BEGIN
UPDATE
sys_users
SET
password = V_Password,
status = V_Status,
photo_path = V_PhotoPath
WHERE
id = V_ID;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
-- 删除用户
ELSIF Action = 2 THEN
BEGIN
DELETE FROM sys_users WHERE id = V_ID;
--EXCEPTION WHEN OTHERS THEN
-- stateCount:= -1;
END;
END IF;
EXCEPTION WHEN OTHERS THEN
stateCount:= -1;
END; begin
1:=1;END;