解决方案 »
- 问下oracle的备份与还原在官方文档的地址是哪个 谢谢
- 关于DMP文件的导入
- 急急急!!!Oracle数据库恢复问题
- 求解:[org.hibernate.util.JDBCExceptionReporter] ORA-00904: "SHOWTIPS": 标识符无效
- 几乎送分。。
- oracle 9i从SQL SERVER 2005导入数据
- 怎么看sql执行计划
- 急~~~~~~~寻求高手出山,用存储过程实现对两张结构相同\主键编号相同表进行比较,将比较后结果不同的字段及内容放在临时表中,并能显示出来.
- 在NLS_LANG=WE8ISO8859P1,数据库字符集为UTF16的环境下用Oralce OLEDB操作汉字的朋友吗?
- TNS: 监听程序无法找到适用于客户机连接的例程
- 求SQL语句。
- 大神们帮我优化下
报的是什么错?
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没有掌握~~