这个什么问题啊? 说我语法有错!!

Question

create or replace procedure gen_password(lenght)
is
  p_len integer;
  j varchar2(13);
begin
  p_len :=pwdlen;
  for   i   in   1..p_len  loop 
          if i <10 then
             j :='000000000000'||i;
          elsif i<100 then
             j :='00000000000'||i;
          elsif i<1000 then
             j :='0000000000'||i;
          elsif i<10000 then
             j :='0000000000'||i;
          elsif i<100000 then
             j :='0000000000'||i;
          insert   into  tblcustinfo(custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag,branchcode,transacntcode,
          transacntstatus,certtype,mobile) 
          select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag
          ,branchcode,transacntcode,transacntstatus,certtype,mobile from (select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,b.*,rownum t from tblgrb,
          (select '1' as indiinstflag,'9901' as branchcode,'9901'||j as transacntcode,
          '1' as transacntstatus,'0' as certtype,'021-12345678'as mobile from dual) b) where t=i 
      end   loop;   
      commit;   
end;exec gen_password(40)里面的 语句 单独执行是可以的... 但是这样 就会报语法有错!!急啊!

Answer 1

create or replace procedure gen_password(lenght in string)

Answer 2

好象这样不行的 报 oracle-00900 invalid SQL statement

Answer 3

create or replace procedure gen_password(lenght in varchar2)
pwdlen这个变量定义了吗？

Answer 4

不好意思 那个我写错了 create or replace procedure gen_password(lenght in integer)
is
  p_len integer;
  j varchar2(13);
begin
  p_len :=lenght;
  for   i   in   1..p_len  loop 
          if i <10 then
             j :='000000000000'||i;
          elsif i<100 then
             j :='00000000000'||i;
          elsif i<1000 then
             j :='0000000000'||i;
          elsif i<10000 then
             j :='0000000000'||i;
          elsif i<100000 then
             j :='0000000000'||i;
          insert   into  tblcustinfo(custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag,branchcode,transacntcode,
          transacntstatus,certtype,mobile) 
          select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag
          ,branchcode,transacntcode,transacntstatus,certtype,mobile from (select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,b.*,rownum t from tblgrb,
          (select '1' as indiinstflag,'9901' as branchcode,'9901'||j as transacntcode,
          '1' as transacntstatus,'0' as certtype,'021-12345678'as mobile from dual) b) where t=i 
      end   loop;   
      commit;   
end;exec gen_password(40)

Answer 5

关键是 我不写 存储过程 只写 循环 也会报 语法错误 只执行一次 不用循环 是可以的/..for   i   in   0..9  loop 
          insert   into  tblcustinfo(custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag,branchcode,transacntcode,
          transacntstatus,certtype,mobile) 
          select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,indiinstflag
          ,branchcode,transacntcode,transacntstatus,certtype,mobile from (select custno,investorname,accountno,riskgrade,nationality,livingplace,addresscountry
          ,address,certcode,birthday,sex,educationlevel,postcode,rc,ao,rcao,b.*,rownum t from tblgrb,
          (select '1' as indiinstflag,'9901' as branchcode,'9901'||'00000000000'||i as transacntcode,
          '1' as transacntstatus,'0' as certtype,'021-12345678'as mobile from dual) b) where t=i 
      end   loop;

Answer 6

应该是这样的：3个错误，if必须用end if 接，end loop前的语句没有';'，接口参数没有定义类型。
CREATE OR REPLACE PROCEDURE gen_password (lenght INTEGER)
IS
   p_len   INTEGER;
   j       VARCHAR2 (13);
BEGIN
   p_len := pwdlen;   FOR i IN 1 .. p_len
   LOOP
      IF i < 10
      THEN
         j := '000000000000' || i;
      ELSIF i < 100
      THEN
         j := '00000000000' || i;
      ELSIF i < 1000
      THEN
         j := '0000000000' || i;
      ELSIF i < 10000
      THEN
         j := '0000000000' || i;
      ELSIF i < 100000
      THEN
         j := '0000000000' || i;         INSERT INTO tblcustinfo
                     (custno, investorname, accountno, riskgrade,
                      nationality, livingplace, addresscountry, address,
                      certcode, birthday, sex, educationlevel, postcode, rc,
                      ao, rcao, indiinstflag, branchcode, transacntcode,
                      transacntstatus, certtype, mobile)
            SELECT custno, investorname, accountno, riskgrade, nationality,
                   livingplace, addresscountry, address, certcode, birthday,
                   sex, educationlevel, postcode, rc, ao, rcao, indiinstflag,
                   branchcode, transacntcode, transacntstatus, certtype,
                   mobile
              FROM (SELECT custno, investorname, accountno, riskgrade,
                           nationality, livingplace, addresscountry, address,
                           certcode, birthday, sex, educationlevel, postcode,
                           rc, ao, rcao, b.*, ROWNUM t
                      FROM tblgrb,
                           (SELECT '1' AS indiinstflag, '9901' AS branchcode,
                                   '9901' || j AS transacntcode,
                                   '1' AS transacntstatus, '0' AS certtype,
                                   '021-12345678' AS mobile
                              FROM DUAL) b)
             WHERE t = i;
      END IF;
   END LOOP;   COMMIT;
END;