[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)--%ROWTYPEµÄʹÓÃ
ÔÚPL/SQL³ÌÐòÖÐ, ÎÒÃÇ»áÓöµ½ÐèÒªÏÈ´ÓÒ»¸ö½á¹û¼¯ÖÐÈ¡³öÈô¸É¼Ç¼, È»ºó¶ÔÿһÌõ¼Ç¼½øÐд¦ÀíµÄÇé¿ö, ×îÀíÏëµÄ×ö·¨ÊÇÔÚÒ»ÌõSQLÓï¾äÖÐÍê³É, µ«ÓÐʱºòÒòΪÐèÇóµÄ¹ØϵËùÒÔ²»Ò»¶¨ÄÜʵÏÖ, ËùÒÔÎÒÃÇͨ³£»á¶¨Ò弸¸ö±äÁ¿, È»ºó¶Ô½á¹û¼¯×öÑ»·, ¸³Öµ¸ø±äÁ¿. ×îµäÐ͵ľÍÊÇselect XX into XX¡£ÎÒÃÇÒ²¿ÉÒÔÓö¨ÒåÒ»¸ö¼Ç¼%rowtypeµÄ·½·¨, ¼õÉÙ²»±ØÒªµÄ´úÂëÁ¿, »¹ÄܱÜÃâÓÉÓÚ±íÖÐ×ֶεıä¸üÔì³ÉµÄ´íÎó¡£Ê¾ÀýÈçÏ£º
´´½¨²âÊÔ±í£º
create table t1 as select * from user_tables
´´½¨PACKAGE HEAD:
create or replace package Type_demo is
procedure process1(p_record in t1%rowtype);
procedure process_data(p_inputs in varchar2);
end Type_demo;
´´½¨PACKAGE BODY:
create or replace package body Type_demo is
procedure process1(p_record in t1%rowtype)
as
begin
dbms_output.put_line(p_record.tablespace_name);
end;
procedure process_data(p_inputs in varchar2)
as
begin
for x in(select * from t1 where table_name like p_inputs)
loop
process1(x);
end loop;
end;
end Type_demo;
ÉÏÃæµÄÀý×ӵõ½µÄÊÇÕû¸ö±íµÄ¼Ç¼ÀàÐÍ, Èç¹ûÏëÒªµÃµ½Ä³¼¸¸öÁеļǼÀàÐÍ, ¿ÉÒÔ°´ÕÕÈçÏÂ×ö·¨£º
create or replace package Type_demo is
cursor template_cursor
is select table_nam
Ïà¹ØÎĵµ£º
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËƺõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐʱºò±¨±íÉÏÃæÐèÒªÏÔʾ¸Ã±Ê²Ù×÷µÄÉÏÒ»²½Öè»òÕßÏÂÒ»²½ÖèµÄÏêϸÐÅÏ¢£¬Õâ¸öʱºò¿ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)- ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)- ......