oracle 存储过程

迈不过友情╰ 2022-04-17 04:17 419阅读 0赞

oracle的for循环，commit放在不同的位置，一个是每次提交，一个是循环玩了之后统一提交！

create or replace procedure pro_test_for is
    i number;
    begin
    i:=0;
    for i in 1..5 loop
      insert into login (username,password) values ('abin','varyall');
    
    end loop;
    commit;
    end pro_test_for;

create or replace procedure pro_test_for is
    i number;
    begin
    i:=0;
    for i in 1..5 loop
      insert into login (username,password) values ('abin','varyall');
      commit;
    end loop;
    
    end pro_test_for;

create or replace procedure abin_4
    is
    i number;
    begin
    i:=0;
    begin
    for i in 1..5 loop
    insert into login (username,password) values ('abin','varyall');
    end loop;
    commit;
    end;
    end;

create or replace procedure abin_5
    is
    i number;
    begin
    i:=0;
    loop
    i:=i+1;
    dbms_output.put_line(i);
    if i>5 then
    exit;
    end if;
    end loop;
    commit;
    end;

create or replace procedure abin_2
    is
    i number;
    begin
    i:=0;
    while i<5 loop
    i:=i+1;
    dbms_output.put_line(i);
    end loop;
    commit;
    end;

create or replace procedure abin(aa in number)
    is
           uu login.username%type;
           pp login.password%type;       
    
    begin
    select username,password into uu,pp from login where username='&aa';
    dbms_output.put_line('用户名'||uu);
    dbms_output.put_line('密码'||pp);
    end;

create or replace procedure abing(aa in number)
    is
    v login%Rowtype;
    begin
    select * into v from login where username='&aa';
    dbms_output.put_line('用户名'||v.username);
    dbms_output.put_line('密码'||v.password);
    end;

create or replace procedure varyall
    is
    cursor myCur is select * from login;
    oneRow login%rowtype;
    begin
    open myCur;
    loop
    fetch myCur into oneRow;
    dbms_output.put_line(oneRow.username||'  '||oneRow.password);
    exit when myCur%notfound;
    end loop;
    close myCur;
    end;

create or replace procedure abin
    is
    cursor myCur is select * from login;
    oneRow login%rowtype;
    begin
    open myCur;
    fetch myCur into oneRow;
    while(myCur%found)
    loop
    dbms_output.put_line(oneRow.username||'  '||oneRow.password);
    fetch myCur into oneRow;
    end loop;
    close myCur;
    end;

create or replace procedure abin
    is
    cursor myCur is select * from login;
    oneRow login%rowtype;
    begin
    for oneRow in myCur loop
    dbms_output.put_line(oneRow.username||'   '||oneRow.password);
    end loop;
    end;