kurs:dbms_sql_mit_fetch_und_cursor
Differences
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> | ||
kurs/dbms_sql_mit_fetch_und_cursor.1323171337.txt.gz · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
