实训内容

Project Setup: The DataThis project will use a case study called TRAVELER ASSISTANCE. A set of database tables is used to provide information and manage the world travelers’ requests. Information is stored about all the countries in the world and their languages, currencies, demographics, and the region in which they are located. This database will assist travelers to obtain specific information about the countries to which they wish to travel. 此项目名称为TRAVELER ASSISTANCE。

我们有一系列数据表用于支撑全球旅游者对旅游 信息的需求。

这些表中数据记录的信息包含所有国家的语言、货币、人口数据和这些国家 所在的区域。

这些数据及信息将帮助旅游者在出行旅游时获取指定国家的信息。

General Programming Guidelines: Code your SQL statements to query the data regardless of whether it is stored in upper or lower case (use the LOWER and/or UPPER functions). Include an exception handler to handle NO_DATA_FOUND and other relevant exceptions in all your procedures. 编写SQL代码来访问表中的数据，无论数据是以大写还是小写形式存储（使用 LOWER和UPPER函数） 包含异常处理部分用于处理NO_DATA_FOUND及其他在过程中出现的相关异 常。

The Assignment and Deliverables:Create the following programs: Part 1: traveler_assistance_package Part 2: traveler_admin_package 你需要提交以下部分程序： Part1：traveler_assistance_package Part2: traveler_admin_packagePart 1: Provide Basic Information to TravelersCreate a package called traveler_assistance_package that will contain the following seven procedures. Make all procedures public. Comment your procedures to explain their purpose and functionality. 创建一个以traveler_assistance_package命名的包，这个包要包含七个过程。

每个过程都需要 是公有的（PUBLIC Procedure）。

每个过程需要有合理的注释来说明它的功能及用处。

Two procedures in this package (countries_in_same_region and country_languages) return their fetched data back to the calling environment as an OUT parameter which is an associative array (ie an INDEX BY table of records). The last two procedures (print_region_array and print_language_array) will accept and display the returned arrays. 此包中有两个过程(countries_in_same_region 和 country_languages)是要使用OUT参数形式 以关联数组（即一个INDEX BY表记录）向调用环境返回他们获取到的数据。

最后两个过 程(print_region_array 和 print_language_array)接受这个OUT参数并将这些返回的值显示出来。

1. Create a procedure called country_demographics to display specific information about a country. Pass COUNTRY_NAME as an IN parameter. Display COUNTRY_NAME, LOCATION, CAPITOL, POPULATION, AIRPORTS, CLIMATE. Use a user-defined record structure for the INTO clause of your select statement. Raise an exception if the country does not exist. Hints: In order to populate the record in the select statement without specifying the record components, the record structure must be identical to the column list on the select statement. 1、创建一个叫country_demographics的过程用于显示关于一个国家的特定信息 传递COUNTRY_NAME作为IN参数。

显示COUNTRY_NAME，LOCATION, CAPITOL, POPULATION, AIRPORTS, CLIMATE。

在SELECT表达式中使用用户自 定义记录结构作为INTO子句。

在某个国家不存在时，抛出异常。

提示：为了能够在查询语句中指定记录元素，记录结构需要与表中的列结构完 全一致。

2. Create a procedure called find_region_and_currency to fetch and return the currency and region in which a country is located. 2、创建一个叫find_region_and_currency的过程用于获取和返回一个国家的所在地区 和使用的货币 Pass COUNTRY_NAME as an IN parameter and use a user-defined record as an OUT parameter that returns the country name, its region and currency. 传递COUNTRY_NAME作为一个IN参数，并使用用户自定义记录作为OUT参数 用于返回国家名称、所在区域及货币。

Hints: Declare a user-defined record TYPE in the package spec with appropriate components. Use this record type to declare record variables in your procedure. 提示： 在包的申明部分申明一个用户自定义记录TYPE， 使用这个TYPE来申明过程中的变量。

3. Create a procedure countries_in_same_region to fetch and return all the countries in the same region. 3、创建一个叫countries_in_same_region的过程用户获取和返回在同一区域国家的数 据。

Pass REGION_NAME as an IN parameter and a PLSQL associative array of records (an INDEX BY table) as an OUT parameter. Return REGION_NAME, COUNTRY_NAME, and CURRENCY_NAME through the OUT parameter for all the countries in the requested region. 传递REGION_NAME作为IN参数，并使用一个关联数组（一个INDEX BY表） 作为一个OUT参数。

使用OUT参数返回所需区域的REGION_NAME， COUNTRY_NAME, 和 CURRENCY_NAME。

Hints: The OUT parameter should be an associative array of records. Declare an associative array of records TYPE in the package spec and use this type declaration to declare the OUT parameter. The print_region_array procedure will display the contents of the array returned by the OUT parameter. 提示： OUT参数要是一个关联数组记录 在包的包体部分申明一个关联数组的TYPE，并使用这个TYPE来申明OUT参数。

下面的print_region_array过程会用于显示OUT参数所返回的数据。

4. Create a procedure print_region_array to display the content of an array of records that is passed to it. 4、创建一个叫print_region_array的用于返回传递过来的关联数组的内容。