Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

oracle cursor ÓαêÓ÷¨

Óα꣺
ÓÃÀ´²éѯÊý¾Ý¿â£¬»ñÈ¡¼Ç¼¼¯ºÏ£¨½á¹û¼¯£©µÄÖ¸Õ룬¿ÉÒÔÈÿª·¢ÕßÒ»´Î·ÃÎÊÒ»Ðнá¹û¼¯£¬ÔÚÿÌõ½á¹û¼¯ÉÏ×÷²Ù×÷¡£
·ÖÀࣺ
¾²Ì¬Óα꣺
·ÖΪÏÔʽÓαêºÍÒþʽÓαꡣ
REFÓα꣺
ÊÇÒ»ÖÖÒýÓÃÀàÐÍ£¬ÀàËÆÓÚÖ¸Õë¡£
ÏÔʽÓα꣺
 CURSOR ÓαêÃû ( ²ÎÊý ) [·µ»ØÖµÀàÐÍ] IS
  Select Óï¾ä
ÉúÃüÖÜÆÚ£º
1.´ò¿ªÓαê(OPEN)
½âÎö£¬°ó¶¨¡£¡£¡£²»»á´ÓÊý¾Ý¿â¼ìË÷Êý¾Ý
2.´ÓÓαêÖлñÈ¡¼Ç¼(FETCH INTO)
Ö´Ðвéѯ£¬·µ»Ø½á¹û¼¯¡£Í¨³£¶¨Òå¾ÖÓò±äÁ¿×÷Ϊ´ÓÓαê»ñÈ¡Êý¾ÝµÄ»º³åÇø¡£
3.¹Ø±ÕÓαê(CLOSE)
Íê³ÉÓα괦Àí£¬Óû§²»ÄÜ´ÓÓαêÖлñÈ¡ÐС£»¹¿ÉÒÔÖØÐ´ò¿ª¡£
Ñ¡Ï²ÎÊýºÍ·µ»ØÀàÐÍ
set serveroutput on
declare
 cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;
l_emp employees%rowtype;
begin
 dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
 loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
 end loop;
 close emp_cur;
 dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
 loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
 end loop;
 close emp_cur;
end;
/
ÒþʽÓα꣺
²»ÓÃÃ÷È·½¨Á¢Óαê±äÁ¿£¬·ÖÁ½ÖÖ£º
1.ÔÚPL/SQLÖÐʹÓÃDMLÓïÑÔ£¬Ê¹ÓÃORACLEÌṩµÄÃûΪSQLµÄÒþʾÓαê
2.CURSOR FOR LOOP£¬ÓÃÓÚfor loop Óï¾ä
1¾ÙÀý£º
declare
begin
 update departments set department_name=department_name;
 --where 1=2;
 
 dbms_output.put_line('update '|| sql%rowcount ||' records');
end;
/
2¾ÙÀý£º
declare
begin
 for my_dept_rec in ( select department_name, department_id from departments)
 loop
  dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
 end lo


Ïà¹ØÎĵµ£º

Oracle ³£ÓÃ¼à¿ØSQL

1.¼à¿ØÊÂÀýµÄµÈ´ý£º
         select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*)
from v$session_wait
group by event order by 4;
2.»Ø¹ö¶ÎµÄÕùÓÃÇé¿ö£º
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollnam ......

oracle¶¨Ê±±¸·Ý

δʵÑé
ORACLEÊý¾Ý¿â×Ô´øµÄDBMS_JOB¹¦ÄÜ¿ÉÒÔʵÏÖ¶¨Ê±Ö´ÐÐPL/SQLµÄ´æ´¢¹ý³Ì£¬µ«ÊÇÈç¹ûSQLÓï¾äºÜ¸´ÔÓ£¬
        SQLÓï¾äºÜ¶à£¬ÒÔ¼°¾­³£Òª¸Ä±äSQLÓï¾äµÄд·¨£¬ÓÃдPL/SQL´æ´¢¹ý³ÌµÄ·½·¨ÔÙ¶¨Ê±Ö´Ðлá±È½Ï·±Ëö¡£ºÎ
        ¿ö»¹ÓÐһЩUNIXϵͳ¹ÜÀíÔ±²»»áдPL/SQL´æ´¢¹ý³Ì£¬ËùÒÔÎÒ½éÉ ......

OracleÊý¾Ý¿âµÄ±¸·ÝÓë»Ö¸´

OracleÊý¾Ý¿âÓÐÈýÖÖ±ê×¼µÄ±¸·Ý·½·¨£¬ËüÃÇ·Ö±ðÊǵ¼³ö£¯µ¼È루EXP/IMP£©¡¢Èȱ¸·ÝºÍÀ䱸·Ý¡£µ¼³ö±¸¼þÊÇÒ»ÖÖÂß¼­±¸·Ý£¬À䱸·ÝºÍÈȱ¸·ÝÊÇÎïÀí±¸·Ý¡£
Ò»¡¢ µ¼³ö£¯µ¼È루Export£¯Import£©
ÀûÓÃExport¿É½«Êý¾Ý´ÓÊý¾Ý¿âÖÐÌáÈ¡³öÀ´£¬ÀûÓÃImportÔò¿É½«ÌáÈ¡³öÀ´µÄÊý¾ÝËͻص½OracleÊý¾Ý¿âÖÐÈ¥¡£
£±¡¢ ¼òµ¥µ¼³öÊý¾Ý£¨Export£©ºÍµ¼ÈëÊý¾ ......

Oracleѧϰ±Ê¼Ç4 ÉÔ¸´ÔӵIJéѯºÍ·ÖÒ³

ÈÔȻʹÓÃSCOTTÓû§À´²Ù×÷£º
1¡¢         ÏÔʾ¹¤×ʱȲ¿ÃÅ30µÄËùÓÐÔ±¹¤µÄ¹¤×ʶ¼¸ßµÄÔ±¹¤µÄÐÅÏ¢£º
Select * from emp where sal>all(select sal from emp where deptno=30);
2¡¢         ÏÔʾ¹¤×ʱȲ¿ÃÅ30µÄÈÎÒâÒ»¸öÔ±¹¤µÄ¹¤×ʸ߾ͿÉÒ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ