Menu
homeSocial network

We work with Oracle. Interaction of application components for the web. Creating Java Stored Programs in Oracle

Year of manufacture: 2002

Publisher: Gelios ARV

Format: PDF

The book "Working with Oracle" is quick introduction into methods and tools of the distributed Oracle DBMS. Considered methodological basis distributed information processing, the main objects of the Oracle database, the SQL language - the basic means of interaction with the database server and its procedural extension PL/SQL. There is an opportunity to gain in-depth knowledge of using SQL to work with large databases data. Special attention focused on Oracle technologies that ensure data security and integrity under multi-user access conditions. The book also contains a description of tools designed to create applications in the Java language. The book is aimed at students, young professionals and everyone who wants to get acquainted with Oracle - a distributed DBMS for efficient processing data.

ABC of Oracle

Preface to the second edition

Acknowledgments

Section 1. Architecture of distributed data processing systems

Data processing tools: evolution of ideas and systems

The evolution of relational DBMS against the background of the history of Oracle

Oracle 8 . Key Features

Data Processing Architectures

Local area networks as a data transmission medium

Reference model interaction open systems

Components distributed system and EMVOS

Configuring Oracle Network Components

Oracle Server Architecture

Using the SQL*Plus Workbench

Information about the results of the operation

Multilingual support in Oracle

Conventions adopted to describe commands

Section 2. SQL - Oracle Data Processing Language

Oracle Core Objects

SQL Data Manipulation Tools

Request structure

The simplest queries

Formation of selection criteria,

Basic means of determining selection criteria

Oracle Data Description Language

Oracle Data Types

Character strings

Numeric types

ROWID

Bit strings

date and time

LOBs

Tables. Representation. Users

Creating and Deleting Tables in Oracle

Means for defining and destroying views

User registration and exclusion tools

Row insertion operation

Row deletion operation

String modification operation

SQL Special Predicates

Predicate IN

Predicate BETWEEN

LIKE predicate

Predicate IS NULL

Predicate EXISTS

Predicates with quantifiers ALL, ANY and SOME

Set-theoretic operations

Outer join

Sorting

Hierarchies

Grouping and Aggregate Functions

Query language syntax

Connections with remote bases data. Data Snapshots

Creating links to a remote Oracle database

Tools for identifying and destroying images

Sequences. Synonyms

Creating Sequences

Creating Synonyms in Oracle

Working with Table Areas in Oracle

Section 3. PL/SQL - a procedural extension of the SQL language

PL/SQL Program Structure

Variables, constants and types

Program execution control

Branch operator

Loop statements

GOTO statement

Cursors

Exception Handling

Procedures, functions and packages

Oracle SQL Functions

Functions that match numeric codes and symbols

Substring character conversion functions

Character string truncation and padding functions

Character string conversion functions

Functions related to substring extraction

Numerical functions related to exponentiation and logarithm

Trigonometric functions

Numeric functions related to rounding

Numeric functions related to the sign of a number

Numerical functions related to modular arithmetic

Date functions

Data type conversion functions

Argument replacement functions

Help functions

Creating Custom Procedures and Functions

Packages

Database Triggers

Standard packages Oracle

Dynamic SQL

File I/O

Job management

LOB Management

Multi-user access control

Using PL/SQL Functions in SQL Expressions

Section 4. Access Control Tools in Oracle

Analysis of the enabling infrastructure

User identification

Basic concept of access-privilege differentiation system

Granting system privileges

System privileges that define rights to work with tables and views

System privileges that determine the rights to work with procedures and triggers

System privileges that determine the rights to work with users

System privileges that determine rights to work with table areas

System privileges that determine rights to work with sequences

System privileges that determine the rights to work with synonyms

System privileges that define execution rights global action in system

System privileges that determine the rights to perform actions with other database objects

Using the PUBLIC construct and WITH ADMIN OPTION

Granting object access privileges

Managing privileges using roles

System privileges that determine rights to work with roles

Predefined Roles in Oracle

Creating roles and granting them privileges

Managing role eligibility

Revoking privileges

Revoke system privileges and roles

Revoking object access privileges

Using views to control access

Stored procedures as a means of access control

Using triggers to improve system security

Audit Tools

Audit system events

Auditing events related to object access

Stopping event logging

Audit Data Processing

User profiles as a means of increasing system security

additional information

Section 5: Creating Java Applications

Tools for building applications and organizing access to databases

Creating Applications in Java

The simplest application in Java

The simplest applet

Executing SQL statements for creating tables, entering and modifying data

Simple data sampling

Parametric queries

Stored procedures in Java

Section 6: Data Integrity Tools

Definition of a transaction and its role in a DBMS

Start and end of a transaction

SQL statements that control transactions

COMMIT WORK clause

Using the SAVEPOINT Clause

ROLLBACK WORK offer

Consistency and parallel processing

Types of locks

Export/import data

Section 7: Techniques for Improving Productivity

Optimizer

Access Method Ranking

Analyzing queries to improve their execution speed

Setting the optimization mode

Oracle Indexes Overview

Encoding SQL Expressions Efficiently

Changing the query execution plan

Partitioning tables

Partitioning indexes

Operations with sections

Index-tables

Section 8. Object Extensions in OracleS

Object types

Objects in the database

Arrays

Nested tables

Access Specifications

Object Views

Oracle 9i

Conclusion

Literature

For proper use Oracle need to have a good understanding SQL language. Well " Oracle. Programming in SQL, PL/SQL and Java"reveals the full range of capabilities of the SQL language in Oracle and a number of aspects of non-obvious features of building standard database designs.

  • PL/SQL - procedural language, developed by Oracle for writing subroutines stored in the database. PL/SQL provides the general framework procedural programming both in client applications and on the server side, including server-based routines, packages, and database triggers.
  • Java - object language , which can be used to work with Oracle in the most different configurations, including thanks to the Java machine built into Oracle, as a second language for stored procedures. Java is platform independent and can serve effective means integration of the Oracle database with other applications, including the Internet.

The course is accompanied by practical exercises that allow you to consolidate your understanding of basic concepts and master the basic technical techniques of programming in SQL, PL/SQL and Java.

Upon completion of the course Students get the opportunity to independently program Oracle in these three languages ​​to solve application development problems in a client-server architecture and in a three-tier architecture, as well as database administration tasks.

The main difference between this course and a number of others with similar topics is that the goal is to teach specific students real work with Oracle in these languages, rather than reading a formal program prepared by a third party.

The course is intended for developers, programmers and database administrators. Listeners must have good level computer literacy, and have programming experience.

Knowledge is given according to the following versions:

  • Oracle Database 8i
  • Oracle Database 9i
  • Oracle Database 10g
  • Oracle Database 11g
  • Oracle Database 12c

Course program "Oracle. Programming in SQL, PL/SQL and Java"

Introduction to Oracle SQL

1. Basic concepts

  • Databases and relational model
  • Database
  • Relational Approach to Data Modeling
  • Implementation of a relational DBMS
    Other approaches to data modeling and other types of DBMS
  • What is SQL?
  • History and standards
  • Oracle dialect SQL
  • PL/SQL

2. SQL*Plus and entering sentences in SQL

3. Example of a database "schema"

4. Creating, deleting tables and changing the structure

  • CREATE TABLE clause
  • Column Data Types
  • Clarifications in column descriptions
  • NOT NULL indication
  • Default values
  • Checking CHECK of values ​​entering the table
  • Creating tables based on the results of a database query
  • Naming tables and columns
  • Virtual Columns
  • Removing tables
  • Changing the table structure
  • Logical and technical features of deleting a column
  • Using synonyms to name tables
  • Renaming
  • Reference information about tables in the database

5. Basic Elements of DML Sentences: Expressions

  • Immediate data values ​​(literals)
  • Numeric values
  • Lines of text
  • Moments and time intervals
  • "System Variables"
  • Numeric Expressions
  • Expressions over lines of text
  • Expressions over the "point in time" type
  • Functions
  • Scalar functions
  • CASE expressions
  • Scalar query
  • Conditional Expressions
  • Special notes on missing values ​​in expressions

6. Data sampling

  • SELECT clause phrases
  • General rules for constructing a SELECT clause
  • How to process a SELECT clause
  • SELECT clause example 1
  • Example 2 SELECT clause
  • Logical integrity of SELECT clause processing
  • FROM clause of the SELECT clause
  • Options for specifying a column
  • Columns from different tables
  • Using aliases in a request
  • Subquery as a data source
  • Special case for request-connection
  • WHERE clause of SELECT clause
  • General algorithm for processing the WHERE phrase
  • Comparison operators to obtain a conditional expression
  • AND, OR and NOT connectives for combining conditional expressions
  • Conditional operator IS
  • Conditional LIKE operator
  • Conditional operator BETWEEN
  • Conditional IN operator with explicitly enumerable set
  • Conditional operator IN with a set obtained from the database
  • Comparison conditions with a subquery
  • Specifying ANY and ALL to compare against elements of a value set
  • Conditional operator EXISTS
  • SELECT clause and functions in the SELECT clause
  • Shorthand for group selection of columns
  • Expressions in the SELECT clause
  • Subqueries in the SELECT clause
  • Clarification DISTINCT
  • Features of the behavior of standard aggregate functions in the SELECT clause
  • Naming columns in query results
  • System function ("variable") ROWNUM and features of its use
  • Analytical functions
  • Expression of type reference to cursor
  • ORDER BY clause of the SELECT clause
  • The simplest sorting
  • Order by expression value
  • Specifying a column number
  • Binary and "language" string ordering
  • Features of handling missing values ​​(NULL)
  • GROUP BY and HAVING clauses of the SELECT clause
  • An example of working out the phrase GROUP BY ... HAVING
  • Missing value in grouping expression
  • Other examples
  • Specifying ROLLUP, CUBE and GROUPING SETS in the GROUP BY clause
  • CONNECT BY clause of the SELECT clause
  • Special system functions in sentences with CONNECT BY
  • Ordering the result
  • WITH clause pre-formulation of subqueries
  • Combining SELECT Clauses
  • Combining with the UNION operator
  • Combining with the INTERSECT operator
  • Combining with the MINUS operator
  • Subqueries
  • Join operation in SELECT clause
  • Types of connections
  • New syntax in version 9
  • Features of the connection operation

7. Updating data in tables

  • Adding New Rows
  • Explicitly adding a row
  • Adding Rows Retrieved by a Subquery
  • Adding to multiple tables with one statement
  • Changing existing field values
  • Using default values ​​in INSERT and UPDATE
  • Removing rows from a table
  • Selective deletion
  • Complete removal option
  • Combining UPDATE, INSERT and DELETE in one statement
  • Logical integrity of table data update operators and response to errors
  • Reaction to errors during execution
  • Recording changes in the database
  • System change number information for a line
  • Speeding up COMMIT execution

8. Quickly access past data values

  • Reading old table row values
  • Recovering tables and data from previously deleted tables

9. Circuit integrity constraints

  • Types of Circuit Integrity Constraints
  • NOT NULL constraint
  • Primary keys
  • Uniqueness of values ​​in columns
  • Foreign keys
  • Additional condition for value in string field
  • Additional condition that links values ​​in multiple fields in a row
  • Adding a restriction if there are violations
  • Suspending schema constraint checking within a transaction
  • Disable or enable circuit integrity constraints
  • Technology for enabling and disabling circuit integrity constraints
  • More complex integrity rules

10. Virtual tables (derived, displayed: views)

  • Basic ("basic") and virtual tables
  • Updating virtual tables
  • Limitations of direct data modification via virtual tables
  • Prohibiting immediate updates
  • Reducing the possibility of immediate updates
  • Virtual tables with data storage
  • Features of named virtual tables
  • Unnamed virtual tables without data storage

11. Non-scalar types for “complex” data in Oracle

  • Stored objects
  • Simple example
  • Using Object Properties and Methods
  • Using Object References
  • Collections
  • Nested tables
  • VARRAY arrays
  • XMLTYPE
  • Simple example
  • XMLTYPE data tables
  • Converting tabular data to XMLTYPE
  • Type ANYDATA

12. Auxiliary types of stored objects

  • Unique numbers generator
  • Operating system directory
  • Tables with temporary storage data
  • Link to another database
  • Subroutines
  • Indexes
  • Indexes for checking circuit integrity constraints
  • Tables with external storage data

13. Some notes on optimizing SQL statements

14. Transactions and locks

  • Transactions in Oracle
  • Examples of data locking by transactions
  • Types of locks
  • Implicit locks on DML operations
  • Impact of Foreign Keys
  • Explicit table locking (TM type) with the LOCK command
  • Explicitly locking individual table rows
  • An undocumented form of group blocking
  • Blocking by DDL clauses

15. Users (schemas) and additional means of restricting access to data

16. Tables of the system catalog (dictionary-reference)

18. Embedded SQL

  • Some examples of query writing

19. Issue employees in accordance with a higher (lower) salary

  • Question to the database
  • "Obvious" but wrong solution
  • The right decisions
  • Top-N solution (since version 8.1.5)
  • Solution using analytical ranking functions

20. Reformulating the query with HAVING

22. NOT Conditional Trap

23. Trap in NOT IN (S)

Introduction to PL/SQL

1. Basic concepts

  • Place of PL/SQL in Oracle architecture
  • General structure of a PL/SQL program

2. Basic data types and structures

  • Scalar variables
  • Numeric types
  • String types
  • Time points and intervals
  • Boolean variables
  • LOB types
  • Declaring Variables and Constants
  • Posts
  • Declaring entries in a program
  • Assignments
  • Link to types of data already available
  • Custom Subtypes

3. Expressions

4. Main control structures

  • Program branching
  • IF-THEN offer
  • IF-THEN-ELSE clause
  • IF-THEN-ELSIF proposal
  • CASE offers
  • Unconditional transfer of control
  • Cycles
  • Simple loop
  • Counting cycle (FOR)
  • Cycle on cursor (FOR)
  • WHILE loop
  • Simulating a REPEAT UNTIL loop
  • Labels in cycles and blocks

5. Subroutines

  • Local routines
  • Redefining "external" names
  • Forward announcements
  • Repeating names at the same level (overloading)
  • 6. Interaction with the database: static SQL
  • Using records instead of (list of) scalars

7. Regulating changes in the database

  • Transaction management
  • Locks
  • Autonomous Transactions

8. Built-in dynamic SQL

  • Embedded dynamic SQL statements
  • Comparative example of two ways to work with dynamic SQL

9. Using cursors

  • Explicit cursors
  • Declaring Explicit Cursors
  • Opening Explicit Cursors
  • Retrieving the result via an explicit cursor
  • Closing an explicit cursor
  • No restrictions on table changes when the cursor is open
  • Attributes for Explicit Cursors
  • Some examples of using loops and cursors
  • Cursors with table row locking
  • SELECT ... FOR UPDATE clause
  • Precautions for using locking cursors
  • Ability to change the lines selected by the cursor
  • Cursor references
  • General information
  • Example of use for structuring a program
  • Implicit cursors

10. Handling exceptions

  • Declaring Exceptions
  • Processing examples
  • Throwing exceptions
  • Coverage and distribution
  • "Internal" block exceptions
  • Using the SQLCODE and SQLERRM Functions

11. Stored procedures and functions

  • General syntax
  • Options
  • Parameter type
  • Parameter usage mode
  • Default values
  • Ways to specify actual values ​​for parameters
  • Accessing parameters and local variables in the body of a subroutine
  • Instructions for the compiler when creating subroutines
  • Stored routines and data access privileges in the database
  • Two logics for implementing access privileges to database data
  • Features of transferring privileges through roles

12. Trigger procedures

  • Creating a trigger procedure
  • Disabling trigger procedures
  • Trigger procedures for DML category events
  • Combined trigger procedures
  • Managing transactions in the body of a trigger procedure
  • The sequence of trigger procedures when there are several of them
  • Trigger procedures INSTEAD OF for inferred tables
  • Trigger procedures for DDL events
  • Trigger procedures for schema and database level events

13. Packages in PL/SQL

  • General package structure
  • Accessing package elements
  • (Global) package data
  • Mutual calls and repetitions of names
  • Package initialization
  • Pragma SERIALLY_REUSABLE

14. Calling PL/SQL Functions in SQL Statements

  • Requirements and restrictions for using user functions in SQL
  • Calling functions from packages in SQL
  • Resolving Column and Function Name Conflicts
  • Table functions in SQL

15. More complex data types: collections

  • Type declaration syntax for collections
  • Working with associative arrays
  • Creating a nested table and VARRAY array in a program
  • Adding and removing items in collections
  • Multiple actions with collections
  • Collection Conversions
  • Methods for working with collections in the program
  • Examples of using collections in the program
  • Privilege
  • Serial execution and binding of queries to arrays
  • Serial execution of similar operations: FORALL construction
  • Binding with arrays: BULK COLLECT INTO construct
  • Example for SCOTT circuit
  • Using collections in table functions (streaming implementation)
  • Simple example
  • Use for data conversion

16. Debugging procedures in PL/SQL

  • Dictionary tables
  • Routine dependencies
  • System packages
  • Package DBMS_PROFILER
  • DBMS_TRACE package
  • DBMS_UTILITY package functions
  • Package DBMS_DEBUG
  • An example of constructing a program operation profile

17. Programming systems for PL/SQL

18. PL/SQL system packages

  • Packages STANDARD and DBMS_STANDARD
  • Other system packages
  • Writing data from a program to a file and back
  • Data encryption
  • Automatically running jobs in Oracle
  • Managing the dynamic placement of objects in the library buffer
  • Manipulating large unstructured NULL objects
  • Accessing Old Data Values
  • Sending messages from a PL/SQL program
  • Opportunities for working in PL/SQL with COM Automation
  • additional information
  • A simple example of separating cursor opening and processing
  • A more complex example of work division

20. Attributes of trigger procedures at the level of the database schema and events in the DBMS

INTRODUCTION TO ORACLE PROGRAMMING IN JAVA

1. Basic concepts

  • Java's Place in Oracle Architecture
  • Relationship and relationship between PL/SQL and Java in Oracle

2. Features of Java and the operating environment for Java programs

  • Java architecture
  • Software components in a Java development environment
  • Installing the Java Development Environment
  • OS Environment

3. Creation independent programs in Java

  • An example of broadcasting and executing a program

4. Creating Java Stored Programs in Oracle

  • Additional Oracle DBMS components for working with stored programs in Java
  • Limitations on Stored Programs in Java
  • Installing, uninstalling and updating JServer/OJVM
  • Example of creating a stored Java program
  • Building with loadjava
  • Creation by SQL statement
  • Calling a loaded class
  • Working with a reference dictionary
  • Organization of reference information
  • Viewing Java Elements
  • View Sources
  • Name conversion
  • Features of the embedded JVM
  • ojvmjava interpreter

5. Elements of Java programming

  • Language Basics
  • Basic language constructs
  • Variables
  • Operators
  • Expressions, clauses and blocks
  • Transfer of control
  • Objects and simple structures
  • Classes and inheritance
  • Creating classes
  • Mechanics of using a class in a program
  • Inheritance
  • Interfaces
  • Exception Handling
  • Some Java Programming Techniques
  • GUI
  • Groups of objects (collections)
  • Streaming input and output
  • Parameterization of program operation using property sets
  • Object serializability

6. Interaction with the database via JDBC

  • Using JDBC
  • JDBC and JDBC drivers
  • JDBC Drivers in Oracle
  • Installing JDBC drivers for working with Oracle
  • Java program to test connectivity via JDBC
  • Work with Oracle data from external Java programs
  • Accessing the database through a thick OCI driver
  • Working with Oracle data from stored Java programs
  • Accessing the database through a thick driver (“native”, kprb)
  • Accessing the database through a thin driver
  • Accessing data from Oracle trigger procedures

7. Additional properties JDBC protocol

  • Connecting to a DBMS using the DataSource technique
  • A simple example of connecting using the DataSource technique
  • Example connection using JNDI service
  • Connection caching example
  • Examples of organizing logical connections
  • Changing data in the database and accessing stored routines
  • Changing data
  • Transaction management
  • Accessing stored programs
  • Query parameterization
  • Using Oracle Data Types
  • Improving the efficiency of database calls
  • Recurring requests
  • Batch execution
  • Link from the program to the cursor in the DBMS

8. Interacting with the database via SQLJ

  • Simple example program
  • Translating and executing a program with SQLJ
  • A more complex example: multiple sampling from a database
  • Using SQLJ in Java Stored Procedures
  • Example with external loading
  • Example with internal broadcast

9. Basics of building applications for the web using Java and Oracle

  • Client part: working with applets
  • An example of broadcasting and executing an applet
  • Apache Web Server
  • Communication with a web server via HTTP protocol
  • General concepts of HTTP messaging
  • Organizing a dialogue in HTML
  • Working with Java Servlets
  • Understanding Java Servlets and Servlet Containers
  • Java Servlet Device
  • An example of composing a servlet in Java
  • Example of calling a servlet
  • Example of a servlet accessing a database
  • Using the HttpServlet class
  • Working with JavaServer Pages
  • Example of composing a JSP page
  • Example of accessing a JSP page
  • Some options for building JSP pages
  • Ways to access a database from a JSP page
  • MVC model of organizing an application for the web

10. Interaction of application components for the web

  • Transferring control to components web applications each other
  • Contact us HTML pages to JavaServer pages and servlets
  • Transferring control from JavaServer pages
  • Transfer of control web components from servlets
  • Java abstractions for building a web application
  • Request and response
  • Scope
  • Servlet context
  • Predefined JavaServer Page Objects
  • Passing web application component data to each other
  • Passing data through request parameters
  • Passing Data Through Context and JavaBeans Components

11. An example of building an application for the web using Java and Oracle

  • Logon.html page
  • Servlet Logon
  • LogonError.html page
  • Main.jsp page
  • Page CompanyData.jsp and class orajava.demos.StuffData
  • Servlet Logout
  • Broadcasting classes, hosting files, and testing the application
  • additional information

12. Example of programming custom JSP markup

  • An example of using ready-made markup
  • Example of programming your own markup

At the end of the course, a final certification is carried out in the form of a test or based on grades for practical work completed during the learning process.

IN modern world it's hard to do without information technologies and their derivatives - computers, mobile phones, Internet, etc., especially in large companies and government organizations working with big amount people, and not just a couple of VIP clients, as may be the case in the case of a small company. And where there is a large number of counterparties, applicants, etc. - you cannot do without databases necessary for processing information. Naturally, the days of ledgers and cards, remembered by many from libraries, are long gone; today they are used personal computers And electronic databases data.

Today it is impossible to imagine the work of the largest companies, banks or government organizations without the use of databases and tools Business Intelligence. Databases allow us to store and access large amounts of information, and database management system(DBMS) - manage available information repositories.

IN Training center "Interface" You will learn to use database management systems effectively: quickly find necessary information, navigate the database schema, create queries, develop and create databases.

Training will allow you not only to gain knowledge and skills, but also to confirm them by passing the appropriate exams for the status of a certified specialist. Experienced specialists in DBMS Microsoft SQL Server or Oracle may be interested in learning business intelligence systems. These tasks are quite complex, using cumbersome mathematical apparatus, but they allow not only to analyze ongoing processes, but also to make predictions for the future, which is in demand large companies. This is why business analytics specialists are in demand on the market, and the level of remuneration for their work is very, very decent, although qualified database specialists, administrators and developers also cannot complain about the low level of income. Come to our courses and get a sought-after and highly paid profession. We are waiting for you!

At the end of the course, final certification is carried out in the form of a test or by giving a grade by the teacher for the entire course of study based on the grades received by the student when checking the mastery of the material being studied based on grades for practical work performed during the training process.

Training center "Interface" provides consulting services on building business process models, design information systems, development of database structure, etc.

  • Need help finding a course?
    Our goal is to provide training to professionals when and where they need it. It is possible to adjust course programs at the request of customers! We will tell you about what interests you, and not just about what is strictly fixed in the course program. If you need a course that you do not see on the schedule or on our website, or if you would like to take the course at a different time or location, please let us know at

Structured Query Language ( structured language queries) or SQL is a declarative programming language for use in quasi-relational databases. Many of the original features of SQL were taken from tuple calculus, but latest extensions SQL includes more and more relational algebra.
SQL was originally created by IBM, but many vendors have developed their own dialects. It was adopted as an American standard National Institute standards (ANSI) in 1986 and ISO in 1987. In the language standard SQL programming,ANSI stated that the official pronunciation of SQL is "es qu el". However, many database specialists used the "slang" pronunciation "Sequel", which reflects the original name of the language, Sequel, which was later changed due to conflict. brands and names from IBM. Programming for beginners.
SQL programming language was revised in 1992 and this version is known as SQL-92. 1999 was then revised again to become SQL:1999 (AKA SQL3). Programming for dummies. SQL 1999 supports objects that were not previously supported in other versions, but as of late 2001, only a few database management systems supported SQL implementations: SQL 1999.
SQL, although defined as ANSI and ISO, has many variations and extensions, most of which have their own characteristics, such as Oracle Corporation's "PL/SQL" implementation or Sybase and Microsoft's implementation called "Transact-SQL", which can be confusing to those familiar with programming basics. It is also not uncommon for commercial implementations to omit support for the main features of the standard, such data types as date and time, preferring some of their own variant. As a result, unlike ANSI C or ANSI Fortran which can usually be ported from platform to platform without major structural changes, SQL programming language queries can rarely be ported between different database systems without significant modifications. Most people in the database industry believe that this lack of compatibility is intentional, in order to provide each developer with their own database management system and tie the buyer to a specific database.
As its name suggests, the SQL programming language is designed for specific, limited purposes - querying the data contained in relational database data. As such, it is a set of programming language instructions for creating data samples, rather than a procedural language such as C or BASIC, which are designed to solve a much wider range of problems. Language extensions such as "PL/SQL" are designed to solve this limitation by adding procedural elements to SQL while maintaining the benefits of SQL. Another approach is to embed procedural programming language commands into SQL queries and interact with the database. For example, Oracle and others support Java language in the database, while PostgreSQL allows you to write functions in Perl, Tcl, or C.
One joke about SQL: "SQL is neither structured nor a language." The point of the joke is that SQL is not a Turing language. .

Select * from T
C1 C2
1 a
2 b
C1 C2
1 a
2 b
Select C1 from T
C1
1
2
C1 C2
1 a
2 b
Select * from T where C1=1
C1 C2
1 a

Given the T table, Select request* from T will display all elements of all rows in the table.
From the same table, the query Select C1 from T will display the elements from column C1 of all rows in the table.
From the same table, the query Select * from T where C1=1 will display all elements of all rows where the value of column C1 is "1".

SQL keywords

SQL words are divided into a number of groups.

The first one is Data Manipulation Language or DML(data management language). DML is a subset of the language used to query databases and add, update, and delete data.

  • SELECT is one of the most commonly used DML commands and allows the user to specify a query as a set description of the desired result. The query does not specify how the results should be arranged - translating the query into a form that can be executed in the database is the job of the database system, more specifically the query optimizer.
  • INSERT is used to add rows (formal set) to an existing table.
  • UPDATE is used to change data values ​​in an existing table row.
  • DELETE definition existing rows, which will be removed from the table.

Three others keywords Well, we can say that they fall into the DML group:

  • BEGIN WORK (or START TRANSACTION, depending on the SQL dialect) can be used to mark the start of a database transaction that will either complete all or not execute at all.
  • COMMIT states that all data changes made after operations are performed are saved.
  • ROLLBACK specifies that all data changes after the last commit or rollback must be destroyed, up to the point that was recorded in the database as a “rollback”.

COMMIT and ROLLBACK are used in areas such as transaction control and locking. Both instructions complete all current transactions (sets of operations on the database) and remove all locks on changing data in tables. The presence or absence of a BEGIN WORK or similar statement depends on the particular SQL implementation.

The second group of keywords belongs to the group Data Definition Language or DDL (Data Definition Language). DDL allows the user to define new tables and their associated elements. Most commercial SQL databases have their own DDL extensions that allow control over non-standard, but usually vital, elements of a particular system.
The main points of the DDL are the create and delete commands.

  • CREATE specifies the objects (such as tables) that will be created in the database.
  • DROP specifies which existing objects in the database will be deleted, usually permanently.
  • Some database systems also support the ALTER command, which allows the user to change existing object in different ways - for example, this is how you can add columns to an existing table.

The third group of key SQL words is Data Control Language or DCL (Data Control Language). DCL is responsible for data access rights and allows the user to control who has access to view or manipulate data in the database. There are two main keywords here:

  • GRANT - allows the user to perform operations
  • REVOKE - Removes or restricts the user's ability to perform operations.

Database systems using SQL

How to become a website development professional and start making money? Inexpensive video courses with an introductory introduction.