庄周梦蝶

生活、程序、未来
   :: 首页 ::  ::  :: 聚合  :: 管理

安装了JProfiler和LoadRunner,最近对软件性能测试产生很大兴趣,发现一个很棒的blog,做下推荐:

Jackei 的测试生活与人文社会读本

posted @ 2007-02-12 09:19 dennis 阅读(351) | 评论 (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 阅读(410) | 评论 (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 阅读(1556) | 评论 (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 阅读(4708) | 评论 (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 阅读(911) | 评论 (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 阅读(1048) | 评论 (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 阅读(1126) | 评论 (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 阅读(1591) | 评论 (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 阅读(997) | 评论 (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.00.51.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 阅读(684) | 评论 (0)编辑 收藏

仅列出标题
共56页: First 上一页 48 49 50 51 52 53 54 55 56 下一页