OracleÖеİ󶨱äÁ¿
°ó¶¨±äÁ¿¾ÍÊÇÄܹ»Ìæ´úSQLÓï¾äÖг£Á¿µÄÌæ´ú±äÁ¿
×÷Óãº
°ó¶¨±äÁ¿Ö»ÊÇÆðµ½Õ¼Î»µÄ×÷Óã¬Í¬ÃûµÄ°ó¶¨±äÁ¿²¢²»Òâζ×ÅÔÚËüÃÇÊÇͬÑùµÄ£¬ÔÚ´«µÝʱҪ¿¼ÂǵÄÊÇ´«µÝµÄÖµÓë°ó¶¨±äÁ¿³öÏÖ˳ÐòµÄ¶Ô룬¶ø²»Êǰ󶨱ä Á¿µÄÃû³Æ¡£
°ó¶¨±äÁ¿ÊÇÔÚͨ³£Çé¿öÏÂÄÜÌáÉýЧÂÊ£¬·ÇÕý³£µÄÇé¿öÈçÏ£º
ÔÚ×Ö¶Î(°üÀ¨×ֶμ¯)½¨ÓÐË÷Òý£¬ÇÒ×Ö¶Î(¼¯)µÄ¼¯µÄÊƷdz£´ó£¨Ò²¾ÍÊÇÓиöÖµÔÚ×Ö¶ÎÖгöÏֵıÈÀýÌرðµÄ´ó£©µÄÇé¿öÏ£¬Ê¹Óð󶨱äÁ¿¿ÉÄܻᵼÖ²éѯ¼Æ»®´íÎó£¬Òò¶ø»áʹ²éѯЧÂʷdz£µÍ¡£ÕâÖÖÇé¿ö×îºÃ²»ÒªÊ¹Óð󶨱äÁ¿¡£
oracle ÖУ¬¶ÔÓÚÒ»¸öÌá½»µÄsqlÓï¾ä,´æÔÚÁ½ÖÖ¿ÉÑ¡µÄ½âÎö¹ý³Ì, Ò»ÖÖ½Ð×öÓ²½âÎö,Ò»ÖÖ½Ð×öÈí½âÎö.
ÔÚ¹²Ïí³ØÖдæÔÚµÄÊÇÈí½âÎö£¬¼´£ºÀàËÆÓÚPL/sql
Èí½âÎöºÍ°ó¶¨±äÁ¿£ºselect * from dept where id= :d_id;
oracle»áÊ×Ïȼì²éһϹ²Ïí»º³å³Ø£¨shared pool£©ÀïÓÐûÓÐÓëÖ®ÍêÈ«ÏàͬµÄÓï¾ä£¬Èç¹ûÓеĻ°Ö»ÐëÖ´ÐÐÈí·ÖÎö¼´¿É£¬·ñÔò¾ÍµÃ½øÐÐÓ²·ÖÎö¡£
¶øΨһʹµÃoracle Äܹ»Öظ´ÀûÓÃÖ´Ðмƻ®µÄ·½·¨¾ÍÊDzÉÓð󶨱äÁ¿¡£°ó¶¨±äÁ¿µÄʵÖʾÍÊÇÓÃÓÚÌæ´úsqlÓï¾äÖеij£Á¿µÄÌæ´ú±äÁ¿¡£°ó¶¨±äÁ¿Äܹ»Ê¹µÃÿ´ÎÌá½»µÄsqlÓï¾ä¶¼ÍêÈ«Ò»Ñù¡£
µ«ÊDz¢²»ÊÇÈκÎÇé¿ö϶¼ÐèҪʹÓð󶨱äÁ¿£¬ÏÂÃæÊÇÁ½ÖÖÀýÍâÇé¿ö£º
1£®¶ÔÓÚ¸ôÏ൱һ¶Îʱ¼ä²ÅÖ´ÐÐÒ»´ÎµÄSQLÓï¾ä£¬ÕâÊÇÀûÓð󶨱äÁ¿µÄºÃ´¦»á±»²»ÄÜÓÐЧÀûÓÃÓÅ»¯Æ÷¶øµÖÏû
2£®Êý¾Ý²Ö¿âµÄÇé¿öÏ¡£
°ó¶¨±äÁ¿²»Äܵ±×÷ǶÈëµÄ×Ö·û´®À´Ê¹Óã¬Ö»Äܵ±×÷Óï¾äÖеıäÁ¿À´Óᣲ»ÄÜÓð󶨱äÁ¿À´´úÌæ±íÃû¡¢¹ý³ÌÃû¡¢×Ö¶ÎÃûµÈ.
ÆÕͨsqlÓï¾ä£º
SELECT fname, lname, pcode from cust WHERE id = 674;
SELECT fname, lname, pcode from cust WHERE id = 234;
SELECT fname, lname, pcode from cust WHERE id = 332;
º¬°ó¶¨±äÁ¿µÄsql Óï¾ä£º
SELECT fname, lname, pcode from cust WHERE id = :cust_no;
Sql*plus ÖÐʹÓð󶨱äÁ¿£º
SQL> set timing on
SQL> variable x number;
SQL> exec :x :=8
PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
ÒÑÓÃʱ¼ä: 00: 00: 00.03
SQL> select * from A;
ID
----------
3
5
ÒÑÓÃʱ¼ä: 00: 00: 00.06
SQL> insert into A values(:x);
ÒÑ´´½¨ 1 ÐС£
ÒÑÓÃʱ¼ä: 00: 00: 00.01
SQL> select * f
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
´´½¨ÁËÒ»oracleµÄ¶¨Ê±ÈÎÎñ£¬¶¨Ê±µÄ½«Ò»¸ö±íÖеļǼ²åÈëµ½ÁíÒ»¸ö±íÖУ¬²éѯµÄʱºò±íÖо¹È»Ã»Êý¾Ý£¬ÔÚpl/sqlÖв鿴½¨µÄÈÎÎñ·¢ÏÖfailuresµÄֵΪ3£¬ÄѵÀ½¨µÄ´æ´¢¹ý³ÌÓÐ´í£¿ÔËÐÐһϴ洢¹ý³ÌÊÔÊÔ°É£¬²éÊý¾Ý¿âÊ飬ִÐд洢¹ý³ÌÃüÁîÊÇ£ºcall procedure ¹ý³ÌÃû£»
Ö´ÐÐÒ»ÏÂÃüÁ²»¶Ô£¿ÔÙ´ÓÍøÉϲé°É£¬Ò»²é£¬oracleÖÐÖ´Ðд洢¹ý³ÌµÄ ......
create table test1 as select 1 id,'create table test2(id number,name varchar2(200));insert into test2 values(1,''a'');' sqltext from dual
2 union all select 2,'truncate table test2;' from dual
3 union all select 3,'insert into test2 select rownum,dbms_random.string(''l'', ......
Oracle µÄrownum ÔÀíºÍʹÓÃ
ÔÚOracle ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum ¾Í¸ã¶¨ÁË¡£
select * from emp where rownum <= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp where rownum > 5
ÔòÊÇʧ°ÜµÄ¡£ÒªÖªµÀΪʲô»áʧ°Ü£¬ÔòÐèÒªÁ˽ârownum ±³ºóµÄ»úÖÆ£ ......