User Tools

Site Tools


kurs:dbms_sql_mit_fetch_und_cursor

Differences

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

Link to this comparison view

Next revision
Previous revision
kurs:dbms_sql_mit_fetch_und_cursor [2011/12/06 12:35]
mh created
kurs:dbms_sql_mit_fetch_und_cursor [2018/08/24 15:15] (current)
admin
Line 26: Line 26:
     end loop;     end loop;
    ​dbms_sql.close_cursor(statement_cursor);​    ​dbms_sql.close_cursor(statement_cursor);​
 +
 +END; -- Procedure
 +</​code>​
 +
 +<​code>​
 +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 END; -- Procedure
 </​code>​ </​code>​
kurs/dbms_sql_mit_fetch_und_cursor.1323171337.txt.gz ยท Last modified: 2014/09/10 21:22 (external edit)