未测试,参考 首先建立一个过程 FUNCTION GETVALUE(P_SQLTEXT IN VARCHAR2)RETURN VARCHAR2 IS type curtype is REF cursor; A curtype; V_RESULT VARCHAR2(4000); BEGIN OPEN A for P_SQLTEXT; fetch A into V_RESULT; CLOSE A; RETURN V_RESULT; END; 你的过程 PROCEDURE get_nb_qty ( v_type IN VARCHAR2, v_ IN VARCHAR2, v_model IN VARCHAR2, begin_date IN VARCHAR2, end_date IN VARCHAR2, v_prod_type OUT VARCHAR2, v_repair_qty OUT NUMBER, v_total_qty OUT NUMBER, v_analysis_qty OUT NUMBER, v_ntf_qty OUT NUMBER ) IS sql_str VARCHAR2 (4000); model_name VARCHAR2(200); BEGIN model_name:=''; sql_str := ' SELECT product_type_desc FROM fa_weekly_model_report_step1 WHERE ROWNUM = 1'; v_prod_type:=getvalue(sql_str); sql_str := ' SELECT COUNT (*) FROM raw_item_n ri, raw_repair rr WHERE ri.closed_date >= TO_DATE (''' || begin_date ||''', ''YYYY/MM/DD'') and ' || 'ri.closed_date < TO_DATE (''' ||end_date ||''', ''YYYY/MM/DD'') AND' ||' ri.apply_no = rr.apply_no(+) AND ri.rma_center = rr.rma_center(+) AND ri.line_no = rr.line_no(+) AND ri.product_type = ''' ||v_type ||''''|| fn_get_multi_model_str(v_model,v_,v_prod_type); --AND UPPER (ri.model) = v_model; v_repair_qty:=getvalue(sql_str );
sql_str := 'SELECT COUNT (*) FROM fa_weekly_model_report_step1 where closed_date >= TO_DATE (''' ||begin_date ||''', ''YYYY/MM/DD'') AND reason_code in (''N01'',''N02'')'; v_ntf_qty:=getvalue(sql_str ); END get_nb_qty;
我在网上又找到一种写法,这种可行吗,但是还是请问一下怎么测试呢,对SQL navigator这个工具太不熟了,或者应该把写好的放在哪里测试呢~~知道吗~~ PROCEDURE get_nb_qty ( v_type IN VARCHAR2, v_ IN VARCHAR2, v_model IN VARCHAR2, begin_date IN VARCHAR2, end_date IN VARCHAR2, v_prod_type OUT VARCHAR2, v_repair_qty OUT NUMBER, v_total_qty OUT NUMBER, v_analysis_qty OUT NUMBER, v_ntf_qty OUT NUMBER ) IS sql_str VARCHAR2 (4000); model_name VARCHAR2(200); BEGIN model_name:=''; sql_str :=''; sql_str := sql_str || ' SELECT product_type_desc FROM fa_weekly_model_report_step1 WHERE ROWNUM = 1'; execute immediate sql_str into v_prod_type; Dbms_output.put_line(sql_str); IF (v_ = 'E') THEN sql_str := sql_str || ' SELECT COUNT (*) FROM raw_item_n ri, raw_repair rr WHERE '; sql_str := sql_str || 'ri.closed_date >= TO_DATE (''' || begin_date ||''', ''YYYY/MM/DD'') and ' || 'ri.closed_date < TO_DATE (''' ||end_date ||''', ''YYYY/MM/DD'') AND' ||' ri.apply_no = rr.apply_no(+) AND ri.rma_center = rr.rma_center(+) AND ri.line_no = rr.line_no(+) AND ri.product_type = ''' ||v_type ||''''; sql_str := sql_str || fn_get_multi_model_str(v_model,v_,v_prod_type); execute immediate sql_str into v_repair_qty; dbms_output.put_line(sql_str); ELSIF (v_ = 'F') THEN sql_str := sql_str ||' SELECT COUNT (*) FROM raw_item_n ri, raw_repair rr WHERE' || 'ri.closed_date >= TO_DATE (''' ||begin_date ||''', ''YYYY/MM/DD'') AND' ||'ri.closed_date < TO_DATE (''' ||end_date ||''', ''YYYY/MM/DD'') AND' ||'ri.apply_no = rr.apply_no(+) AND ri.rma_center = rr.rma_center(+) AND ri.line_no = rr.line_no(+) AND ri.product_type =''' || v_type ||''''; --AND UPPER (ri.model) LIKE v_model || '%'; sql_str := sql_str || fn_get_multi_model_str(v_model,v_,v_prod_type); execute immediate sql_str into v_repair_qty; END IF; sql_str := sql_str ||' SELECT COUNT (*) FROM fa_weekly_model_report_step1 where closed_date >= TO_DATE (''' ||begin_date ||''', ''YYYY/MM/DD'') AND reason_code in (''N01'',''N02'')'; execute immediate sql_str into v_ntf_qty; commit; END get_nb_qty;
execute immediate into 这种语法没什么问题只是你上面拼接的时候总是把上一次执行的语句,与下一次语句拼接在一起,这样合在一起去执行,肯定要报错的
俺就过来告诉你,plsql developer比较好用
谢谢bw555 问题已经解决了,拼接的时候出了问题,还有execute immediate sql_str into XXX没有掌握~~
报的是什么错?
select into 在pl/sql中执行可以
而拼接出来的的动态语句必须是DML或DDL语句,select into的写法在动态语句中是不被支持的
首先建立一个过程
FUNCTION GETVALUE(P_SQLTEXT IN VARCHAR2)RETURN VARCHAR2 IS
type curtype is REF cursor;
A curtype;
V_RESULT VARCHAR2(4000);
BEGIN
OPEN A for P_SQLTEXT;
fetch A into V_RESULT;
CLOSE A;
RETURN V_RESULT;
END;
你的过程
PROCEDURE get_nb_qty (
v_type IN VARCHAR2,
v_ IN VARCHAR2,
v_model IN VARCHAR2,
begin_date IN VARCHAR2,
end_date IN VARCHAR2,
v_prod_type OUT VARCHAR2,
v_repair_qty OUT NUMBER,
v_total_qty OUT NUMBER,
v_analysis_qty OUT NUMBER,
v_ntf_qty OUT NUMBER
)
IS
sql_str VARCHAR2 (4000);
model_name VARCHAR2(200);
BEGIN
model_name:='';
sql_str := '
SELECT product_type_desc
FROM fa_weekly_model_report_step1
WHERE ROWNUM = 1';
v_prod_type:=getvalue(sql_str); sql_str := '
SELECT COUNT (*) FROM raw_item_n ri, raw_repair rr
WHERE ri.closed_date >= TO_DATE ('''
|| begin_date
||''', ''YYYY/MM/DD'') and '
|| 'ri.closed_date < TO_DATE ('''
||end_date
||''', ''YYYY/MM/DD'') AND'
||' ri.apply_no = rr.apply_no(+)
AND ri.rma_center = rr.rma_center(+)
AND ri.line_no = rr.line_no(+)
AND ri.product_type = '''
||v_type
||''''|| fn_get_multi_model_str(v_model,v_,v_prod_type);
--AND UPPER (ri.model) = v_model;
v_repair_qty:=getvalue(sql_str );
sql_str := 'SELECT COUNT (*) FROM fa_weekly_model_report_step1 where closed_date >= TO_DATE ('''
||begin_date
||''', ''YYYY/MM/DD'')
AND reason_code in (''N01'',''N02'')'; v_ntf_qty:=getvalue(sql_str );
END get_nb_qty;
PROCEDURE get_nb_qty (
v_type IN VARCHAR2,
v_ IN VARCHAR2,
v_model IN VARCHAR2,
begin_date IN VARCHAR2,
end_date IN VARCHAR2,
v_prod_type OUT VARCHAR2,
v_repair_qty OUT NUMBER,
v_total_qty OUT NUMBER,
v_analysis_qty OUT NUMBER,
v_ntf_qty OUT NUMBER
)
IS
sql_str VARCHAR2 (4000);
model_name VARCHAR2(200);
BEGIN
model_name:='';
sql_str :='';
sql_str := sql_str || '
SELECT product_type_desc
FROM fa_weekly_model_report_step1
WHERE ROWNUM = 1';
execute immediate sql_str into v_prod_type;
Dbms_output.put_line(sql_str); IF (v_ = 'E')
THEN
sql_str := sql_str || '
SELECT COUNT (*)
FROM raw_item_n ri, raw_repair rr
WHERE ';
sql_str := sql_str || 'ri.closed_date >= TO_DATE ('''
|| begin_date
||''', ''YYYY/MM/DD'') and '
|| 'ri.closed_date < TO_DATE ('''
||end_date
||''', ''YYYY/MM/DD'') AND'
||' ri.apply_no = rr.apply_no(+)
AND ri.rma_center = rr.rma_center(+)
AND ri.line_no = rr.line_no(+)
AND ri.product_type = '''
||v_type
||'''';
sql_str := sql_str || fn_get_multi_model_str(v_model,v_,v_prod_type);
execute immediate sql_str into v_repair_qty;
dbms_output.put_line(sql_str); ELSIF (v_ = 'F')
THEN
sql_str := sql_str ||'
SELECT COUNT (*)
FROM raw_item_n ri, raw_repair rr
WHERE'
|| 'ri.closed_date >= TO_DATE ('''
||begin_date
||''', ''YYYY/MM/DD'') AND'
||'ri.closed_date < TO_DATE ('''
||end_date
||''', ''YYYY/MM/DD'') AND' ||'ri.apply_no = rr.apply_no(+)
AND ri.rma_center = rr.rma_center(+)
AND ri.line_no = rr.line_no(+)
AND ri.product_type ='''
|| v_type
||'''';
--AND UPPER (ri.model) LIKE v_model || '%';
sql_str := sql_str || fn_get_multi_model_str(v_model,v_,v_prod_type);
execute immediate sql_str into v_repair_qty;
END IF; sql_str := sql_str ||'
SELECT COUNT (*)
FROM fa_weekly_model_report_step1 where closed_date >= TO_DATE ('''
||begin_date
||''', ''YYYY/MM/DD'')
AND reason_code in (''N01'',''N02'')';
execute immediate sql_str into v_ntf_qty;
commit;
END get_nb_qty;
问题已经解决了,拼接的时候出了问题,还有execute immediate sql_str into XXX没有掌握~~