1 / 18

Oracle seminar

Oracle seminar. Presented by Dimitar Dimitrov 29.11.2001 Dimitar.Dimitrov@Semantec.bg For Course: Modern Software Technologies. Agenda :. What is Oracle Oracle and otherwise Certification Oracle & Internet How Oracle works Oracle indexes PL/SQL

catherine-mccall
Download Presentation

Oracle seminar

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle seminar Presented by Dimitar Dimitrov 29.11.2001 Dimitar.Dimitrov@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 DeveloperCertification 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 US$125. 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 rowsource 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 rowsfrom 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 • createorreplaceprocedure SST(Name intype, • Name inouttype, • ...) • isbegin…end SST; • createorreplacefunction SST(Name intype, • 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 • createorreplacepackage SST is-- Public type declarationstype <TypeName> is <Datatype>;-- Public constant declarations <ConstantName> constant <Datatype> := <Value>;-- Public variable declarations <VariableName> <Datatype>;-- Public function and procedure declarationsfunction <FunctionName>(<Parameter> <Datatype>) return <Datatype>;end SST;

  16. Oracle Packages • create or replace package body SST is • -- Private type declarations • type <TypeName> is <Datatype>; • -- Private constant declarations • <ConstantName> constant <Datatype> := <Value>; • -- Private variable declarations • <VariableName> <Datatype>;

  17. Oracle Packages • -- Function and procedure implementations • function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is • <LocalVariable> <Datatype>; • begin • <Statement>; • return(<Result>); • end; • begin • -- Initialization • <Statement>; • end SST;

  18. Links • http://www.oracle.com/ • Oracle University • PL/SQL Help and Tutorial

More Related