CREATE OR REPLACE PACKAGE BODY test_wff IS
/*********************************************\
* Name(存储过程名称): queryff
* Purpose(功能说明):练习查询
* Author(作者): wangfeifei
* Create At(创建时间): 2007-7-13
\*********************************************/
PROCEDURE querytest (in_qrybeginpos IN NUMBER, -- 本次查询的首条记录在所有满足条件的结果集中的位置
in_qrynum IN NUMBER, -- 每次查询的结果集的大小
v_put_prod_id IN VARCHAR2,
v_put_prod_status IN VARCHAR2,
v_create_name IN VARCHAR2,
single_begindate IN DATE,
single_enddate IN DATE,
flag_put_prod_id IN NUMBER,
flag_put_prod_status IN NUMBER,
flag_create_name IN NUMBER,
flag_begindate IN NUMBER,
flag_enddate IN NUMBER,
ret OUT VARCHAR2,
totalnum OUT NUMBER,
v_list OUT c_cursor
)IS
BEGIN ret := '0';
BEGIN
SELECT COUNT(*) INTO totalnum FROM put_prod;
EXCEPTION
WHEN OTHERS THEN
totalnum := 0;
END;
OPEN v_list FOR
SELECT put_prod_id,put_prod_status,create_name,create_time
FROM (
SELECT pp.put_prod_id,pp.put_prod_status,pp.create_name,pp.create_time,rownum rid
FROM
(
SELECT put_prod_id,put_prod_status,create_name,to_char(create_time,'yyyy-mm-dd') create_time
FROM put_prod
where ????????????????
) pp
)
WHERE rid BETWEEN in_qrybeginpos and (in_qrybeginpos + in_qrynum -1);
EXCEPTION
WHEN OTHERS THEN
ret := '1';
DBMS_OUTPUT.put_line(SQLERRM);
RETURN;
END querytest;
END test_wff;
------------------------------------------------------
我想用上边的 flag的值来确定 where 的条件有哪几个,应该怎么写啊?
/*********************************************\
* Name(存储过程名称): queryff
* Purpose(功能说明):练习查询
* Author(作者): wangfeifei
* Create At(创建时间): 2007-7-13
\*********************************************/
PROCEDURE querytest (in_qrybeginpos IN NUMBER, -- 本次查询的首条记录在所有满足条件的结果集中的位置
in_qrynum IN NUMBER, -- 每次查询的结果集的大小
v_put_prod_id IN VARCHAR2,
v_put_prod_status IN VARCHAR2,
v_create_name IN VARCHAR2,
single_begindate IN DATE,
single_enddate IN DATE,
flag_put_prod_id IN NUMBER,
flag_put_prod_status IN NUMBER,
flag_create_name IN NUMBER,
flag_begindate IN NUMBER,
flag_enddate IN NUMBER,
ret OUT VARCHAR2,
totalnum OUT NUMBER,
v_list OUT c_cursor
)IS
BEGIN ret := '0';
BEGIN
SELECT COUNT(*) INTO totalnum FROM put_prod;
EXCEPTION
WHEN OTHERS THEN
totalnum := 0;
END;
OPEN v_list FOR
SELECT put_prod_id,put_prod_status,create_name,create_time
FROM (
SELECT pp.put_prod_id,pp.put_prod_status,pp.create_name,pp.create_time,rownum rid
FROM
(
SELECT put_prod_id,put_prod_status,create_name,to_char(create_time,'yyyy-mm-dd') create_time
FROM put_prod
where ????????????????
) pp
)
WHERE rid BETWEEN in_qrybeginpos and (in_qrybeginpos + in_qrynum -1);
EXCEPTION
WHEN OTHERS THEN
ret := '1';
DBMS_OUTPUT.put_line(SQLERRM);
RETURN;
END querytest;
END test_wff;
------------------------------------------------------
我想用上边的 flag的值来确定 where 的条件有哪几个,应该怎么写啊?
如果要执行的sql条件不一定,那就要用动态sql。
create or replace package body pckg_icbcvm_put_prod is/*********************************************\
* Name(存储过程名称): querytest
* Purpose(功能说明):投产查询
* Author(作者): wangfeifei
* Create At(创建时间): 2007-7-13
\*********************************************/
PROCEDURE querytest (in_qrybeginpos IN NUMBER, -- 本次查询的首条记录在所有满足条件的结果集中的位置
in_qrynum IN NUMBER,
v_put_prod_id IN VARCHAR2,
v_put_prod_status IN VARCHAR2,
ret OUT VARCHAR2,
totalnum OUT NUMBER,
v_list OUT c_cursor
)IS
v_temp_where VARCHAR2(1000);
v_temp_status VARCHAR2(10);
v_temp_datefomat VARCHAR2(10);
BEGIN ret := '0';
v_temp_status :='投产已创建';
v_temp_datefomat := 'yyyy-mm-dd';
IF v_put_prod_id IS NOT NULL THEN
v_temp_where := 'put_prod_id ='''||v_put_prod_id||'''AND'||v_temp_where;
END IF;
IF v_put_prod_status IS NOT NULL THEN
v_temp_where := 'put_prod_status ='''||v_put_prod_status||'''AND'||v_temp_where;
ELSE
v_temp_where := 'put_prod_status ='''||v_temp_status||'''AND'||v_temp_where;
END IF;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM put_prod 'INTO totalnum ;
OPEN v_list FOR
'SELECT put_prod_id,put_prod_status,create_name,create_time
FROM (
SELECT pp.put_prod_id,pp.put_prod_status,pp.create_name,pp.create_time,rownum rid
FROM
(
SELECT put_prod_id,put_prod_status,create_name,to_char(create_time,'||v_temp_datefomat||') create_time
FROM put_prod
where 1=1 and '||v_temp_where||'
) pp
)
WHERE rid BETWEEN '||in_qrybeginpos||' and ('||in_qrybeginpos||' + '||in_qrynum||' - 1)';
EXCEPTION
WHEN OTHERS THEN
ret := '1';
DBMS_OUTPUT.put_line(SQLERRM);
RETURN;
END querytest;
END pckg_icbcvm_put_prod;
------------------------------------------