This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
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'); | ||row_count||' rows'); | ||
END; -- Procedure | 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> | </code> |