Database Management Systems: SQL Part 4

Questions and Answers

What is the primary purpose of the OUT parameter in a PL/SQL procedure?

To modify the value of a variable declared outside the procedure (correct)

To return multiple values to the caller of the procedure

To provide input to the procedure only

To initialize parameters before execution

Which of the following statements accurately describes an IN OUT parameter in a PL/SQL procedure?

It allows reading and modifying the parameter's value. (correct)

It is required for all PL/SQL procedures.

It serves the same purpose as an OUT parameter.

It can only be used to receive input data.

What would be the correct command to drop a PL/SQL procedure named Sum_ab?

REMOVE PROCEDURE Sum_ab;

DROP PROCEDURE Sum_ab; (correct)

ERASE PROCEDURE Sum_ab;

DELETE PROCEDURE Sum_ab;

In a PL/SQL procedure, what does the statement 'DBMS_OUTPUT.PUT_LINE('N is ' || N);' do?

<p>It concatenates 'N is ' with the variable value and prints it.</p> Signup and view all the answers

Which of the following procedures will successfully double the value of an integer parameter?

<p>CREATE OR REPLACE PROCEDURE DOUBLEN (N IN OUT INT) IS BEGIN N := N * 2; END;</p> Signup and view all the answers

Study Notes

Procedural Language SQL

PL/SQL allows for the creation of procedures to perform specific tasks, such as printing output to the screen.

Creating a Simple Procedure

A basic example is the procedure to print "hello World":

Code snippet:

CREATE OR REPLACE PROCEDURE PrintH IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello World');
END;

Parameters in Procedures

Parameters are optional and can be declared similarly to variables.
- Example: (X INT) defines an integer parameter.

Displaying Parameter Value

To display the value of an integer parameter, a procedure can be defined as follows:
- Code snippet:
```
CREATE OR REPLACE PROCEDURE DISPN(N INT) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('N is ' || N);
END;
```

Types of Parameters

IN, OUT, IN OUT are the types of parameters:
- IN: Default that passes data to the procedure.
- OUT: Used to retrieve data from the procedure.
- IN OUT: Reads and then modifies the parameter.

Example of OUT Parameter

A procedure that returns the sum of two integers using an OUT parameter:

Code snippet:

CREATE OR REPLACE PROCEDURE Sum_ab(A INT, B INT, C OUT INT) IS
BEGIN
    C := A + B;
END;

To call this procedure within a PL/SQL program:

Code snippet:

DECLARE
    X INT := &X;
    Y INT := &Y;
    Z INT;
BEGIN
    Sum_ab(X, Y, Z);
    DBMS_OUTPUT.PUT_LINE('the sum of ' || X || ' and ' || Y || ' is ' || Z);
END;

IN OUT Parameter Example

An example of a procedure that doubles a number:

Code snippet:

CREATE OR REPLACE PROCEDURE DOUBLEN(N IN OUT INT) IS
BEGIN
    N := N * 2;
END;

Dropping Procedures

Use the following format to drop a procedure:
- Code snippet: DROP PROCEDURE procedure_name;

Description

This quiz covers the basics of PL/SQL, focusing on how to create procedures and use parameters in your code. You'll learn how to write simple PL/SQL programs that display outputs, such as printing 'hello world' on the screen. Test your understanding of procedural language concepts in the context of database management systems.