Reference no: EM131060094

Response must be 250 Words

Required APA Format

Q1. A new developer, who is new to PL/SQL, has just been hired to assist in your development group. Explain the differences between scalar, record and table variables to the new developer.

Response must be 150 Words

Required APA Format

Q2. PL/SQL includes three types of variable, including scalar, record, and table variables. All three of these serve a different purpose depending on how much data you want to store. For instance, if you want to only hold a singular value, a scalar variable should be assigned. Frequently, you only want to start something like the quantity of product number 4 in stock. A scalar variable is the most suitable for this value.
However, sometimes you may need to store multiple values of different datatypes, such as a table row. "A record can store and handle multiple values or fields, each having its own name and data type, as one unit." (Casteel, 2013). Records are appropriate for storing a single row of a database or a subset of columns from a row. Suppose you want to keep track of all of the books you own. One could use a record to grab the information about a book by a specific Title or ISBN and observe the fields of the record to gain addition information such as author, publisher, and date published.

Finally, you can think of table variables as array-types in traditional programming languages. Table variables are also known as collections and are ordered groups of elements having the same datatype. Just like an array, each element is represented by a unique subscript that represent the position in the collection (PL/SQL Collections, n.d.). These can be further subdivided into three types:

• Associative arrays - handles many rows of data, but one field

• Table of records - Similar to a record, but can hold multiple rows of data

• VARRAYS and Nested Tables - database columns that hold more than one value

References
Casteel, J. (2013). Oracle 11g: PL/SQL programming. Boston, MA: Course Technology, Cengage Learning.
PL/SQL collections.(n.d.). Retrieved from https://www.tutorialspoint.com/plsql/plsql_collections.htm

Response must be 150 Words

Required APA Format

Q3. I think you have all explained the scalar, record and table variables very well so I'll try not to repeat what has already been covered.
If a new developer started I would first out what programming languages they are already know and explain the variables using the language they are most familiar with. For example, If the developer was a C programmer I would tell them that scalars are the simple building block variables like int and char (Casteel, 2013, p.40)and that records are similar to structs in that you can group primitive variables into a logical unit. Finally, table variables or collections can be though of as an array. This could be an array of primitives such as int or could be an array of complex variables like struct's. There are a number of different types of collections in PL/SQL. I don't think there is a direct equivalent in plain old C to Associative Arrays but they work in Perl land they are called Hashes. Other collections in PL/SQL include Nested tables and VARRAY's. ("Composite Data Types",2006)

References

Casteel, J. (2013). Oracle 11g: PL/SQL Programming, 2nd Edition. [VitalSource Bookshelf Online].Retrieved on the 10th of May 2016 from https://cengagebrain.vitalsource.com/#/books/9781285531571/

Oracle Inc. (2006).Working with Composite Data Types. Retrieved on the 10th of May 2016 from https://www.macro.com.ar/PortalMacro/content/conn/macro/path/Contribution%20Folders/Revision/MacroEnLosMedios/PLLes06.pdf

Response must be 150 Words

Required APA Format

Q4. Scalar variables can hold a single value and common data types are VARCHAR2, CHAR, NUMBER, DATE, and BOOLEAN. Scalar variables allow to perform calculations and data manipulation tasks. However, composite variable can hold multiple values of scalar data type or composite data type. There are two types of composite data types:

1. PL/SQL records - when you want to store values of different data types but only one occurrence at a time.

A record is a group of related data items stored in fields, each with its own
name and data type. Each record defined can have as many fields as necessary and records can be assigned initial values and can be defined as NOT NULL. We can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, we can declare and reference nested records. One record can be the component of another record.

2. PL/SQL collections - when you want to store values of the same data type.

Collections are of three types:
- INDEX BY tables or associative arrays
- Nested table
- VARRAY

Casteel, J. (2013). Oracle 11g: PL/SQL Programming. Boston, MA: Course Technology.
Oracle Database 10g: PL/SQL Fundamentals. (n.d.). Retrieved May 11, 2016, from https://www.macro.com.ar/PortalMacro/content/conn/macro/path/Contribution Folders/Revision/MacroEnLosMedios/PLLes06.pdf
The major decision structures in coding are loops. Compare and contrast the basic, WHILE, and FOR loops.

Response must be 150 Words

Required APA Format

Q5. The major decision structures in coding are loops. Compare and contrast the basic, WHILE, and FOR loops.

Response must be 150 Words

Required APA Format

Q6. A WHILE loop has a condition at the top of the LOOP in the LOOP clause to check. Each time the loop runs then that condition is checked and if that condition is TRUE then the loop continues. If on the other hand the condition is FALSE then the loop action stops.
A FOR loop on the other hand will only run for how many times it is directed to do so by a range in the statement. The following is an example of a FOR loop. i is 1 in the first iteration and for each loop it increase by 1 so once it reaches 5 the loop ends.

BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

Reference

Casteel, J. (2013). Oracle 11g: PL/SQL Programming. Boston, MA: Course Technology.