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
- Use the
DBMS_OUTPUT.PUT_LINE package procedure to display values to the SQL*Plus screen
- 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;
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: 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.
- 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.