52) what is REF Cursor?
To execute a multi-row query, oracle opens an unnamed work area that stores processing information, to access the information, an explicit, which names the work area or, a cursor variable, which points to the work area.
where as a cursor always refers to the same query work area, a cursor variable can refer to a different work areas, cursor variable area like ‘c’ or ‘pascal’ pointers, which hold the memory location(address) of some object instead of the object itself.
So, declaring a cursor variable creates a pointers, not an object.
32) Can u define exceptions twice in same block?
No
33) Can you have two functions with the same name in a pl/sql block?
Yes
34) Can you have two stored functions with in the same name?
Yes
35) Can function be overload?
Yes
36) What is the maximum number of statements that can be specified in a trigger statement?
One.
32) Stored procedure?
Stored procedure is a sequence of statements that perform specific function.
53) What is procedure?
---- is a named pl/sql block to perform a specific task.
---- A procedure may have DML statements.
---- It may or may not return a value.
---- Procedure can return more than one value.
Example for procedure
1) To accept the year as a parameter and list emp belong to the year?
Create or replace
Procedure empy(y number) is
Cursor emp_cursor is
Select * from emp where to_char(hiredate,’yyyy’)=’y’;
Emp_record emp%rowtype;
Begin
For emp_record in emp_cursor loop
Print (emp_record.empno);
Print (emp_record.ename);
Print (emp_record.sal);
End loop;
End;
Output :
var empx number;
Begin
:empx := ‘1234’;
End;
Exec empy(:empx);
Print empy;
54) What is function?
---- is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.
---- A function is called as part of an exception.
---- Every function should return a value
Example for function
Create or replace
Function get_sal(p_id in emp.emp_no% type)
Return number
Is
v_sal emp.sal%type :=0;
Begin
Select salary into v_salary
From emp
Where emp_no = p_id;
Return v_salary
End get_sal;
End;
Output :
var g_sal number;
Exec :g_sal := get_sal(99);
Print g_salary;
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype
No.
55) What is the package?
---- Group logically related pl/sql types, items and subprograms.
1) package specification
2) package body
Advantages of a package:
· Modularity
· Easier Application Design
· Information Hiding
· Overloading
You cannot overload:
•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).
•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)
•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)
•Two functions that differ only in return type, even if the types are in different families.
56) What is FORWARD DECLARATION in Packages?
PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:
• Define subprograms in logical or alphabetical order.
• Define mutually recursive subprograms.(both calling each other).
• Group subprograms in a package
Example of forward Declaration:
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
PROCEDURE calc_rating(. . .); -- forward declaration
PROCEDURE award_bonus(. . .)
IS -- subprograms defined
BEGIN -- in alphabetical order
calc_rating(. . .);
. . .
END;
PROCEDURE calc_rating(. . .)
IS
BEGIN
. . .
END;
END forward_pack;
No comments:
Post a Comment