1 / 9

Database Programming

Database Programming. Using databases from applications Using Stored Procedures. Using Databases in Applications. Embedded SQL EXEC SQL statement Requires language specific pre-processor Library of database functions ADO.NET ODBC, JDBC Connect, execute SQL statements, disconnect

miles
Download Presentation

Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Programming Using databases from applications Using Stored Procedures UCN T&B / PBA DB

  2. Using Databases in Applications • Embedded SQL • EXEC SQL statement • Requires language specific pre-processor • Library of database functions • ADO.NET • ODBC, JDBC • Connect, execute SQL statements, disconnect • Both types can use stored procedures UCN T&B / PBA DB

  3. Stored Procedures? Procedures or functions stored in the database Executes on the database server UCN T&B / PBA DB

  4. Why? • A stored procedure can be used by several applications • Reduces code duplication and maintenance • Reduce data transfer between client and server • Intermediate results that the client does not need do not have to be marshaled or transferred between server and client • Multiple rounds of query parsing can be avoided • Shift workload from client to server • More complex modelling than what views offer • Result: Considerable performance increase as compared to an application that does not use stored procedures/functions UCN T&B / PBA DB

  5. Stored Procedure Syntax General syntax: CREATE PROCEDURE <procedure name>(<parameters>) <local declarations> <procedure body>; CREATE FUNCTION <procedure name>(<parameters>) RETURNS <return type> <local declarations> <procedure body>; UCN T&B / PBA DB

  6. Using a Stored Procedure Stored procedures can be invoked from the usual SQL interfaces: CALL <procedure or function name> (<argument list>); UCN T&B / PBA DB

  7. SQL/PSM Stored Procedure Language Constructs IF <condition> THEN <statement list>ELSEIF <condition> THEN <statement list>ELSE <statement list> END IF; WHILE <condition> DO<statement list> END WHILE; REPEAT<statement list> UNTIL <condition> END REPEAT; FOR <loop name> AS <cursor name> CURSOR FOR <query> DO<statement list> END FOR; UCN T&B / PBA DB

  8. Stored Procedure Example UCN T&B / PBA DB

  9. Languages for Stored Procedures • Standard SQL • SQL/PSM (SQL/Persistent Stored Modules) • Oracle • PL/SQL • MS SQL Server • Transact-SQL • PostgreSQL • PL/pgSQL • PL/Tcl • PL/Perl • PL/Python • MySQL UCN T&B / PBA DB

More Related