[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)--%ROWTYPEµÄʹÓÃ
ÅúÁ¿´¦ÀíÒ»°ãÓÃÔÚETL²Ù×÷, ETL´ú±íÌáÈ¡(extract),ת»»(transform),×°ÔØ(load), ÊÇÒ»¸öÊý¾Ý²Ö¿âµÄ´Ê»ã!
ÀàËÆÓÚÏÂÃæµÄ½á¹¹£º
for x (select * from...)
loop
Process data;
insert into table values(...);
end loop;
Ò»°ãÇé¿öÏÂ, ÎÒÃÇ´¦Àí´ó±ÊµÄÊý¾Ý²åÈ붯×÷, ÓÐ2ÖÖ×ö·¨, µÚÒ»ÖÖ¾ÍÊÇÒ»±Ê±ÊµÄÑ»·²åÈë
create table t1 as select * from user_tables where 1=0;
create table t2 as select * from user_tables where 1=0;
create table t3 as select table_name from user_tables where 1=0;
create or replace procedure Nor_Test
as
begin
for x in(select * from user_tables)
loop
insert into t1 values x;
end loop;
end;
µÚ2ÖÖ·½·¨¾ÍÊÇÅúÁ¿´¦Àí(insertÈ«²¿×Ö¶Î)£º
create or replace procedure Bulk_Test1(p_array_size in number)
as
type array is table of user_tables%rowtype;
l_data array;
cursor c is select * from user_tables;
begin
open c;
loop
fetch c bulk collect into l_data limit p_array_size;
&
Ïà¹ØÎĵµ£º
ÔÚDELPHIÖг£³£ÒªÓõ½ADOQUERYÖеÄSQLÓï¾äÔö¼ÓÐÅÏ¢ºóÖ´ÐУ¬µ±ÒªÇóÔö¼ÓÌõ¼þ£¨Èç²ÎÊýʱ£©ÖмäµÄ¼ä¸ôºÜÖØÒª£¬¸ã²»ºÃ¾ÍÒª³ö´í£ºÏ¾ÙÀý˵Ã÷ÎÒÒª±í´ïµÄÒâ˼£º
È磺һ¸ö±í:student ÓÐ ksh,xm,xb,lqzy,lqcc¼¸¸ö×ֶΣ¬¶¼Îª×Ö·ûÐÍ¡£ÏÖÒªÇó°´xb·Ö×éͳ¼ÆÈËÊý£¬Í¬Ê±lqccÒªÇóÏÞ¶¨Ìõ¼þ¡£
Ò»°ãÇé¿öϵÄSQLÓï¾äÓ¦¸ÃΪ£º select xb,count(*) ......
SQL×¢Èë¼òµ¥·ÖÎö
ʾÀýÓï¾ä£º
select * from admintable where adminName like '%a%'
ÔÚ²éѯÖÐÎÒÃÇÒ»°ãÔÚaÕâ¸öµØ·½ÓɽçÃæ´«È벻ͬµÄÖµ£¬µ±ÎÒÃÇÔÚaÕâÀï´«ÈëµÄֵΪ“'”µ¥ÒýºÅʱ£¬Æ´´Õ³ÉµÄSQLÓï¾ä¾ÍÈçÏ£º
select * from admintable where adminName like '%'%'
Ö´ÐÐÕâ¾äÓï¾äÎÒÃǻᷢÏÖ³öÏÖÒÔÏÂÒì³££º
......
½ñÌìÎÒÃÇ¿ªÊ¼SQL SERVER BIµÄÁíÍâÒ»¸öÖØÒªµÄ²¿·Ö --Reporting Service£¬Ïà¶ÔÓÚIntegration ServiceºÍAnalysis Service£¬Reporing ServiceÔÚ¹úÄÚµÄʹÓÃÕßÓ¦¸Ã¶àºÜ¶à.Ò»·½ÃæÓÉÓÚReporing Service·ÑÓñȽϵͣ¬Ö±½Ó¸½ÊôÔÚSQL SERVERÖУ¬ÁíÍâÒ»·½ÃæÆäʵSSRSÔںܴó³Ì¶ÈÉÏ»¹ÊÇÂú×ãÎÒÃǵı¨±íÐèÇóµÄ¡£ ÔÚSQL Server 2008ÖУ¬ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐʱºò±¨±íÉÏÃæÐèÒªÏÔʾ¸Ã±Ê²Ù×÷µÄÉÏÒ»²½Öè»òÕßÏÂÒ»²½ÖèµÄÏêϸÐÅÏ¢£¬Õâ¸öʱºò¿ ......