dokuwiki

PROCEDURE fetch_table_data
   ( table_name IN varchar2)
   IS
   statement_cursor integer;
   row_selected      number;
   v_first_name     employees.first_name%type;
   v_last_name      employees.last_name%type;
BEGIN
   statement_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(statement_cursor,
                  'select first_name, last_name from employees',
                  dbms_sql.native);
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 1, v_first_name, 100); 
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 2, v_last_name, 100);                  
   row_selected := dbms_sql.execute(statement_cursor);
    LOOP 
       IF DBMS_SQL.FETCH_ROWS(statement_cursor)>0 THEN 
         -- get column values of the row 
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 1, v_first_name); 
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 2, v_last_name);                                      
         dbms_output.put_line(v_first_name||' '||v_last_name);         
       else
         exit;
       end if;
    end loop;
   dbms_sql.close_cursor(statement_cursor);

END; -- Procedure

PROCEDURE fetch_table_data
   ( p_first_name IN varchar2 default null,
     p_last_name IN varchar2 default null,
     p_department_name in varchar2 default null
     )
   IS
   statement_cursor integer;
   row_selected      number;
   v_first_name      employees.first_name%type;
   v_last_name       employees.last_name%type;
   v_department_name departments.department_name%type;
   v_sql             VARCHAR2(1000);
BEGIN
   v_sql := 'select first_name, last_name, department_name
          from employees e join departments d
          on d.department_id = e.department_id where '||chr(10);
   if p_first_name is not null then
   
-- https://xkcd.com/327/
     v_sql := v_sql || ' first_name = '''|| p_first_name
             ||''' and '||chr(10);
   end if;
   if p_last_name is not null then
     v_sql := v_sql || ' last_name = :b_last_name and ' ||chr(10);
   end if;
   if p_department_name is not null then
      v_sql := v_sql || ' department_name = :b_department_name and '
        ||chr(10);
   end if;

   v_sql := v_sql || ' 1=1'; -- zum gueltig machen aller SQL statments!!!
   
   dbms_output.put_line(v_sql);
   statement_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(statement_cursor,
                  v_sql,
                  dbms_sql.native);
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 1, v_first_name, 100);
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 2, v_last_name, 100);
   DBMS_SQL.DEFINE_COLUMN(statement_cursor, 3, v_department_name, 100);
   if p_last_name is not null then
     DBMS_SQL.BIND_VARIABLE(statement_cursor, 'b_last_name', p_last_name);
   end if;
   if p_department_name is not null then
     DBMS_SQL.BIND_VARIABLE(statement_cursor, 'b_department_name',
        p_department_name);
   end if;
   row_selected := dbms_sql.execute(statement_cursor);
    LOOP
       IF DBMS_SQL.FETCH_ROWS(statement_cursor)>0 THEN
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 1, v_first_name);
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 2, v_last_name);
         DBMS_SQL.COLUMN_VALUE(statement_cursor, 3, v_department_name);
         dbms_output.put_line(v_first_name||' '||v_last_name||' '||
                              v_department_name);
       else
         exit;
       end if;
    end loop;
 --  dbms_sql.close_cursor(statement_cursor);

END; -- Procedure