我现在有存储过程proc1,使用了游标V_Cursor作为返回记录集。存储过程有参数 proc1(S_TableName,S_Value,V_Cursor),表示所要查找的表名,和数据值,是要手动输入,不能固定某个表和某个值我如何能够
open V_Cursor for
select *
from (S_TableName)
where (S_TableName).F_Value=(S_Value)注意!!S_TableName,S_Value都是参数,是动态输入的,不能固定某个值!!
open V_Cursor for
select *
from (S_TableName)
where (S_TableName).F_Value=(S_Value)注意!!S_TableName,S_Value都是参数,是动态输入的,不能固定某个值!!
is
shuchu varchar2(100);
begin
declare cursor V_Cursoris
select F_Value
from S_TableName
where S_TableName.F_Value=S_Value;Begin
open V_Cursor;
dbms_output.put_line(F_Value);
close V_Cursor;
End;end;
set @sqlstr='select * from '+@S_TableName +'where '+@S_TableName+'.F_Value='+@S_Value
exec('declare V_Cursor cursor for '+@sqlstr)
open V_Cursor
尝试多次了,确实是不行,我实在Oracle官方提供的SQL Developer工具上开发的。以下是我完整的程序包:create or replace PACKAGE Package_CheckTableData
as
type t_cursor is REF CURSOR;
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2,S_KeyValue VARCHAR2,V_cursor out t_cursor);
end Package_CheckTableData;create or replace PACKAGE BODY Package_CheckTableData
as
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2 ,S_KeyValue VARCHAR2,V_cursor out t_cursor)
as
csSQL VARCHAR2(1000);
begin declare @sqlstr as nvarchar(1024)
set @sqlstr='select * from '+@S_TableName +'where '+@S_TableName+'.F_Value='+@S_Value
exec('declare V_Cursor cursor for '+@sqlstr)
open V_Cursor
end PROC_CheckTabelData;
end Package_CheckTableData;
怎么个动态法?
scott用户下emp表的EMPNO属性列,为number类型,如果你传递的是一个varchar类型或者date类型呢?
放心,我保证每个表的主键都是varchar2的
as
type t_cursor is REF CURSOR;
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2,S_KeyValue VARCHAR2,Res_cursor out t_cursor);
end Package_CheckTableData;create or replace PACKAGE BODY Package_CheckTableData
as
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2 ,S_KeyValue VARCHAR2,Res_cursor out t_cursor)
as
v_cursor t_cursor;
csSQL VARCHAR2(1000);
begin
csSQL := 'open v_cursor for select * from '||S_UserID||'.'|| S_TableName||' where '||S_KeyName||'='||S_KeyValue;
EXECUTE IMMEDIATE csSQL;
Res_cursor := v_cursor;
end PROC_CheckTabelData;
end Package_CheckTableData;这是我尝试的例子,编译通过的,但是执行后提示错误:无效字符
create or replace PACKAGE Package_CheckTableData
as
type t_cursor is REF CURSOR;
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2,S_KeyValue VARCHAR2,Res_cursor in out t_cursor);
end Package_CheckTableData;create or replace PACKAGE BODY Package_CheckTableData
as
PROCEDURE PROC_CheckTabelData(S_UserID VARCHAR2,S_TableName VARCHAR2,S_KeyName VARCHAR2 ,S_KeyValue VARCHAR2,Res_cursor in out t_cursor)
as
begin
open Res_cursor for 'select * from '||S_UserID||'.'|| S_TableName||' where '||S_KeyName||'='||S_KeyValue;
end PROC_CheckTabelData;
end Package_CheckTableData;
万分感激,实在要说万分感激!请恕我多问一句:Res_cursor 不需要close吗?这样我执行若干次岂不是使内存溢出?