Differences

This shows you the differences between two versions of the page.

--- kurs:execute_immediate [2014/09/10 21:22]
127.0.0.1 external edit
+++ kurs:execute_immediate [2014/11/18 12:48] (current)
mh
@@ Line 31: / Line 31: @@
                         ||row_count||' rows');
 END; -- Procedure
+</code>
+<code>
+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;
 </code>

dokuwiki