Oracle SQL

Oracle SQL

SQL = Structured Query Language

All relational databases, such as Oracle and SQL Server, use their own version of Structured Query Language (SQL) to allow users to interact with the data in the database. This could be adding data, deleting, updating or just viewing it. In general the SQL used in Oracle and by other manufacturers of database software looks very similar. Most of it is, but there are differences.

SQL Top Tips

  • Learn SQL and you will be able to interact with any relational database
  • Don’t become dependent on using GUIs – a SQL command prompt will always be available to use, so know how to write SQL from scratch and where the Oracle documentation is
  • Always use SQL if you can, instead of writing PL/SQL, as straight SQL is faster and more efficient
  • Take advantage of the specific SQL functions only available in your relational database software, such as Oracle or SQL Server; they are usually the most powerful tools available to you
  • If you only write portable code you are not using the benefits of the proprietary SQL language differences and will be at a disadvantage
  • If you are writing SQL you should know how to generate and read an explain plan

Oracle SQL Details

You have four main types of statement in Oracle

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Control Language (DCL)
  4. Transaction Control (TCL)

DDL – Used to create, drop or alter objects. For example, a CREATE TABLE statement is DDL

DML – Allows you to manipulate, add or delete data. For example, INSERT, UPDATE or DELETE SQL statements are DML

DCL – Permission control, such as GRANT and REVOKE for tables

TCL – Used to manage transactions. For example, COMMIT to save, ROLLBACK to undo, SAVEPOINT to create a place holder you can return to

In the Oracle database you must either COMMIT or ROLLBACK a transaction for it to be complete. COMMIT is the same as saying “please save the changes I have made”. ROLLBACK is the same as saying “please undo the changes I just made”.

Note: When you run any DDL command Oracle will automatically commit any transactions which were in progress.

When you are writing more complex SQL statements which have ordering and sorting commands in them you will want to learn about monitoring TEMP space. And on a similar note, if you are updating large numbers of rows you should check out my article on UNDO monitoring.

PL/SQL = Procedural Language Structured Query Language

PL/SQL is an extension of Oracle SQL which allows the user to use programming logic, such as IF statements, LOOPs, etc in combination with SQL to provide a very powerful language to manipulate the data with.

PLSQL Top Tips

  • Use PLSQL to efficiently interact with the data in your Oracle database
  • Using PL/SQL packages to perform common operations, such as adding orders to an order entry system, helps to build a scalable system
  • Keeping the functions used for adding data within the database helps to maintain data integrity and results in only one place where the code changes, thereby reducing code development confusions

Further Reading

Learn some fundamentals of Oracle SQL from W3 Schools

Like it, share it...

Category: SQL

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *