[Ad-Oracle-Sql] 4. Introduction to PL/SQL

📋 This is my note-taking from what I learned in the class “Advanced Database Concepts”

Objective

• PL/SQL and application programming
• Application models
• How to locate Oracle resources
• SQL and PL/SQL tools
• The databases used in this book
• SQL SELECT statement and data manipulation syntax

Procedural Languages

• Programming languages allow actions of the end user to be converted to computer instructions
• Procedural languages allow the inclusion of logic processes
• PL/SQL is a procedural language, SQL is not a procedural language

Application Programming

Example application screen

Brewbean’s Application

Processing needed to support the shopping cart check out button:

• Verify quantities are > 0
• Calculate shipping cost
• Calculate taxes
• Check/update product inventory
• Check shopper profile for credit card information

The PL/SQL Language

• Proprietary Oracle language
• Tightly integrated with SQL
• Can increase performance by grouping statements into blocks of code
• Portable to any Oracle platform
• Used within many Oracle tools
• Stored program units can increase security

Application Models

• Three main components

  • User interface or screens

  • Program logic (brains behind the screens)

  • Database

• Most models are based on a two- or three-tier structure

Two-tier Model

• Commonly referred to as client/server
• Parts of the processing occur both on the user’s computer and the database server
• Named or stored program units are blocks of PL/SQL code saved in the Oracle database to provide server-side processing

Two-tier Diagram

Three-tier Model

• Thin client with no code loaded on the user machine (browser access)
• Middle tier is the application server – Forms server for Oracle
• Last tier is the database server
• Processing load is on the middle and last tier
• Maintenance is simplified

Three-tier Diagram

Oracle Documentation

• Oracle Technology Network (OTN): otn.oracle.com

  • Documentation

  • Sample Code

  • Discussion Forums

• User Web sites: PL/SQL Obsession

SQL & PL/SQL Tools

• SQL*Plus

• SQL Developer

  • Appendix B

• Other software introduced in appendices

  • TOAD

  • SQL Navigator

SQL*Plus Client Interface

SQL Developer

Databases Used

• Brewbean’s Company

  • In text examples

  • Assignments

• DoGood Donor

  • Assignments

• More Movie Rentals

  • Case Projects

The Brewbean’s Company

• Retails coffee and brewing equipment via the Internet, phone, and stores
• Used in chapter explanations, examples, and exercises
• Databases create script provided for each chapter

ERD for Brewbean’s DB

DoGood Donor ERD

More Movies ERD

SQL Query Syntax

SELECT <columns>
	FROM <tables, views>
	WHERE <conditions>
	GROUP BY <columns>
	HAVING <aggregation conditions>
	ORDER BY <columns>;

Traditional Join

ANSI Join

Aggregate function

WHERE clause filter

Creating Tables

DML - Insert

DML - Update

DML - Delete

Drop Table

---

Back to Top

See other articles in Category Ad-Oracle-Sql