小弟刚学ORACLE 请大哥们进来看看我的过程那儿不对了?小弟在这谢谢了

Question

create or replace procedure insert_bbc
(
bbc_id number,
bbc_name varchar2,
bbg_uiid number,
bbg_porv varchar2,
bbg_City varchar2
)
as
declare uud number(2);
begin
execute immediate 'insert into bbg(uiid,porv,City)values(:bbg_uiid,:bbg_porv,:bbg_City);';
commit;
/*select 这条语句是用来测试用的不知道ORACLE中有没有像SQL SERVER那样的
IDENT_CURRENT含数呀*/select uiid into uud from bbg where porv=bbg_porv and City=bbg_City;
execute immediate 'insert into bbc(id,name,uiid)values(:bbc_id,:bbc_name,uud);';
commit;
end insert_bbc;
总是提示错误但是不知道是那里不对了 老大们给我看看
谢谢了

Answer 1

SQL> create or replace procedure insert_bbc
  2  (
  3  bbc_id number,
  4  bbc_name varchar2,
  5  bbg_uiid number,
  6  bbg_porv varchar2,
  7  bbg_City varchar2
  8  )
  9  as
 10  uud number;
 11  begin
 12  execute immediate 'insert into bbg(uiid,porv,City)values(:bbg_uiid,:bbg_porv,:bbg_City);';
 13  commit;
 14  select uiid into uud from bbg where porv=bbg_porv and City=bbg_City;
 15  execute immediate 'insert into bbc(id,name,uiid)values(:bbc_id,:bbc_name,uud);';
 16  commit;
 17  end insert_bbc;
 18  /过程已创建。
但是
我用EXEC调用咋报错呀SQL> execute insert_bbc(1,'小斌',1,'河南省','郑州市');
BEGIN insert_bbc(1,'小斌',1,'河南省','郑州市'); END;*
第 1 行出现错误:
ORA-00911: 无效字符
ORA-06512: 在 "SYSTEM.INSERT_BBC", line 12
ORA-06512: 在 line 1
大哥给讲讲吧
谢谢了

Answer 2

你的values前面怎没没有空格啊?

Answer 3

那个老大给我说说为什么我编以通过
但在调用时出现错呀
这个过程也是一样的
create or replace procedure insert_zl_user(zl_Username   varchar2,
                                           zl_PassWord   varchar2,
                                           zl_PassASK    varchar2,
                                           zl_passanswer varchar2,
                                           zl_Levelname  varchar2,
                                           zl_name       varchar2,
                                           zl_sex        varchar2,
                                           zl_Birth      date,
                                           zl_Idetity    varchar2,
                                           zl_HandSet    varchar2,
                                           zl_Phone      varchar2,
                                           zl_Fax        varchar2,
                                           zl_Email      varchar2,
                                           zl_QQ         varchar2,
                                           zl_PostCode   varchar2,
                                           zl_Province   varchar2,
                                           zl_City       varchar2,
                                           zl_Address    varchar2) as
  in_user_LE number;
  in_user_CI number;
begin
  execute immediate 'insert into zl_user_Level(Levelname) values(:zl_Levelname);';
  select LevelID
    into in_user_LE
    from zl_user_Level
   where Levelname = zl_Levelname;  execute immediate 'insert into zl_City(Province,City) values(:zl_Province,:zl_City);';
  select Cid
    into in_user_CI
    from zl_City
   where Province = zl_Province and City = zl_City;  execute immediate 'insert into zl_user(Username,PassWord,PassASK,PassAnswer,LevelID,Name,Sex,Birth,Idetity,HandSet,Phone,Fax,Email,QQ,PostCode,Cid,Address,InputData,ReelectData) values(:zl_Username,:zl_PassWord,:zl_PassASK,:zl_passanswer,in_user_LE,:zl_name,:zl_sex,:zl_Birth,:zl_Idetity,:zl_HandSet,:zl_Phone,:zl_Fax,:zl_Email,:zl_QQ,:zl_PostCode,in_user_CI,:zl_Address,sysdate,sysdate);';  commit;
end insert_zl_user;

Answer 4

SQL> execute insert_zl_user('hubin','123456','asdfas','asfasdf','pro','hubin','男','1982/11/21','876
5443','130524543','452454','4542424','gsdfgsdfgs','546354634','drtgserga','dfasdfa','sdfawerfas','as
dfas');
BEGIN insert_zl_user('hubin','123456','asdfas','asfasdf','pro','hubin','男','1982/11/21','8765443','       *
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
ORA-06512: 在line 1

Answer 5

绑定变量用法不对，参照下面例子改下酒可以了：DECLARE
v_sql VARCHAR2(200);
v1 NUMBER := 2;
v2 NUMBER := 3;
BEGIN
v_sql := 'insert into d(id,num) values(:1,:2)';
EXECUTE IMMEDIATE v_sql USING v1,v2;
COMMIT;
END;

Answer 6

varchar2数据类型是不是需要指定长度，如不指定默认长度是多少？

Answer 7

create or replace procedure insert_zl_user(zl_Username   varchar2,
                                           zl_PassWord   varchar2,
                                           zl_PassASK    varchar2,
                                           zl_passanswer varchar2,
                                           zl_Levelname  varchar2,
                                           zl_name       varchar2,
                                           zl_sex        varchar2,
                                           zl_Birth      date,
                                           zl_Idetity    varchar2,
                                           zl_HandSet    varchar2,
                                           zl_Phone      varchar2,
                                           zl_Fax        varchar2,
                                           zl_Email      varchar2,
                                           zl_QQ         varchar2,
                                           zl_PostCode   varchar2,
                                           zl_Province   varchar2,
                                           zl_City       varchar2,
                                           zl_Address    varchar2) as
  v_Sqlstr   varchar2(200);
  in_user_LE number := 0;
  in_user_CI number := 0;
begin
  execute immediate 'insert into zl_user_Level(Levelname) values(:zl_Levelname) uing zl_Levelname;';
  select LevelID
    into in_user_LE
    from zl_user_Level
   where Levelname = zl_Levelname;  execute immediate 'insert into zl_City(Province,City) values(:zl_Province,:zl_City) using zl_Province,zl_City';
  select Cid
    into in_user_CI
    from zl_City
   where Province = zl_Province and City = zl_City;
  v_Sqlstr := 'insert into zl_user(Username,PassWord,PassASK,PassAnswer,LevelID,Name,Sex,Birth,Idetity,HandSet,Phone,Fax,Email,QQ,PostCode,Cid,Address,InputData,ReelectData) values(:zl_Username,:zl_PassWord,:zl_PassASK,:zl_passanswer,:in_user_LE,:zl_name,:zl_sex,:zl_Birth,:zl_Idetity,:zl_HandSet,:zl_Phone,:zl_Fax,:zl_Email,:zl_QQ,:zl_PostCode,:in_user_CI,:zl_Address,sysdate,sysdate)';
  execute immediate v_Sqlstr
    using zl_Username, zl_PassWord, zl_PassASK, zl_passanswer, in_user_LE, zl_name, zl_sex, zl_Birth, zl_Idetity, zl_HandSet, zl_Phone, zl_Fax, zl_Email, zl_QQ, zl_PostCode, in_user_CI, zl_Address;  commit;
end insert_zl_user;这个也是一样的呀
编以时没有错
一用EXECUTE调用就出错

Answer 8

明显有错误，都没看出来？搂主写好程序自己多调试下，出错提示信息已经很明确了

execute immediate 'insert into zl_user_Level(Levelname) values(:zl_Levelname) uing zl_Levelname;';
。。
execute immediate 'insert into zl_City(Province,City) values(:zl_Province,:zl_City) using zl_Province,zl_City';