Oracle×Ó²éѯ
×Ó²éѯ
µ¥ÐÐ×Ó²éѯ(single-row subqueries)
ʹÓõÄÔËËã·ûºÅ(=,>,<,>=,<=,<>)
¶àÐÐ×Ó²éѯ(multiple-row subqueries)
ʹÓõÄÔËËã·ûºÅ(in,not in,exists,not exits,all,any)
Ïà¹Ø×Ó²éѯ(correlated subqueries)
¸ñʽ select ÁÐÃû,(select Óï¾ä) from ±íÃû
±êÁ¿×Ó²éѯ(scalar subqueries)
×Ó²éѯÊÇ·µ»Øµ¥Ðе¥ÁÐ,¸ñʽͬÉÏ
¶àÁÐ×Ó²éѯ(multiple-column subqueries)
ÔÚDDLÓï¾äÖÐʹÓÃ×Ó²éѯ
ÔÚDMLÓï¾äÖÐʹÓÃ×Ó²éѯ
--------
µ¥ÐÐ×Ó²éѯ
--ÏÔʾ¹¤×Ê×î¸ßµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal from emp
Where sal=(select max(sal) from emp);
--------
¶àÐÐ×Ó²éѯ
--ÏÔʾÓ벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp
Where job in (select distinct job from emp where deptno=20);
--ÏÔʾ²»Ó벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄËùÓйÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>all(select sal from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄÈκιÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>any(select sal from emp where deptno=20);
---------
Ïà¹Ø×Ó²éѯ
--ÏÔʾÿ¸ö²¿ÃŵÄ×î¸ß¹¤×ʵĹÍÔ±ÐÅÏ¢
select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--Ôö¼Ódistinct
select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--ÏÔʾ¹¤×÷ÔÚNEW YORKµÄ¹ÍÔ±ÐÅÏ¢
select ename,deptno,sal,job from emp
where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');
---------
±êÁ¿×Ó²éѯ
--·µ»Øµ¥Ðе¥ÁÐ
Select count(*) from emp;
Select sum(sal)
Ïà¹ØÎĵµ£º
ojdbc14.jarÓëclasses12.jarÓÐʲôÇø±ð??
×î½üÓÃclasses12.jar£¬hibernate3.1ÀÏÊdzöÎÊÌ⣿
ÎÒµÄoracle9.2.0.10!
ÔÚÍøÉÏÕÒ£¬·¢ÏÖÓÐÈËÓÃojdbc14.jar¿ª·¢£¬Ã»ÓÐÓùýÄØ£¿¿´oracleµÄreadme.txtҲûÓп´Ê²Ã´ÄØ£¿
ÄÇλ´ó¸çÖªµÀ˵һÏ£¡Ê®·Ö¸Ðл£¡
------------------------------------------------------------------------- ......
ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖظ´¼Ç¼£¬µ«Ö»ÄÜÈ¡Ò»¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......
ORACLE º¯Êý¿ª·¢
1¡¢½¨Á¢º¯ÊýµÄÓï·¨£º
CREATE [OR REPLACE] FUNCTION º¯ÊýÃû
[ (arg1 [mode] datatype[,........])] --µ±Ö¸¶¨²ÎÊýÊý¾ÝÀàÐÍʱ£¬²»ÄÜÖ¸¶¨Æ䳤¶È¡£modeÖµ£ºIN¡¢OUT¡¢IN OUT£¬µ±mode δָ¶¨Ê±£¬±íʾΪIN
RETURN datatype ......
¾³£ÓÐDBAÅóÓÑÃÇÏòÎÒ×Éѯ£¬Èç¹û´òËã½øÈëDBAÕâÒ»ÐУ¬Ó¦¸ÃÔõÑùÈëÃÅ£¬»òÕß¿ÉÒÔͨ¹ýÔõÑùÒ»¸ö·ÏßͼÀ´½øÐÐѧϰ¡£
¹ØÓÚÕâ¸öÖ÷Ì⣬ÆäʵÍøÂçÉÏÒѾÓÐÁËһЩÄÚÈÝ¿ÉÒԲο¼¡£µ«ÊǼÈÈ»Õâô¶àÅóÓÑÎÊÌ⣬ÎҾͽ«Ö®Ç°Ð´ºÃµÄÒ»µãÄÚÈÝ·¢±í³öÀ´¡£
ËãÊǶԴó¼ÒµÄÒ»¸ö»Ø´ð£¬Èç¹ûÄܹ»ÓÐËù½è¼øÄÇÊÇ×îºÃµÄ¡£
ÎÒµÄÕâ·Ý·Ïßͼ£¬ÊÇ°´ÕÕÎÒд×÷µÄÁ½±¾Êé ......
1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
²ÎÊý1 IN NUMBER,
²ÎÊý2 IN NUMBER
) IS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN
END ´æ´¢¹ý³ÌÃû×Ö
2.SELECT INTO STATEMENT
½«select²éѯµÄ½á¹û´æÈëµ½±äÁ¿ÖУ¬¿ÉÒÔͬʱ½«¶à¸öÁд洢¶à¸ö±äÁ¿ÖУ¬±ØÐëÓÐ ......