Oracleѧϰ±Ê¼Ç4 ÉÔ¸´ÔӵIJéѯºÍ·ÖÒ³
ÈÔȻʹÓÃSCOTTÓû§À´²Ù×÷£º
1¡¢ ÏÔʾ¹¤×ʱȲ¿ÃÅ30µÄËùÓÐÔ±¹¤µÄ¹¤×ʶ¼¸ßµÄÔ±¹¤µÄÐÅÏ¢£º
Select * from emp where sal>all(select sal from emp where deptno=30);
2¡¢ ÏÔʾ¹¤×ʱȲ¿ÃÅ30µÄÈÎÒâÒ»¸öÔ±¹¤µÄ¹¤×ʸ߾ͿÉÒÔµÄÔ±¹¤µÄÐÅÏ¢£º
Select * from emp where sal>any(select sal from emp where deptno=30);
Æäʵ£¬»»ÖÖ·½·¨Ò²ÊÇ¿ÉÒԵģº
Select * from emp where sal>(select min(sal) from emp where deptno=30);
3¡¢ ÕÒ³öºÍSMITHµÄ²¿Ãźš¢¹¤×÷¶¼ÏàͬµÄÔ±¹¤µÄÐÅÏ¢£º
Select * from emp where (deptno,job)=(Select deptno,job from emp where ename=’SMITH’);
4¡¢ ÈçºÎÏÔʾ¸ßÓÚ×Ô¼º²¿ÃÅÆ½¾ù¹¤×ʵÄÔ±¹¤µÄÐÅÏ¢
½â¾öÕâ¸ö¸´ÔÓÒ»µãµÄ²éѯ£¬ÎÒÃÇ¿ÉÒÔÕâÑù¿¼ÂÇ£º
ÏÈÕÒ³öÿ¸ö²¿Ãŵį½¾ù¹¤×Ê£º
Select deptno,avg(sal) avg_sal from emp group by deptno;½á¹ûÈçÏ£º
SQL> select deptno,avg(sal) avg_sal from emp group by deptno;
DEPTNO AVG_SAL
------ ----------
30 1566.66666
20 2175
10 2916.66666
È»ºóÎÒÃǾͿÉÒÔ°ÑÕâÕÅ±íµ±³ÉÒ»ÕÅ×Ó±íʹÓãº
select a2.ename,a2.sal,a2.deptno,a1.avg_sal from emp a2,(select deptno,avg(sal) avg_sal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.avg_sal;
½á¹ûΪ£º
ENAME SAL DEPTNO AVG_SAL
---------- --------- ------ ----------
ALLEN 1600.00 30 1566.66666
JONES 2975.00 20 2175
BLAKE 2850.00 30 1566.66666
SCOTT 3000.00 20 2175
K
Ïà¹ØÎĵµ£º
PL/SQLÊǶÔOracleÊý¾Ý¿â½øÐвÙ×÷µÄÒ»ÖÖ¹ý³ÌÐÔ±à³ÌÓïÑÔ£¬Ëü¿ÉÒÔÔÚSQL * plusÖÐÖ´ÐУ¬Ò²¿ÉÒÔǶÈëµ½JAVA»òÕßC++ÖС£Æä»ù±¾×é³ÉΪ£º
DECLARE
...
BEGIN
...
EXCEPTION
...
END
¿ÉÓÃÀ´½«¶ÔÊý¾Ý¿âµÄ¸÷ÖÖ²Ù×÷·âװΪһ¸ö´úÂë¿é£¬ÎªÁ˼ÓÇ¿¿ÉÖØÓÃÐÔ£¬»¹¿É½«Öظ´ÐÔ´úÂëд³Éº¯ÊýÓë´æ´¢¹ý³Ì£¬Ó ......
recordʾÀý£º
create or replace procedure pro_test_record(vid in varchar2) is
type userRow is record(
id t_user.id%type,
name t_user.name%type
);
realRow userRow;
begin
select id,name into realRow from t_user where id=vid;
dbms_output.put_line(realRow.id||','||realRow.name);
end pro_test_re ......
1£®OracleΪ¿Í»§¶Ë¿ªÆô»á»°ÓÐÁ½ÖÖ·½Ê½£º¹²Ïí·þÎñºÍרÓ÷þÎñ¡£ÔÚרÓ÷þÎñÇé¿öÏ£¬¼àÌýÆ÷ΪÁ¬½ÓÇëÇó´´½¨Ð½ø³Ì£¨Unix»·¾³ÏÂÊÇProcess£¬WindowsÏÂÎÒÏëÓ¦¸ÃÊÇThread°É£©£»¹²Ïí·þÎñÇé¿öÏ£¬¼àÌýÆ÷½«¿Í»§ÇëÇ󽻸øDispatcher£¬ÓÉDispatcher°²ÅŶà¿Í»§µÄ×÷Òµ¡£SQL ServerÔÚĬÈÏÇé¿öÏÂ×Ô¶¯Îª¿Í»§¶ËÁ¬½Ó´´½¨Ị̈߳¬µ±Óзdz£¶àµÄ¿Í ......
ÏȽ¨ÁËÕŲâÊÔ±í
SQL> select * from test_a;
ID PLAYNAME SCORE
-------------------- --- ......
ÔÚÕâÀïÎÒÃǽ«½éÉÜOracleÊý¾Ý¿âÓÅ»¯·½°¸Óëʵ¼ù£¬²»Í¬µÄ»·¾³»áÓв»Í¬µÄµ÷ÊÔ£¬µ«ÊÇÒ²»áÓвî±ð£¬Ï£Íû´ó¼ÒÄܺÏÀíµÄÎüÊÕ¡£ Ò»¡¢Ç°ÑÔ ¶þ¡¢ORACLEÊý¾Ý¿âÓÅ»¯¸ÅÊö 1¡¢ÄÚ´æµÈ²ÎÊýÅäÖõÄÓÅ»¯ 2¡¢¼õÉÙÎïÀí¶ÁдµÄÓÅ»¯ 3¡¢ÅúÁ¿Öظ´²Ù×÷µÄSQLÓï¾ä¼°´ó±í²Ù×÷µÄÓÅ»¯ ¶þ¡¢ORACLEÊý¾Ý¿âÓÅ»¯·½°¸ 1¡¢ÄÚ´æµÈOracleϵͳ²ÎÊýÅäÖà 2¡¢Ê ......