1 / 28

Introduction to PL/SQL Oracle Database PL/SQL 10g Programming

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. Introduction to PL/SQL SQL*Plus Environment.

cheri
Download Presentation

Introduction to PL/SQL Oracle Database PL/SQL 10g Programming

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. Introduction to PL/SQLOracle Database PL/SQL 10g Programming Chapter 1 & 2

  2. Introduction to PL/SQL • SQL*Plus Environment • Development Environments • Database Design Concepts • SQL Concepts • PL/SQL Language Oracle Database PL/SQL 10g Programming (Chapter 1 & 2)

  3. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  4. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  5. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  6. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  7. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  8. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  9. Introduction to PL/SQLSQL*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 (Chapter 1 & 2)

  10. Introduction to PL/SQLDevelopment Environments • SQL*Plus is the interface to the database. • SQL runs inside of the SQL*Plus environment. • PL/SQL runs inside of the SQL environment. • Oracle development in SQL & PL/SQL is also supported by external Integrated Development Environments (IDEs). • External programs connect through the SQL*Plus environment. • User-defined libraries are declared by SQL. • User-defined libraries are wrapped by PL/SQL named blocks. Oracle Database PL/SQL 10g Programming (Chapter 1 & 2)

  11. Introduction to PL/SQLDevelopment 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 (Chapter 1 & 2)

  12. Introduction to PL/SQLDevelopment 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 (Chapter 1 & 2)

  13. Introduction to PL/SQLDevelopment 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 (Chapter 1 & 2)

  14. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  15. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  16. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  17. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  18. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  19. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  20. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  21. Introduction to PL/SQLDatabase 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 (Chapter 1 & 2)

  22. Introduction to PL/SQLSQL 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 (Chapter 1 & 2)

  23. Introduction to PL/SQLSQL 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 (Chapter 1 & 2)

  24. Introduction to PL/SQLSQL 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 (Chapter 1 & 2)

  25. Introduction to PL/SQLPL/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 (Chapter 1 & 2)

  26. Introduction to PL/SQLPL/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 (Chapter 1 & 2)

  27. Introduction to PL/SQLPL/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 (Chapter 1 & 2)

  28. Summary • SQL*Plus Environment • Development Environments • Database Design Concepts • SQL Concepts • PL/SQL Language Oracle Database PL/SQL 10g Programming (Chapter 1 & 2)

More Related