星期一, 4月 23, 2012

[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>

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...