Posts

Showing posts from January, 2013

PL/SQL Use the Case Statement

create table students
(
  student_id number not null,
  student_name varchar2(50),
  student_surname varchar2(50) not null,
  section_id number,
  final_grade number,
  letter_grade char(1),
  constraint students_pk primary key(student_id)
);

insert into students
values
(
  1,
  'Nurhak',
  'Kaya',
  89,
  85,
  'D'
);

declare
  v_student_id number:=1;
  v_section_id number:=89;
  v_final_grade number;
  v_letter_grade char(1);
begin
  select final_grade
  into v_final_grade
  from students
  where student_id=v_student_id
  and section_id=v_section_id;

  case
      when v_final_grade>=90 then v_letter_grade:='A';
      when v_final_grade>=80 then v_letter_grade:='B';
      when v_final_grade>=70 then v_letter_grade:='C';
      when v_final_grade>=60 then v_letter_grade:='D';
      else v_letter_grade:='F';
    end case;
    --check result
    dbms_output.put_line('Exam result is: '||v_letter_grade);
e…

PL/SQL Multiple Loop Transactions

declare
  v_counter number;
begin
  v_counter := 0;
  for i in 1..20 loop
      v_counter:=v_counter+1;
      dbms_output.put_line(i || '. value is: '||v_counter);
      if v_counter=10 then
            commit;
            v_counter:=0;
          end if;
    end loop;
end;

PL/SQL Use of Rollback and Savepoint

insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak',
  'Kaya',
  'test1@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point1;
insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak',
  'Kaya',
  'test2@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point2;
insert into employees
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  job_id
)
values
(
  seq_employee_id.nextval,
  'Nurhak3',
  'Kaya',
  'test3@gmail.com',
  sysdate,
  'IT_PROG'
);
savepoint point3;

--ROLLBACK!!


select * from employees
where email='test3@gmail.com'; --Data has been found!

rollback to savepoint point2; --'Rollback to savepoint point2' command has undone the last
                          --inserted …

Happy Birthday Blog!

Image
It's been a year since I have started to post my first blog and today it's my blog's birthday!
That has been an amazing experience to be a blog writer! I've learnt a lot by blogging and that feels great to have followers from all over the world!
Thank you to everyone who's been with me for this past year! You're always welcome to follow my blog in the coming years!

PL/SQL Concat & Create Concat View

select concat(concat(first_name, ' '), last_name)
as "Name_Surname"
from employees
where employee_id = 152;


create or replace force view vew_emp_full_name
as
select concat(concat(first_name, ' '), last_name)
as "Name_Surname",
manager_id+department_id
as "Total"
from employees;

PL/SQL Create&Replace&Alter View

create or replace force view "xyz"."vew_test_table_name"
as
select * from test_table_name;

alter view "xyz"."vew_test_table_name"
add constraint pk_vew_test_table_name_id primary key (id) disable;

PL/SQL Update one table from another

select * from personel;

create table personel_yeni
(
 PERSONEL_ID   NUMBER(10,0) NOT NULL ENABLE,
 PERSONEL_NAME VARCHAR2(20 BYTE),
 OFFICE_ID     NUMBER(10,0)
);

select * from personel_yeni;

insert into personel_yeni
values
(
  19,
  'Some data',
  9
);

update personel_yeni y set
  personel_name=
  (
    select personel_name
    from personel e
    where e.personel_id=y.personel_id
  ),
  office_id=
  (
    select office_id
    from personel e
    where e.personel_id=y.personel_id
  );

PL/SQL Triggers that listen to changes of a certain table

select * from user_triggers where table_name='my_table_name';
select * from all_triggers where table_name='my_other_table_name';

PL/SQL One-to-one Relationship

create table Office
(
  office_id numeric(10) not null,
  building_name varchar2(20),
  constraint u_office unique(building_name),
  constraint office_pk primary key(office_id)
);

create table Personel
(
  personel_id number(10) not null,
  personel_name varchar2(20),
  office_id number(10),
  constraint u_personel unique(office_id), --with this constraint each personel will have just one office id.
  constraint personel_pk primary key(personel_id)
);


--Here in the blow code block, as you can see in personel_detail table, personel_id is a PK and also it is a FK that references personel table.
--In this condition we can't insert any personel data in to personel detail table which does not have any reference in personel table. For example if there is a personel with personel_id=1 then we can insert one data with this same personel_id-which is 1- into personel_detail table but we can't do the same thing with the personel_id=2, because there is no personel with personel_id=2.

cre…