FA 3 Flashcards
These subprograms are compiled and stored in the database
Subprograms
The block structure of the subprograms is similar to the structure of ____ blocks.
anonymous
(T OR F) You cannot invoke a procedure from inside a SQL statement such as SELECT
TRUE
(T OR F) Subprograms are implicitly shared
FALSE
(T OR F) Later subprograms become the building blocks of packages and triggers.
TRUE
(T OR F) In Procedure, all parameters must not be specified
FALSE
(T OR F) The functions used in SQL statements cannot use OUT or IN OUT modes.
TRUE
In PL/SQL ______, the function identifier acts like a variable whose value depends on the parameters passed to it.
EXPRESSIONS
(T OR F) In the stored function, the parameter mode should only be IN
TRUE
The executable section of the procedure body requires a minimum of _____ statement.
1**
In anonymous block, DECLARE states “this is the start of a block”. In Procedure, ____________ states “this is the start of a subprogram”.
CREATE PROCEDURE
Subprograms are named PL/SQL blocks that are compiled and stored in the _______.
DATABASE
(T OR F) In Procedure, all parameter modes must be specified
TRUE
(T OR F) Another way of initializing a default value for parameter is by using assignment operator.
TRUE
(T OR F) The DEFAULT keyword is used to assign a default value for formal parameters.
TRUE
(T OR F) The actual is a special form of a variable, whose input values are initialized by the calling environment when the subprogram is called, and whose output values are returned to the calling environment when the subprogram returns control to the caller.
FALSE (PARAMETERS)
(T OR F) The positional parameter passing uses the associate operator.
FALSE
(T OR F) A procedure can be invoked in another procedure.
TRUE
Using an _______ parameter mode, you can pass a value into a procedure that can be updated within the procedure.
IN OUT
What is the symbol used for association operator?
=>
What is missing in the given code below?
CREATE or replace procedure raise_sal
empid my_employees.employee_id%TYPE := 1234, emp_raise number IS
BEGIN
update employees
set salary = salary + (salary * (emp_raise/100) where employee_id = emp;
END;
Procedure name after END keyword
What is the error of the given code?
BEGIN
add_dept(name =>’new dept’, ‘new location’);
END;
Positional parameter must come before named parameter**
The ____ parameters can be literal values, variables, or expressions that are sent to the parameter list of a called subprogram.
ACTUAL
Functions return only a single value, and the value is returned through a ______ statement.
RETURN
The functions used in SQL statements cannot use _____ or IN OUT modes.
OUT
The purpose of the OR REPLACE clause is to ______ an existing procedure.
OVERWRITE
The _______ is added to CREATE PROCEDURE clause to overwrite an existing procedure.
OR REPLACE
(T OR F) A procedure is compiled and stored in the database as a schema object.
TRUE
(T OR F) The DECLARE keyword is not used in a subprogram.
TRUE
(T OR F) The Positional parameter passing is the common method of passing parameters.
TRUE
(T OR F) To invoke a procedure from another procedure, use a direct call inside an executable section of the block.
TRUE
(T OR F) Another way of initializing a default value for parameter is by using assignment operator.
TRUE
What is missing in the procedure header?
CREATE PROCEDURE raise_sal(p_id IN NUMBER, p_sal IN NUMBER) IS
BEGIN
…
END;
Procedure name after END
Given the procedure below, how many parameters will return a value to the calling
environment?
CREATE OR REPLACE PROCEDURE show_emps
(p_emp_id NUMBER, p_department_id OUT number,
p_hiredate IN OUT DATE) IS
begin
. . . . . .
End;
3
When invoking a function as part of a PL/SQL expression, you can use a _______ to store the returned result.
LOCAL VARIABLE
A function can be called as a part of:
PL/SL expression
What is missing in the code below?
create or replace function sample
return number
is
begin
return 5+5;
end;
FORMAL PARAMETER
A function must have a _______ clause in the header and at least one RETURN statement in the executable section.
RETURN
(T OR F) Subprograms are explicitly shared.
TRUE
(T OR F) The symbol»_space; is used for association operator?
FALSE
If Combination notation is used, the _______ parameters must come first.
POSITIONAL
How many parameters are there in the given code?
CREATE or replace procedure raise_sal
(empid my_employees.employee_id%TYPE := 1234, emp_raise number) IS
BEGIN
2
How many value is returned by a function?_____
SINGLE VALUE/1/ONE ^
In stored function, the RETURN clause is used instead of ____ mode.
OUT
In PL/SQL ___________, the function identifier acts like a variable whose value depends on the parameters passed to it.
EXPRESSION
What is missing in the code below?
create or replace function sample
return number
is num number(2);
begin
num := 5+5;
end;
No return value
In the given Procedure header, the ________clause is optional.
CREATE
OR
REPLACE
PROCEDURE
name
[parameters]
IS|AS
IS|AS
The executable section of the procedure body requires a minimum of _____ statement.
ONE
Using an _______ parameter mode, you can pass a value into a procedure that can be updated within the procedure.
IN OUT
The _____ are named PL/SQL blocks that are compiled and stored in the database.
PROCEDURE
The _____ are named PL/SQL blocks that are compiled and stored in the database.
OVERWRITE
The type of parameter that is declared in procedure heading is called ________.
FORMAL
The operator => is called _______.
ASSOCIATION OPERATOR
A function must have a RETURN clause in the _____ and at least one RETURN statement in the executable section.
HEADER
In stored function, the parameter mode should only be ______ .
IN
The _______ is added to CREATE PROCEDURE clause to overwrite an existing procedure.
OR REPLACE
In Procedure, the default parameter mode is _______.
IN
Which type of parameter passing uses an association operator?
NAMED
Three ways of passing parameters from the calling environment:
POSITIONAL, NAMED, COMBINATION
In stored function, the ______ clause is used instead of OUT mode
RETURN
A stored function must return exactly ____ value.
ONE
(T OR F) Parameters in subprograms are optional.
TRUE
(T OR F) If Combination notation is used, the positional parameters must come first before the named parameters.
TRUE
(T OR F) Subprograms are explicitly shared.
TRUE
(T OR F) Subprograms become the building blocks of packages and triggers.
TRUE
(T OR F) The block structure of the subprograms is similar to the structure of anonymous blocks.
TRUE
(T OR F) A procedure can be invoked in another procedure.
TRUE
(T OR F) Another way of initializing a default value for parameter is by using assignment operator.
TRUE
(T OR F) To invoke a procedure from another procedure, use a direct call inside an executable section of the block
TRUE
(T OR F) The Positional parameter passing is the common method of passing parameters.
TRUE
The block structure of the subprograms is similar to the structure of _____ blocks.
ANONYMOUS
(T OR F) The formal parameters can be literal values, variables, or expressions that are sent to the parameter list of a called subprogram.
FALSE (ACTUAL)
(T OR F) The actual is a special form of a variable, whose input values are initialized by the calling environment when the subprogram is called, and whose output values are returned to the calling environment when the subprogram returns control to the caller.
FALSE
(T OR F) The positional parameter passing uses the associate operator.
FALSE
(T OR F) The code below is a correct example of passing parameters by combination notation.
add_dept (p_loc=>1400, ‘EDUCATION’ );
FALSE
(T OR F) The OUT parameter mode supplies an input value, which can be returned (output) as a modified value.
FALSE
(T OR F) The symbol»_space; is used for association operator?
FALSE
Assuming add_dept procedure has two parameters: p_name and p_id, what is the error
in the given procedure call?
add_dept (‘ADVERTISING’, p_loc => 1400);
THE NAMED PARAMETER
Given the procedure below, how many parameters will return a value to the calling
environment?
CREATE OR REPLACE PROCEDURE show_emps
(p_emp_id NUMBER, p_department_id OUT number,
p_hiredate IN OUT DATE) IS
begin
. . . . . .
End;
3
What is missing in the code below?
create or replace function sample
is num number(2);
begin
num := 5+5;
return num;
End;
NO RETURN VALUE
create or replace function sample
return number
is
begin
return 5+5;
end;
NOTHING IS MISSING
(T OR F) The DEFAULT keyword is used to assign a default value for formal parameters
FALSE
(T OR F) A procedure is a PL/SQL block containing local variables, a BEGIN, and an END. The procedure_name after the END keyword is optional.
TRUE
(T OR F) The code below is a correct example of passing parameters by combination notation.
add_dept (p_loc=>1400, ‘EDUCATION’ )
FALSE
(T OR F) The Named parameter passing is the common method of passing parameters.
FALSE
(T OR F) Using an _______ parameter mode, you can pass a value into a procedure that can be updated within the procedure.
IN OUT
The parameter modes are specified in:
FORMAL PARAMETER
How many value is returned by a function?_____
1
Subprograms are named PL/SQL blocks that are compiled and stored in the
_______.
DATABASE
Which keyword is omitted in the declaration section of the procedure? _____
DECLARE
(T OR F) The DECLARE keyword is not used in a subprogram.
TRUE
(T OR F) The code below is a correct example of passing parameters by combination
notation.
add_dept (p_loc=>1400, ‘EDUCATION’ );
FALSE
(T OR F) The IN parameters can only be read within the procedure and cannot be modified.
TRUE
Given the procedure below, how many parameters will return a value to the
calling environment?
CREATE OR REPLACE PROCEDURE show_emps
(p_emp_id NUMBER, p_department_id OUT number,
p_hiredate IN OUT DATE) IS
begin
. . . . . .
End;
2
(T OR F) The IN mode parameters may or may not be specified
TRUE
The _____ are named PL/SQL blocks that are compiled and stored in the database.
SUBPROGRAMS
(T OR F) The IN mode parameters may or may not be specified.
TRUE
(T OR F) The Positional parameter passing is the common method of passing parameters.
TRUE
(T OR F) If Combination notation is used, the positional parameters must come first before the named parameters.
TRUE
What is the error of the given code?
BEGIN
add_dept(name =>’new dept’, ‘new location’);
END;
- Positional parameter must come before named parameter
How many private variables are there in the given code?
CREATE or replace procedure raise_sal
(empid my_employees.employee_id%TYPE := 1234, emp_raise number) IS
BEGIN
update employees
set salary = salary + (salary * (emp_raise/100) where employee_id = emp;
END;
0
The functions used in SQL statements cannot use OUT or _____ modes.
IN OUT
What keyword is missing in the given function construct?
CREATE [OR REPLACE] function_name
[(parameter1 [mode1] datatype1, …)]
RETURN datatype IS|AS
[local_variable_declarations; …]
BEGIN
actions;
RETURN expression;
END [function_name];
FUNCTION
The functions used in SQL statements cannot use ____ or IN OUT modes.
IN
(T OR F) Parameters in subprograms are optional.
TRUE