Definitions of Oracle terms 3

[florida41]

Advise what are contraints and procedures and a schema.
I just need a brief explanation if possible.

 

[SantaMufasa]

Florida,

In the Oracle context, here are some definitions:

Constraints: Rules about your data that Oracle is able to enforce at the database level. Oracle constraints on columns fall into these categories:
Primary Key: Ensures that values in Primary Key column(s) are unique and not null.

Unique: Ensures that no two rows have the same value in column(s) with this constraint.

Not Null: Ensures that each row has an explicit value in a column with this constraint.

Foreign Key: Ensures that each value in a column with this constraint matches a value in a column with a Primary Key or Unique constraint.

Check: Enforces compliance with an explicit data rule such as "COMMISSION_PCT between .02 and .12"

When data disobeys the data rule that a constraint defines, then Oracle throws an error message explaining the disobedience.

Procedure: A named collection of syntax written in PL/SQL coding language. Procedures allow definition of input, output, and input-output arguments. A simple example of a Procedure is:

create or replace procedure area_of_circle (radius in number, area out number) is
begin
   area := 3.14159 * (power(radius,2));
end;

Procedure created.

I can then refer to the procedure in other code:

set serveroutput on
declare
    my_area   number;
begin
    area_of_circle(7,my_area);
    dbms_output.put_line('The area of a circle with radius 7 is '||my_area||' square units.');
end;
/

The area of a circle with radius 7 is 153.93791 square units.

PL/SQL procedure successfully completed.

A sibling topic of Procedures is Functions. They look syntactically similar to Procedures, but are often much easier to use. If you are interested in an example of a Function, then let us know.

Schema: In Oracle terms, a schema is virtually synonymous with a User. Every database object in Oracle is owned by an Oracle user also known as an Oracle schema. If you drop an Oracle user named, "YADA", then you are also getting rid of all objects that YADA owns.

This definition/behaviour/functionality of schema is virtually exclusive to the Oracle world. Other database engines (such as MS SQL Server, DB2, Sybase, Informix, MySQL, et cetera) differ significantly from Oracle in the definition/behaviour/functionality of schema.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 19:06 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:06 (11Nov04) Mountain Time

 

[johnherman]

As usual Santa Mufasa has exceeded expectations. Thanks for your very good explanation of schema in the Oracle world. As someone who crosses the boundaries of multiple databases, I find it to be the most clear explanation I have heard. Thanks!

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[SantaMufasa]

Very kind of you, John.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 19:49 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:49 (11Nov04) Mountain Time

 

[florida41]

Thanks for all the details.

Procedures sound similiar to functions or maybe methods?

Also, I think of schema from my Access database experience as tables with relationships but not familiar with the user term?

 

[SantaMufasa]

Florida,

So that you can compare Oracle Procedure to Oracle Functions, here is an example of an Oracle (PL/SQL) Function that offers the same calculation support as the earlier "area_of_circle" procedure:

create or replace function circle_area (radius in number) return number is
begin
   Return 3.14159 * (power(radius,2));
end;
/

Function created.

Notice the syntactical simplicity, not only of the function definition, above, but the invocation flexibility, below (which can now take the form of a non-procedural SQL SELECT statement):

select circle_area(7) from dual;

CIRCLE_AREA(7)
--------------
     153.93791

1 row selected.

I typically prefer Oracle FUNCTIONS since I can use them with equal facility in both SQL statements and procedural PL/SQL blocks.

Now to your statement:

I think of schema from my Access database experience as tables with relationships but not familiar with the user term?

In Oracle, schema most significantly relates to ownership. A schema can own tables, views, indexes, synonyms, procedures, functions, packages, triggers, sequences, clusters, database links, et cetera. For the schema to exist, an Oracle User must exist by the same name as the schema. In the Oracle world, a User and a Schema are congruent and inextricable...You cannot have a schema without a user and vice versa.

To help you compare, these terms in Oracle to the same terms in other database engines, I'll try to distinguish the terms from non-Oracle database perspectives. In most other database environments, you create "logins" with passwords to allow people to connect to the database. Once a person connects to the database installation, they can access information from a variety of databases (provided they have been GRANTed permission to access the database), which live independently of any login. Those "databases" compare favourably with Oracle "schemas". (Recall that a non-Oracle "database" can own tables, views, functions, et cetera, just as can an Oracle schema.)

So, in the final analysis, Oracle "welds" into one unit the concept of "logins" (Oracle Users) and "databases" (Oracle Schemae). In Oracle, these concepts are one and the same; in non-Oracle environments, the concepts are independent and distinct.

Also, it is important to mention that the Oracle term, "database" refers to all of the Oracle USERS (schema) that reside within the data dictionary of a single Oracle Instance. So, what would be 20 "databases" in a Sybase, or MS SQL Server, or DB2, or MySQL installation would be 20 "Users" (or 20 "Schema") in a single Oracle "database".

For more background and insight on Oracle's database and instance architecture, I suggest clicking on an earlier thread from last week, thread759-943578.

Let us know if you any follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 20:31 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:31 (11Nov04) Mountain Time

 

[JaxtheDog]

SantaMufasa,
I have benefited from your posting to others, and I also want to take this opportunity to thank you for your excellent help. Your clear explanations are works of beauty!

Jax

 

[SantaMufasa]

Jax,

Thoughtful comments like yours make all the time I spend on Tek-Tips very worthwhile. Frankly, I prefer spending time on Tek-Tips to spending "billable" time with customers. (Why is that? Perhaps I need some therapy.)

In any case, it is truly my pleasure and thanks again for the encouragement...It is my "pay" while on Tek-Tips.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:31 (12Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:31 (12Nov04) Mountain Time