dokuwiki

PROCEDURE DELETE_ROWS
   ( p_table_name IN varchar2)
   IS
   statement_cursor integer;
   rows_del         number;
BEGIN
   statement_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(statement_cursor, 
                  'delete from '||p_table_name,
                  dbms_sql.native);
   rows_del := dbms_sql.execute(statement_cursor);
   -- dbms_sql.fetch_rows
   dbms_output.put_line('zeilen geloescht '||rows_del);
   dbms_sql.close_cursor(statement_cursor);
END; -- Procedure

PROCEDURE COUNT_ROWS
   ( p_table_name in varchar2
    )
   IS
   row_count number;
   sql_code  varchar2(1000);
BEGIN
   sql_code := 'select count(*) from '||p_table_name;
   -- objekt namen koennen nicht als bind variablen verwendet werden
   execute immediate sql_code into row_count;
   dbms_output.put_line(p_table_name ||' contains '
                        ||row_count||' rows');
END; -- Procedure

CREATE OR REPLACE PROCEDURE CREATE_DB_USER 
authid current_user AS 

/* 
cursor ueber alle employees der IT Abteilung
und fuer jeden dieser employees einen db user anlegen

+ falls es den benutzer schon gibt, nichts machen bzw exception
fangen
*/

cursor c_emp_it is
 select first_name,
           last_name,
           salary,
           department_name
      from hr.departments d join hr.employees e
        on d.department_id = e.department_id
        where department_name = 'IT';

v_cur integer;
v_user_count number;
v_sql varchar2(100);
v_ret number;
BEGIN
  
  for r_emp in c_emp_it loop
    dbms_output.put_line(r_emp.last_name);
    select count(*) into v_user_count 
      from all_users where 
       username = upper(r_emp.last_name);
       
    if v_user_count = 0 then    
      execute immediate 'create user '||r_emp.last_name||
      ' identified by '||r_emp.last_name;
      dbms_output.put_line('User '||upper(r_emp.last_name)|| 
         ' created');
    end if;  
      
  end loop;
END CREATE_DB_USER;