±±´óÇàÄñoracleѧϰ±Ê¼Ç31
pl/sql ±í
ÔÚpl/sql¿éÖÐÁÙʱʹÓá¢ÏñÊý×éÒ»ÑùµÄ¶ÔÏó
°üº¬Ò»ÁкÍÒ»¸öÖ÷¼ü
²»ÄܶÔÁкÍÖ÷¼ü½øÐÐÃüÃû
ÁпÉÒÔÊÇÈκαêÁ¿Êý¾ÝÀàÐÍ
Ö÷¼ü±ØÐëÊÇbinary_integerÀàÐÍ
´óСûÓÐÏÞÖÆ
ÉùÃ÷pl/sql±í
¶¨Òå±íµÄÀàÐÍ
type ÀàÐÍÃû is table of ÁÐÀàÐÍ|±äÁ¿Êý¾ÝÀàÐÍ index by binary_integer;
ÉùÃ÷±í±äÁ¿
±íÃû ÀàÐÍÃû;
ÒýÓÃpl/sql±í
±íÃû(ϱê)
¸³Öµ
±íÃû(ϱê):=±í´ïʽ;
ÊôÐÔ·½·¨:
count --·µ»Øpl/sql±íµÄ×ÜÐÐÊý£»
delect --ɾ³ýpl/sql±íµÄËùÓÐÄÚÈÝ£»
delect(ÐÐÊý) --ɾ³ýpl/sql±íµÄÖ¸¶¨µÄÐУ»
delct(¿ªÊ¼ÐУ¬½áÊøÐÐ) --ɾ³ýpl/sql±íµÄ¶àÐУ»
first --·µ»Ø±íµÄµÚÒ»¸öINDEX;
next(ÐÐÊý) --Õâ¸öÐÐÊýµÄÏÂÒ»ÌõµÄINDEX;
last --·µ»Ø±íµÄ×îºóÒ»¸öINDEX;
declare
Type stuNameTableType is table of student.stu_name%type index by binary_integer;
stuNameTable stuNameTableType;
begin
for i in 1..10
loop
stunametable(i):='student'||i;
end loop;
for i in 1..stunametable.count
loop
dbms_output.put_line('µÚ'||i||'¸öÔªËØ£º'||stunametable(i));
end loop;
end;
±íÀàÐÍÐèҪʹÓÃbulk collectÅúÁ¿°ó¶¨½øÐÐselect¸³Öµ£¬bulk collect×Ó¾äÒ²¿ÉÓÃÓÚFetch bulk collect into ×Ó¾äÖÐ
declare
TYPE studentTableType is table of student%rowtype index by binary_integer;
studentTable studentTableType;
begin
select * bulk collect into studentTable from student order by stu_id;
for i in 1..studentTable.count loop
dbms_output.put_line(studentTable(i).stu_id);
dbms_output.put_line(studentTable(i).stu_name);
dbms_output.put_line(studentTable(i).stu_sex);
dbms_output.put_line(studentTable(i).stu_birthday);
end loop;
end;
RecordÀàÐÍ
ÉùÃ÷
type ¼Ç¼ÀàÐÍÃû is record (
×Ö¶Î1 ÀàÐÍ[not null[:=±í´ïʽ]]
×Ö¶În ÀàÐ
Ïà¹ØÎĵµ£º
alter table µÄ¹¦ÄÜÊÇÐ޸ıí¸ñ¡£°üÀ¨ÖØÃûÃü£¬¼Ó¼õ×Ö¶Î,ÐÞ¸Ä×Ö¶ÎÀàÐͺʹóС£¬´¦Àí Ô¼ÊøµÈµÈ¡£±¾Àý×ÓÖ®´¦Àí±íÃûºÍ×ֶΣ¬´úÂëÈçÏ£º
create table liu(a varchar2(20),b number(2))
alter table liu rename to jin
rename jin to cai
alter table cai add c varchar2(30)
alter table cai add (d varchar2(3 ......
select nvl2(replace(translate('69584.00.00','.0123456789','000000000000'),'0',''),'·ñ','ÊÇ') IsNumber from dual;
select id,nvl2(replace(translate(id,'.0123456789','000000000000'),'0',''),'·ñ','ÊÇ') IsNumber
from tbl2 ......
ÔÚOracle 9iÖпÉÒÔ·½±ãµÄ°ÑÊý¾Ýµ¼³öΪÎļþ£¬»òÕß´ÓÎļþµ¼È롣ͨ¹ýOracleµÄÖÎÀí·þÎñÆ÷£¨Oracle Management Server£¬OMS£©¿ÉÒÔ·½±ãµÄʵÏÖ£¬Õû¸ö²Ù×÷¹ý³ÌÖÐÓкܶàµØ·½ÐèÒª½âÊÍ˵Ã÷£¬µ«ÊÇÍêÕûµÄÖ¸µ¼×ÊÁϲ»¶à¡£¸÷´óÂÛ̳ÉϺܶàËùνµÄ¸ßÊÖ£¬¶Ô¸ÃÖ÷ÌâµÄ»Ø´ðÍùÍùÊÇ£¬¸ßÊÖÊDz»ÓÃOMSµÄ£¬ÓÃeXP/impl°É¡£¼ÙÈçÄãÖ´Òâ×·ÎÊ£¬Ëû¶à°ë»á¸æËßÄ㣠......
Ò»¡¢Óï·¨
´óÖÂд·¨£ºselect * from some_table [where Ìõ¼þ1] connect by [Ìõ¼þ2] start with [Ìõ¼þ3];
ÆäÖÐ connect by Óë start with Óï¾ä°Ú·ÅµÄÏȺó˳Ðò²»Ó°Ïì²éѯµÄ½á¹û£¬[where Ìõ¼þ1]¿ÉÒÔ²»ÐèÒª¡£
[where Ìõ¼þ1]¡¢[Ìõ¼þ2]¡¢[Ìõ¼þ3]¸÷×Ô×÷Óõķ¶Î§¶¼²»Ïàͬ£º
[where Ìõ¼þ1]ÊÇÔÚ¸ù¾Ý“connect by [Ìõ¼þ2] ......