Advanced database lab oracle structure query language

Considerations Before Creating a Database • Database creation prepares several operating system files so they can work together as an Oracle database. • You need only create a database once, regardless of how many data files it has or how many instances access it. • Creating a database can also erase information in an existing database and create a new database with the same name and physical structure.

Considerations Before Creating a Database Creation Prerequisites To create a new database, you must have the following: • the operating system privileges associated with a fully operational database administrator • sufficient memory to start the Oracle instance • sufficient disk storage space for the planned database on the computer that executes Oracle

Object Types • Listener port: Allows Oracle client connections to the database via the Oracle's SQL*Net protocol. You can configure it during installation. Port 1521 is the default client connections port, however, you can configure another TCP port via the Oracle configuration and administration tools. • A CDB includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a non-CDB . ... A common user is a database user known in every container . • A pluggable database (PDB) is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. PDBs can be plugged into to CDBs. A CDB can contain multiple PDBs. Each PDB appears on the network as a separate database.

Oracle create table statement • To create table in oracle database, you use the create table statement. • The basic syntax of the create table statement CREATE TABLE schema_name.table_name( Column_1 data_type column_constraint, Column_2 data_type column_constraint, …… Table_constraint );

Oracle create table example • The following example shows how to create a new table named persons in csStudent schema CREATE TABLE csStudent2.persons( Person_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(person_id) );

create user csStudent2 IDENTIFIED by "oracle"; grant all privileges to csStudent identified by "oracle";

Oracle Insert statement: insert into CSSTUDENT.persons values(1, 'abebe', 'kebede’); Select statement select * from csstudent.persons; Update statement update csstudent.persons set first_name = 'Belay' where last_name = 'kebede’; Drop table statement drop table csStudent.persons;

Oracle alter table example • The alter table statement is used to add, delete, or modify columns in existing table. • the alter table statement is also used to add and drop various constraints on an existing table. • Syntax: ALTER TABLE table_name ADD column_name datatype; • Example: Alter table csStudent.persons Add email varchar(255);

Oracle alter table example • Syntax: ALTER TABLE table_name DROP COLUMN column_name; • Syntax: ALTER TABLE table_name MODIFY COLUMN column_name data_type;

Introduction to Oracle Objects • Oracle object types are user-defined types that make it possible to model real-world entities, such as customers and purchase orders, as objects in the database. • New object types can be created from any built-in database types and any previously created object types, object references, and collection types. • Object types can work with complex data, such as images, audio, and video. • Oracle Database stores metadata for user-defined types in a schema that is available to SQL, PL/SQL, Java, and other languages. • Object types are also known as user-defined types or ADTs.

Advantages of Objects • In general, the object-type model is similar to the class mechanism found in C++ and Java. • Like classes, the reusability of objects makes it possible to develop database applications faster and more efficient. • Objects offer other advantages over a purely relational approach, such as: • Objects Can Encapsulate Operations Along with Data • Objects Are Efficient • Objects Can Represent Part-Whole Relationships

Database Features of Oracle Objects • The following are features and concepts of the object-relational model that are related to the database. • Object types • Object instances • Object methods • Object identifiers • Object views • Type inheritance

Object Types • An object type is a kind of data type. • You can use it in the same ways that you use standard data types such as NUMBER or VARCHAR2.

Oracle Objects • An object type allows you to create composite types. • Using objects allow you to implement real world objects with specific structure of data and methods for operating it. • Objects have attributes and methods. • Attributes are properties of an object and are used for storing an object's state; and methods are used for modelling its behaviour. • Objects are created using the CREATE [OR REPLACE] TYPE statement.

Oracle create object example • an example to create a simple address object consisting of few attributes − CREATE OR REPLACE TYPE address AS OBJECT (house_no varchar2(10), street varchar2(30), city varchar2(20), state varchar2(10), pincode varchar2(10) );

Oracle Methods • Object methods implement behaviour that objects of that type perform. • Object methods, also known as subprograms, are functions or procedures that you can declare in an object type definition to implement behaviour that you want objects of that type to perform. • An application calls the subprograms to invoke the behaviour. • Subprograms can be written in PL/SQL or virtually any other programming language.

• Member Methods • Methods for Comparing Objects • Static Methods • Constructor Methods • External Implemented Methods Oracle Methods … Cont..

Member Methods • Member methods provide an application with access to the data of an object instance. • You define a member method in the object type for each operation that you want an object of that type to be able to perform. • Non-comparison member methods are declared as either MEMBER FUNCTION or MEMBER PROCEDURE. • Comparison methods use MAP MEMBER FUNCTION or ORDER MEMBER FUNCTION as described in "Member Methods for Comparing Objects".

Oracle create object example • Let's create one more object customer where we will wrap attributes and methods together to have object-oriented feeling CREATE OR REPLACE TYPE customer AS OBJECT (code number(5), name varchar2(30), contact_no varchar2(12), addr address, member procedure display );

Instantiating an Object • Defining an object type provides a blueprint for the object. • To use this object, you need to create instances of this object. • You can access the attributes and methods of the object using the instance name and the access operator (.) .

DECLARE residence address; BEGIN residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); dbms_output.put_line('House No: '|| residence.house_no); dbms_output.put_line('Street: '|| residence.street); dbms_output.put_line('City: '|| residence.city); dbms_output.put_line('State: '|| residence.state); dbms_output.put_line('Pincode: '|| residence.pincode); END;

Member Methods • Member methods are used for manipulating the attributes of the object. • You provide the declaration of a member method while declaring the object type. • The object body defines the code for the member methods. The object body is created using the CREATE TYPE BODY statement. • Constructors are functions that return a new object as its value. • Every object has a system defined constructor method. • The name of the constructor is same as the object type.

The comparison methods are used for comparing objects. There are two ways to compare objects − Map method The Map method is a function implemented in such a way that its value depends upon the value of the attributes. Order method The Order method implements some internal logic for comparing two objects. https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/object- methods.html#GUID-62ACE97A-5DD9-402A-B8B0-999AC488CAA2

Using Map method CREATE OR REPLACE TYPE rectangle AS OBJECT ( length number, width number, member function enlarge( inc number) return rectangle, member procedure display, map member function measure return number );

The syntax to create a function in Oracle is: CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [function_name]; Drop Function Once you have created your function in Oracle, you might find that you need to remove it from the database. DROP FUNCTION function_name;

Creating the type body CREATE OR REPLACE TYPE BODY rectangle AS MEMBER FUNCTION enlarge(inc number) return rectangle IS BEGIN return rectangle(self.length + inc, self.width + inc); END enlarge; MEMBER PROCEDURE display IS BEGIN dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width); END display; MAP MEMBER FUNCTION measure return number IS BEGIN return (sqrt(length*length + width*width)); END measure; END;

Now using the rectangle object and its member functions DECLARE r1 rectangle; r2 rectangle; r3 rectangle; inc_factor number := 5; BEGIN r1 := rectangle(3, 4); r2 := rectangle(5, 7); r3 := r1.enlarge(inc_factor); r3.display; IF (r1 > r2) THEN -- calling measure function r1.display; ELSE r2.display; END IF; END;

Using Order method CREATE OR REPLACE TYPE rectangle AS OBJECT (length number, width number, member procedure display, order member function measure(r rectangle) return number );

Creating the type body CREATE OR REPLACE TYPE BODY rectangle AS MEMBER PROCEDURE display IS BEGIN dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width); END display; ORDER MEMBER FUNCTION measure(r rectangle) return number IS BEGIN IF(sqrt(self.length*self.length + self.width*self.width)> sqrt(r.length*r.length + r.width*r.width)) then return(1); ELSE return(-1); END IF; END measure; END;

Now using the rectangle object and its member functions DECLARE r1 rectangle; r2 rectangle; BEGIN r1 := rectangle(23, 44); r2 := rectangle(15, 17); r1.display; r2.display; IF (r1 > r2) THEN -- calling measure function r1.display; ELSE r2.display; END IF; END;

Here are some other differences between map and order methods Automatic invocation • Both map and order methods are called automatically when two objects of the same type need to be compared. Number of methods • You can only have one map or order method in an object type definition. Object type • Only a type that is not derived from another type can declare an order method. Return values • Map methods return values that are Oracle built-in data types, or ANSI SQL types. • Order methods return a negative number, zero, or a positive number. You can implement either a map or order method in the CREATE TYPE and CREATE TYPE BODY statements.

Inheritance for PL/SQL Objects • PL/SQL allows creating object from the existing base objects. • To implement inheritance, the base objects should be declared as NOT FINAL. The default is FINAL. • Let us create another object named TableTop, this is inherited from the Rectangle object. • For this, we need to create the base rectangle object −

create the base rectangle object CREATE OR REPLACE TYPE rectangle AS OBJECT (length number, width number, member function enlarge( inc number) return rectangle, NOT FINAL member procedure display) NOT FINAL

Creating the base type body CREATE OR REPLACE TYPE BODY rectangle AS MEMBER FUNCTION enlarge(inc number) return rectangle IS BEGIN return rectangle(self.length + inc, self.width + inc); END enlarge; MEMBER PROCEDURE display IS BEGIN dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width); END display; END;

Creating the child object tabletop CREATE OR REPLACE TYPE tabletop UNDER rectangle ( material varchar2(20), OVERRIDING member procedure display )

Creating the type body for the child object tabletop CREATE OR REPLACE TYPE BODY tabletop AS OVERRIDING MEMBER PROCEDURE display IS BEGIN dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width); dbms_output.put_line('Material: '|| material); END display; END;

Using the tabletop object and its member functions DECLARE t1 tabletop; t2 tabletop; BEGIN t1:= tabletop(20, 10, 'Wood'); t2 := tabletop(50, 30, 'Steel'); t1.display; t2.display; END;

Abstract Objects in PL/SQL • The NOT INSTANTIABLE clause allows you to declare an abstract object. You cannot use an abstract object as it is; • you will have to create a subtype or child type of such objects to use its functionalities.

create table csstudent.graphics_table ( bfile_id number, bfile_desc varchar2(30), bfile_loc bfile, bfile_type varchar2(4) ); INSERT INTO csstudent2.graphics_table VALUES(4,'April Book of Days’, bfilename('C:UsersFauxDesktop','1.JPG'),'JPEG');

Advanced database lab oracle structure query language

More Related Content

Similar to Advanced database lab oracle structure query language

More from afendimohammed288

Recently uploaded

Advanced database lab oracle structure query language