CREATE OR REPLACE PROCEDURE UP_Log_Find
(
V_TableName varchar2,
V_Fileds varchar2,
V_SqlWhere varchar2,
o_cur OUT SYS_REFCURSOR
)
AS
v_Sql varchar2(4000);
BEGIN
v_Sql := 'SELECT ' || V_Fileds
||' FROM ia_bill' ;
v_Sql := v_Sql ||' WHERE ' || V_SqlWhere;
OPEN o_cur FOR v_Sql;
END UP_Log_Find;要把 v_Sql := 'SELECT ' || V_Fileds
||' FROM ia_bill'
改成 v_Sql := 'SELECT ' || V_Fileds
||' FROM '|| V_TableName
改完后 运行不了 出错 SQL命令没有正确的结尾谢谢大家 帮个忙 才用ORACLE
SQL> CREATE OR REPLACE PROCEDURE UP_Log_Find(V_TableName varchar2,
2 V_Fileds varchar2,
3 V_SqlWhere varchar2,
4 o_cur OUT SYS_REFCURSOR) AS
5 v_Sql varchar2(4000);
6 BEGIN
7 v_Sql := 'SELECT ' || V_Fileds || ' FROM ' || V_TableName;
8 v_Sql := v_Sql || ' WHERE ' || V_SqlWhere;
9 OPEN o_cur FOR v_Sql;
10 END UP_Log_Find;
11 /Procedure createdSQL>
SQL> declare
2 tablename varchar2(20) := 'a';
3 field varchar2(10) := 'id';
4 sqlwhere varchar2(100) := 'id = 1';
5 cur sys_refcursor;
6 begin
7 UP_Log_Find(tablename, field, sqlwhere, cur);
8 end;
9 /PL/SQL procedure successfully completedSQL>
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE UP_Log_Find
2 (
3 V_TableName varchar2,
4 V_Fileds varchar2,
5 V_SqlWhere varchar2,
6 o_cur OUT SYS_REFCURSOR
7 )
8 AS
9 v_Sql varchar2(4000);
10 BEGIN
11 v_Sql := 'SELECT ' || V_Fileds||' FROM '|| V_TableName;
12 v_Sql := v_Sql ||' WHERE ' || V_SqlWhere;
13 OPEN o_cur FOR v_Sql;
14 END UP_Log_Find;
15 /Procedure created.SQL> var cur refcursor
SQL> exec UP_Log_Find('emp','ename','empno=7369',:cur);PL/SQL procedure successfully completed.SQL> print curENAME
--------------------
SMITHSQL>
好像我执行了 Oraclefans的调用方式 是可以的
但是 我.NET C#调用还是出错
OracleConnection con = new OracleConnection( DbHelperOra.connectionString );
OracleCommand comm = new OracleCommand("UP_Log_Find", con);
comm.Parameters.Add("V_TableName", OracleType.VarChar,100);
comm.Parameters.Add("V_Fileds", OracleType.VarChar,100);
comm.Parameters.Add("V_SqlWhere", OracleType.VarChar,200);
comm.Parameters.Add("o_cur", OracleType.Cursor);
comm.Parameters[0].Value = "ia_bill'";
comm.Parameters[1].Value = "vbillcode,HLID";
comm.Parameters[2].Value = "VBILLCODE='XSTH.FF04.0005'";
comm.Parameters[3].Direction = ParameterDirection.Output;
comm.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
OracleDataAdapter ada = new OracleDataAdapter();
ada.SelectCommand = comm;
ada.Fill(ds, "mytable");
SQL> var cur refcursor
REFCURSOR not supported我用的你的出了这个错误
create or replace
PROCEDURE PRO_TEST
( p_TabName IN VARCHAR2 --输入表名参数
, p_CityID IN NUMBER --输入城市ID参数
, cur_poi_city OUT SYS_REFCURSOR
) AS
v_Sql_Select varchar2(1000);
BEGIN
v_Sql_Select := 'select POI_NAME,POI_PROVINCE,POI_CITY,XPOS,YPOS from '||p_TabName||
' where POI_CITY = '||p_CityID;
dbms_output.put_line(v_Sql_Select); --打印出语句,调用时将此语句注释掉
OPEN cur_poi_city FOR v_Sql_Select;END PRO_TEST;
SQL> CREATE OR REPLACE PROCEDURE UP_Log_Find
2 (
3 V_TableName varchar2,
4 V_Fileds varchar2,
5 V_SqlWhere varchar2,
6 o_cur OUT SYS_REFCURSOR
7 )
8 AS
9 v_Sql varchar2(4000);
10 BEGIN
11 v_Sql := 'SELECT ' || V_Fileds
12 ||' FROM '||V_TableName ;
13 v_Sql := v_Sql ||' WHERE ' || V_SqlWhere;
14 OPEN o_cur FOR v_Sql;
15
16 END UP_Log_Find;
17 /Procedure created.SQL> var v_a refcursor
SQL> exec up_log_find('emp','ename,empno','empno=7369',:v_a);PL/SQL procedure successfully completed.SQL> print v_aENAME EMPNO
---------- ----------
SMITH 7369SQL>
找不到 晕死 都一样的 难道 ORACLE 的客户端 PLSQL版本有关
比如条件是enmae='smith';
那么条件应该是这么写.'ename='''||v_ename||'''';
晕死CREATE OR REPLACE PROCEDURE UP_Log_Find
(
V_TableName ALL_TABLES.Table_Name%TYPE,
V_Fileds varchar2,
V_SqlWhere varchar2,
o_cur OUT SYS_REFCURSOR
)
AS
v_Sql varchar2(4000);
BEGIN
v_Sql := 'SELECT ' || V_Fileds ;
v_Sql := v_Sql || ' FROM ' || V_TableName;
--v_Sql := v_Sql || ' FROM ia_bill ';
v_Sql := v_Sql || ' WHERE ' || V_SqlWhere;
OPEN o_cur FOR v_Sql;
END UP_Log_Find;
参数 类型改下 终于好了 感谢楼上的朋友
为什么.NET 调用要这样改
难道 JAVA能直接使用 字符串的方式
是动态sql的问题.
如果你不加引号你执行的语句就变成了enmae=smith
这样oracle会把smith当字段,但是又不存在这么个字段,就会报错. 你需要为他家加上引号.
而在字符串里加引号要用两个',所以就变成了上面那样