PL/SQL Tutorial : Learn PL/SQL in 7 Days

Object sorts

An object kind may be a user-defined composite data type that encapsulates an information structure in conjunction with the functions and procedures required to govern the information. The variables that type the information structure are units referred to as attributes. The functions and procedures that characterize the behavior of the article kind area unit referred to as strategies. A special quiet technique referred to as the creator creates a replacement instance of the article kind and fills in its attributes.

Object sorts should be created through SQL ANd hold on in Oracle information, wherever they will be shared by several programs. Once you outline AN object kind victimization of the produce kind statement, you produce AN abstract example for a few real-world objects. The example specifies the attributes and behaviors the article wants within the application setting.

The data structure shaped by the set of attributes is public (visible to shopper programs). However, well-behaved programs don’t manipulate it directly. Instead, they use the set of strategies provided, so the information is unbroken in a very correct state.

attribute_name

An object attribute. The name should be distinctive inside the item kind (but is reused in alternative object types). you can’t initialize Associate in Nursing attribute in its declaration exploitation the assignment operator or DEFAULT clause. you can’t impose the NOT NULL constraint on Associate in Nursing attribute.

AUTHID Clause

Determines whether or not all member ways execute with the privileges of their definer (the default) or invoker, and whether or not their unqualified references to schema objects are unit resolved within the schema of the definer or invoker. For additional info, see “Using Invoker’s Rights Versus Definer’s Rights (AUTHID Clause)”.

call_spec

Publishes a Java methodology or external C perform within the Oracle knowledge wordbook. It publishes the routine by mapping its name, parameter sorts, and come back kind to their SQL counterparts. to find out the way to write Java decision specs, see Oracle info Java Developer’s Guide. to find out the way to write C decision specs Oracle info Application Developer’s Guide – Fundamentals.

function_body

MAP

Indicates that a technique orders objects by mapping them to values of a scalar data type like CHAR or REAL, that have a predefined order. PL/SQL uses the ordering to judge mathematician expressions like x > y, and to try and do comparisons understood by the DISTINCT, GROUP BY, and ORDER BY clauses. A map methodology returns the relative position of Associate in Nursing object within the ordering of all such objects.

An object kind will contain just one map methodology, that should be a parameterless perform having the come back kind DATE, NUMBER, VARCHAR2, or Associate in Nursing ANSI SQL kind like CHARACTER, INTEGER, or REAL.

MEMBER | builder | STATIC

Declares a software package or decision specification as a technique in Associate in Nursing object kind specification. A builder methodology should have an equivalent name because of the object kind, whereas member and static methods should have names that are totally different from the item kind or any of its attributes.

MEMBER ways area unit invoked on instances of objects, and skim or amendment the attributes of that exact instance:

The system defines a default builder methodology with one parameter for every object attribute, thus you merely ought to outline your own builder ways if you wish to construct the item supported by a special set of parameters.

STATIC ways area unit invoked on the item kind, not any specific object instance, and so should limit themselves to “global” operations that don’t involve the item attributes:

For each software package specification in Associate in Nursing object kind specification, there should be a corresponding software package body within the object kind body. To match specs and bodies, the compiler will do a token-by-token comparison of their headers. The headers should match word for word. variations in whitespace area units allowed.

CONSTRUCTOR and MEMBER ways settle for an integral parameter named SELF, that is Associate in Nursing instance of the item kind. whether or not declared implicitly or expressly, it’s continually the primary parameter passed to a MEMBER methodology. However, STATIC ways cannot settle for or reference SELF.

In the methodology body, SELF denotes the item whose methodology was invoked. for instance, methodology rework declares SELF as Associate in Nursing IN OUT parameter:

You cannot specify a special datatype for SELF. In builder functions, SELF continually has the parameter mode IN OUT. In MEMBER functions, if SELF isn’t declared, its parameter mode defaults to IN. In MEMBER procedures, if SELF isn’t declared, its parameter mode defaults to IN OUT. you can’t specify the OUT parameter mode for SELF.

ORDER

Indicates that a way compares 2 objects. An object sort will contain just one order technique, that should be an operation that returns a numeric result.

Every order technique takes simply 2 parameters: the inbuilt parameter SELF and another object of constant sort. If c1 and c2 area unit client objects, a comparison like c1 > c2 calls technique match mechanically. The tactic returns a negative variety, zero, or a positive variety signifying that SELF is severally but, equal to, or bigger than the opposite parameter. If either parameter passed to AN order technique is null, the tactic returns a null.

pragma_restrict_refs

Pragma RESTRICT_REFERENCES, that checks for violations of “purity” rules. To be owed from SQL statements, a member operator should conform to those rules, that area unit meant to manage aspect effects. If any SQL statement within the operating body violates a rule, you get a mistake at run time (when the statement is parsed).

The pragma asserts that a member operator doesn’t scan or write information tables or package variables. For a lot of info regarding the purity rules and pragma RESTRICT_REFERENCES, see Oracle information Application Developer’s Guide – Fundamentals.

schema_name

The schema contains the article sort. If you omit schema_name, Oracle assumes the article sort is in your schema.

subprogram_body

Implements a MEMBER or STATIC operation or procedure. Its syntax is like that of function_body or procedure_body while not the slayer. See “Functions” and/or “Procedures”.

subprogram_spec

Declares the interface to a builder, MEMBER or STATIC operate or procedure. Its syntax is like that of function_spec or procedure_spec while not the slayer. See “Functions” and/or “Procedures”.

type_name

A user-defined object sort that was outlined victimization the datatype intellect OBJECT.

Usage Notes

Once AN object sort is made in your schema, you’ll be able to use it to declare objects in any PL/SQL block, subprogram, or package. As an example, you’ll be able to use the article sort to specify the datatype of AN object attribute, table column, PL/SQL variable, bind variable, record field, assortment part, formal procedure parameter, or operation result.

Like a package, AN object sort has 2 parts: a specification and a body. The specification (spec for short) is the interface to your applications; it declares a knowledge structure (set of attributes) alongside the operations (methods) required to control the info. The body absolutely defines the strategies, and then implements the specification.

All the knowledge a consumer program must use the strategies is within the specification. consider the specification as AN operational interface and of the body as a recording equipment. you’ll be able to rectify, enhance, or replace the body while not ever-changing the specification.

An object sort encapsulates knowledge and operations. you’ll be able to declare attributes ANd strategies in an object sort specification, however not constants, exceptions, cursors, or types. a minimum of one attribute is needed (the most is 1000); strategies are unit facultative.

In AN object sort specification, all attributes should be declared before any strategies. solely subprograms have AN underlying implementation. If an object sort specification declares solely attributes and/or decision specs, the article sort body makes no sense. you can’t declare attributes within the body. All declarations within the object sort specification are unit public (visible outside the article type).

You can talk over AN attribute solely by name (not by its position within the object type). To access or modify the worth of AN attribute, you employ dot notation. Attribute names may be in chains, which enables you to access the attributes of a nested object sort.

In AN object sort, strategies will reference attributes and different strategies while not a qualifier. In SQL statements, calls to a parameterless technique need AN empty parameter list. In procedural statements, an empty parameter list is facultative unless you chain calls, during which case it’s needed for nearly the last decision.

From a SQL statement, if you decide on a MEMBER technique on a null instance (that is, SELF is null), the tactic isn’t invoked and a null is returned. From a procedural statement, if you decide on a MEMBER technique on a null instance, PL/SQL raises the predefined exception SELF_IS_NULL before the tactic is invoked.

You can declare a map technique or AN order technique however not each. If you declare either technique, you’ll be able to compare objects in SQL and procedural statements. However, if you declare neither technique, you’ll be able to compare objects solely in SQL statements and just for equality or difference. 2 objects of constant sort are unit equal provided that the values of their corresponding attributes are unit equal.

Like packaged subprograms, strategies of constant kind (functions or procedures) may be full. That is, you’ll be able to use constant name take issueent|for various} strategies if their formal parameters differ in variety, order, or datatype family.

Every object sort incorporates a default builder technique (constructor for short), that could be a system-defined operation with constant name because the object sort. you employ the builder to initialize ANd come an instance of that object sort. you’ll be able to conjointly outline your own builder strategies that settle for completely different sets of parameters. PL/SQL never calls a builder implicitly, thus you want to decide it expressly. builder calls area unit allowed where operate calls area unit allowed.

Object-Oriented Programming is particularly suited to building reusable elements and complicated applications. They’re organized around “objects” instead of “actions” i.e. the program’s area unit is designed to figure and move with the complete object instead of the one action. This idea permits the technologist to populate and manipulate the small print at object entities level.

Below image depicts an associate example of the item sort within which a checking account is taken into account associate object entity. The item attributes embody things that are unit holding some attribute values, for instance in Bank Account; its Account range, bank balance, etc. whereas object ways describe the items like calculative rate of interest, generating financial statement, etc. which needs a sure method to be completed.

In PL/SQL object-oriented programming is predicated on object varieties.

An object sort will represent any real-world entity. We have a tendency to area unit planning to discuss a lot of object varieties during this chapter.

In this tutorial – you may learn,

PL/SQL object sort contains in the main 2 elements.

• Attributes
• Members/Methods

Attributes

Attributes area unit the column or field within which information area unit keep. every attribute is mapped to the data type that defines the process and storage sort for that attribute. The attribute will be of any valid PL/SQL datatype, or it will be of another object sort.

Members/Methods

Members or ways are unit subprograms that are outlined within the object sort. they don’t want to store any information. they’re in the main wont to outline the method within the item sort. For example supportive information before populating the item sort. they’re declared within the object sort section and outlined within the object sort body section of the item sort. Body section in object sort is associated with optional half. If no members are a unit gift, then associate object sort can contain no piece.

Create Object in Oracle

An Object sort can not be created at function level, they will be created solely at the schema level. Once the item sort is outlined within the schema, then constant will be utilized in subprograms. the item sort will be created mistreatment ‘CREATE TYPE’. {the sort|the sort|the kind} body will be created solely once making its object type.

Inheritance in Object kind

Inheritance property permits the sub-object kind to access all the attributes and members of the super object kind or parent object kind.

The sub-object kind is termed familial object kind, and therefore the super object kind is termed parent object kind. The below syntax shows the way to produce parent and familial object kind.

Syntax Explanation:

The on top of syntax shows the ORDER operation that must be enclosed within the kind body for equality check.

The parameter for this operation ought to be an associated instance of a similar object kind. The on top of operate is referred to as as “obj_instance_1.match(obj_instance_2)” and this expression can come back the numerical worth as shown, wherever obj_instance_1 and obj_instance_2 area unit the instance of object_type_name.

Example1: within the following example we tend to area units aiming to see the way to compare 2 objects. we tend to area units aiming to produce 2 instances and that we area units aiming to compare attribute ‘salary’ between them. We tend to area units aiming to do int 2 steps.

Step 1: making the article kind and body.

Step 2: making the anonymous block to decide to compare the article instance.

Step 1) making the article kind and body.

Code Explanation:

Code line one-4: making the ’emp_object_equality’ object kind with one attribute and 1 member.

Code line 6-16: shaping the ORDER operation that compares the ‘salary’ attribute of SELF instance and parameter instance kind. It returns negative if SELF regular payment is lesser or positive if SELF regular payment is bigger and zero if salaries are unit equal.

Code Output:

Type created

Step 2) making the anonymous block to decide to compare the article instance.

Output

Salary of second instance is bigger

Declaration initialisation of Object sort

Like different parts in PL/SQL, object sorts also are required to be declared before victimization within the program.

Once the article sort is formed it is utilized in the software system declarative section to declare a variable of that object sort.

Whenever any variable is asserted within the software system as object sort, at run-time a replacement instance of the article sort is created, and this recently created instance is stated as the variable name. By this fashion, one object sort will store multiple values underneath completely different instances.

Syntax Explanation:

The higher than syntax shows the declaration of a variable as Associate in Nursing object sort within the declarative section.

Once the variable is asserted as Associate in Nursing object sort in an exceedingly software system, it’ll be atomically null i.e. the complete object itself a null. It must be initialized with values to use them within the program. they’ll be initialized victimization constructors.

Constructors square measure the implicit methodology of Associate in Nursing object that may be referred with a similar name as that of the article sort. The below syntax shows the initialisation of the article sort.

Constructors

Constructors square measure the implicit methodology of Associate in Nursing object that may be referred with a similar name as that of the article sort. Whenever the article is referred for the primary time, this builder is referred to implicitly.

We can conjointly initialize the objects victimizing these builders. The builder is outlined expressly by process the member in object sort body with a similar name of the article sort.

Example 1: within the following example we have a tendency to square measure about to use the article sort member to insert the record into emp table with values (‘RRR’, 1005, 20000, 1000) and (‘PPP’, 1006, 20000, 1001). Once the info is inserted, we have a tendency to square measure about to show a similar victimization object sort member. We have a tendency to also use the express builder to populate the manager id by default with 1001 price for the second record.