Differences

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

--- kurs:execute_immediate [2008/11/06 13:30]
mh
+++ kurs:execute_immediate [2014/11/18 12:48]
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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools