-- Oracle 10g:n PL/SQL-harjoitukset
-- kopioi aluksi \\GHOST\vmware\huojo\MySQL_Oracle D-asemaasi

-- harjoitus 1
-- JH 20.11.2003
-- Aihe: yksinkertainen PL/SQL-lohko
-- Huom: aseta näytölle tulostus päälle käskyllä 
   SET SERVEROUTPUT ON
-- ohjelma tulostaa näytölle tekstin 'Moi...'

BEGIN
    DBMS_OUTPUT.PUT_LINE('Moi - osaan PL/SQL:ää');
END;
/

-- harjoitus 2
-- JH 20.11.2003, 1.4.2008
-- Aihe: EXCEPTION eli poikkeuksien hallinta
-- Tulostaa näytölle yhden työntekijän nron ja nimen 
-- jos osastossa ei ole yhtään työntekijää tai 
-- työntekijöitä on useampia, niin seuraa virheilmoitus

DECLARE
   EmpRec employees%rowtype;
BEGIN
  select * into EmpRec from employees where DEPARTMENT_ID = 66;
  DBMS_OUTPUT.put_line(EmpRec.EMPLOYEE_ID || ', ' || EmpRec.LAST_NAME);        
EXCEPTION
  when no_data_found then
    DBMS_OUTPUT.put_line('Tietoja ei ole');
  when too_many_rows then
    DBMS_OUTPUT.put_line('Rivejä > 1');  
  when others then
    DBMS_OUTPUT.put_line('Outo homma');
END;
/


-- harjoitus 3
-- JH 16.11.2002, 1.4.2008
-- Aihe: kursorin ja muuttujien määrittely, loop
-- Tulostaa näytölle kaikki työntekijät (EMP-taulusta)

DECLARE
-- kursori on nimetty työalue keskusmuistista
   cursor emp_cursor is
      select employee_ID, last_name 
      from employees 
      where department_ID = &Osasto;
   nEmpNo employees.employee_ID%TYPE;
   cEname employees.last_name%TYPE;
BEGIN
   open emp_cursor;
   loop
      fetch emp_cursor into nEmpNo, cEname;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.put_line(nEmpNo || ', ' || cEname);
   end loop;
   close emp_cursor;
END;
/


-- harjoitus 4
-- JH 1.4.2008, Oracle 10g-versio
-- Aihe: Tallennetun proseduurin (stored procedure) tekeminen
-- Proseduuri tulostaa parametrina annetun osastonumeron kaikki tyontekijat
-- (nro ja nimi)
-- Jos osastossa ei ole yhtään työntekijää, niin seuraa virheilmoitus

-- työntekijän ID annetaan parametrinä
-- lisäksi kursoria ei määritellä, koska työntekijä ID on yksilöllinen
-- => proseduuri palauttaa vain yhden rivin

CREATE OR REPLACE PROCEDURE TulostaTyonTekija (nEmpno employees.employee_ID%TYPE)
IS
  EmpRec employees%rowtype;
BEGIN
  select * into EmpRec from employees where employee_ID = nEmpno;
  DBMS_OUTPUT.put_line(EmpRec.employee_ID || ' ' || EmpRec.last_name);
EXCEPTION
  when no_data_found then
    DBMS_OUTPUT.put_line('Tietoja ei ole');
  when too_many_rows then
    DBMS_OUTPUT.put_line('Rivejä > 1');  
  when others then
    DBMS_OUTPUT.put_line('Outo homma');
END TulostaTyonTekija;
/

-- proseduuria kutsutaan käskyllä EXEC TulostaTyonTekija (206)
-- suluissa annetaan työntekijän numero