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:
Define the term PL/SQL and describe some of the common tools used to develop PL/SQL
Identify different types of variables
Identify variable datatypes
Use the non-PL/SQL bind and host variables to manipulate data within SQL*Plus
DBMS_OUTPUT.PUT_LINE package procedure to display values to the SQL*Plus screen
Illustrate the importance of naming conventions for application code
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;
6 SELECT commission_pct / 100 INTO commission
7 FROM employees
8 WHERE employee_id = emp_id;
10 IF commission IS NULL THEN
11 RAISE comm_missing;
13 UPDATE employees
14 SET salary = salary + bonus*commission
15 WHERE employee_id = emp_id;
16 END IF;
18 WHEN comm_missing THEN
20 ('This employee does not receive a commission.');
21 commission := 0;
22 WHEN OTHERS THEN
24 END award_bonus;
In this module you were introduced to the following glossary terms:
- 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.
- code walkthrough
- Global: Globals do not respect the boundaries of program interfaces. You can read and write globals without using the parameter list of a module.
- Package: A database program consisting of one or more procedures. A package executes when called using SQL or a programming language interface.
- 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.
- 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.