SQL DATABASE PROGRAMMING (PL/SQL AND T-SQL)

25  Download (0)

Full text

(1)

SQL DATABASE PROGRAMMING

(PL/SQL AND T-SQL)

(2)

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

(3)

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

(4)

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

(5)

1. INTRODUCTION TO ORACLE/SQL SERVER

DATABASE PROGRAMMING

(6)

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.

(7)

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.

(8)

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

(9)

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

(10)

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.

(11)

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

(12)

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

(13)

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

(14)

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

(15)

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.

(16)

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,

(17)

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

(18)

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

(19)

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.

(20)
(21)
(22)
(23)

1.4 GRAPHICAL DATABASE MANAGEMENT TOOLS

Oracle SQL Developer  Free, Oracle Java-based Database Management tool.

(24)
(25)

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

Figure

Updating...

References

Related subjects : SQL and Programming