PL/SQL Database Programming PDF

Database Programming with PL/SQL 1-2 Benefits of PL/SQL Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: List and explain the benefits of PL/SQL List the differences between PL/SQL and other prog...

Database Programming with PL/SQL 1-2 Benefits of PL/SQL Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: List and explain the benefits of PL/SQL List the differences between PL/SQL and other programming languages Give examples of how to use PL/SQL in other Oracle products PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 3 Benefits of PL/SQL Purpose PL/SQL is a programming language suitable for several tasks involving an Oracle database. In this lesson, you learn about the benefits of the PL/SQL programming language and how it compares to other programming languages. You also learn how PL/SQL relates to other Oracle products. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 4 Benefits of PL/SQL Benefits of PL/SQL There are many benefits to using the PL/SQL programming language with an Oracle database. Integration of procedural constructs with SQL Modularized program development Improved performance Integration with Oracle tools Portability Exception handling PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 5 Benefits of PL/SQL Benefit 1: Integration of Procedural Constructs With SQL The primary advantage of PL/SQL is the integration of procedural constructs with SQL. SQL is a nonprocedural language. When you issue an SQL command, your command tells the database server what to do. However, you cannot specify how to do it or how often to do it. PL/SQL integrates control statements and conditional statements with SQL. This gives you better control of your SQL statements and their execution. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 6 Benefits of PL/SQL Benefit 2: Modularized Program Development The basic unit in a PL/SQL program is a block. All PL/SQL programs consist of blocks. You can think of these blocks as modules and you can “modularize” these blocks in a sequence, or nest them in other blocks. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 7 Benefits of PL/SQL Benefit 2: Modularized Program Development Modularized program advantages: You can group logically related statements within blocks. You can nest blocks inside other blocks to build powerful programs. You can share blocks with other programmers to speed up development time. PL/SQL allows you to break down a complex application into smaller, more manageable, and logically related modules increasing ease of maintenance and debugging. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 8 Benefits of PL/SQL Benefit 3: Improved Performance PL/SQL allows you to logically combine multiple SQL statements as one unit or block. The application can send the entire block to the database instead of sending the SQL statements one at a time. This significantly reduces the number of database calls. SQL 1 SQL 2 SQL; IF...THEN SQL ELSE SQL; END IF; SQL; PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 9 Benefits of PL/SQL Benefit 4: Integration With Oracle Tools PL/SQL is integrated in Oracle tools, such as Oracle Forms Developer, Oracle Report Builder, and Application Express. SQL; IF...THEN SQL ELSE SQL; END IF; SQL; PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 10 Benefits of PL/SQL Benefit 5: Portability PL/SQL programs can run anywhere an Oracle server runs, regardless of the operating system and the platform. PL/SQL programs do not need to be tailored for different operating systems and platforms. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 11 Benefits of PL/SQL Benefit 5: Portability You can write portable program packages and create libraries that can be reused on Oracle databases in different environments. You can even anticipate those differences and establish instructions to run a specific way given a specific environment. HP Tru64 Solaris Linux IBM z/OS PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 12 Benefits of PL/SQL Benefit 6: Exception Handling An exception is an error that occurs when accessing a database. Examples of exceptions include: – hardware or network failures – application logic errors – data integrity errors, and so on. You can prepare for these errors by writing exception handling code. Exception handling code tells your application what to do in the event of an exception. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 13 Benefits of PL/SQL Benefit 6: Exception Handling PL/SQL allows you to handle database and program exceptions efficiently. You can define separate blocks for dealing with exceptions. If no data is found then… If too many rows are found then… If an invalid number is calculated then… In this way, your application can handle the error, communicating the problem to the user, without causing a system crash. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 14 Benefits of PL/SQL PL/SQL Compared to Other Languages PL/SQL C Java Requires Oracle Yes No No database or tool Some Object-oriented No Yes features Performance against Less Very efficient Less efficient an Oracle database efficient Portable to different Yes Somewhat Yes operating systems Relatively More Ease of learning More difficult easy difficult PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 15 Benefits of PL/SQL PL/SQL in Oracle Products Oracle Product PL/SQL You can write PL/SQL code to manage application data or to manage the Oracle database itself. For example, you can write code for updating data (DML), creating data (DDL), generating reports, managing security, and so on. Using the Web Application Toolkit, you can create database-centric web applications written entirely or partially in PL/SQL. Using Forms Builder and Reports Developer, Oracle’s client-side developer tools, you can build database-centric web applications and reports that include PL/SQL. Using a Web browser you can develop web applications that include PL/SQL. PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 16 Benefits of PL/SQL Terminology Key terms used in this lesson included: Blocks Portability Exceptions PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 17 Benefits of PL/SQL Summary In this lesson, you should have learned how to: List and explain the benefits of PL/SQL List differences between PL/SQL and other programming languages Give examples of how to use PL/SQL in other Oracle products PLSQL S1L2 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 18 Benefits of PL/SQL