Which two statements correctly differentiate functions and procedures? (Choose two.)
A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL7SQL statement.
B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment.
C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement.
D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Also think correct answers are BC.
Not A.
Example of function called from PL/SQL:
create or replace function test(p_var number) return varchar2 as
begin
dbms_output.put_line(‘Function called as PLSQL statement with parameter’||p_var);
return null;
end;
/
set serveroutput on;
variable a varchar2(30);
exec :a:=test(123);
Function called as PLSQL statement with parameter123
PL/SQL procedure successfully completed.
B:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
“Because every function must return a value”
Regarding procedures: They can’t return values using return statement.
But looks like in this question OUT parameters are meant.
C:
Below test shows that procedure can’t be called from SQL statment,
but works fine from PL/SQL
SQL> select dbms_output.put_line(‘Procedure test’) from dual;
select dbms_output.put_line(‘Procedure test’) from dual
ERROR at line 1:
ORA-00904: “DBMS_OUTPUT”.”PUT_LINE”: invalid identifier
set serveroutput on;
exec dbms_output.put_line(‘Procedure test’);
Procedure test
PL/SQL procedure successfully completed.
Not E:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#CHDEBECI
“In a procedure, a RETURN statement cannot contain an expression and does not return a value.”
By function returning multiple values, again looks like OUT parameters are meant.
Because RETURN statement can return only one value.
Also think correct answers are BC.
Not A.
Example of function called from PL/SQL:
create or replace function test(p_var number) return varchar2 as
begin
dbms_output.put_line(‘Function called as PLSQL statement with parameter’||p_var);
return null;
end;
/
set serveroutput on;
variable a varchar2(30);
exec :a:=test(123);
Function called as PLSQL statement with parameter123
PL/SQL procedure successfully completed.
B:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
“Because every function must return a value”
Regarding procedures: They can’t return values using return statement.
But looks like in this question OUT parameters are meant.
C:
Below test shows that procedure can’t be called from SQL statment,
but works fine from PL/SQL
SQL> select dbms_output.put_line(‘Procedure test’) from dual;
select dbms_output.put_line(‘Procedure test’) from dual
*
ERROR at line 1:
ORA-00904: “DBMS_OUTPUT”.”PUT_LINE”: invalid identifier
set serveroutput on;
exec dbms_output.put_line(‘Procedure test’);
Procedure test
PL/SQL procedure successfully completed.
Not E:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#CHDEBECI
“In a procedure, a RETURN statement cannot contain an expression and does not return a value.”
By function returning multiple values, again looks like OUT parameters are meant.
Because RETURN statement can return only one value.