Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.1 SQL commands (PL / pgSQL)
Sample Question
3.44
A stored program was defined by executing the following:
declare
begin
raise info 'Hello World!';
return x * 2;
end;
$$ language plpgsql;
Choose three appropriate descriptions for the defined program.
-
To execute, you can use 'call sample_func(10)'.
-
To execute, you can use 'select sample_func(10)'.
-
When executed from a terminal, it displays 'Hello World!'.
-
sample_func(10) returns a value of 20.
-
When executed, an exception occurs and the command terminates abnormally.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/02
Answer and Explanation
In PostgreSQL, functions and procedures can be defined as stored programs using procedural languages such as PL/pgSQL or SQL. This example defines a function of the integer data type named sample_func in PL/pgSQL.
Stored programs include functions that return values (FUNCTION) and procedures that do not return values (PROCEDURE). Strictly speaking, there are also functions of type VOID that do not return a value. The main difference in usage is that functions are used in places where a value is required in SQL, such as SELECT, whereas procedures are executed with the CALL command. The example is a function, so it can be executed from SELECT.
PL/pgSQL programs start with DECLARE, which defines variables, followed by BEGIN, which is the execution part, and then END. In the example, RAISE and RETURN are executed.
RAISE is a command used to display messages or report errors, which can be used for debugging programs. The 'info' command instructs to display a message on the screen. If 'exception' is used instead of 'info', an exception is raised and the program terminates.
RETURN specifies the value to be returned as the return value of the function. When sample_func(10) is called, x is 10, so x*2, or 20, is returned.
Therefore, the correct answers are B, C, and D.
© EDUCO All Rights Reserved.