This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:execute_immediate [2008/11/06 13:09] mh |
kurs:execute_immediate [2014/11/18 12:48] (current) mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | 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 | ||
+ | </code> | ||
+ | <code> | ||
+ | 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 | ||
+ | </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> |