Questions and Answers
What is the primary purpose of the OUT parameter in a PL/SQL procedure?
Which of the following statements accurately describes an IN OUT parameter in a PL/SQL procedure?
What would be the correct command to drop a PL/SQL procedure named Sum_ab?
In a PL/SQL procedure, what does the statement 'DBMS_OUTPUT.PUT_LINE('N is ' || N);' do?
Signup and view all the answers
Which of the following procedures will successfully double the value of an integer parameter?
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;
- Code snippet:
Parameters in Procedures
- Parameters are optional and can be declared similarly to variables.
- Example:
(X INT)
defines an integer parameter.
- Example:
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;
- Code snippet:
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;
- Code snippet:
- 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;
- Code snippet:
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;
- Code snippet:
Dropping Procedures
- Use the following format to drop a procedure:
- Code snippet:
DROP PROCEDURE procedure_name;
- Code snippet:
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
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.