This shows you the differences between two versions of the page.
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> |