CREATE OR REPLACE FUNCTION GET_COUNT(v_ID varchar2) return VARCHAR2 as
PRAGMA AUTONOMOUS_TRANSACTION; --自定义事务
v_count varchar2(10);
v_month char(2);
v_sql varchar2(2000);
Begin
select to_char(sysdate, 'mm') into v_month from dual;
v_sql := 'select count(1) into ' || 'v_count' || ' from LOG' || v_month || ' t where t.id =' || v_ID; --感觉下面两行代码执行有问题
execute immediate v_sql; --执行到这里返回-1
commit;
return v_count;Exception
When Others Then
Return - 1;
End GET_COUNT;
----------------------调用上面的Function
select GET_COUNT('3') AS retVal from dual;拼接出来的v_sql语句如下:
select count(1) into v_count from LOG04 t where t.id =3
解决方案 »
- Oracle 10g : ORA-12514:listener does not currently know of service requested in connect descriptor
- 帮忙改一句sql,提取规律性数据,谢谢。
- advance replication(高级复制)问题----配置好了,但不能触发
- 问点关于ORACLE9I的问题!
- 求SELECT语句
- 现在做的一个项目需要通过SQL*Loader进行程序导入问题,分数不够可以再加!或者当面酬谢也行!
- 关于v$sql视图的一些问题,急,请教
- 一个删除表的的问题!!!!!??
- 初级,快来看呀,100分
- 关于数据库连接的问题,请赐教!
- oracle goldengate
- database link ORA-01017: invalid username/password;logon denied
修改成
v_sql := 'select count(1) from LOG' || v_month || ' t where t.id =' || v_ID; execute immediate v_sql into v_count;
试一下
v_sql := 'select count(1) from LOG' || v_month || ' t where t.id =' || v_ID;
execute immediate v_sql into v_count ;
去掉
PRAGMA AUTONOMOUS_TRANSACTION;
和
commit;
CREATE OR REPLACE FUNCTION GET_COUNT(v_ID varchar2) return VARCHAR2 as
---PRAGMA AUTONOMOUS_TRANSACTION;
v_count varchar2(10);
v_month char(2);
v_sql varchar2(2000);
Begin
select to_char(sysdate, 'mm') into v_month from dual;
--v_sql := 'select count(1) into ' || 'v_count' || ' from LOG' || v_month || ' t where t.id =' || v_ID;
--execute immediate v_sql;
v_sql := 'select count(1) from LOG' || v_month || ' t where t.id =' || v_ID;
execute immediate v_sql into v_count ;
dbms_output.put_line(v_sql);
--commit;
return v_count;Exception
When Others Then
Return - 1;
End GET_COUNT;
---------------------------------------
select GET_COUNT('2') AS retval from dual;