Section 7: Using and Managing Procedures
7.01. Creating Procedures
Click on the “Save” button and save the procedure code
Retrieve the saved code from “Saved SQL” in SQL Commands
Modify the code in the SQL Commands window
Execute the code to re-create the procedure
♦Execute the code to create the procedure (*)
♦Invoke the procedure from an anonymous block
2. Which of the following are characteristics of anonymous PL/SQL blocks but not PL/SQL subprograms? (Choose three.)
♦Can take parameters
♦Are stored in the database
♦Can begin with the keyword DECLARE (*)
♦Are unnamed (*)
♦Are compiled every time they are executed (*)
3. Which of the following are benefits of using PL/SQL subprograms rather than anonymous blocks? (Choose three.)
♦Better data security (*)
♦Code reuse (*)
♦Stored externally
♦Easier code maintenance (*)
♦Do not need to define exceptions
4. Why will the following procedure fail?
CREATE OR REPLACE PROCEDURE mainproc
…
IS
PROCEDURE subproc (…) IS BEGIN
…
BEGIN
…
subproc (…);
…
END;
♦Procedure main proc must use the keyword AS not IS
♦Procedure mainproc does not need the keyword BEGIN
♦Procedure subproc does not need the keyword BEGIN
♦Procedure subproc does not have an END; statement (*)
5. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose two.)
♦BEGIN (*)
♦REPLACE
♦EXCEPTION
♦DECLARE
♦END (*)
6. Subprograms and anonymous blocks can be called by other applications. True or False?
♦True
♦False (*)
7. When modifying procedure code, the procedure must be re-executed to validate and store it in the database. True or False?
♦True (*)
♦False
8. A stored PL/SQL procedure can be invoked from which of the following?
A calling application
A SELECT statement
Another PL/SQL procedure
♦A and B
♦A and C
♦A, B and D (*)
♦B and C
9. PL/SQL subprograms, unlike anonymous blocks, are compiled each time they are executed. True or False?
♦True
♦False (*)
10. A stored procedure add_dept may be invoked by the following command in Application Express. True or False?
BEGIN
add_dept;
END;
♦False
♦False (*)
♦An action (*)
♦A return of values
♦All of the above
♦None of the above
7.02. Using Parameters in Procedures
♦CREATE PROCEDURE testproc IS p1 VARCHAR2(100); BEGIN ….
♦CREATE PROCEDURE testproc DECLARE p1 VARCHAR2(100); BEGIN ….
♦CREATE PROCEDURE testproc p1 VARCHAR2 IS BEGIN ….
♦CREATE PROCEDURE testproc (p1 VARCHAR2) IS BEGIN …. (*)
♦Formal and actual parameters must have different names.
♦A formal parameter is declared within the called procedure, while an actual parameter is declared in the calling environment. (*)
♦An actual parameter is declared within the called procedure.
3. Procedure NUMPROC has been created as:
(x NUMBER, y NUMBER := 100, z NUMBER)
IS BEGIN ….
You want to call the procedure, passing arguments of 10 for X and 20 for Z. Which one of the following calls is correct?
♦myproc(10,,20);
♦myproc(x=10,z=20);
♦myproc(10,z=>20); (*)
♦myproc(x=>10,20);
4. Procedure SUBPROC was created as:
(p_param VARCHAR2)
IS BEGIN …
v_param VARCHAR2(20) := ‘Smith’;
BEGIN
subproc(v_param);
END;
Which of the following is the actual parameter?
♦p_param
♦v_param (*)
♦Smith’
♦None of the above.
5. A procedure has been created as:
(p_left NUMBER, p_right NUMBER)
IS BEGIN ….
You want to call the procedure from an anonymous block. Which of the following calls is valid?
♦myproc(p_left, p_right);
♦myproc(v_left, v_right);
♦myproc(v_left, 30);
♦All of the above. (*)
6. Which of the following best describes the difference between a parameter and an argument?
♦They are both names of variables. A parameter is passed into the procedure, while an argument is passed out of the procedure
♦A parameter is the name of a variable, while an argument is the datatype of that variable
♦A parameter is the name of a variable that is passed into or out of a procedure, while an argument is the value of that variable (*)
♦There is no difference, parameters and arguments are the same thing
7. What is the purpose of using parameters with stored procedures?
♦They prevent the procedure from modifying data in the database.
♦They allow values to be passed between the calling environment and the procedure. (*)
♦They count the number of exceptions raised by the procedure.
♦They speed up the execution of the procedure.
8. What is the correct syntax to create procedure MYPROC that accepts two number parameters X and Y?
♦CREATE PROCEDURE myproc (x NUMBER, y NUMBER) IS … (*)
♦CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS …
♦CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) …
♦CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) ?
9. You want to create a procedure which accepts a single parameter. The parameter is a number with a maximum value of 9999.99. Which of the following is a valid declaration for this parameter?
♦(v_num NUMBER(6,2))
♦(v_num NUMBER) (*)
♦(v_num)
♦(v_num NUMBER(4,2))
10. Which of the following can be used as an argument for a procedure parameter?
♦The name of a variable.
♦A literal value.
♦An expression.
♦All of the above. (*)
♦None of the above.
7.03. Passing Parameters
♦After the keyword IS or AS.
♦Before the procedure name.
♦After the keyword PROCEDURE.
♦IN (*)
♦COPY
♦DEFAULT
♦R(ead)
3. What are the three parameter modes for procedures?
♦IN, OUT, IN OUT (*)
♦R(ead), W(rite), A(ppend)
♦CONSTANT, VARIABLE, DEFAULT
♦COPY, NOCOPY, REF
4. The following procedure has been created:
(p_p1 NUMBER, p_p2 VARCHAR2)
IS BEGIN …
Which one of the following calls to the procedure will NOT work?
♦myproc(80, ‘Smith’);
♦myproc(p_p1 => 80, ‘Smith’); (*)
♦myproc(80, p_p2 => ‘Smith’);
♦myproc(p_p1 => 80, p_p2 => ‘Smith’);
5. Three IN parameters for procedure ADD_EMPLOYEE are defined as:
p_salary NUMBER := 1000,
p_hired DATE DEFAULT SYSDATE)
What is the value of P_SALARY when the procedure starts to execute?
♦NULL
♦1000 (*)
♦The procedure will not compile because P_SALARY should have been coded as DEFAULT 1000
♦The call will fail because P_SALARY is a required parameter
6. A procedure is invoked by this command:
What is the method of passing parameters used here?
♦Positional
♦Named
♦A combination of positional and named (*)
♦None of the above
7. A procedure is invoked by this command:
What is the method of passing parameters used here?
♦Positional (*)
♦Named
♦A combination of positional and named.
♦None of the above
8. Which of the following statements about IN OUT parameters is true? (Choose two.)
♦The data type for the parameter must be VARCHAR2.
♦The parameter value passed into the subprogram is always returned unchanged to the calling environment.
♦The parameter value can be returned as the original unchanged value. (*)
♦The parameter value can be returned as a new value that is set within the procedure. (*)
9. Which kind of parameters cannot have a DEFAULT value?
♦OUT (*)
♦IN
♦CONSTANT
♦R(ead)
♦W(rite)
InfoCad Blog 