Oracle seminar - PowerPoint PPT Presentation

1 / 18
About This Presentation
Title:

Oracle seminar

Description:

The Oracle Java Developer certification track is comprised of five tests that ... Sun Certified Programmer for the Java Platform (1 exam) ... – PowerPoint PPT presentation

Number of Views:239
Avg rating:3.0/5.0
Slides: 19
Provided by: dimitard
Category:
Tags: oracle | seminar

less

Transcript and Presenter's Notes

Title: Oracle seminar


1
Oracle seminar
  • Presented by Dimitar Dimitrov
    29.11.2001
  • Dimitar.Dimitrov_at_Semantec.bg
  • For Course Modern Software Technologies

2
Agenda
  • What is Oracle
  • Oracle and otherwise
  • Certification
  • Oracle Internet
  • How Oracle works
  • Oracle indexes
  • PL/SQL
  • Stored Procedures
  • Oracle Packages

3
Certification
  • Oracle Java Developer Certification TrackThe
    Oracle Java Developer certification track is
    comprised of five tests that cover three
    progressive skill levels.
  • 1). Sun Certified Programmer for the Java
    Platform (1 exam)Fundamental skills in the Java
    Programming language
  • 2). Oracle Certified Solution Developer -
    JDeveloper (2 exams)Application development for
    the Java Platform
  • 3). Oracle Certified Enterprise Developer -
    Oracle iPlatform (2 exams)Enterprise development
    with an application server
  • Pricing and RegistrationOCP exams are offered at
    Authorized Prometric Testing Centers worldwide.
    The cost of each exam is US125. Pricing is
    subject to change.

4
How Oracle Works
  • Physical database structure
  • Logical Database Structures
  • The logical structures of an Oracle database
    include tablespaces, schema objects, data blocks,
    extents, and segments.
  • Tablespaces
  • A database is divided into logical storage units
    called tablespaces, which group related logical
    structures together.
  • For example, tablespaces commonly group all of an
    application's objects to simplify some
    administrative operations.

5
How Oracle Works

Schemas and Schema Objects A schema is a
collection of database objects. Schema objects
are the logical structures that directly refer to
the database's data. Schema objects include such
structures as tables, views, sequences, stored
procedures, synonyms, indexes, clusters, and
database links.
6
How Oracle Works
7
Oracle Indexes
8
Oracle Indexes
  • Parser
  • The parser performs two functions
  • Syntax analysis This checks SQL statements for
    correct syntax.
  • Semantic analysis This checks, for example, that
    the current database objects and object
    attributes referenced are correct.
  • Optimizer
  • The optimizer is the heart of the SQL processing
    engine. The Oracle server provides two methods of
    optimization rule-based optimizer (RBO) and
    cost-based optimizer (CBO).
  • Row Source Generator
  • The row source generator receives the optimal
    plan from the optimizer. It outputs the execution
    plan for the SQL statement. The execution plan is
    a collection of row sources structured in the
    form of a tree. A row source is an iterative
    control structure. It processes a set of rows,
    one row at a time, in an iterated manner. A row
    source produces a row set.

9
Oracle Indexes
  • SQL Execution
  • SQL execution is the component that operates on
    the execution plan associated with a SQL
    statement. It then produces the results of the
    query.
  • Create Indexes when select a small percentage of
    rows from a table
  • Indexes are logically and physically independent
    of the data in the associate table.
  • Required CREATE ANY INDEX system privilege for
    creating index

10
Oracle Indexes
  • Advices

11
PL/SQL
  • PL/SQL is Oracle's procedural language extension
    to SQL. PL/SQL combines the ease and flexibility
    of SQL with the procedural functionality of a
    structured programming language, such as IF ...
    THEN, WHILE, and LOOP.
  • When designing a database application, a
    developer should consider the advantages of using
    stored PL/SQL
  • Because PL/SQL code can be stored centrally in a
    database, network traffic between applications
    and the database is reduced, so application and
    system performance increases.
  • Data access can be controlled by stored PL/SQL
    code. In this case, the users of PL/SQL can
    access data only as intended by the application
    developer (unless another access route is
    granted).
  • PL/SQL blocks can be sent by an application to a
    database, executing complex operations without
    excessive network traffic.
  • Even when PL/SQL is not stored in the database,
    applications can send blocks of PL/SQL to the
    database rather than individual SQL statements,
    thereby again reducing network traffic.

12
Store Procedures
  • Procedures and functions consist of a set of SQL
    and PL/SQL statements that are grouped together
    as a unit to solve a specific problem or perform
    a set of related tasks. A procedure is created
    and stored in compiled form in the database and
    can be executed by a user or a database
    application.
  • Procedures and functions are identical except
    that functions always return a single value to
    the caller, while procedures do not return values
    to the caller.
  • Examples

13
Store Procedures
  • create or replace procedure SST (Name in type,
  • Name in out
    type,
  • ...)
  • isbegin end SST
  • create or replace function SST (Name in type,
  • Name in type,
  • ...)
  • return type is
  • Result type
  • begin
  • return (Result)
  • end SST

14
Oracle Packages
  • Packages provide a method of encapsulating and
    storing related procedures, functions, variables,
    and other package constructs together as a unit
    in the database. While packages allow the
    administrator or application developer the
    ability to organize such routines, they also
    offer increased functionality (for example,
    global package variables can be declared and used
    by any procedure in the package) and performance
    (for example, all objects of the package are
    parsed, compiled, and loaded into memory once).
  • Examples

15
Oracle Packages
  • create or replace package SST is -- Public
    type declarations type ltTypeNamegt is
    ltDatatypegt -- Public constant declarations
    ltConstantNamegt constant ltDatatypegt ltValuegt
    -- Public variable declarations ltVariableNamegt
    ltDatatypegt -- Public function and procedure
    declarations function ltFunctionNamegt(ltParametergt
    ltDatatypegt) return ltDatatypegtend SST

16
Oracle Packages
  • create or replace package body SST is
  • -- Private type declarations
  • type ltTypeNamegt is ltDatatypegt
  • -- Private constant declarations
  • ltConstantNamegt constant ltDatatypegt ltValuegt
  • -- Private variable declarations
  • ltVariableNamegt ltDatatypegt

17
Oracle Packages
  • -- Function and procedure implementations
  • function ltFunctionNamegt(ltParametergt ltDatatypegt)
    return ltDatatypegt is
  • ltLocalVariablegt ltDatatypegt
  • begin
  • ltStatementgt
  • return(ltResultgt)
  • end
  • begin
  • -- Initialization
  • ltStatementgt
  • end SST

18
Links
  • http//www.oracle.com/
  • Oracle University
  • PL/SQL Help and Tutorial
Write a Comment
User Comments (0)
About PowerShow.com