Oracle cursor usage

 

Oracle uses a cursor to perform joint queries on multiple tables

Declare -- defines the cursor mycur is select T. dept_id, M. station_id, M. name from person_inform T left join contacts m on T. dept_id = m. id order by T. dept_id; -- temporary field dept_id person_inform.dept_id % type; station_id contacts. station_id % type; Name contacts. name % type; begin open mycur; loop fetch mycur into dept_id, station_id, name; exit when mycur % notfound; dbms_output.put_line (dept_id | ''| station_id |'' | Name); End loop; close mycur; end;

Single Table query

Declare -- cursor mycur2 is select T. * From person_inform t; -- Row variable onerow person_inform % rowtype; begin open mycur2; loop fetch mycur2 into onerow; exit when mycur2 % notfound; dbms_output.put_line (onerow. id | ''| onerow. name); End loop; close mycur2; end;

When querying a single table, execute the SQL statement.

 

Declare -- cursor mycur2 is select T. * From person_inform t; -- Row variable onerow person_inform % rowtype; begin open mycur2; loop fetch mycur2 into onerow; exit when mycur2 % notfound; If onerow. name = 'A' then update person_inform set name = 'aaa' where id = onerow. ID; dbms_output.put_line ('execution successful _ 11'); elsif onerow. name = 'sss' then update person_inform set name = 's' where id = onerow. ID; dbms_output.put_line ('execution successful _ 22'); else dbms_output.put_line (onerow. id | ''| onerow. name); end if; end loop; close mycur2; end;

 

Execution statement

Declare -- defines the cursor mycur is select M. station_id, M. name, T. dept_id, T. name, T. ID from person_inform T left join contacts m on T. dept_id = m. id order by T. dept_id; -- temporary field m_station_id contacts. station_id % type; m_name contacts. name % type; t_dept_id detail % type; t_name person_inform.name % type; t_id person_inform.id % type; begin open mycur; loop fetch mycur into m_station_id, m_name, t_dept_id, t_name, t_id; exit when mycur % notfound; if m_station_id> 0 then dbms_output.put_line ('----' | m_station_id | ''| m_name |'' | t_dept_id | ''| t_name |'' | t_id ); update person_inform set dept_id = m_station_id where id = t_id; end if; end loop; close mycur; end;