Docstoc

check

Document Sample
check Powered By Docstoc
					   Introduction to PL/SQL
Oracle Database PL/SQL 10g Programming


           Chapter 1 & 2
           Introduction to PL/SQL

      SQL*Plus Environment
      Development Environments
      Database Design Concepts
      SQL Concepts
      PL/SQL Language



                  Oracle Database PL/SQL 10g Programming
2006                           (Chapter 1 & 2)             Page 2
                         Introduction to PL/SQL
                SQL*Plus Environment
      The interactive and batch processing environment for SQL and
       PL/SQL statements.
      Command line interface to Oracle.
      The connection and session environment:
           Interactive scripting.
           Batch execution using silent option.
           Session environment when connecting by using OCI, ODBC and JDBC
            external programming models.
      Supports formatting commands.
      Supports session scope variables, also known as bind variables.
      Supports a GUI versions for Microsoft Windows, which is being
       deprecated.



                              Oracle Database PL/SQL 10g Programming
2006                                       (Chapter 1 & 2)                Page 3
                            Introduction to PL/SQL
                  SQL*Plus Environment
      SQL*Plus is run by using the sqlplus executable on Microsoft Windows,
       Linux or Unix.
      SQL*Plus supports option flags:
           -c   sets command compatibility to current or older version.
           -h   provides help screen.
           -l   sets the log on attempts to a number other than the default 3.
           -m   lets you set a series of HTML formatting options.
           -r   restricts user interaction with the file system.
           -s   sets silent mode for batch processing by suppressing console feedback.
           -v   returns the version.
      SQL*Plus supports different connection models:
           DBA Administration connections using “/ AS SYSOPER” or “/ AS SYSDBA”.
           Default connection, which enables a user to connect to a schema and database.
           Open environment not conneted to a database using “/NOLOG” mode.




                                   Oracle Database PL/SQL 10g Programming
2006                                            (Chapter 1 & 2)                           Page 4
                         Introduction to PL/SQL
                SQL*Plus Environment
      SQL*Plus connection samples:
           sqlplus –h | -v
           sqlplus username/password[@sid]
           sqlplus –s username/password[@sid] @script.sql
      SQL*Plus commands do not require semicolons to run.
      SQL*Plus redirects output to files with a default .lst extension:
            SQL> spool C:\mydir\myoutputfile.log
      SQL*Plus runs files from local or qualified directories using the @
       symbol:
            SQL> @C:\mydir\myfile.sql
      SQL*Plus runs scripts with a .sql extension when the extension is
       excluded.



                               Oracle Database PL/SQL 10g Programming
2006                                        (Chapter 1 & 2)                Page 5
                        Introduction to PL/SQL
               SQL*Plus Environment
      SQL*Plus can edit the current buffer:
          Microsoft is preconfigured, you need only type:
           SQL> ed
          Linux or Unix requires:
           SQL> define _editor=vi
           SQL> ed
      SQL*Plus can edit a file:
          Microsoft is preconfigured, you need only type:
           SQL> ed C:\mydir\myfile.sql
          Linux or Unix requires:
           SQL> define _editor=vi
           SQL> ed C:\mydir\myfile.sql


                             Oracle Database PL/SQL 10g Programming
2006                                      (Chapter 1 & 2)             Page 6
                            Introduction to PL/SQL
                 SQL*Plus Environment
      SQL*Plus has many environment variables:
           You can find the default settings by:
            SQL> show all
           You change many environment variables by:
            SQL> set echo on | off
           You set some environment variables by providing a value:
            SQL> set sqlpluscompatibility 9.2.0
           You set some environment variables by providing a delimited value:
            SQL> set null “<null>”
            SQL> set suffix “pls”
      SQL*Plus common requirements:
           Suppress column headers:
            SQL> set pagesize 0
           Enabling maximum standard out redirection from PL/SQL:
            SQL> set serveroutput on size 1000000



                                  Oracle Database PL/SQL 10g Programming
2006                                           (Chapter 1 & 2)                   Page 7
                          Introduction to PL/SQL
                SQL*Plus Environment
      SQL*Plus can read only a file into the buffer:
           You use the GET command.
           You cannot run the file without errors if there are SQL*Plus commands
            in it.
           You should use the @ command to read and execute a script when it
            contains SQL*Plus commands.
      SQL*Plus supports global session variables, also known as bind
       variables, and they must be SQL types not subtypes, like:
            BINARY_DOUBLE
            BINARY_FLOAT
            CHAR (n char | byte)
            CLOB
            NUMBER
            REF CURSOR
            VARCHAR (n char | byte)

                               Oracle Database PL/SQL 10g Programming
2006                                        (Chapter 1 & 2)                   Page 8
                       Introduction to PL/SQL
               SQL*Plus Environment
      SQL*Plus global session variables:
          Are declared in SQL*Plus.
          Are defined in PL/SQL program units.
      SQL*Plus declares a global session variable using the
       following syntax:
           SQL> variable mynum NUMBER
           SQL> variable mystr VARCHAR2(10)
      SQL*Plus defines the global session variables in PL/SQL
       program units, which can be:
          Anonymous block programs, or
          Named block programs.

                            Oracle Database PL/SQL 10g Programming
2006                                     (Chapter 1 & 2)             Page 9
                            Introduction to PL/SQL
           Development Environments
     SQL*Plus is the interface to the
      database.
     SQL runs inside of the SQL*Plus                       Oracle
      environment.
     PL/SQL runs inside of the SQL
      environment.                                                         SQL*Plus
     Oracle development in SQL & PL/SQL                       Connect
                                                                              SQL
      is also supported by external                                                      Standard

      Integrated Development Environments                                                Libraries


      (IDEs).                                                   ODBC           PL/SQL
      External programs connect through
                                                                                           User
                                                                                        Libraries


      the SQL*Plus environment.                                 JDBC


     User-defined libraries are declared by
      SQL.
     User-defined libraries are wrapped by
      PL/SQL named blocks.




                                  Oracle Database PL/SQL 10g Programming
    2006                                       (Chapter 1 & 2)                          Page 10
                         Introduction to PL/SQL
           Development Environments
      Oracle PL/SQL Development is supported by:
          Oracle SQL*Plus
          Oracle JDeveloper.
          Oracle SQL Developer.
          Oracle Application Developer.
          Quest Software’s Toad Developer.
      IDE tools provide:
          Diagnostic tools.
          Debugging tools.
          Syntax auto fill tools.
          Syntax highlighting.


                              Oracle Database PL/SQL 10g Programming
2006                                       (Chapter 1 & 2)             Page 11
                       Introduction to PL/SQL
       Development Environments
      The Oracle Transparent Network Substrate (TNS) implements
       an application layer of the OSI model.
      Users connect through TNS to the database using TCP sockets
       or IPC connections.
      Clients use the tnsnames.ora file like a host file, and map
       machine names to a combination of IP and port address.
           Net service name resolves to machine name and port number.
           The service name in the tnsnames.ora file maps to the Oracle
            database SID value.
      The Oracle server listens on a port for incoming TNS requests,
       and uses three configuration files:
           listener.ora
           sqlnet.ora
           tnsnames.ora



                             Oracle Database PL/SQL 10g Programming
2006                                      (Chapter 1 & 2)                  Page 12
                         Introduction to PL/SQL
           Development Environments
      Oracle PL/SQL development is supported by:
          Oracle SQL*Plus
          Oracle JDeveloper.
          Oracle SQL Developer.
          Oracle Application Developer.
          Quest Software’s Toad Developer.
      IDE tools provide:
          Diagnostic tools.
          Debugging tools.
          Syntax auto fill tools.
          Syntax highlighting.


                              Oracle Database PL/SQL 10g Programming
2006                                       (Chapter 1 & 2)             Page 13
                            Introduction to PL/SQL
           Database Design Concepts
      Database Tables are:
          2-dimensional arrays of rows and columns.
          Columns can hold a scalar or compound variable:
               Scalar variables hold only one thing at a time.
               Compound variables hold:
                     A list of one thing, which can be scalar or compound.
                     A structure, which is also known as a record.
          Normalized tables with a primary key.
          Normalized tables with one or more foreign keys.
               Foreign keys hold a copy of a primary key in another table.
               Foreign keys hold a copy of a primary key in the same table.
          Normalized tables with one or more superkey:
               Super keys identify one or more rows in a table.
               Super keys identify one row in a relation.

                                   Oracle Database PL/SQL 10g Programming
2006                                            (Chapter 1 & 2)                Page 14
                         Introduction to PL/SQL
           Database Design Concepts
      Database Tables hold data.
      Database Tables have a binary or n-ary
       relationships:
          Binary relations exist between two tables and are:
               One-to-one
               One-to-many
               Many-to-many between two tables.
          N-ary relations exist between three or more tables
           and are:
               Typically many-to-many
               Occasionally one-to-many.
               Rarely one-to-one.

                             Oracle Database PL/SQL 10g Programming
2006                                      (Chapter 1 & 2)             Page 15
                    Introduction to PL/SQL
           Database Design Concepts
      Database tables are normalized in:
          3rd or higher normal form
          Domain Key Normal Form (DKNF):
            DKNF uses a surrogate key, also known as
             automatic numbering.
            DKNF cannot use a natural key.

      Database tables have constraints on
       column values.
                        Oracle Database PL/SQL 10g Programming
2006                                 (Chapter 1 & 2)             Page 16
                    Introduction to PL/SQL
           Database Design Concepts
      Unique:
          A unique constraint tells the database to
           disallow a column to hold two or more equal
           values, and typically made using “out-of-line”
           constraint syntax.




                        Oracle Database PL/SQL 10g Programming
2006                                 (Chapter 1 & 2)             Page 17
                  Introduction to PL/SQL
         Database Design Concepts
      Not null:
        A not null constraint tells the database to
         disallow entry of a null value into the column
         from an INSERT or UPDATE statement, a not
         null constraint must be made using “inline”
         syntax or it is a check constraint.




                      Oracle Database PL/SQL 10g Programming
2006                               (Chapter 1 & 2)             Page 18
                    Introduction to PL/SQL
           Database Design Concepts
      Check:
          A check constraint tells the database to
           disallow entry or update of a column value
           that fails to meet the expression governing
           the constraint, and is typically made using
           “out of line” constraint syntax.




                        Oracle Database PL/SQL 10g Programming
2006                                 (Chapter 1 & 2)             Page 19
                    Introduction to PL/SQL
           Database Design Concepts
      Primary key:
          A primary key tells the database to allow
           entry or update of a column value that is only
           both unique and not null, and it enables a
           foreign key constraint to reference the column
           or set of columns defined as the primary key,
           and typically made using “out-of-line”
           constraint syntax.

                       Oracle Database PL/SQL 10g Programming
2006                                (Chapter 1 & 2)             Page 20
                    Introduction to PL/SQL
           Database Design Concepts
      Foreign key:
          A foreign key constraint tells the database to
           disallow entry or update of a column value
           that is not found in the referenced primary
           key column list of values, and typically made
           using “out-of-line” constraint syntax.




                        Oracle Database PL/SQL 10g Programming
2006                                 (Chapter 1 & 2)             Page 21
                     Introduction to PL/SQL
                     SQL Concepts
      Structured Query Language (SQL)
          Structured English QUEry Language (SEQUEL)
      ANSI Standards
          ANSI   SQL-86
          ANSI   SQL-89
          ANSI   SQL-92
          ANSI   SQL:1999
          ANSI   SQL:2003

                        Oracle Database PL/SQL 10g Programming
2006                                 (Chapter 1 & 2)             Page 22
                      Introduction to PL/SQL
                       SQL Concepts
      Security is tiered by ANSI-SPARC rules
          Superuser – holds internal data
          Administrator – holds conceptual data
          User – holds external data
      Superuser grants privileges to users
          Grants individual privileges one at a time.
          Creates roles as groups of privileges.
          Grants grouped privileges as roles.
      Data can be restricted at various levels

                          Oracle Database PL/SQL 10g Programming
2006                                   (Chapter 1 & 2)             Page 23
                      Introduction to PL/SQL
                       SQL Concepts
      Data Control Language (DCL)
          Manages transaction controls.
          Uses the COMMIT, ROLLBACK, and SAVEPOINT commands.
      Data Definition Language (DDL)
          Creates or modifies data structures in the database.
          Uses the ALTER, CREATE, DROP, FLASHBACK, GRANT, PURGE,
           RENAME, REVOKE, and TRUNCATE commands.
          Uses the recycle bin in Oracle 10g Release 2.
      Data Manipulation Language (DML)
          Inserts, updates and deletes data from tables.
          Uses the INSERT, UPDATE, and DELETE commands.


                          Oracle Database PL/SQL 10g Programming
2006                                   (Chapter 1 & 2)             Page 24
                         Introduction to PL/SQL
                      PL/SQL Language
      Procedural Language/Structured Query Language (PL/SQL)
          Introduced in Oracle 6 as a report formatting language.
          Improved to a useful programming language in Oracle 7.
          Improved to include collections in Oracle 8.
          Improved to include instantiable object types in Oracle 9i Release 2.
          Improved to include conditional compilation in Oracle 10g.
      PL/SQL Legacy
          Derived syntax from Ada programming language.
          Inherited operators and structure from Pascal through Ada.
      PL/SQL Block Structure
          Implemented as a strongly typed language.
          Implemented as a formal blocked language.




                               Oracle Database PL/SQL 10g Programming
2006                                        (Chapter 1 & 2)                    Page 25
                     Introduction to PL/SQL
                  PL/SQL Language
      PL/SQL Integration
          Tightly integrated with SQL.
          Shares SQL data types.
          Includes PL/SQL only data types.
          Enables user-defined data types.
          Interface between external procedures and SQL*Plus.
          Interface between external libraries and SQL*Plus.



                         Oracle Database PL/SQL 10g Programming
2006                                  (Chapter 1 & 2)             Page 26
                       Introduction to PL/SQL
                    PL/SQL Language
      PL/SQL Advanced Features
          Full object-oriented programming is available from Oracle 9i
           Release 2.
          Full support for dynamic SQL.
          Full support for pass-by-value and pass-by-reference models.
          Full support for wrapping solutions in other programming
           languages.
          Full catalog definitions act like OO class files.
          Robust extensions made through built-in packages.
          Conditional compilation available in Oracle 10g Release 2.




                            Oracle Database PL/SQL 10g Programming
2006                                     (Chapter 1 & 2)              Page 27
                   Summary

      SQL*Plus Environment
      Development Environments
      Database Design Concepts
      SQL Concepts
      PL/SQL Language



                  Oracle Database PL/SQL 10g Programming
2006                           (Chapter 1 & 2)             Page 28

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:3/18/2013
language:English
pages:28