建议将
SELECT COUNT(*) into o_count(counter)
FROM totfin
WHERE passno=i_passno(counter);
改为
select nvl(sum(1),-1)
into o_count(counter)
FROM totfin
WHERE passno=i_passno(counter);
否则,原句当passno找不到时会触发一个异常,或者你捕捉这个异常。 源程序缺少了这句啊。
SELECT COUNT(*) into o_count(counter)
FROM totfin
WHERE passno=i_passno(counter);
改为
select nvl(sum(1),-1)
into o_count(counter)
FROM totfin
WHERE passno=i_passno(counter);
否则,原句当passno找不到时会触发一个异常,或者你捕捉这个异常。 源程序缺少了这句啊。
解决方案 »
- 如何转换表中字符类型?
- 怎么给VMware中的Linux系统安装oracle10g?
- JSP连接ORACLE的双机服务器不稳定呢,现在怎么做单机连接?
- 在ORACLE中如何编写返回count(*)的存储过程?
- 急:如何大批量选择,更新,插入数据
- 请教:用户SYS和SYSTEM有什么区别?
- 写一个to_date小于日期条件的选择语句 报错,请帮助
- sqlplus中,命令如何显示帮助信息。就象dos中的/?。oracle是否有类似的命令
- Tns 无法启动,老问题重谈!
- jdbc与ORACLE怎样连接?哪位大侠帮帮忙?
- 求用jbuilder调用oracle数据库的开发过程
- 拿出所有分求SQL语句!!
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name] 创建包主体使用CREATE PACKAGE BODY语句:CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
谢谢楼上的,但id=:w_id何解?
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
使用的是动态的语句,id=:w_id 的意思是 :
=:w_id 可以认为是变量。
是存储过程的输入变量,还是PL/SQL的变量?
AS
type g_rs is ref curosr;PROCEDURE proc_tmp(
i_passno IN CHAR,rs out g_rs);
END tmp_pack;CREATE OR REPLACE PACKAGE BODY tmp_pack
AS
PROCEDURE proc_tmp(i_passno IN CHAR,rs out g_rs)
ASstr varchar2(2000);BEGINstr:= 'SELECT * FROM totfin
WHERE passno =:id;
OPEN rs FOR str USING i_passno;
END proc_tmp;END tmp_pack;
是一个占位符,不用官它...
p_id 才是它的真正变量