Class XII Informatics Practices Paper (Chapter Wise With Answer)

PROCEDURES/FUNCTIONS

Q.1 Explain the usage of IN OUT parameter of a PL/SQL procedure with the help of an example.
Ans. An IN OUT parameter pass initial values to the procedure being called and return updated values to the caller subprogram. Inside the procedure, an IN OUT parameter acts like an initialized variable. So the actual parameter must be a variable, it can not be a constant or an expression.
For example, if we have to write a procedure, which receives salary as parameter and increases it by 10% if it less then 5000 otherwise increases it by 5%.

CREATE OR REPLACE PROCEDURE salary_increment (salary IN OUT NUMBER) AS
BEGIN
IF salary > 5000 THEN
Salary : = Salary +Salary*0.10;
ELSE
Salary : = Salary+ Salary *0.05;
END IF;
END;

Q.2 Why are named procedure referred to as stored procedures?
Ans: The named procedures are referred to as stored procedures because the named procedures are compiled and stored as schema objects in the Oracle database. In contrast the local or anonymous procedures are compiled at the time of their execution and are not saved as part of database.

Q.3 What are actual parameters? What are formal parameters?
Ans. The variable or literals listed in the procedure call statement are called actual parameter, whereas formal parameters are the ones that are listed in the procedure header and are used in procedure definition.

Q.4 What are the advantages of stored procedures?
Ans. The advantages are –
(i)They are stored in compiled form and thus save on execution time.
(ii)They can accept parameter therefore they are flexible in nature.
(iii)They are stored in database and hence are accessible to various applications that can connect to Oracle.

Q.5 What is following procedure doing:
CREATE OR REPLACE FUNCTION getBDate ( v_ssn VARCHAR2)
RETURN DATE
AS
v_bdate employee.bdate%TYPE;
BEGIN
SELECT bdate INTO v_bdate FROM employee
WHERE ssn = v_ssn;
RETURN v_bdate:
END;
Ans. The function gets the serial number ssn of an employee as an IN parameter, reads the corresponding values for the field bdate and return the read value.

Download Full Paper Here!