chPL_SQLPart1

chPL_SQLPart1 - 1 Introduction to PL/SQL IS 620 Advanced...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Introduction to PL/SQL IS 620 Advanced Database Projects 2 Roadmap n Motivation for PL/SQL n Anonymous vs. named PL/SQL subprogram n Basic PL/SQL features: n Basic structure n Variables and Assignment n Control structures (if then, loop) n Using SQL in PL/SQL n Insert, update, delete n Implicit cursor (select) n Explicit cursor (select) 3 Is SQL Powerful Enough? n Do you need to write multiple SQL statements for a task? n E.g., to transfer a certain amount from your checking account to saving, only transfer when there is enough balance in checking. n Does SQL have variables, loops, if then else …? n How can others use the SQL statements you developed? 4 Why PL/SQL n Real life example: E.g., a credit bureau provides a credit scoring service. n The company collects personal financial data from credit card companies, banks, …., and store the data in a database n Customers can ask the credit score of some person n The score is computed from the data using a complex formula (a top commercial secret) n Can we just use SQL? What's my credit score 700! 5 Why PL/SQL n Solution 1: embed SQL into a procedure language such as JAVA or C n E.g., JDBC, ODBC n Client program sends SQL to DB server and gets back intermediate results n Client program then computes credit score n Problems? What's my credit score 700! SQL Intermediate results JAVA program 6 Why PL/SQL n Oracle Procedure Language/SQL n SQL + procedure language features such as variables, IF…THEN, loop, function or procedure, etc. n A named PL/SQL program is stored within the database, and can be used by other users n Benefits n Communication cost n Portability n Security n Learning curve What's my credit score? Call a PL/SQL function on database server 700! 7 Why PL/SQL n Oracle Procedure Language/SQL n Downside? n Which DMBS to use? n Does PL/SQL provide a GUI? What's my credit score? Call a PL/SQL function on database server 700! 8 Roadmap n Motivation for PL/SQL n Anonymous vs. named PL/SQL subprogram n Basic PL/SQL features: n Basic structure n Variables and Assignment n Control structures (if then, loop) n Using SQL in PL/SQL n Insert, update, delete n Implicit cursor (select) n Explicit cursor (select) 9 Anonymous PL/SQL Programs n Program has no name n Code is NOT stored in database (discarded after execution) n Code can be stored as a script file in local file system, but you have to manually save & load it....
View Full Document

This note was uploaded on 02/28/2012 for the course IS 620 taught by Professor Chen,z during the Spring '08 term at UMBC.

Page1 / 123

chPL_SQLPart1 - 1 Introduction to PL/SQL IS 620 Advanced...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online