declare sDeptCode VARCHAR(20) :='0001';
sDeptCodeReturn out varchar2(20) ;
sDeptName out Varchar2(50) ;
nStopFlag out NUMBER ;
dynamic_sql VARCHAR2(800);BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
exception
when no_data_found then
begin
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
end;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dynamic_sql :=' select ' + sDeptCodeReturn + ' as deptcode, ' + sDeptName + ' as DeptName,' + nStopFlag + ' as StopFlag from System.Pub_Department' ;
execute immediate dynamic_sql;
end;
sDeptCodeReturn out varchar2(20) ;
sDeptName out Varchar2(50) ;
nStopFlag out NUMBER ;
dynamic_sql VARCHAR2(800);BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
exception
when no_data_found then
begin
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
end;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dynamic_sql :=' select ' + sDeptCodeReturn + ' as deptcode, ' + sDeptName + ' as DeptName,' + nStopFlag + ' as StopFlag from System.Pub_Department' ;
execute immediate dynamic_sql;
end;
sDeptCodeReturn varchar2(20) ;
sDeptName Varchar2(50) ;
nStopFlag NUMBER ;
dynamic_sql VARCHAR2(800);BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
exception
when no_data_found then
begin
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
end;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dynamic_sql :=' select ' + sDeptCodeReturn + ' as deptcode, ' + sDeptName + ' as DeptName,' + nStopFlag + ' as StopFlag from System.Pub_Department' ;
execute immediate dynamic_sql;
end;刚才多了几个关键字 out
了也没显示么?
而且单独执行下面的语句是可以出数据的
select 1 as deptcode, 2 as DeptName, 3 as StopFlag from System.Pub_Department
去掉out 其他没什么问题 另外你最后的执行动态语句有何用 返回结果集?
我这边调试没有任何问题
sDeptCodeReturn varchar2(20) ;
sDeptName Varchar2(50) ;
nStopFlag NUMBER ;
dynamic_sql VARCHAR2(800);BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
exception
when no_data_found then
begin
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
end;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dynamic_sql :=' select ' + sDeptCodeReturn + ' as deptcode, ' + sDeptName + ' as DeptName,' + nStopFlag + ' as StopFlag from System.Pub_Department' ;
execute immediate dynamic_sql;
end;为什么没有返回数据,有知道的么,请多关照,
你的逻辑有问题啊 declare
sDeptCode VARCHAR(20) := '0001';
sDeptCodeReturn varchar2(20);
sDeptName Varchar2(50);
nStopFlag NUMBER;
dynamic_sql VARCHAR2(800);
--要么给nStopFlag附初值 nStopFlag NUMBER := 0;BEGIN SELECT deptcode, DeptName, StopFlag
INTO sDeptCodeReturn, sDeptName, nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001';
--要么 dbms_output.put_line(nStopFlag);exception
when no_data_found then
begin
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
end;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dynamic_sql := ' select ' + sDeptCodeReturn + ' as deptcode, ' +
sDeptName + ' as DeptName,' + nStopFlag +
' as StopFlag from System.Pub_Department';
execute immediate dynamic_sql;
--要么 dbms_output.put_line(dynamic_sql);end;
declare
sDeptCode VARCHAR(20) :='0001';
sDeptCodeReturn varchar2(20) ;
sDeptName Varchar2(50) ;
nStopFlag NUMBER ;BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dbms_output.put_line(sDeptCodeReturn||' '||sDeptName ||' '||nStopFlag );
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
sDeptCode VARCHAR(20) :='0001';
sDeptCodeReturn varchar2(20) ;
sDeptName Varchar2(50) ;
nStopFlag NUMBER ;BEGIN
SELECT deptcode,DeptName,StopFlag
INTO sDeptCodeReturn, sDeptName,nStopFlag
FROM System.Pub_Department
WHERE deptcode = '0001' ;
if sDeptCodeReturn is null THEN
raise_application_error(-20016, '此部门不存在');
end if;
IF nStopFlag = 0 THEN
raise_application_error(-20016, '此部门已停用');
END if;
dbms_output.put_line(sDeptCodeReturn||' '||sDeptName ||' '||nStopFlag );
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
如何把 sDeptCodeReturn,sDeptName ,nStopFlag 返回到C#的dataset里,C#执行的是一条SQL语句
实际上已经有了。你在exception
前面加上
dbms_output.putline(sDeptCodeReturn)
看看有没有数据就知道了。