如何在過程Oracle中傳遞數據和循環

我在 Oracle 數據庫中有一個過程，用於將使用者插入表中。但是現在，我需要實現一個循環，以便我使用相同的過程來插入許多使用者。

我對 pl/sql 知之甚少，所以我不知道哪種方法是最好的方法。我應該通過可變數組傳遞數據嗎？

如果是這樣，我怎樣才能在我的程序中實現一個循環？

CREATE OR REPLACE PROCEDURE insert_users(
    p_username     IN xx_users.username%TYPE,
    p_pass         IN xx_users.pass%TYPE,
    p_role         IN VARCHAR2
    )

IS

l_id_role    NUMBER;
l_role       VARCHAR2(200);

l_user       VARCHAR2(200);
l_pass       VARCHAR2(200);

BEGIN

l_user := p_user;
l_pass := p_pass;
l_role := p_role;

SELECT id_role
INTO l_id_role
FROM xx_roles
WHERE role_name = l_role;

INSERT INTO xx_users (id_user,
                       username,
                       pass,
                       id_role
                       ) VALUES (xx_users_id.nextval,
                                l_user,
                                l_pass,
                                l_id_role
                                );

COMMIT;
END;

我在想也許我可以創建另一個過程，它需要一個數組並為每個元素呼叫該過程insert_users，但我認為這不是最有效的方法。

假設您有 xx_roles 和 xx_users 表，以及（而不是數組）第三個表 new_users，其中包含新使用者的名稱和臨時 ID，例如：

-- table for roles
create table xx_roles (
 id_role number primary key
, role_name varchar2(32) unique );

begin
 for i in 65 .. 90  loop -- ASCII A-Z
   insert into xx_roles (id_role, role_name) 
     values (i, 'role_' || chr(i)); 
 end loop;
end;
/
-- temporary table containing names of NEW users
-- (everything can be DELETEd once xx_users is populated)
create table new_users(
 tempid number
, username varchar2(32) );

begin
 for i in 97 .. 122 loop -- ASCII a-z
   insert into new_users values (i, 'user_' || chr(i));
 end loop;
end;
/
-- "final" users table: this table will be populated via the procedure
create table xx_users(
 id_user number primary key
, username varchar2(64)
, pass varchar2(128)
, role_name varchar2(32) references xx_roles(role_name)
);

create sequence xxuserid_seq start with 1000 increment by 1;

執行完以上所有程式碼後，xx_users 表為空：

SQL> select * from xx_users;
no rows selected

現在，您可以使用類似於此的過程，利用“循環游標”（注意：這最終需要異常處理！）：

create or replace procedure populate( 
 p_startid  new_users.tempid%type
, p_endid    new_users.tempid%type
, p_roleid   xx_roles.id_role%type
)
is
 role xx_roles.role_name%type ;
begin
 select role_name into role from xx_roles where id_role = p_roleid ; 
 for rec in (
   select tempid, username from new_users 
   where tempid between p_startid and p_endid )
 loop
   insert into xx_users values( 
       xxuserid_seq.nextval
     , rec.username
     , standard_hash( rec.tempid )
     , role );
 end loop;
end populate;
/

測試：

-- populate the xx_users table, using the procedure 
-- new users' and temporary ids taken from the new_users table
-- eg temporary ids 100 - 110, id_role 65 -> role_A

begin
 populate(100, 110, 65); 
 populate(111, 115, 82);
 populate(120, 122, 90);
end;
/

SQL> select * from xx_users;
ID_USER  USERNAME  PASS                                      ROLE_NAME  
1039     user_d    4C6B9619DED0EA1E8A802D85DED8E6A751D27A65  role_A     
1040     user_e    15848F7CECB65A6B6C6DD43A7F135B23C3C0B5FC  role_A     
1041     user_f    2DA6C40C74BA92AA966BBB3791DD1147BD273305  role_A     
1042     user_g    ADD9483D8920D56BF313B60A2F6EB0CDBE0E8785  role_A     
1043     user_h    CA7EB2084F5046E3BFA61003174E5042A5B7F3CC  role_A     
1044     user_i    48087C2C84F074B09F6611658B6FF1E9535E669F  role_A     
1045     user_j    90ACB83C29B5EF24BE0598C59BF5863DD0E96A4B  role_A     
1046     user_k    F55EF9C7E3465999FA1FFA6563BF87F4F700F453  role_A     
1047     user_l    27E35BDEBFD7EB16D4BE7FD4E2D08D406A94BADD  role_A     
1048     user_m    36DEEF8699FDAD1BE9216E91438419D1D3679EEB  role_A     
1049     user_n    12EB0BC8F5C5D878120B59481136049D42407BC3  role_A     
1050     user_o    F6576DF67EB05A22F36A2841B027E9986D009E7C  role_R     
1051     user_p    C9D87CF97D4C3CD5315D487505E28B611123D351  role_R     
1052     user_q    600A35A1CC089A880A03296F2ED51700B31CCC93  role_R     
1053     user_r    345FA3C5E123079850898D43FD794E4BD5CFC53E  role_R     
1054     user_s    65B1606205328C61B7749C18956F7224264A6F5B  role_R     
1055     user_x    FB4DF69CC20EF71B716182B0F16A0FA9A6047E00  role_Z     
1056     user_y    9DB01E75E374EDAB7AEB5DE2B51571896BB4DB47  role_Z     
1057     user_z    58F5BA70289B1C028DB82C153D9DB94B97DAA892  role_Z 

更新- 正如@miracle173 所指出的，在這種情況下使用單個 INSERT 可能是一個更好的主意。例子：

insert into xx_users
select 
 xxuserid_seq.nextval
, U.username
, standard_hash( U.tempid )
, R.role_name
from xx_roles R, new_users U
where ( ( U.tempid between 100 and 110 ) and R.id_role = 65 )
  or ( ( U.tempid between 111 and 115 ) and R.id_role = 82 )
  or ( ( U.tempid between 120 and 122 ) and R.id_role = 90 ) ;

引用自：https://dba.stackexchange.com/questions/175298