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

My PL/SQL practice 8/2/10

SQLPlus  :http://www.orafaq.com/wiki/SQL*Plus_FAQ
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm
1. Transfer values from a sql scripts:
   CNT=`sqlplus -s username/password1@dbname @getUVQuery_NULLCNT`;
 
   Note : Remeber to use option "-s"  (-s or -silent      -- start sqlplus in silent mode. Not recommended for beginners!)
   if not use "-s" , will return something as below :
        SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 9 11:03:08 2010
    Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    0
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
2.getUVQuery_NULLCNT.sql
        set term off
    column cnt noprint new_value total
    SELECT count(inquiry_id) cnt
      from inquire_all_current a
     WHERE FORM_COMPLETE_STATUS = 'Y'
      and ITEM_POPULATE_STATUS = 'N'
     and expand_type='uvall'
    and uv_query is null
     ORDER BY INQUIRY_ID ASC;
    set term on
    prompt &total
    exit
3. Define cursor and use cursor:
    DECLARE
    CURSOR cur IS
    SELECT inquiry_id
      from inquire_all_current a
     WHERE FORM_COMPLETE


Ïà¹ØÎĵµ£º

SQL Server 2000 Java¿ª·¢Öе±Ç°Èͮ򵀫¦Àí

1¡¢ÔÚÊý¾Ý¿â½¨±íµÄʱºò×Ö¶ÎÖ±½ÓÉèÖÃΪDATETIMEÀàÐÍ£»
2¡¢Ö´ÐвåÈëµÄʱºòʹÓÃÈçÏÂÓï¾ä£º
PreparedStatement pstmt = conn.prepareStatement("insert into guestbook(gst_user,gst_title,gst_content,gst_ip,gst_time) values(?,?,?,?,getdate())");
3¡¢Òª°ÑÈÕÆÚ´ÓÊý¾Ý¿âÖÐÈ¡³ö£¬Ö´ÐÐÈçÏÂÓï¾ä£º
     ......

»ñÈ¡¾ÖÓòÍøSQL ServerʵÀýÐÅÏ¢

ÓÉÓÚ´úÂë¹ýÓÚ¼òµ¥£¬Ö±½ÓÌù³ö£º
SqlDataSourceEnumerator SseInstance = SqlDataSourceEnumerator.Instance;
//ServerName:·þÎñÆ÷Ãû;
//InstanceName:ʵÀýÃû;
//IsClustered:ÊÇ·ñΪȺ¼¯·þÎñÆ÷µÄÒ»²¿·Ö;
//Version:°æ±¾,8.*ÊÇSQL 2000,9.*ÊÇSQL 2005
DataTable DtSqlInstance = SseInstance.GetDataSources();

×¢£º ......

¸ß¼¶SQL 2

1 ÓÃUNIONÌæ»»OR (ÊÊÓÃÓÚË÷ÒýÁÐ)
ͨ³£Çé¿öÏÂ, ÓÃUNIONÌæ»»WHERE×Ó¾äÖеÄOR½«»áÆðµ½½ÏºÃµÄЧ¹û. ¶ÔË÷ÒýÁÐʹÓÃOR½«Ôì³ÉÈ«±íɨÃè. ×¢Òâ, ÒÔÉϹæÔòÖ»Õë¶Ô¶à¸öË÷ÒýÁÐÓÐЧ.
Èç¹ûÓÐcolumnûÓб»Ë÷Òý, ²éѯЧÂÊ¿ÉÄÜ»áÒòΪÄãûÓÐÑ¡ÔñOR¶ø½µµÍ. ÔÚÏÂÃæµÄÀý×ÓÖÐ, LOC_ID ºÍREGIONÉ϶¼½¨ÓÐË÷Òý.
¸ßЧ: SELECT LOC_ID , LOC_DESC , ......

sqlÓï¾ä

select *from student
select student_id from student
select student_id ,student_name from student
select student_id student_name from student  ½«student_name ×÷Ϊstudent_idµÄ±ðÃû´¦Àí
È磺   select student_id a from student
 select a=student_id from student
´Óstudent±íÖзֱð¼ ......

SQLµÄÈýÖÖ·ÖÒ³·½·¨

±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º 
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
  (SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
  from ±íÃû
  ORDER BY ID))
  ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ