±±´óÇàÄñoracleѧϰ±Ê¼Ç19 20 21 22
Óαê
Ö¸ÏòÉÏÏÂÎÄÇøÓòµÄ¾ä±ú»òÖ¸Õë
ÓαêÔÚ·þÎñÆ÷¶Î´æ´¢
ÊôÐÔ
%NOTFOUND ûÓÐÕÒµ½½á¹û»òûÓвÙ×÷³É¹¦
%FOUND ÕÒµ½½á¹û»ò²Ù×÷³É¹¦
%ROWCOUNT ÓαêÓ°ÏìµÄÐÐÊý
%ISOPEN ÓαêÊÇ·ñ´ò¿ª ÒþʽÓαêϵͳ×Ô¶¯Î¬»¤£¬ÓÀԶΪ¼Ù
ÀàÐÍ£º
¾²Ì¬Óαê
-ÒþʽÓαê
ÔÚoracleÄÚ²¿ÉùÃ÷
ÓÃÓÚ´¦ÀíDMLÓï¾äºÍ·µ»Øµ¥ÐеIJéѯ
SQLΪÒþʽÓαêĬÈÏÃû³Æ£¬Àý£º
begin
insert into student values('3','test','m',sysdate);
dbms_output.put_line('ÓαêÓ°ÏìµÄÐÐÊý£º'||SQL%ROWCOUNT);
end;
-ÏÔʽÓαê
ÓÉÓû§ÏÔʾÉùÃ÷
ÓαêÖ¸Ïò»î¶¯¼¯µÄµ±Ç°ÐÐ
¿ØÖÆÏÔʾÓαê
Open ´ò¿ªÓαê
Fetch … into …
Close ¹Ø±ÕÓαê
declare
cursor stuCur is select * from student;
stuRow student%rowtype;
begin
open stuCur;
loop
fetch stuCur into sturow;
exit when stuCur%NotFound;
dbms_output.put_line(sturow.stu_name);
dbms_output.put_line(stuCur%RowCount);
end loop;
close stuCur;
end;
REFÓαê(¶¯Ì¬Óαê)
ÔÚÔËÐÐʱʹ²»Í¬µÄÓï¾äÓëÖ®¹ØÁª
REFÓαêʹÓÃÓαê±äÁ¿
Óαê±äÁ¿£ºÒ»ÖÖÒýÓÃÀàÐÍ£¬¿ÉÒÔÔÚÔËÐÐʱָÏò²»Í¬µÄ´æ´¢Î»Öã¬closeÓï¾ä¹Ø±ÕÓα겢ÊÍ·ÅÓÃÓÚ²éѯµÄ×ÊÔ´¡£
ÀàÐÍ£º
ÓÐÔ¼ÊøµÄÓαê±äÁ¿£º¾ßÓзµ»ØÀàÐÍ ¶¨ÒåÓαêʱ¼ÓÉÏreturn ÓαêÀàÐÍ
declare
type RefstuCur is ref cursor return stu%rowtype;
stuCur RefstuCur;
sturow student%rowtype;
flag int :=0;
begin
flag := &flag;
if flag = 0 then
open stucur for select * from student where stu_id <= '2';
else
open st
Ïà¹ØÎĵµ£º
ORACLE 10 ѧϰ±Ê¼Ç-µÚ2½Ú-ÃüÁî¡£
1. inner join / left join/ right join / full join
select a.dname, b.ename from dept a, emp b where a.deptno=b.deptno and a.deptno=10;
select a.dname, b.ename from dept a inner join emp b
on a.deptno=b.deptno and a.deptno=10;
select dname,ename from dept natural ......
²é¿´ÕýÔÚÖ´ÐеÄsqlÓï¾ä
SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
²¶×½ÔËÐкܾõÄSQL
select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text from v$session_longops , v$ ......
Òì³£
Ô¤¶¨ÒåÒì³£
oracleΪ³£¼û´íÎóÔ¤¶¨Òå
ÔÚDBMS_STANDARD³ÌÐò°üÖÐÌṩÁËÕâЩ¶¨Òå
²»ÐèÒªÏÔʾÉùÃ÷
declare
sex student.stu_sex%type;
begin
select stu_sex into sex from student;
dbms_output.put_line('sex:'||sex);
exception
&nbs ......
½ñÌì×öͳ¼ÆµÄʱºò·¢ÏÖÒ»¸öÊý¾Ý±íÃæÉÏ¿´À´ÊÇ‘920 ’µ«È¥¿Õ¸ñÎÞÂÒÈçºÎ¶¼È¥²»µô£¬×îºó·¢ÏÖÊDz»¿É¼û×Ö·û¡£¡£¡£
ÓÚÊÇÔÚÍøÉÏËѵ½Ò»ÆªÎÄÕ£¬ÈçºÎÀ´ÅжÏÕâ¸ö²»¿É¼û×Ö·ûµÄÎÊÌâ~
°´ÏÂÃæµÄ·½·¨Åжϳö²»¿É¼û×Ö·ûµÄascIIÂëÊÇ10£¬ÓÚÊÇÓÃrtrim('Ô×Ö·û','chr(10)')À´È¥µôÕâ¸ö²»¿É¼û×Ö·û¡£
½ñÌìÓöµ½Ò»Ôò°¸Àý£¬ÎÊÌâÊÇÊý¾Ý¿âÖп ......