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

OracleÓαêʵÓÃ

  declare
sqlstring varchar2(100);
emprow emp_bak%rowtype;
myno emp_bak.empno%type;
begin

--sqlstring:='create table emp_bak as select * from emp';

sqlstring:='select * from emp_bak where empno=:myno and sal>:mysal';

myno:='&ÇëÊäÈëÔ±¹¤±àºÅ';

--¶¯Ì¬SQL
execute immediate sqlstring into emprow using myno,3001 ;

dbms_output.put_line(emprow.sal);

exception
when no_data_found then
dbms_output.put_line('ÊäÈëµÄÔ±¹¤±àºÅ²»´æÔÚ');
end;
select * from emp_bak
--ÒþÊ¿Óαê ÓαêÃû×Ö SQL
--ÒþʽÓαê ÊÇoracle ϵͳ×Ô¶¯ÔÚÔËÐÐDMLÓï¾äµÄʱºò Éú³ÉµÄ¡£ Ëû×Ô¶¯´ò¿ª ×Ô¶¯¹Ø±Õ¡£
-- Ò»°ãÇé¿öÏ ¸ÃÓÎ±ê ¶ÔÓÚ³ÌÐòÔ±À´Ëµ ÊÇ͸Ã÷µÄ
declare
begin

update emp_bak set sal = 2500 where empno = 8888;


--Êä³öÒþÊ¿ÓαêÖеÄÒ»¸öÊôÐÔ %rowcount ¾ÍÊÇÓ°ÏìµÄÐÐÊý
dbms_output.put_line(sql%rowcount);
end;
-- %ISOPEN - ÓαêÊÇ·ñ´ò¿ª ²¼¶ûÖµ
-- %noopen ûÓÐÕâ¸öÊôÐÔ£¡£¡
-- %FOUND – ÓαêÖÐÊÇ·ñ»¹ÓÐÊý¾Ý
-- %NOTFOUND – ÓαêÖÐÊÇ·ñ»¹ÓÐÊý¾Ý
-- %ROWCOUNT – SQL Óï¾äÓ°ÏìµÄÐÐÊý
-- CURSOR Óαê
--ÏÔʽÓαê ÓгÌÐòÔ±×Ô¼º¶¨ÒåÓαê ×Ô¼º¶¨Òå ×Ô¼ºÊ¹ÓÃ
--ÓαêҪʹÓõĻ° ²½Öè 1 ¶¨Òå 2 ´ò¿ª 3 ʹÓã¨Ñ­»·£© 4 ¹Ø±ÕÓαê
declare
--`1 ¶¨ÒåÓαê
cursor mycursor is select ename,sal from emp_bak;
emprow emp_bak%rowtype;
begin
-- 2 ´ò¿ªÓαê
open mycursor;

-- 3 ʹÓÃÓαê

loop
-- ÓαêÖеÄÊý¾Ý ÊÇͨ¹ý¹Ø¼ü×Ö ÌáÈ¡ fetch
fetch mycursor into emprow.ename,emprow.sal; -- 1 ÌáÈ¡Êý¾Ý 2 ²¢ÇÒ¸ÃÓαê»áÖ¸ÏòÏÂÒ»ÐÐ

if(emprow.sal >2000 and emprow.sal<3000) then
dbms_output.put_line(emprow.ename||' '|| emprow.sal);
end if;
-- dbms_output.put_line(emprow.ename||' '|| emprow.sal);
exit when mycursor%NOTFOUND; --2 ʹÓÃÓαêµÄ%NOTFOUNDÊôÐÔ¼ì²âÓαêÊÇ·ñ»¹ÓÐÊý¾Ý Èç¹ûûÓÐÁË ÄÇô¾ÍÍ˳öÑ­»·£¡£¡

end loop;

-- ¹Ø±ÕÓαê
close mycursor;
end;
--¼òµ¥Ð´·¨ Ñ­»·Óαê
declare
--`1 ¶¨ÒåÓαê
cursor mycursor is select ename,


Ïà¹ØÎĵµ£º

oracle¸´Ï°£¨Ò»£© Ö®OracleÌåϵ½á¹¹

      ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬Ô­À´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·­Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
      µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......

Oracle ±Ê¼Ç


          µÚ1Ò³
 
1. Oracle µÄʹÓÃ
1.1. SQLPLUS µÄÃüÁî
³õʼ»¯±íµÄλÖãº
set
NLS_LANG=american_america.us7ascii  (ÉèÖñàÂë²Å¿ÉÒÔʹÓÃÏÂÃæ½Å±¾)
cd
$ORACLE_HOME/rdbms   cd demo     summit2.sql
********* ......

¡¾×ª¡¿ORACLEÓû§Á¬½ÓµÄ¹ÜÀí

ÓÃϵͳ¹ÜÀíÔ±£¬²é¿´µ±Ç°Êý¾Ý¿âÓм¸¸öÓû§Á¬½Ó£º

SQL> select username,sid,serial# from v$session;

Èç¹ûҪͣij¸öÁ¬½ÓÓÃ

SQL> alter system kill session 'sid,serial#';

 ......

ORACLE ÖÐROWNUMÓ÷¨×ܽá


    ¶ÔÓÚ Oracle µÄ rownum ÎÊÌ⣬ºÜ¶à×ÊÁ϶¼Ëµ²»Ö§³Ö>,>=,=,between...and£¬Ö»ÄÜÓÃÒÔÉÏ·ûºÅ(<¡¢<=¡¢!=)£¬²¢·Ç˵ÓÃ>,>=,=,between..and ʱ»áÌáʾSQLÓï·¨´íÎ󣬶øÊǾ­³£ÊDz鲻³öÒ»Ìõ¼Ç¼À´£¬»¹»á³öÏÖËƺõÊÇĪÃûÆäÃîµÄ½á¹ûÀ´£¬ÆäʵÄúÖ»ÒªÀí½âºÃÁËÕâ¸ö rownum αÁеÄÒâÒå¾Í²»Ó¦¸Ã¸Ðµ½¾ªÆæ£ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ