Posts

Showing posts from February, 2014

PL/SQL Update Multiple Columns of one Table

update test_table
set id=id+22
where id between 912 and 916;

PL/SQL Connect By Prior

select * from test_table_name connect by prior id=root_id start with root_id=211;

declare
  cursor c_birim
  is
  select *
  from test_table_name
  connect by prior id=root_id
  start with root_id=211;
begin
  for r_birim in c_birim loop
      update test_table
      set tur_id=1
      where id=r_birim.id;
    end loop;
end;



PL/SQL Not Exists

select     user.id, user.name
     from     schema.table_users user
   where not exists ( select dept.kullanici_id
                  from schema.table_departments dept
                  where dept.user_id=user.id)
 order by user.id;

PL/SQL wv_concat Function

SELECT wmsys.wm_concat(table.name)
FROM table_name table
WHERE table.id := p_id;

PL/SQL Adding Columns to a Table without Loosing any Data

-- Renaming the last 4 column names
alter table TEST_TABLE_NAME rename column KAYIT_KULLANICI_ID to XKAYIT_KULLANICI_ID;
alter table TEST_TABLE_NAME rename column KAYIT_TARIHI to XKAYIT_TARIHI;
alter table TEST_TABLE_NAME rename column GUNCELLEME_KULLANICI_ID to XGUNCELLEME_KULLANICI_ID;
alter table TEST_TABLE_NAME rename column GUNCELLEME_TARIHI to XGUNCELLEME_TARIHI;
alter table TEST_TABLE_NAME drop CONSTRAINT "FK_TEST_TABLE_GNC_KULL_ID";
alter table TEST_TABLE_NAME drop CONSTRAINT "FK_TEST_TABLE_KYT_KULL_ID";

-- Adding new columns
alter table TEST_TABLE_NAME add"SICIL_NO"    VARCHAR2(20 BYTE);
alter table TEST_TABLE_NAME add "ACIKLAMA" VARCHAR2(200 BYTE);

-- Adding new "last 4 fields" which were renamed at the beginning
alter table TEST_TABLE_NAME add"KAYIT_KULLANICI_ID" NUMBER(18,0);
alter table TEST_TABLE_NAME add"KAYIT_TARIHI" DATE;
alter table TEST_TABLE_NAME add"GUNCELLEME_KULLANICI_ID" NUMBER(18,0…