DECLARE @DEPTCODE CHAR(20)
DECLARE @DEPTNAME NVARCHAR(30)
DECLARE @STOPFLAG BIT
SELECT @DEPTCODE=DEPTCODE,@DEPTNAME=DEPTNAME,
@STOPFLAG= STOPFLAG
FROM PUB_DEPARTMENT
WHERE DEPTCODE='062103'
AND XSFLAG=1
IF @DEPTCODE IS NULL
RAISERROR('此部门不存在!',16,1)
ELSE
BEGIN
IF @STOPFLAG=0
SELECT @DEPTCODE AS DEPTCODE,@DEPTNAME AS DEPTNAME,1 AS STOPFLAG
ELSE
RAISERROR('此部门已停用!',16,1)
END怎么转化成oracle中使用呢?特别是异常那块怎么处理呢?
DECLARE @DEPTNAME NVARCHAR(30)
DECLARE @STOPFLAG BIT
SELECT @DEPTCODE=DEPTCODE,@DEPTNAME=DEPTNAME,
@STOPFLAG= STOPFLAG
FROM PUB_DEPARTMENT
WHERE DEPTCODE='062103'
AND XSFLAG=1
IF @DEPTCODE IS NULL
RAISERROR('此部门不存在!',16,1)
ELSE
BEGIN
IF @STOPFLAG=0
SELECT @DEPTCODE AS DEPTCODE,@DEPTNAME AS DEPTNAME,1 AS STOPFLAG
ELSE
RAISERROR('此部门已停用!',16,1)
END怎么转化成oracle中使用呢?特别是异常那块怎么处理呢?
declare
v_deptcode varchar2(20);
v_deptname varchar2(30);
v_stopflag number(1);
begin
select deptcode,deptname,stopflag
into v_deptcode,v_deptname,v_stopflag
from pub_department
where deptcode='062103'
and xsflag=1;
if stopflag=0 then
dbms_output.put_line(v_deptcode||' '||v_deptname||' '||1);
else dbms_output.put_line('此部门已停用!');
end if;
exception
when no_data_found then
dbms_output.put_line('此部门不存在!');
when others then
dbms_output.put_line('查询出错!');
end;
稍作修改:
declare
v_deptcode varchar2(20);
v_deptname nvarchar2(30);--此处为nvarchar2(30)
DECLARE
V_DEPTCODE VARCHAR2(20);
V_DEPTNAME VARCHAR2(30);
V_DEPTNAME char(1);
IS
BEGIN
SELECT V_DEPTCODE INTO DEPTCODE,V_DEPTNAME INTO DEPTNAME,V_DEPTNAME INTO STOPFLAG
FROM PUB_DEPARTMENT WHERE DEPTCODE='062103' AND XSFLAG=1;
IF V_DEPTCODE IS NULL
RAISERROR('此部门不存在!',16,1);
ELSE
BEGIN
IF V_DEPTNAME=0
SELECT V_DEPTCODE AS DEPTCODE,V_DEPTNAME AS DEPTNAME,1 AS STOPFLAG FROM DUAL;
ELSE
RAISERROR('此部门已停用!',16,1);
END
END
declare
v_deptcode pub_department.deptcode%type;
v_deptname pub_department.deptname%type;
v_stopflag pub_department.stopflag%type;
begin
select deptcode,deptname,stopflag
into v_deptcode,v_deptname,v_stopflag
from pub_department
where deptcode='062103'
and xsflag=1;
if stopflag=0 then
dbms_output.put_line(v_deptcode||' '||v_deptname||' '||1);
else dbms_output.put_line('此部门已停用!');
end if;
exception
when no_data_found then
dbms_output.put_line('此部门不存在!');
when others then
dbms_output.put_line('查询出错!');
end;
--试试
DECLARE
v_DEPTCODE varCHAR(20);
v_DEPTNAME NVARCHAR(30);
v_STOPFLAG number;
begin
SELECT DEPTCODE,DEPTNAME,STOPFLAG into v_DEPTCODE,v_DEPTNAME,v_STOPFLAG FROM PUB_DEPARTMENT
WHERE DEPTCODE='062103' AND XSFLAG=1;
if v_STOPFLAG=0 then
dbms_output.put_line('此部门已停用!'||v_DEPTCODE||' '||v_DEPTNAME||' '||1);
end if;
exception
when no_data_found then
dbms_output.put_line('此部门不存在!');
END;