Java程序辅导

Lab Assignment 2 on UDT and Table Function 
CIS 611         
Dr. Sunnie S. Chung 
 
Creating a User Defined Type (UDT) and Create a Table Function 
Using the UDT Data Type 
 
In a modern web application in any common Enterprise Data Analytics/Big 
data processing system, Text Data Processing usually requires two or three 
Phases of unstructured text file processing. At the end of those data 
processing phases, the extracted data need to be stored as tables in a 
database server. 
 
Most of the existing advanced SQL database systems allow users to create 
databases tables from complex structured objects such as in a CSV/JASON 
file structures using database programming features like UDT (User Defined 
Data Type), UDF (User Defined Function), or Table Function in 
programming languages.     
 
In this Lab2, you are exploring such database features by creating your 
own Data Type and Table Function for data processing in a SQL Server 
from objects like a CSV file. 
        
For this Lab2, you can use any language of your choice with any RDBMS 
Server that supports UDF, UDT, Table Function with any external 
programming language. Some suggestions would be:   
 
1. C++/C# in MS .NET Framework with MS SQL Server 
2. Java/JDBC with Oracle RDBMS Server 
Part 1: 
Although most of database servers already have a built in Geo Point Type, 
we want to study how to make such a built in data type in a database by 
implementing it your own through a simple example. 
    
1. Creating User Defined Type GeoPoint and Table Function 
Dept_GeoLocations that Uses GeoPoint type 
  
1. Create User Defined Type named PointType as follow: 
 a) Write the body of UDT PointType in your choice of a language  
               like C# or Java 
  Structure: x  // x coordinate 
         y  // y coordinate 
                                     ToString   //x:y 
  Methods: Parse an input string x:y,  
      Get each member / Set each Member, 
      Check IsNull  
Example: 
  
	


	
	


 b) Create UDT PointType to be able to be used in the following two 
              script  
 
          c) Test with the following two scripts: 
 
Example use of PointType: 
This is a possible test script that uses the UDT you created. 
Test Script1: 




 !"!# $ "

	
%&

'(%&'
)*"+,-.+
%&

'(%&'
)*"+/!-0+
%&

'(%%+!-11+%
)*
%"
%

2%

2
"
33"
  
4&#


	



5
2

6
5

)*


%&5
2

6
5
'(%+,-7+
%&5
2

6
5
'(%+/!-!+
%&5
2

6
5
'(%+/!-/!+
%&5
2

6
5
'(%+/8-/1+
%&5
2

6
5
'(%+.-/1+


///////////////////////////////////////////////////////////////////
//
//9
:;
//
///////////////////////////////////////////////////////////////////
%<4&#5



%2

6
5
3%=3="
  2

6
5
 %= ="
  2

6
5
%

2%=
=
4&#5



///////////////////////////////////////////////////////////////////
//
//	*>
:
//
///////////////////////////////////////////////////////////////////
(5
%2

6
5
3?0@A2

6
5
 BC

%2

6
5
3%=3="
  2

6
5
 %= ="
  2

6
5
%

2%=
=
4&#5


 
 
  
Part 2: 
2. Create Table Function that returns a table Dept_GeoLocations as follow: 
 
Dept_GeoLocations (Dnumber int,  
     Dlocation varchar(15),  
     Geolocation PointType) has  
a) All the data from the table Company.Dept_Locations in your 
database Company and  
b) One more additional column named GeoLocation and its type is 
PointType you created in 1). 
c) GeoLocation column has  

		
		+/8-/1++.-/1+

5;5

*
2	*

>*)*65

 

Useful Resources: 
Creating UDT function 
http://msdn.microsoft.com/en-us/library/ms131120.aspx 
 

	
	
	
http://msdn.microsoft.com/en-us/library/ms131079.aspx 
 
Other References 
http://msdn.microsoft.com/en-us/library/ms131046.aspx 
 
 
 
 
3. Lab OUTPUT for Part 1 and Part 2: 
- For the Lab Output for 1) UDT, Submit Screen Shots of each result of each 
test script given above  
- For the Lab Output for 2) Table Function, Submit Screen Shots of each 
result of  the following query: Select * From Dept_GeoLocation;