有一张表
SQL> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y 有一条存储过程
create or replace procedure proc_insert
(
id number,
name varchar2
)as
v_cursor number; --定义光标
v_string varchar2(200); --字符串变量
v_row number; --行数
begin
v_cursor:=dbms_sql.open_cursor; --为处理打开光标
v_string:='update dinya_test a set a.name=:p_name where a.id=:p_id';
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句
dbms_sql.bind_variable(v_cursor,':p_name',name); --绑定变量
dbms_sql.bind_variable(v_cursor,':p_id',id); --绑定变量
v_row:=dbms_sql.execute(v_cursor); --执行动态SQL
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;执行后报错,具体错误信息:
Compilation errors for PROCEDURE TEST_VIEW.PROC_INSERTError: PLS-00103: 出现符号 ""在需要下列之一时:
begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
符号 "" 被忽略。
Line: 15
Text: v_row:=dbms_sql.execute(v_cursor); --执行动态SQLError: Hint: Value assigned to 'v_row' never used in 'proc_insert'
Line: 15
Text: v_row:=dbms_sql.execute(v_cursor); --执行动态SQL各位大虾们,帮帮忙看看咧.
SQL> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y 有一条存储过程
create or replace procedure proc_insert
(
id number,
name varchar2
)as
v_cursor number; --定义光标
v_string varchar2(200); --字符串变量
v_row number; --行数
begin
v_cursor:=dbms_sql.open_cursor; --为处理打开光标
v_string:='update dinya_test a set a.name=:p_name where a.id=:p_id';
dbms_sql.parse(v_cursor,v_string,dbms_sql.native); --分析语句
dbms_sql.bind_variable(v_cursor,':p_name',name); --绑定变量
dbms_sql.bind_variable(v_cursor,':p_id',id); --绑定变量
v_row:=dbms_sql.execute(v_cursor); --执行动态SQL
dbms_sql.close_cursor(v_cursor); --关闭光标
exception
when others then
dbms_sql.close_cursor(v_cursor); --关闭光标
raise;
end;执行后报错,具体错误信息:
Compilation errors for PROCEDURE TEST_VIEW.PROC_INSERTError: PLS-00103: 出现符号 ""在需要下列之一时:
begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
符号 "" 被忽略。
Line: 15
Text: v_row:=dbms_sql.execute(v_cursor); --执行动态SQLError: Hint: Value assigned to 'v_row' never used in 'proc_insert'
Line: 15
Text: v_row:=dbms_sql.execute(v_cursor); --执行动态SQL各位大虾们,帮帮忙看看咧.
create or replace procedure proc_insert
(
v_id number,
v_name varchar2
)as
begin
update dinya_test a set a.name=v_name where a.id=v_id;
commit;
end;
参数不要和表的字段一样,有时会发生莫名其妙的错误,如id和name