Oracle PL/SQL

Amar · July 24, 2012, 6:10pm

Ima li neko da nije na vi sa PL/SQL-om?

Ovakva je situacija.

Imam tabelu employees koja sadrzi, izmedju ostalih, kolone: first_name, last_name, salary.

Treba mi row (ili vise njih) sa najvecom platom.

Pri tome, ne smijem koristiti subqueries sa kojima se “problem” da vrlo lako rijesiti:

SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

U mySQL-u i postgresu mogu koristiti ovo:

SELECT first_name, last_name, MAX(salary)
FROM employees
GROUP BY first_name, last_name
ORDER BY salary DESC LIMIT 1;

ali Oracle ne podrzava LIMIT.

Probao sam i sa rownum ali mi uvijek vraca pogresan row.

Nije sad da mi je ovo nesto pretjerano bitno, ali mi smijesno da za ovakvu sitnicu moram pisati subquery pa me zanima moze li bez toga.

adioe3 · July 24, 2012, 6:38pm

Ne treba ti MAX(salary) u mysql/pgsql niti GROUP BY:

Amar · July 24, 2012, 6:40pm

[quote=adioe3]Ne treba ti MAX(salary) u mysql/pgsql niti GROUP BY:

OK, moze i ovako.

Ali mi to ne rijesava problem sa PL/SQL.om

die7 · July 24, 2012, 9:21pm

[quote=Amar]Ima li neko da nije na vi sa PL/SQL-om?

Ovakva je situacija.

Imam tabelu employees koja sadrzi, izmedju ostalih, kolone: first_name, last_name, salary.

Treba mi row (ili vise njih) sa najvecom platom.

Pri tome, ne smijem koristiti subqueries sa kojima se “problem” da vrlo lako rijesiti:

SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

U mySQL-u i postgresu mogu koristiti ovo:

SELECT first_name, last_name, MAX(salary)
FROM employees
GROUP BY first_name, last_name
ORDER BY salary DESC LIMIT 1;

ali Oracle ne podrzava LIMIT.

Probao sam i sa rownum ali mi uvijek vraca pogresan row.

Nije sad da mi je ovo nesto pretjerano bitno, ali mi smijesno da za ovakvu sitnicu moram pisati subquery pa me zanima moze li bez toga.[/quote]

kao prvo dobro dosao u svijet ORACLE ANALYTICAL FUNKCIJA

pa promisli malo o tom selectu i sta on radi pa ce ti biti jasnije , sem toga sto u jednom ®DBMS ide nemora ici u drugom

mozes to odraditi i bez subselecta koristeci having

SELECT first_name, last_name, MAX(salary) as “moneymaker” FROM employees GROUP BY salary HAVING MAX(salary) > 1;

dexter · July 24, 2012, 9:35pm

Jeste da, HAVING je magicna rijec u Oracle SQL-u

Amar · July 24, 2012, 10:56pm

Probao sam razne HAVING kombinacije, ni jedna nije dala zeljeni rezultat.

@die7

konkretno ovaj tvoj prijedlog daje error:

SQL> select first_name, last_name, max(salary) as money from employees group by salary having max(salary) > 1;
select first_name, last_name, max(salary) as money from employees group by salary having max(salary) > 1
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Dakle, ako u SELECT dijelu imam grupnu funkciju (u ovom slucaju MAX) i takodjer selektujem kolone bez grupne funkcije, sve one kolone koje nisu dio grupne funkcije se moraju naci u GROUP BY dijelu.

Znaci, trebalo bi izgledati nekako ovako:

SELECT first_name, last_name, MAX(salary)
FROM employees
GROUP BY first_name, last_name
HAVING MAX(salary) > 1;

Eventualno, mogu jos ukljuciti salary u HAVING dio, ali to ne mijenja output, u oba slucaja dobijem svih 107 rezultata.

U principu, ako imam aggregate (grupnu) funkciju u SELECT dijelu, query ce proci kroz svaki row u bazi prije nego sto aplicira tu funkciju. U ovom slucaju, proci ce kroz 107 linija first_name i last_name i pokusati pronaci grupu na kojoj ce aplicirati MAX funkciju. S obzirom da je svih 107 linija unique query to vidi kao 107 grupa i MAX funkcija ce biti aplicirana na svaki row. Odnosno racunace maksinalnu platu za svakog radnika, uzimajuci jedan i jedini unos za salary koji postoji.
Drugacije bi bilo da postoje recimo tri radnika koja se zovu John Smith. Onda bi u rezultatima dobio jednog John Smitha cijoj bi koloni bila dodijeljena najveca plata od postojecih tri John Smitha.

Fazon je sto unaprijed ne znam najvecu platu a HAVING MAX(salary) > 1 ce mi vratiti sve plate, jer su sve vece od 1

EDIT:
adioe3 je pronasa razlog zasto rownum vraca pogresan row. Ako se rownum koristi u ORDER BY dijelu, sam ORDER BY izvrsi re-indeksaciju redova, samim time ignorisuci prijasnji rownum u WHERE dijelu.

die7 · July 25, 2012, 4:50am

pa nisi ni izveo moj prijedlog kako je napisan

btw. HAVING ide u oba pravca < 100000

u nekim slucajevima je subselect potreban, bitno je da funkcionise elegancija je za manekene

Amar · July 25, 2012, 8:05am

Evo copy/paste tvog prijedloga:

SQL> SELECT  first_name, last_name, MAX(salary) as "moneymaker" FROM employees GROUP BY salary HAVING MAX(salary) > 1;
SELECT	first_name, last_name, MAX(salary) as "moneymaker" FROM employees GROUP BY salary HAVING MAX(salary) > 1
        *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

u SELECT dijelu povlacis first_name i last_name sto su kolone nad kojima ne radis nikakvu grupnu funkciju. Samim time te dvije kolone moras imati u GROUP BY klauzuli jer nad trecom kolonom koju povlacis radis grupnu funkciju.

Evo da malo smanjim output. Recimo da me zanimaju samo uposlenici cije ime pocinje sa J

SELECT first_name, last_name, salary
FROM employees
WHERE first_name LIKE 'J%';

FIRST_NAME	     LAST_NAME			   SALARY
-------------------- ------------------------- ----------
Jennifer	     Whalen			     4400
John		     Chen			     8200
Jose Manuel	     Urman			     7800
Julia		     Nayer			     3200
James		     Landry			     2400
James		     Marlow			     2500
Jason		     Mallin			     3300
John		     Seo			     2700
Joshua		     Patel			     2500
John		     Russell			    14000
Janette 	     King			    10000

FIRST_NAME	     LAST_NAME			   SALARY
-------------------- ------------------------- ----------
Jonathon	     Taylor			     8600
Jack		     Livingston 		     8400
Jean		     Fleaur			     3100
Julia		     Dellinger			     3400
Jennifer	     Dilly			     3600

16 rows selected.

Znaci, ovo mi izbacuje 16 rezultata, bez grupne funkcije.
Medjutim, postoje duplikati i to samo kod first_name.a, John se pojavljuje tri puta, a Jennifer, James i Julia po dva puta.

Ako odradim sada neku grupnu funkciju nad salary, i sa njom povucem i first_name i last_name, opet cu dobiti svih 16 redova:

SQL> edit
Wrote file afiedt.buf

  1  SELECT first_name, last_name, MAX(salary)
  2  FROM employees
  3  WHERE first_name like 'J%'
  4* GROUP BY first_name, last_name
SQL> /

FIRST_NAME	     LAST_NAME		       MAX(SALARY)
-------------------- ------------------------- -----------
John		     Chen			      8200
Julia		     Dellinger			      3400
Jennifer	     Dilly			      3600
Jean		     Fleaur			      3100
Janette 	     King			     10000
James		     Landry			      2400
Jack		     Livingston 		      8400
Jason		     Mallin			      3300
James		     Marlow			      2500
Julia		     Nayer			      3200
Joshua		     Patel			      2500

FIRST_NAME	     LAST_NAME		       MAX(SALARY)
-------------------- ------------------------- -----------
John		     Russell			     14000
John		     Seo			      2700
Jonathon	     Taylor			      8600
Jose Manuel	     Urman			      7800
Jennifer	     Whalen			      4400

16 rows selected.

U oba ova slucaja, svaki row je identican i tretiran je kao jedna grupa.

Medjutim, ako me last_name ne zanima, hocu samo first_name i MAX(salary), rezultat ce biti drugaciji:

SQL> edit
Wrote file afiedt.buf

  1  SELECT first_name, MAX(salary)
  2  FROM employees
  3  WHERE first_name like 'J%'
  4* GROUP BY first_name
SQL> /

FIRST_NAME	     MAX(SALARY)
-------------------- -----------
Jose Manuel		    7800
Jean			    3100
John			   14000
Julia			    3400
Jonathon		    8600
Jack			    8400
Jennifer		    4400
Jason			    3300
Joshua			    2500
Janette 		   10000
James			    2500

11 rows selected.

U ovom slucaju su identicna imena tretirana kao jedna grupa, tako da su tri Johna dobila jedan entry sa najvecom platom itd.

Amar · July 25, 2012, 8:35am

Evo sa mysqlom:

mysql> select first_name, last_name, salary
    -> from employees
    -> order by salary desc limit 1;
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| John       | Russell   |  14000 |
+------------+-----------+--------+
1 row in set (0.00 sec)

mysql>

testni_hamo2 · July 25, 2012, 5:31pm

Ovo se ne moze rijesiti bez subqueryja u nekom obliku.

radi optimizacije mozes napraviti nesto kao

select first_name, last_name, salary from
employees e join (select max(salary) as salary from employees) max on e.salary = max.salary

u kojem slucaju ce se subquery samo jednom izvrsiti
(mada ne znam kako oracle radi - mozda moze tvoj originalni kveri automatski optimizirati
tako da samo jednom pokrene subquery).

Dakle to je to.

Amar · July 25, 2012, 5:46pm

fala druze hamo

Cudno mi malo da Oracle ne podrzava LIMIT, al hajd.

dexter · July 25, 2012, 8:02pm

Zaboravih pitati - jesil probao sa rownum, pasel’ ti mozda to? To ti je neka varijanta ‘limit-a’ iz MySQL-a (s tim koliko se sjecam moras ga koristiti u where klauzuli tako da moras i query tome prilagoditi).

testni_hamo2 · July 25, 2012, 8:09pm

ne znam kako bi ti limit pomogao …
ni da imas limit ne bi mogao uraditi to sto ti treba …
tj. ako ima vise ljudi koji imaju maksimalnu platu - onda ti kveri treba vise imena izbaciti
dakle to vise moze biti i sva polja, a moze biti i dva polja … sta bi stavio u limit onda da imas mogucnost koristenja LIMIT ?

Edit:
procitah sad
onaj originalni kveri sa limitom bi uvijek vratio samo jedan red …
a to nije u skladu sa mojim shvatanjem dijela zadatka u kojem se kaze
da trebas i vise redova vratiti ukoliko zadovoljavaju uslov …

Amar · July 25, 2012, 9:14pm

@dexter
probao sam rownum, vraca uvijek pogresan row. Zasto i kako, mrsko mi bilo da istrazujem

@testni_hamo2
u pravu si, limit bi vratio uvijek samo jedan row. U principu ono prvo rjesenje je izgleda i jedino, s tim da je tvoj query bolji jer se subquery izvrsava samo jednom a ne na svakoj liniji.

testni_hamo2 · July 26, 2012, 3:49am

Treba jos napomenuti da je onaj prvi oblik - ukoliko je tabela relativno mala puno citljiviji za programera koji treba da to odrzava

Kada radis u timu treba se truditi programirati kao da je covjek koji ce doci da to odrzava iza tebe osudjivani ubica koji zna gdje zivis …

vedran · July 26, 2012, 7:27am

Interesantna činjenica: LIMIT statement je “izumio” Rasmus Lerdorf, inače poznatiji kao autor PHPa

Amar · July 26, 2012, 8:49am

Ali drug hamo je u potpunosti u pravo glede ovog mog slucaja. LIMIT je ustvari losa ideja jer vraca samo unaprjed definisan broj linija, ne uzimajuci u obzir koliko bi se linija teoretski moglo vratiti.

Ovaj case je closed, ali nek stoji tema, imacu ja sigurno jos pitanja

adioe3 · July 26, 2012, 9:36am

Koji za sebe kaze da nije programer

testni_hamo2 · July 26, 2012, 10:22am

Jah … koji je prema poznatom historijskom djelu - a brief, incomplete and mostly wrong history of programming languages http://james-iry.blogspot.com/2009/05/brief-incomplete-and-mostly-wrong.html
sjedeci u talijanskom restoranu shvatio da su spagete jako dobra analogija za razumjevanje i programiranje WWW-a …

Amar · August 1, 2012, 2:16pm

Novo pitanje:

kada se kolona oznaci kao unused sa:

ALTER TABLE tablename SET UNUSED COLUMN kolona;

postoji li nacin da. umjesto da je dropam, vratim u tabelu? Probao sam ROLLBACK, ali kolona i dalje nije u tabeli.

EDIT: Logicno je da ROLLBACK ne radi jer je ALTER DDL Statement koji ima implicit COMMIT…