Posts

Showing posts from May, 2013

PL/SQL Working with Functions

--package

create or replace
package pkg_xyz is

  function fnc_varsayilan_cevap_kontrol (p_soru_id in number) return number;

end pkg_mzb;


--package body
create or replace
package body         pkg_xyz is

function fnc_varsayilan_cevap_kontrol (p_soru_id number) return number is

    varsayilanKontrol       number := 0;

    begin

        select count (varsayilan)
          into varsayilanKontrol
          from mzb_muayene_soru_cevaplari
         where soru_id = p_soru_id
         and varsayilan=1;

        return varsayilanKontrol;

        exception
            when No_Data_Found
            then
            return null ;

    end ;

end;

PL/SQL Working with Cursors

SELECT-INTOSimple Select
declare
  l_last_name employees.last_name%type;
begin
  select last_name
    into l_last_name
    from employees
    where employee_id=138;

    dbms_output.put_line(l_last_name);
end;

Join

declare
  l_last_name employees.last_name%type;
  l_department_name departments.department_name%type;
begin
  select last_name, department_name
  into l_last_name, l_department_name
  from employees e, departments d
  where e.department_id=d.department_id
  and e.employee_id=138;

  dbms_output.put_line(
        l_last_name||
        ' is working in '||
        l_department_name ||
        ' department.');
end;

Cursor FOR Loop


declare
  cursor test_cur
  is
    select * from employees
    where department_id=50;
begin
  for c1
  in test_cur
  loop
    dbms_output.put_line(
      c1.first_name||
      ' '||
      c1.last_name
    );
  end loop;
end;