[PLSQL] 使用迴圈大量建立使用者

參考資料 : http://abu.tw/2010/04/plsql-table-oracle-array-like.html

set serveroutput on

DECLARE 

  -- 宣告Array TYPE 及變數 

  TYPE t_name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; 

  name_array          t_name_array; 

BEGIN 

  -- 塞值進 Array  

  name_array(1) := 'USER1'; 

  name_array(2) := 'USER2'; 

  -- 迴圈處理 

  FOR i IN 1..name_array.COUNT LOOP

    -- 建立表格空間

    DBMS_OUTPUT.put_line( 'create tablespace '|| name_array(i) ||'_TBS datafile ''D:\oracle\oradata\wgopd\'||LOWER(name_array(i))||'_tbs.dbf'' size 16M autoextend on next 64M;');  

    -- 新增使用者

    DBMS_OUTPUT.put_line( 'create user '|| name_array(i) ||' identified by '||name_array(i)||'520 default tablespace '||name_array(i)||'_TBS temporary tablespace temp;');

    -- 賦予權限

    DBMS_OUTPUT.put_line( 'grant connect , resource , dba to '|| name_array(i) ||' ;');

  END LOOP; 

 

END;

/ 

Output:

create tablespace USER1_TBS datafile 'D:\oracle\oradata\wgopd\user1_tbs.dbf'

size 16M autoextend on next 64M;

create user USER1 identified by USER1520 default tablespace USER1_TBS temporary

tablespace temp;

grant connect , resource , dba to USER1 ;

create tablespace USER2_TBS datafile 'D:\oracle\oradata\wgopd\user2_tbs.dbf'

size 16M autoextend on next 64M;

create user USER2 identified by USER2520 default tablespace USER2_TBS temporary

tablespace temp;

grant connect , resource , dba to USER2 ;

PL/SQL procedure successfully completed.

SQL>