Manipulating objects in pl sql, PL-SQL Programming

Manipulating Objects:

You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statements to insert an object, call its methods, select its attributes, & update its state.

In the SQL*Plus script shown below, the INSERT statement calls the constructor for the object type Rational, and then inserts the resultant object. The SELECT statements retrieve the value of the attribute num. The UPDATE declaration calls member method reciprocal, that returns a Rational value after exchanging the attributes num and den. Note that the table alias is needed whenever you reference an attribute or method.

CREATE TABLE numbers (rn Rational, ...)


INSERT INTO numbers (rn) VALUES (Rational(3, 62)) -- inserts 3/62


SELECT n.rn.num INTO my_num FROM numbers n ... -- returns 3


UPDATE numbers n SET n.rn = n.rn.reciprocal ... -- yields 62/3


When you instantiate an object in this way, it has no individuality outside the database table. Though, the object type exists separately of any table, and can be used to create objects in the other ways.

In the later illustration, you build a table which stores objects of the type Rational in its rows. These tables, having rows of objects, are known as the object tables. Each column in a row corresponds to the attribute of the object type. The Rows can have various column values.

CREATE TABLE rational_nums OF Rational;

Each row in the object table has an object identifier that uniquely identifies the object stored in that row and serve as a reference to the object.

Posted Date: 10/6/2012 8:16:18 AM | Location : United States

Related Discussions:- Manipulating objects in pl sql, Assignment Help, Ask Question on Manipulating objects in pl sql, Get Answer, Expert's Help, Manipulating objects in pl sql Discussions

Write discussion on Manipulating objects in pl sql
Your posts are moderated
Related Questions
Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr