Loading Consent Dialog

PL/SQL Programming   «Prev  Next»

Lesson 8

Building Queries with PL-SQL Conclusion

This module discussed the tools that you to use to build your queries with PL/SQL. Then you learned about the different variable types that are used within PL/SQL. Further, you enhanced your knowledge of non-PL/SQL bind and host variables. You also reviewed the DBMS_OUTPUT.PUT_LINE package procedure to display the output to your screen. Finally, you learned the importance of using standard naming conventions while writing your code.

In this module, you learned how to:
  1. Define the term PL/SQL and describe some of the common tools used to develop PL/SQL
  2. Identify different types of variables
  3. Identify variable datatypes
  4. Use the non-PL/SQL bind and host variables to manipulate data within SQL*Plus
  5. Use the DBMS_OUTPUT.PUT_LINE package procedure to display values to the SQL*Plus screen
  6. Illustrate the importance of naming conventions for application code

Oracle PL/SQL Programming

Creating a Standalone PL/SQL Procedure

SQL> CREATE OR REPLACE PROCEDURE award_bonus (
2	emp_id NUMBER, bonus NUMBER) AS
3 commission REAL;
4 	comm_missing EXCEPTION;
5 BEGIN
6  SELECT commission_pct / 100 INTO commission
7   FROM employees
8    WHERE employee_id = emp_id;
9
10  IF commission IS NULL THEN
11   RAISE comm_missing;
12  ELSE
13   UPDATE employees
14   SET salary = salary + bonus*commission
15   WHERE employee_id = emp_id;
16  END IF;
17 EXCEPTION
18  WHEN comm_missing THEN
19   DBMS_OUTPUT.PUT_LINE
20    ('This employee does not receive a commission.');
21    commission := 0;
22  WHEN OTHERS THEN
23   NULL;
24 END award_bonus;
25 /
Procedure created.
SQL>

Glossary

In this module you were introduced to the following glossary terms:
  1. Block: A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size.
  2. code walkthrough: Code Walkthrough is a form of peer review in which a programmer leads the review process and the other team members ask questions and spot possible errors against development standards.
  3. Global: Globals do not respect the boundaries of program interfaces. You can read and write globals without using the parameter list of a module.
  4. Package: A database program consisting of one or more procedures. A package executes when called using SQL or a programming language interface.
  5. Procedure: A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL.
  6. Runtime: In computer science, runtime, or execution time is the time during which a program is running a.k.a. (executing).
In the next module, you will learn in detail about datatypes.