<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    莊周夢蝶

    生活、程序、未來
       :: 首頁 ::  ::  :: 聚合  :: 管理

    安裝了JProfiler和LoadRunner,最近對軟件性能測試產生很大興趣,發現一個很棒的blog,做下推薦:

    Jackei 的測試生活與人文社會讀本

    posted @ 2007-02-12 09:19 dennis 閱讀(353) | 評論 (1)編輯 收藏

    The Java automatic garbage collection process typically operates as a low-priority thread that constantly searches memory for unreachable objects, objects not referenced by any other object reachable by a live thread. Different JVMs use different algorithms to determine how to collect garbage most efficiently.

    In the JVM, memory is allocated in two regions:

    • Stack: Where local variables (declared in methods and constructors) are allocated. Local variables are allocated when a method is invoked and de-allocated when the method is exited.
    • Heap: Where all objects created with the new keyword are allocated. Since local variables are few in number, only primitive types and references, usually the stack will not overflow, except in cases of unusually deep or infinite recursion. The JVM throws a Java out-of-memory error if it is not able to get more memory in the heap to allocate more Java objects. The JVM cannot allocate more objects if the heap is full of live objects and unable to expand further.

    Causes of memory leaks in Java
    The four typical causes of memory leaks in a Java program are:

    1. Unknown or unwanted object references: These objects are no longer needed, but the garbage collector can not reclaim the memory because another object still refers to it. Long-living (static) objects: These objects stay in the memory for the application's full lifetime. Objects tagged to the session may also have the same lifetime as the session, which is created per user and remains until the user logs out of the application. Failure to clean up or free native system resources: Native system resources are resources allocated by a function external to Java, typically native code written in C or C++. Java Native Interface (JNI) APIs are used to embed native libraries/code into Java code.
    2. Bugs in the JDK or third-party libraries: Bugs in various versions of the JDK or in the Abstract Window Toolkit and Swing packages can cause memory leaks.

    Detection of memory leaks
    Some approaches to detecting memory leaks follow in the list below:

    1. Use the operating system process monitor, which tells how much memory a process is using. On Microsoft Windows, the task manager can show memory usage when a Java program is running. This mechanism gives a coarse-grained idea of a process's total memory utilization. Use the totalMemory() and freeMemory() methods in the Java runtime class, which shows how much total heap memory is being controlled by the JVM, along with how much is not in use at a particular time. This mechanism can provide the heap's memory utilization. However, details of the heap utilization indicating the objects in the heap are not shown.
    2. Use memory-profiling tools, e.g., JProbe Memory Debugger. These tools can provide a runtime picture of the heap and its utilization. However, they can be used only during development, not deployment, as they slow application performance considerably.

    Causes of memory leaks in enterprise Java applications
    In the subsequent sections, I analyze some causes of memory leaks in enterprise Java applications using a sample application and a memory profiling tool. I also suggest strategies for detecting and plugging such leaks in your own projects. ResultSet and Statement Objects
    The Statement and ResultSet interfaces are used with Java Database Connectivity (JDBC) APIs. Statement/PreparedStatment objects are used for executing a SQL Statement; ResultSet objects are used for storing SQL queries' results. A Java Enterprise Edition (Java EE) application usually connects to the database by either making a direct connection to the database using JDBC thin drivers provided by the database vendor or creating a pool of database connections within the Java EE container using the JDBC drivers. If the application directly connects to the database, then on calling the close() method on the connection object, the database connection closes and the associated Statement and ResultSet objects close and are garbage collected. If a connection pool is used, a request to the database is made using one of the existing connections in the pool. In this case, on calling close() on the connection object, the database connection returns to the pool. So merely closing the connection does not automatically close the ResultSet and Statement objects. As a result, ResultSet and Statement will not become eligible for garbage collection, as they continue to remain tagged with the database connection in the connection pool. To investigate the memory leak caused by not closing Statement and ResultSet objects while using a connection pool, I used a sample Java EE application that queries a database table and displays the results in a JSP (JavaServer Pages) page. It also allows you to save records to the database table. The application is deployed in iPlanet App Server 6.0. I used JProbe to analyze the memory utilization by the application. The sample application uses a database table with the following structure:

    ID?? NUMBER
    NAME?? VARCHAR2(300)
    STREET?? VARCHAR(500)
    CITY?? VARCHAR(500)
    STATE?? VARCHAR(200)
    CREATEDON?? DATE
    VERSIONNO?? NUMBER
    DELETESTATUS?? NUMBER
    UPDATEDBY?? VARCHAR(20)
    UPDATEDON?? DATE

    First, I executed the application with the Statement and ResultSet objects closed. Subsequently, I executed the application by not closing the Statement and ResultSet objects. I did a query operation 50 times and observed the memory usage pattern. Scenario 1
    The database table contains 100 rows and 10 columns. ResultSet and Statement objects are closed. The database connection is made using a connection pool. The memory usage results of this scenario are shown in Figure 1.


    Figure 1. When queried once, the heap memory usage increases by 166.308 KB. Click on thumbnail to view full-sized image.

    Figure 1 is a heap usage chart provided by JProbe. It gives a runtime summary of the heap memory in use over time as the Java EE application runs. The green area indicates the heap usage. The vertical line indicates a heap usage checkpoint has been set at that time. After setting the checkpoint, the query occurs and the heap memory usage shoots up as objects are created. Once the operation completes, the objects no longer referenced will be garbage collected by the JVM, so the memory usage decreases. Ideally at this time, all new objects should be released and garbage collected, and the heap usage should return to the value before the checkpoint was set. In this case, some new objects continue to occupy memory space, reflecting an increase in heap usage by 166.308 KB. When queried 10 times, the heap memory usage increases by 175.512 KB, as illustrated in Figure 2.


    Figure 2. Ten queries. Click on thumbnail to view full-sized image.

    When queried 50 times, the heap memory usage increases by 194.128 KB, as shown in Figure 3.


    Figure 3. Fifty queries. Click on thumbnail to view full-sized image.

    The observed increase in memory was traced to the connection objects stored in the pool for subsequent reuse. Scenario 2
    The database table contains 100 rows and 10 columns. ResultSet and Statement objects are not closed. The database connection is made using a connection pool. When queried once, the heap memory usage increases by 187.356 KB, as shown in Figure 4.


    Figure 4. Results from one query. Click on thumbnail to view full-sized image.

    When queried 10 times, the heap memory usage increases by 217.016 KB.


    Figure 5. Ten queries. Click on thumbnail to view full-sized image.

    When queried 50 times, the heap memory usage increases by 425.404 KB


    Figure 6. Fifty queries. Click on thumbnail to view full-sized image.

    The difference in memory usage after 50 queries with open ResultSet and Statement objects is 231.276 KB. These results show that over time, these objects will cause a huge memory leak, thereby generating an OutOfMemoryError. In addition to the heap usage chart, JProbe also provides a runtime view of class instances in the heap. From the class instance summary, we can identify the objects present in the heap at any point in time. Figure 7 shows a part of the class instance view of Scenario 2.


    Figure 7. Class instance summary. Click on thumbnail to view full-sized image.

    Figure 7 clearly shows that 50 objects of OracleStatement, 500 objects of DBColumn, etc., exist in the heap and are not garbage collected. JProbe provides a reference/referrer tree for each class instance in the table, shown in Figure 8. From this tree we can identify how each class instance was created.


    Figure 8. Referrer tree for the DBColumn object

    From the referrer tree of DBColumn, we can see that it is created by the OracleStatement object. The class oracle.jdbc.driver.OracleStatement is the implementation for the Statement interface. So by closing the Statement object, all associated DBColumn objects will be garbage collected. Recommendation
    When using connection pools, and when calling close() on the connection object, the connection returns to the pool for reuse. It doesn't actually close the connection. Thus, the associated Statement and ResultSet objects remain in the memory. Hence, JDBC Statement and ResultSet objects must be explicitly closed in a finally block. Collection objects
    A collection is an object that organizes references to other objects. The collection itself has a long lifetime, but the elements in the collection do not. Hence, a memory leak will result if items are not removed from the collection when they are no longer needed. Java provides the Collection interface and implementation classes of this interface such as ArrayList and Vector. Using the same Java EE application tested in the previous scenario, I added the database query results to an ArrayList. When 35,000 rows were present in the database table, the application server threw a java.lang.OutOfMemoryError, with a default JVM heap size of 64 MB.


    Figure 9. Heap summary when JVM throws java.lang.OutOfMemoryError. Click on thumbnail to view full-sized image.

    A collection with no policy for removing data causes a memory leak, known as the Leak Collection anti-pattern (read J2EE Design Patterns, for more information on anti-patterns). Recommendation
    When collections are used, the object references stored in the collections should be programmatically cleaned to ensure that the collection size does not grow indefinitely. If the collection is being used to store a large table's query results, data access should be completed in batches. Static variables and classes
    In Java, usually a class member (variable or method) is accessed in conjunction with an object of its class. In the case of static variables and methods, it is possible to use a class member without creating an instance of its class. A class with static members is known as a static class. In such cases, before a class instance is created, an object of its class will also be created by the JVM. The class object is allocated to the heap itself. The primordial class loader will load the class object. In the case of static classes, all the static members will also be instantiated along with the class object. Once the variable is initialized with data (typically an object), the variable remains in memory as long as the class that defines it stays in memory. If the primordial class loader loads class instances, they will stay in memory for the duration of the program and are not eligible for garbage collection. So static classes and associated static variables will never be garbage collected. Thus, using too many static variables leads to memory leaks. Recommendation
    Usage of static classes should be minimized as they stay in memory for the lifetime of the application. The Singleton pattern
    The Singleton pattern is an object-oriented design pattern used to ensure that a class has only one instance and provide a global point of access to that instance. The Singleton pattern can be implemented by doing the following:

    • Implementing a static method that returns an instance of the class Making the constructor private so a class instance can be created only through the static method
    • Using a static variable to store the class instance

    Example code for the Singleton pattern follows:

    public class Singleton {
    ?? private static Singleton singleton=null;
    ?? private singleton () {
    ?? }
    ?? public static Singleton getInstace() {
    ?????? if (singleton != null)
    ?????? singleton=new Singleton();
    ?????? return singleton;
    ?? }
    }

    The Singleton class is typically used as a factory to create objects. I cached these objects into an ArrayList to enable their speedy retrieval. When a new object must be created, it will be retrieved from the cache if it is present there, otherwise, a new object will be created.


    Figure 10. Singleton class diagram. Click on thumbnail to view full-sized image.

    Once the Singleton class is instantiated, it remains in memory for the application's lifetime. The other objects will also have a live reference to it and, as a result, will never be garbage collected. Recommendation
    Avoid referencing objects from long-lasting objects. If such usage cannot be avoided, use a weak reference, a type of object reference that does not prevent the object from being garbage collected. HttpSession vs. HttpRequest
    HTTP is a request-response-based stateless protocol. If a client wants to send information to the server, it can be stored in an HttpRequest object. But that HttpRequest object will be available only for a single transaction. The HTTP server has no way to determine whether a series of requests came from the same client or from different clients. The HttpSession object is generally used to store data required from the time a user logs into the system until he logs out. It brings statefulness into a transaction. The session can be used for storing information such as a user's security permissions. But often, programmers mistakenly store complex long-living data, such as a shopping cart, into the session, instead of using the business tier. I experimented with the sample application to find the difference in memory usage between the HttpSession and HttpRequest objects since data stored in HttpSession will stay in memory until the user logs out of the application. I added the database table's query results to an ArrayList, which I then placed into both the HttpSession and HttpRequest objects. Memory usage was observed for 50 query-and-save operations. Scenario 1
    The database table contains 100 rows. The output ArrayList is stored in the HttpRequest object to be passed back to the JSP page. After performing one query-and-save operation, the increase in memory usage is 166.308 KB.


    Figure 11. Results for one query-and-save operation. Click on thumbnail to view full-sized image.

    After completing 10 query-and-save operations, the increase in memory usage is 175.512 KB.


    Figure 12. Ten operations. Click on thumbnail to view full-sized image.

    After performing 50 query-and-save operations, the increase in memory usage is 194.128 KB.


    Figure 13. Fifty query-and-save operations. Click on thumbnail to view full-sized image.

    Scenario 2
    The database table contains 100 rows. The output ArrayList is stored in the HttpSession object to be passed back to the JSP page. After one query-and-save operation, the increase in memory usage is 176.708 KB.


    Figure 14. One query-and-save operation. Click on thumbnail to view full-sized image.

    After 10 query-and-save operations, the increase in memory usage is 178.46 KB.


    Figure 15. Ten operations. Click on thumbnail to view full-sized image.

    After 50 query-and-save operations, the increase in memory usage is 216.552 KB.


    Figure 16. Fifty operations. Click on thumbnail to view full-sized image.

    When the data is stored in HttpSession, instead of HttpRequest, 50 save-and-query operations increase memory usage by 22.424 KB. This happens on a per client basis. Hence for multiple clients, the multiplicative factor comes in as well. Over a period of time, this will definitely lead to a significant memory leak in the application. The data stored in HttpSession stays in memory as long as the user is logged in. Putting too much data into HttpSession leads to the Overstuffed Session anti-pattern. Since HttpSession is implemented as a collection, this overstuffed session can be considered a variant of the Leak Collection anti-pattern. Recommendation

    1. Use of HttpSessions should be minimized and used only for state that cannot realistically be kept on the request object Remove objects from HttpSession if they are no longer used
    2. Long-living data should be migrated to the business tier

    Conclusion
    I have highlighted some of the important programming scenarios where the JVM's garbage collection mechanism becomes ineffective. These situations necessitate appropriate precautions during design of the application itself. While closing ResultSets and Statements can be done after application development with comparatively low costs, other aspects that I have explained get deeply embedded in the design and could prove costly to correct. The garbage collector is a low-priority thread. Hence in a heavily loaded Java EE application, garbage collection itself may happen infrequently. Even those objects that could have been potentially garbage collected may actually stay in memory for a long time. So explicitly cleaning the heap may be a mandatory programming requirement in some applications; doing so must be considered on a case-by-case basis.



    Join the discussion about this articleClick Here To Add Your Comment
    ..How to migrate? Anonymous?? 03/13/06 06:44 AM
    ..Plug memory leaks in enterprise Java applications JavaWorldAdministrator?? 03/12/06 07:46 PM

    Printer-friendly versionPrinter-friendly version | Send this article to a friendMail this to a friendAbout the author
    Ambily Pankajakshan works as a scientist in the Centre for Artificial Intelligence and Robotics. She has more than five years of experience in the design and development of multitiered Java EE applications. Her areas of interests are performance-tuning Java EE applications and application servers. She holds a B.Tech Degree in computer science and engineering from M.G. University, Kerala India. Currently, she lives in Bangalore with her husband Nishore and son Ananthu.

    posted @ 2007-02-12 08:49 dennis 閱讀(412) | 評論 (0)編輯 收藏

    場景:常見的領導關系樹結構,知道某一節點ID,要查出此節點的所有下級(直接下級和間接下級),此時需要使用SQL遞歸語句。

    oracle中的遞歸語句:?
    start???with?????connect???by???prior??.


    例子:

    ?pid? id
    ? a ? b ?
    ? a ? c ? ?
    ? a ? e ?
    ? b ? b1 ?
    ? b ? b2 ?
    ? c ? c1 ?
    ? e ? e1 ?
    ? e ? e3 ?
    ? d ? d1 ?
    ? ?
    ? 指定pid=a,選出 ?
    ? a ? b ?
    ? a ? c ? ?
    ? a ? e ?
    ? b ? b1 ?
    ? b ? b2 ?
    ? c ? c1 ?
    ? e ? e1 ?
    ? e ? e3?
    SQL語句:
    ??select???parent,child???from???test???start???with???pid='a'???
    ??connect???
    by???prior???id=pid?

    posted @ 2007-02-11 20:02 dennis 閱讀(1563) | 評論 (1)編輯 收藏

    為了合并多個select語句的查詢結果,可以使用集合操作符UNION,UNION ALL,INTERSECT和MINUS.語法如下:

    ?

    SELECT語句1? [ UNION?|?UNION?ALL?|?INTERSECT?|?MINUS ] ?SELECT語句2

    ?

    使用集合操作符有以下一些限制:

    .對于LOB,VARRAY,嵌套表類來說,集合操作符無效

    .對于LONG型,UNION ALL,INTERSECT和MINUS無效

    .如果選擇列表包含了表達式,必須指定別名

    1。UNION,用于獲取兩個結果集的并集,會自動去掉結果集中的重復行,并會以第一列的結果進行排序,例:

    ?

    select ? * ? from ?employee? union ? select ? * ? from ?employee;

    ?

    2。UNION ALL,與UNION相似,不同的是UNION ALL不會自動去處重復行,也不會以任何列排序

    ?

    ?? select ? * ? from ?employee? union ? all ? select ? * ? from ?employee;

    ?

    3。INTERSECT,用于獲取兩個結果集的交集,并且以第一列排序,如:

    ?

    select ? * ? from ?employee? intersect ? select ? * ? from ?employee? where ?id = ' 1 ' ;

    ?

    4。MINUS,用于獲取結果集的差集(或者說補集),顯示第一個結果集存在的,第2個結果集不存在的數據:

    ?

    select ? * ? from ?employee?minus? select ? * ? from ?employee? where ?id = ' 1 ' ;

    ?

    注意事項:

    1。兩個選擇列表必須完全一致

    2。可以連續使用集合操作符,這些操作符擁有相同優先級,多個操作符存在時,從左向右執行,如:

    ?

    SQL > ?? select ? * ? from ?employee?minus? select ? * ? from ?employee? where ?id = ' 1 ' ? union ? select ? * ? from ?employee? where ?id = ' 1 ' ;

    ????????ID?NAME???????????SALARY?EMAIL
    -- --------?----------?----------?------------------------------
    ????????? 1 ?love????????????? 3100 ?fasda
    ?????????
    2 ?love????????????? 4100 ?killme2008 @gmail


    ?

    posted @ 2007-02-11 20:01 dennis 閱讀(4735) | 評論 (0)編輯 收藏

    編寫控制結構:順序結構,條件結構,循環結構

    一。條件語句:

    1。與delphi或者java,或者其他任何語言的條件語句基本一樣咯:

    單條件:

    IF condition THEN

    ? ......

    END IF;

    雙條件:

    IF condition THEN

    ? ......

    ELSE

    ? ...

    END IF;

    多條件:

    IF condition THEN

    ?? ...

    ELSEIF conditon THEN

    ?? ....

    ELSEIF conditon THEN

    ?? ....

    ELSE

    ?? ....

    END IF;

    ?

    舉例:

    DECLARE
    ??v_sal?
    NUMBER ( 6 , 2 );
    ??v_id?
    NUMBER ;
    BEGIN
    ??v_id:
    = ' &id ' ;
    ??
    SELECT ?salary? into ?v_sal? FROM ?employee
    ??
    WHERE ?id = v_id;
    ??
    IF ?v_sal < 4000 ? THEN
    ?????
    update ?employee? set ?salary = (v_sal + 100 )? where ?id = v_id;
    ??
    ELSE
    ?????
    update ?employee? set ?salary = (v_sal - 900 )? where ?id = v_id;
    ??
    END ? IF ;
    END ;


    二。CASE語句:

    各種語言的switch ...case語句相同,只不過沒有switch關鍵字。

    1。使用單一選擇符進行比較:

    ?

    CASE ?selector

    ??
    WHEN ?expression1? THEN ?;

    ??
    WHEN ?expression? 2 ? THEN ?;

    ??

    ?

    END CASE;

    2。多種條件比較:

    ?

    CASE

    ??
    WHEN ?condition1? THEN ?;

    ??
    WHEN ?condition2? THEN ?;

    ??
    WHEN ?condition3? THEN ?;

    END CASE;
    ??

    ?

    ?

    (三)循環語句:

    3種循環:

    1。基本循環,至少執行一次:

    LOOP

    ?? statement1;

    ???...

    ?? EXIT WHEN 退出循環條件;

    END LOOP1;

    例如:

    ?

    LOOP

    ??
    insert ? into ?employee(id,name)? values (i, ' dennis ' );

    ??i:
    = i + 1 ;

    ??
    EXIT ? WHEN ?i > 10 ;

    END ?LOOP;

    ?

    2。while循環:

    WHILE conditon1 LOOP

    ?? statement1;

    ?? ...

    END LOOP;

    比如上面的例子改寫為:

    ?

    WHILE ?i <= 10 ?LOOP

    ???
    insert ? into ?employee(id,name)? values (i, ' dennis ' );?

    ???i:
    = i + 1 ;

    END ?LOOP;

    ?

    3。FOR循環,類似于ruby的FOR循環:

    FOR counter IN [REVERSE] 下限..上限 LOOP

    ? statement1;

    ??...

    END LOOP;

    REVERSE參數可選,有的話表示從上限往下限遞減。

    ?

    (四)順序控制語句

    PL/SQL也提供了GOTO和NULL語句用于控制語句執行順序,GOTO語句與JAVA的機制相似,通過label來實現跳轉,盡量不要使用。NULL語句不會執行任何操作,它的存在主要是為了提高程序的可讀性。?

    posted @ 2007-02-11 19:59 dennis 閱讀(919) | 評論 (0)編輯 收藏

    這部分主要講述在PL/SQL中如何訪問oracle

    (一)檢索單行數據

    在PL/SQL嵌入select語句,使用方法:

    SELECT select_list INTO variable_name1,variable_name2,variable_name3... FROM TABLE WHRE condition

    例:

    ?

    DECLARE
    ??v_sal?
    NUMBER ( 6 , 2 );
    ??v_id?
    NUMBER ;
    BEGIN
    ??v_id:
    = ' &id ' ;
    ??
    SELECT ?salary? into ?v_sal? FROM ?employee
    ??
    WHERE ?id = v_id;

    ??.

    ?

    需要注意事項:

    1.不僅可以使用普通標量接受數據,也可以使用Record接受數據

    2.使用的SELECT語句必須返回一條記錄,并且只能返回一條記錄,否則會觸發PL/SQL異常,或者顯示錯誤信息

    (1)NO_DATA_FOUND異常,當沒有數據返回時拋出此異常

    (2)TOO_MANY_ROWS異常,返回多條數據時拋出此異常

    (3)在WHERE子句中使用變量時必須注意,變量名不能字段名相同,否則會觸發TOO_MANY_ROWS異常

    (二)使用DML語句

    1.在PL/SQL中使用DML語句(insert,update,delete)語句與直接在sqlplus使用沒什么不同,只不過可以使用變量代替具體的值,不再舉例

    2.SQL游標:

    (1)在執行SELECT,INSERT,UPDATE,DELETE語句時,Oracle Server會為這些SQL語句分配相應的Context Area,并且Oracle使用此Context Area解析并執行相應的SQL語句,而游標就是指向Context Area的指針。在oracle中,游標又分為顯式和隱式兩種,其中隱式游標又被成為SQL游標,專門用于處理SELECT INTO,INSERT,DELETE,UPDATE語句。而顯式游標多用于處理多行的SELECT語句。

    (2)SQL游標的四種屬性:

    (A) SQL%ISOPEN

    用于確定SQL游標是否打開。因為oracle在執行SELECT INTO,INSERT,UPDATE,DELETE語句時會隱含著打開游標,并在執行結束后關閉。所以此值對于開發人員來說一直是false,實際開發中不需要此屬性

    (B) SQL%FOUND

    用于確定SQL語句是否執行成功,當SQL語句有作用行時,它為TRUE,否則為FALSE,如:

    DECLARE

    ??v_dept?emp.deptno
    % TYPE;

    BEGIN

    ??
    UPDATE ?emp? SET ?sal = sal * 1.1 ? WHERE ?deptno =& no;

    ??
    if ?SQL % FOUND? then

    ??????dbms_output.put_line(
    ' 語句執行成功 ' );

    ???
    else

    ??????dbms_output.put_line(
    ' 語句執行失敗 ' );

    ???
    end ? if ;

    END ;


    (C)與SQL%FOUND相反的就是SQL%NOTFOUND

    (D)SQL%ROWCOUNT ,用于返回SQL語句作用的行數

    (三)事務控制語句

    在PL/SQL中同樣可以使用COMMIT,ROLLBACK,SAVEPOINT等事務控制語句,與直接使用沒有什么不同。

    posted @ 2007-02-11 19:57 dennis 閱讀(1060) | 評論 (0)編輯 收藏

    養成良好的代碼編寫習慣,PL/SQL代碼編寫規則:

    1.標識符命名規則

    1)定義變量時以v_作為前綴,如v_sal

    2)定義常量時以c_作為前綴,如c_rate

    3)定義游標時,建議使用_cursor作為后綴,如emp_cursor

    4)定義異常時,以e_作為前綴,如e_integrity_error

    5)定義PL/SQL表類型時,使用_table_type作為后綴,如sal_table_type.

    6)定義表變量時,以_table作為后綴,如sal_table

    7)同樣,定義PL/SQL記錄型時以_record_type作為后綴

    8)定義PL/SQL記錄變量時以_record作為后綴

    ?

    2.大小寫規則:

    1)SQL關鍵字采用大寫,比如SELECT,DELETE等

    2)PL/SQL關鍵字也用大寫,比如DECLARE,BEGIN,END等

    3)數據類型也采用大寫,比如VARCHA2

    4)標識符和參數采用小寫,如v_sal

    5)數據庫對象和列以及別名也才用小寫

    3.合適的代碼縮進風格

    posted @ 2007-02-11 19:56 dennis 閱讀(1142) | 評論 (0)編輯 收藏

    定義并使用變量

    PL/SQL有四種類型:標量類型,復合類型,引用類型(reference),LOB(Large Obejct)類型

    一.標量類型

    最常用的就是標量類型,是指只能存放單個數值的變量,包括數字類型、字符類型、日期類型和布爾類型,每種類型又包含相應的子類型。

    常量標量類型如下:

    ?

    VARCHAR2 (n)?, CHAR (n), NUMBER (p,s),DATE, TIMESTAMP , LONG , LONG ? RAW ,BOOLEAN,BINARY_INTEGER(僅PL / SQL使用),BINARY_FLOAT和BINARY_DOUBLE(10g新引入的)

    ?

    定義標量:

    identifier [CONSTANT] datatype [NOT NULL] [:=| DEFAULT expr]

    使用標量需要注意的是=號被:=取代,與delphi一樣的賦值符號@_@

    例子:

    ?

    v_name? VARCHAR2 ( 10 );

    v_rate?CONSTANTS?
    NUMBER ( 4 , 2 )?: = 3.04 ;

    ?

    為了防止定義的變量類型與表中的字段類型不一致,可以使用%TYPE來定義:

    ?

    v_name?employee.name % TYPE;

    ?

    如上面所示,v_name的類型就與表employee中的name字段類型一樣!!

    二。復合變量:

    用于存放多個值的變量稱為復合變量,包括PL/SQL記錄,PL/SQL表,嵌套表和VARRAY四種類型

    1。PL/SQL記錄

    類似于C/C++中的結構概念:

    ?

    declare
    ???TYPE?employee_record?
    is ?RECORD(
    ?????id?employee.id
    % TYPE,
    ?????name?employee.name
    % TYPE,
    ?????email?employee.email
    % TYPE);
    ???em_record?employee_record;
    begin
    ???
    select ?id,name,email? into ?em_record? from ?employee? where ?name =& name;
    ???dbms_output.put_line(
    ' 雇員名: ' || em_record.name || ' ?雇員ID: ' || em_record.id);
    end ;?

    ?

    2。PL/SQL表,類似于數組概念,不同的是PL/SQL表允許負值下標,而且沒有上下限,如:

    ?

    declare
    ???TYPE?employee_table?
    is ? table ? of ?employee.name % TYPE? index ? by ?BINaRY_INTEGER;
    ???em_table?employee_table;
    begin
    ???
    select ?name? into ?em_table( - 1 )? from ?employee? where ?name =& name;
    ???dbms_output.put_line(
    ' 雇員名: ' || em_table( - 1 ));
    end ;?

    ?

    3。嵌套表,與PL/SQL表相似,不同的是嵌套表可以做表列的數據類型,而PL/SQL表不能,使用嵌套表作為表列時,必須為其指定專門的存儲表,如:

    ?

    create ? or ? replace ?TYPE?emp_type?? as ?OBJECT(
    ??name?
    VARCHAR2 ( 10 ),salary? NUMBER ( 6 , 2 ),hiredate?DATE);

    CREATE ? OR ? REPLACE ?TYPE?emp_array? IS ? TABLE ? OF ?emp_type;

    ?
    CREATE ? TABLE ?department(
    ????deptno?
    NUMBER ( 2 ),dname? VARCHAR2 ( 10 ),
    ????employee?emp_array)NESTED?
    TABLE ?employee?STORE? as ?employee_dept;

    ?

    4。VARRAY(變長數組),與嵌套表相似,也可以做為表列的數據類型,但是嵌套表沒有個數限制,而VARRAY有個數限制,如:

    CREATE TYPE TEST_ARRAY IS VARRAY(20) OF emp_type;

    三。引用變量(reference)

    類似于C++中的指針或者JAVA中引用的概念,用于存放數值指針的變量,使用此變量,可以使得應用程序共享相同對象,降低占用空間。此類有兩種類型:游標(REF CURSOR)和對象類型(REF OBJECT)

    1。REF CURSOR,定義時同時指定SELECT語句的游標稱為顯式或者靜態游標,在打開時才指定SELECT語句的游標稱為動態游標,如:

    ?

    ? DECLARE ?
    ?????TYPE?c1?
    IS ?REF? CURSOR ;
    ?????emp_cursor?c1;
    ?????v_name?employee.name
    % TYPE;
    ?????v_sal?employee.salary
    % TYPE;
    ????
    begin
    ?????
    open ?emp_cursor? for
    ???????
    SELECT ?name,salary? FROM ?EMPLOYEE?;
    ?????LOOP
    ???????
    FETCH ?emp_cursor? INTO ?v_name,v_sal;
    ???????
    EXIT ? WHEN ?emp_cursor % NOTFOUND;
    ???????dbms_output.put_line(v_name);
    ?????
    END ?LOOP;
    ?????
    close ?emp_cursor;
    ????
    end ;??

    ??

    2。REF OBJECT,與JAVA的引用概念相同,存儲的是指向對象的指針

    ?

    四。LOB類型

    LOB類型是指用于存儲大批量數據的變量,包括內部的3種(CLOB,BLOB,NCLOB)和外部LOB(BFILE)。

    CLOB,NCLOB用于存儲大量的字符數據。

    BLOB用于存儲大批量二進制數據(如圖象)。

    BFILE則存儲指向OS文件的指針。

    posted @ 2007-02-11 19:55 dennis 閱讀(1606) | 評論 (0)編輯 收藏

    斷斷續續學過,這次系統地來讀讀。

    (一)

    PL/SQL是oracle在標準SQL語言上的過程性擴展,它不僅允許嵌入SQL語句,而且允許定義變量和常量,允許過程語言結構(條件語句和循環語句),允許使用異常處理oracle錯誤等。通過使用PL/SQL,可以在一個PL/SQL塊中包含多條SQL語句和PL/SQL語句。PL/SQL具有以下優點:

    1。提高應用系統的運行性能。

    通過使用PL/SQL塊包含多條SQL語句,從而減少了SQL語句的發送次數,降低了網絡開銷,提高了應用程序性能

    2。提供模塊化的程序設計功能。

    將企業規則和商業邏輯集成到PL/SQL子程序當中,提供給客戶端調用,簡化了客戶端應用程序的開發和維護工作,降低了耦合度

    3。允許定義標識符,允許定義變量、常量、游標和異常等標識符

    4。具有過程語言控制結構,允許使用條件分支語句和循環語句

    5。具有良好的兼容性,在允許運行oracle的任何平臺上執行,不僅在數據庫服務端執行,也可以在oracle提供的工具中開發PL/SQL

    6。處理運行錯誤,使用PL/SQL提供的EXCEPTION,開發人員可以集中處理各種oracle錯誤和PL/SQL錯誤,簡化了錯誤處理。

    (二)

    PL/SQL塊的概念:

    Block是PL/SQL的基本單元,編寫PL/SQL本質上就是編寫PL/SQL塊,塊的嵌套層次沒有限制。

    1。塊的結構:塊由3部分組成

    ?

    DECLARE ?

    ??定義部分——定義常量,變量,游標,異常,復雜數據類型等

    BEGIN

    ??執行部分

    EXCEPTION

    ??異常處理部分

    END ;? /* 塊結束標志 */

    ?

    其中只有執行部分是必須的!

    2。分類:

    1)匿名塊,匿名塊是指沒有名稱的PL/SQL塊,可以內嵌到應用程序中,如:

    declare
    ????v_name?
    VARCHAR2 ( 10 );

    BEGIN
    ????
    select ?name? into ?v_name? from ?test? where ?name =& no;
    ????dbms_output.put_line(
    ' 名字: ' || v_name);
    ????EXCEPTION
    ????
    WHEN ?NO_DATA_FOUND? THEN
    ????dbms_output.put_line(
    ' 找不到該名字 ' );
    end ;


    2)命名塊:與匿名塊相似,使用命名塊主要是為了區分多級嵌套關系,如:

    << out >>

    declare
    ????v_name?
    VARCHAR2 ( 10 );

    BEGIN

    ????
    << inner >>

    ????
    begin

    ???????????
    null ;

    ????
    end ;

    ????
    -- <<inner>>

    ????
    ????
    select ?name? into ?v_name? from ?test? where ?name =& no;
    ????dbms_output.put_line(
    ' 名字: ' || v_name);
    ????EXCEPTION
    ????
    WHEN ?NO_DATA_FOUND? THEN
    ????dbms_output.put_line(
    ' 找不到該名字 ' );
    end ;? -- <<outer>>


    3)子程序,又可以分成3種:過程、函數和包

    A。過程用于執行特定操作,當建立過程時既可以指定輸入參數,也可以指定輸出參數,從而在應用環境和程序間傳遞數據,使用CREATE PROCEDURE語句,如:

    ? create ? or ? replace ? PROCEDURE ?update_test(id2? NUMBER ,money2? LONG )
    ?
    is
    ????e_no_row?EXCEPTION;
    ?
    begin
    ????
    update ?test? set ? money = money2? where ?id = id2;
    ????
    if ?SQL % NOTFOUND? THEN
    ??????RAISE?e_no_row;
    ????
    end ? if ;
    ?EXCEPTION
    ????
    WHEN ??e_no_row? THEN
    ??????raise_application_error(
    - 20004 , ' 該test不存在 ' );
    ?
    end ?update_test;


    B)函數,與過程主要不同是函數需要返回數據,在函數頭和函數體都必須有RETURN語句,如:

    ?

    create ? or ? replace ? FUNCTION ?get_long(name2? VARCHAR2 )
    RETURN ? LONG ? IS
    ??avg_money?
    NUMBER ;
    begin
    ??
    select ? money ? into ?avg_money? from ?test? where ?name = name2;
    ??
    RETURN ?avg_money;
    end ;??

    ?

    C)包,類似JAVA包的概念,包含兩部分,包規范和包體,包規范相當于C++中的頭文件聲明子程序,而包體包含這些子程序的具體實現。調用包中的子程序時,需要 ‘包名.子程序名’ 這樣的形式調用

    4。觸發器是指隱含執行的存儲過程,定義觸發器需要指定觸發事件和觸發操作,常見觸發事件如insert,update,delete等,使用CREATE?TRIGGER命令建立

    posted @ 2007-02-11 19:52 dennis 閱讀(1008) | 評論 (0)編輯 收藏

    ??? 網上到處充斥這c#與java對比的文章,看了看,還是覺的MSDN上的一篇文章很全面和客觀。《針對JAVA開發人員的C#編程語言》。

    ??? 我的第一天C#學習,總體感覺C#在語言層面上保留了更多C++/C的特點,比如virtual,override關鍵字的使用,比如結構(值類型,存儲在棧上),比如delegate(類似函數指針)。比較感興趣的也就是delegate、事件和attribute方面。C#某種意義上講比java稍微復雜,比C++簡單。

    ??? 參照語言規范寫的例子,抽象類、繼承的例子,可以擴展成一個簡單計算器:
    using?System;
    using?System.Collections;

    namespace?VirtualDemo
    {
    ????
    public?abstract?class?Expression
    ????{
    ????????
    public?abstract?double?Evaluate(Hashtable?vars);
    ????????
    ????}
    ????
    public?class?Constant?:?Expression
    ????{
    ????????
    double?value;
    ????????
    public?Constant(double?value)
    ????????{
    ????????????
    this.value?=?value;
    ????????}
    ????????
    public?override?double?Evaluate(Hashtable?vars)
    ????????{
    ????????????
    return?value;
    ????????}
    ????}
    ????
    public?class?VariableReference?:?Expression
    ????{
    ????????
    string?name;
    ????????
    public?VariableReference(string?name)
    ????????{
    ????????????
    this.name?=?name;
    ????????}
    ????????
    public?override?double?Evaluate(Hashtable?vars)
    ????????{
    ????????????
    object?value?=?vars[name];
    ????????????
    if?(value?==?null)
    ????????????{
    ????????????????
    throw?new?Exception("Unknown?variable:?"?+?name);
    ????????????}
    ????????????
    return?Convert.ToDouble(value);
    ????????}
    ????}
    ????
    public?class?Operation?:?Expression
    ????{
    ????????Expression?left;
    ????????
    char?op;
    ????????Expression?right;
    ????????
    public?Operation(Expression?left,?char?op,?Expression?right)
    ????????{
    ????????????
    this.left?=?left;
    ????????????
    this.op?=?op;
    ????????????
    this.right?=?right;
    ????????}
    ????????
    public?override?double?Evaluate(Hashtable?vars)
    ????????{
    ????????????
    double?x?=?left.Evaluate(vars);
    ????????????
    double?y?=?right.Evaluate(vars);
    ????????????
    switch?(op)
    ????????????{
    ????????????????
    case?'+':?return?x?+?y;
    ????????????????
    case?'-':?return?x?-?y;
    ????????????????
    case?'*':?return?x?*?y;
    ????????????????
    case?'/':?return?x?/?y;
    ????????????}
    ????????????
    throw?new?Exception("Unknown?operator");
    ????????}
    ????}
    ????
    public?class?Test
    ????{
    ????????
    public?static?void?Main(string?[]args)
    ????????{
    ????????????Expression?e?
    =?new?Operation(
    ????????????
    new?VariableReference("x"),
    ????????????
    '*',
    ????????????
    new?Operation(
    ????????????????
    new?VariableReference("y"),
    ????????????????
    '+',
    ????????????????
    new?Constant(2)
    ????????????)
    ????????);
    ????????Hashtable?vars?
    =?new?Hashtable();
    ????????vars[
    "x"]?=?3;
    ????????vars[
    "y"]?=?5;
    ????????Console.WriteLine(e.Evaluate(vars));????????
    //?Outputs?"21"
    ????????vars["x"]?=?1.5;
    ????????vars[
    "y"]?=?9;
    ????????Console.WriteLine(e.Evaluate(vars));????????
    //?Outputs?"16.5"

    ????????}
    ????}

    }

    再來一個委托的例子,函數作為變量的傳遞和使用,對有C++經驗或者動態語言經驗的人來說不會奇怪:

    using?System;
    namespace?DelegateDemo
    {
    ????
    delegate?double?Function(double?x);
    ????
    class?Multiplier
    ????{
    ????????
    double?factor;
    ????????
    public?Multiplier(double?factor)
    ????????{
    ????????????
    this.factor?=?factor;
    ????????}
    ????????
    public?double?Multiply(double?x)
    ????????{
    ????????????
    return?x?*?factor;
    ????????}
    ????}

    ????
    class?Test
    ????{
    ????????
    static?double?Square(double?x)
    ????????{
    ????????????
    return?x?*?x;
    ????????}
    ????????
    static?double[]?Apply(double[]?a,?Function?f)
    ????????{
    ????????????
    double[]?result?=?new?double[a.Length];
    ??????????? //直接通過f()調用
    ????????????
    for?(int?i?=?0;?i?<?a.Length;?i++)?result[i]?=?f(a[i]);
    ????????????
    return?result;
    ????????}
    ????????
    static?void?Main()
    ????????{
    ????????????
    double[]?a?=?{?0.0,?0.5,?1.0?};
    ??????????? //delegate Function分別賦予了不同的方法
    ????????????
    double[]?squares?=?Apply(a,?new?Function(Square));
    ????????????
    double[]?sines?=?Apply(a,?new?Function(Math.Sin));
    ????????????Multiplier?m?
    =?new?Multiplier(2.0);
    ????????????
    double[]?doubles?=?Apply(a,?new?Function(m.Multiply));
    ????????????
    foreach?(double?b?in?doubles)
    ????????????{
    ????????????????Console.WriteLine(b);
    ????????????}
    ????????}

    ????}
    }

    posted @ 2007-02-10 17:39 dennis 閱讀(689) | 評論 (0)編輯 收藏

    僅列出標題
    共56頁: First 上一頁 48 49 50 51 52 53 54 55 56 下一頁 
    主站蜘蛛池模板: 国产精品高清免费网站| 久久久久亚洲av无码专区| 国产成人免费手机在线观看视频| 无码av免费毛片一区二区| 日本人的色道免费网站| 91免费播放人人爽人人快乐| 69式国产真人免费视频 | 亚洲成人动漫在线观看| 亚洲无线一二三四区| 亚洲人成影院在线高清| 中文字幕亚洲男人的天堂网络| 亚洲日日做天天做日日谢| 亚洲日韩AV无码一区二区三区人| 亚洲欧美国产日韩av野草社区| 亚洲爆乳精品无码一区二区| 羞羞漫画在线成人漫画阅读免费 | 亚洲一区二区影视| 亚洲无码一区二区三区| 国产亚洲精品美女| 中国毛片免费观看| 最好看最新的中文字幕免费| 97免费人妻无码视频| 日韩中文字幕免费| 国产精品亚洲产品一区二区三区| 亚洲国产精品无码久久一区二区 | 四虎永久免费观看| 在线亚洲97se亚洲综合在线| 亚洲AV永久无码精品成人| 亚洲国产精品张柏芝在线观看| 亚洲精品无码久久久久A片苍井空 亚洲精品无码久久久久YW | 99久久久国产精品免费牛牛四川| 91嫩草国产在线观看免费| 国产免费午夜a无码v视频| 国产gv天堂亚洲国产gv刚刚碰| 亚洲精品高清视频| 亚洲日本久久久午夜精品| 免费的黄色的网站| 18禁止看的免费污网站| 国产免费看插插插视频| 亚洲A∨无码无在线观看| 亚洲精品中文字幕|