SQL DATABASE PROGRAMMING
(PL/SQL AND T-SQL)
Michael Kremer
Currently: Federal Reserve Bank San Francisco
Previously: Lawrence Berkeley National Laboratory
Department of Energy
Database/Application Developer
dBase, Access Developer for over 20 years
Instructor for UC Extension since 1998
DB: Oracle, SQL Server, Access
Prog.: ASP.net. C#, VB/VBA, Java/Javascript
Reporting: Cognos, Actuate
Name/Company/Organization
What do you do?
Computer Experience (OS, Application SW,
Other Classes Taken, etc.)
Database Platform/Experience
Expectations/Goals
Any other information about you such as
1. Introduction to Oracle/SQL Server Database
Programming
1.1 SQL and Database Programming
1.2 Structure of Database Programs
1.3 Differences between Oracle and SQL Server
1.4 Graphical Database Management Tools
1. INTRODUCTION TO ORACLE/SQL SERVER
DATABASE PROGRAMMING
1.1 SQL AND DATABASE PROGRAMMING
Limitations of non-procedural SQL lead to programmatic
extensions
SQL is set-based and non-procedural, you specify the What but
not the how.
You specify your requirement for a processed result to be
obtained from a set of data.
Database engine internally resolves exactly how to get to the end
result.
Using a Procedural approach, you specify the What and the How. You write data operational and manipulation logic using loops,
conditions, and processing statements to produce the final result.
1.1 SQL AND DATABASE PROGRAMMING
Using a cursor that executes on a result set row by row is a
procedural approach.
Also using a user-defined function in your SQL is procedural. Whenever possible, use non-procedural SQL since internal
database engine is optimized for set-based processing.
When using procedural SQL on a row-by-row basis the database
engine optimizes each row separately causing significant overhead (I/O processing).
The underlying message here is to minimize the amount of
procedural SQL due to performance reasons.
Many procedural SQL code can be rewritten into non-procedural
SQL statements using subqueries, With clause, and other sophisticated constructs.
1.2 STRUCTURE OF DATABASE PROGRAMS
Structure of database programs is similar to other procedural
programs.
Header: Program has a distinct name, you may pass arguments
into it, and it also may return values back to the calling environment.
Declaration: Variables and other
special directives.
Execution: Actual programming
code.
Exception: Handling of errors.
Header IS Begin Exception End; Declaration Execution Exception Header AS Begin Try End Catch Execution Exception Begin Catch End Try Oracle PL/SQL SQL Server T-SQL
1.2 STRUCTURE OF DATABASE PROGRAMS
Breaking up large code into
smaller, manageable units.
Oracle only:
Nesting of procedures
Procedures are nested
within a main program.
In SQL Server, use procedures
that can be called from each other. Header IS Begin End; Declaration Execution Oracle PL/SQL Begin End; Execution Header IS Declaration M a in P ro g ra m Sub P ro g ra m
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
Versions and Editions
SQL Server runs only on Windows platform, whereas Oracle runs
additionally on Unix/Linux.
Current Versions: SQL Server 2012, Oracle 12c R1 SQL Server Editions:
Enterprise: Mission critical applications and data
warehousing
Business Intelligence: Premium corporate and self-service
Business Intelligence capabilities
Standard: Basic database capabilities, reporting and
analytics.
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
Oracle Editions:
Enterprise: Top performance for top money, all features
enabled
Standard: Major features enabled, suitable for most
business applications,
Standard Edition One: For small workgroups, licensed for
min. 5 users.
Personal Edition: Single-user development and deployment
environments, full Oracle version
Express: Low footprint,
small-scale, starter DB
SQL Server 2012 Oracle 12c R1 Enterprise Edition Enterprise Edition Business Intelligence Edition Standard Edition Standard Edition Standard Edition One Express Edition Express Edition Developer Edition Personal Edition
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
Instances and Databases/Tablespaces
Major architectural difference between SQL Server and Oracle:
Instance and Database.
SQL Server Instance: Self-contained application service
involving operating system files, memory structures,
background processes and registry information Service running in Windows.
SQL Server database is repository of data and program code. If
instance not running, database cannot be accessed.
Oracle instance is comprised of memory structures (SGA) and
background processes. An instance can be running without a database running.
Oracle database is a collection of OS files, Oracle db does not
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
Oracle files: Data files, Redo log, Control file
SQL Server files: Database files, Transaction log, no control file! Oracle instance starts (without a database), then connecting to
database.
SQL Server instance cannot start
without its system database being online.
Oracle and SQL Server database
cannot be accessed without an instance running. Logical Physical Oracle: Tablespaces SQL Server: Databases Oracle: Segments SQL Server: N/A Oracle: Extents SQL Server: Extents
Oracle: Data Blocks SQL Server: Pages
Oracle: Data Files SQL Server: DB Files
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
In general, one-to-one relationship between instance and DB. SQL Server logical grouping is done by the database itself. In Oracle, it is done through tablespaces. Tablespaces are
logical structures that group tables, views, indexes, SP, etc. together.
Tablespace for payroll, within HR, for example. Oracle Tablespace = SQL Server Database
Databases and tablespaces do differ in the following areas:
SQL Server database files can be logically grouped into filegroups. In
Oracle, no such concept for tablespaces.
Each SQL Server database has its own transaction log and log file. In
Oracle, one transaction log for all tablespaces.
SQL Server database can be configure for simple recovery mode. In
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
Instance Name vs. SIDs
Both SQL Server and Oracle allow multiple instances to run
concurrently on the same machine.
SQL Server: Default instance (backward compatibility for version
2000) and named instances.
Oracle: System ID (SID) names the instance. SQL Server and Oracle
instances must be unique and cannot be changed later.
SQL Server Host Oracle Host
Instance: Self-contained
application service involving os files, memory, processes.
Database: System
databaes(needed for instance to run) and user databases.
Schema: Logical
grouping of database objects, owner and user of objects
Instance: Only memory
allocation and processes.
Database: Collection of
OS files.
Tablespace: Logical
structure for grouping db objects.
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
To find out the instance name:
SQL Server: SELECT @@SERVERNAME
Oracle: SELECT instance_name, host_name, version,
database_status FROM V$INSTANCE
System Databases and System Tablespaces
One SQL Server Instance needs 5 databases:
Master and Resource db are central repositories for SQL Server to
manage itself.
Model db is template used for every new db created.
Tempdb is kind of scratchpad, created when instance is started and
destroyed when it is shut down.
msdb is used for SQL Server agents, such as job scheduling, alerts,
1.3 DIFFERENCES BETWEEN ORACLE AND SQL SERVER
One Oracle database needs 3 tablespaces:
SYSTEM tablespace is similar to SQL Server master db. It holds the data
dictionary about the database itself (Meta data).
TEMP tablespace is used for sort operations.
SYSAUX tablespace is used for Oracle’s Automatic Workload Repository
(AWR), spatial and multimedia data, XML database, etc.
SQL Server (since version 2008) uses tempdb for row versioning
(consistent read).
Oracle rebuilds previous row version data from UNDO
tablespace.
UNDO tablespace is used for several features: ROLLBACK, READ
1.4 GRAPHICAL DATABASE MANAGEMENT TOOLS
Graphical Database Management Tools:
Oracle SQL Developer
SQL Server Management Studio
SQL Server Management Studio (SSMS)
Hierarchical structure contains the following nodes: Databases:
System Databases
Additional nodes
1.4 GRAPHICAL DATABASE MANAGEMENT TOOLS
Security: Manage users, roles, credentials
Server Objects: Backup devices, linked servers, and server
triggers.
Replication: For managing database replication
Management: Policy management, server logs, legacy services
such as DTS (replaced by SISS) in 2008.
Most important node: Database node
Under the Database node, for the scope of this course, the
Programmability node.
1.4 GRAPHICAL DATABASE MANAGEMENT TOOLS
Oracle SQL Developer Free, Oracle Java-based Database Management tool.1.4 GRAPHICAL DATABASE MANAGEMENT TOOLS
Organization of database objects is very different compared to SQL
Server.
Most important nodes for the scope of this course are: Procedures
Functions Triggers
