用动态SQL语句execute immediate,如CREATE OR REPLACE PROCEDURE mygc AS v_count NUMBER; sql_stmt VARCHAR2(1000); BEGIN sql_stmt := 'SELECT COUNT(*) FROM user_objects'; EXECUTE IMMEDIATE sql_stmt INTO v_count; DBMS_OUTPUT.PUT_LINE(v_count); END; /EXEC mygc;
我用了这个方法,但是在sqlwindow调用时会报错。 过程: create or replace procedure mygc isso_charge_id number; sql_stmt varchar2(1000);begin sql_stmt :=' select CHARGE_CAT_ID from T_CHARGE_CATEGORY';
execute immediate sql_stmt into so_charge_id;
dbms_output.put_line(so_charge_id);
end mygc;SQLWindow代码: begin mygc(); end;错误: ORA-01422: exact ftch returns more than requested number of rows ORA-06512: at "BOSSKC_DQ.MYGC", line 10 ORA-06512: at line 2 以前出现过这样的错误,但我加上 str:='sql语句'; 就没事
v_count NUMBER;
sql_stmt VARCHAR2(1000);
BEGIN
sql_stmt := 'SELECT COUNT(*) FROM user_objects';
EXECUTE IMMEDIATE sql_stmt INTO v_count;
DBMS_OUTPUT.PUT_LINE(v_count);
END;
/EXEC mygc;
过程:
create or replace procedure mygc isso_charge_id number;
sql_stmt varchar2(1000);begin sql_stmt :=' select CHARGE_CAT_ID from T_CHARGE_CATEGORY';
execute immediate sql_stmt into so_charge_id;
dbms_output.put_line(so_charge_id);
end mygc;SQLWindow代码:
begin
mygc();
end;错误:
ORA-01422: exact ftch returns more than requested number of rows
ORA-06512: at "BOSSKC_DQ.MYGC", line 10
ORA-06512: at line 2
以前出现过这样的错误,但我加上 str:='sql语句';
就没事