Database Programming with PL/SQL
1-3: Creating PL/SQL Blocks
Practice Activities
Vocabulary
Unnamed blocks of code not stored in the database and do not exist after they are executed - Anonymous PL/SQL block
A program that computes and returns a single value - Functions
Named PL/SQL blocks that are stored in the database and can be declared as procedures or functions - Subprogram
Software that checks and translates programs written in highlevel programming languages into binary code to execute - Compiler
A program that performs an action, but does not have to return a value - Procedures
Try it / Solve it
1. Complete the following chart defining the syntactical requirements for a PL/SQL block:
Optional or Mandatory? Describe what is included in this section
DECLARE optional declaration of the variables
BEGIN mandatory the executable part
EXCEPTION optional an error returned
END; mandatory the end of the executable part
2. Which of the following PL/SQL blocks executes successfully? For the blocks that fail, explain why
they fail
A. BEGIN
END;
Fails because the executable section must contain at least one statement.
B. DECLARE
amount INTEGER(10);
END;
Fails because there is no executable section (BEGIN is missing).
C. DECLARE
BEGIN
END;
Fails because the executable section must contain at least one statement.
D. DECLARE
amount NUMBER(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(amount);
END;
Succeeds
3. Fill in the blanks:
A. PL/SQL blocks that have no names are called anonymous blocks
B.
Procedures and Functions are named blocks and are stored in thedatabase.
4. In Application Express, create and execute a simple anonymous block that outputs “Hello World.”
BEGIN
DBMS_OUTPUT.PUT_LINE ('Hello World');
END;
5. Create and execute a simple anonymous block that does the following:
• Declares a variable of datatype DATE and populates it with the date that is six months from today
• Outputs “In six months, the date will be: <insert date>.”
declare var DATE:= ADD_Months(SYSDATE,6);
begin
DBMS_output.put_line('In six months, the date will be: '|| var||'.');
end;
0 Comments