141. What are the advantages of stored procedure?
a. The procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;
b. The code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
c. Each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
d. As the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients. This results in much less network traffic and again improves scalability;
e. When using PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster stored procedures/functions can be compiled into “native” machine code making them even faster.
142. Difference between %type and %row type.
%TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.
ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table. In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then individually refer these variables inside the ROWTYPE variables.
143. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
• A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
• A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.
144. What is the difference between procedure and functions?
FUNCTIONS:
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure cannot be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
PROCEDURES:
6. Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc.. But actually not exists in database is allowed only in during creation but runtime throws error Function won’t support deferred name resolution.
7. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
145. What is pl/sql. Advantages of pl/sql.
❖ Pl/sql is procedural language. It is an extension of sql with design feature of programming languages.
❖ Pl/sql offers modern software engineering features such as data encapsulation, exception handling information hiding, and object orientation.
❖ pl/sql reduces the network traffic, it provides you with ability to control the flow of constructs.
❖ Pl/sql application can run on any platform on which oracle runs.
❖ Pl/sql is not an oracle product in its own write.
Advantages:
❖ You can program with procedural language with control structures.
❖ Pl/sql can handle errors.
❖ Easy maintenance.
❖ Improved data security and integrity.
❖ Improved performance.
❖ Improved code clarity.
❖ Pl/sql is portable.
❖ You can declare variables.
❖ It reduces network traffic.
146. What is ref cursor?
Ref Cursors also known as Dynamic cursors can be associated with as many SELECT statements you want
at different times. You can associate with different SELECT statements at dynamic time.
147. What is the basic structure of pl/sql.
Declare—————————Optional
Variable declaration
Cursor declaration
User defined exceptions
Begin—————————Mandatory
Sql statements
Application or business logic
Exception —————————Optional
Handling Errors
End; —————————Mandatory
/
148. What will happen after commit statement?
Commit in this context will not do anything except the committing the changes into database, done using DML statements.
However, if the cursor is created with FOR UPDATE clause, it will raise runtime exception as commit, in that case, would also release all locks and thus close the cursor implicitly and user will get ORA-1002 “fetch out of sequence ” error as after the loop is executed one time, with the commit statement, the cursor will be closed and fetch into statement will fail.
149. What is trigger,cursor,functions with example.
Trigger: Trigger is a pl/sql block structure which is fired when DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.
Syntax of Triggers
The Example for creating a trigger is:
CREATE OR REPLACE TRIGGER emp_comm_trig
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :NEW.deptno = 30 THEN
:NEW.comm := :NEW.sal * .4;
END IF;
END;
Functions: Function can accept a parameter and must return a value.Function can be called as an part of an expression. Function is a named pl/sql block that is stored in the database for repeated execution. Function must have at least one return statement.
Example:
Create or replace function nthsal(i number)
Return number
c number;
begin
select salary into c from (select salary,rownum r from (select salary from employees group by
salary))where r=i;
return c;
end;
/
Cursor: Cursor is a sql private work area.it opens an area of memory where the query is parsed is parsed and executed.
DECLARE
v_employeeID employee.id%TYPE;
v_FirstName employee.first_name%TYPE;
v_LastName employee.last_name%TYPE;
CURSOR c_employee IS
SELECT id, first_name, last_name FROM employee WHERE department_id=80;
BEGIN
OPEN c_employee;
LOOP
FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;
DBMS_OUTPUT.put_line(v_employeeID);
DBMS_OUTPUT.put_line(v_FirstName);
DBMS_OUTPUT.put_line(v_LastName);
EXIT WHEN c_employee%NOTFOUND;
END LOOP;
CLOSE c_employee;
END;
/
150. What is the cursor attributes used in pl/sql.
As with explicit cursor there are four attributes for obtaining status information about a cursor. When appended to the cursor variable name these attribute return useful information about the execution of a data manipulation statement.
Attribute Type Description
%ISOPEN Boolean Evaluvates to true if the cursor is open.
%NOTFOUND Boolean Evaluvates to true if the most recent fetch does not return a row.
%FOUND Boolean Evaluvates to true if the most recent fetch returns a row; complement of % not found.
%ROWCOUNT Boolean Evaluvates to the total number of rows returned so far.
151. What are the modes of parameters that can be passed to the procedure.
There are three modes of parameters:
In (default): Passes a constant value from the calling environment to the procedure. Formal parameter acts as a constant. Actual parameter can be literal, expression, constant or initialized variable. Can be assigned a default value.
Out: Passes a value from the procedure to the calling environment. Initialized variable, must be a variable, cannot be assigned a default value.
In Out: Passes a value from the calling environment in to the procedure and a possibly different value from the procedure back to the calling environment using the parameter. Initialized variable must be a variable cannot be assigned a default value.
152. What are two parts of package.
Package body:
A package body usually has a specification and a body stored separately in the data base. The specification is the interface to your applications. It declares the types, variables, constants and subprograms available for use.
Package specification:
The package specification may also include PRAGMAs, which are directives to the compiler.
The body fully defines cursors and subprograms and to implement the specification.
153. What is raise_application_error.
You can use this procedure to issue user_defined error messages from stored subprograms. You can report errors to your application and avoid returning unhandled exceptions. It is used in two different places executable section and exceptional section.
Syntax: raise_application_error(error_number,message[,{true|false}];
In the syntax:
Error_number : is a user specified number for the exception between -20000 and -20999.
Message: is the user specified message for the exception.It is a character string up to 2048 bytes.
True|False : is an optional Boolean parameter(if true the error is placed on the stack of previous error .if false ,the default ,the error replaces all previous errors.
154. What is cursor .why it is recquired ?
A cursor is a private sql work area where the statements are executed and the processing information is
stored.
155. What is cursor for loop.
A cursor for loops processes rows in an explicit cursor. It is a shortcut because the cursor is opened, rows are fetched once for each iteration in the loop, the loop exists when the last row is processed, and the cursor is closed automatically. The loop itself is terminated at the end of the iteration where the last row is fetched.
Syntax: for record_name in cursor_name loop
Statement 1;
Statement 2;
…….
End loop;
156. What is a stored procedure?
- A procedure is a named pl/sql block that can accept parameters and be invoked. You can use it to perform an action.
- A procedure has a header, a declaration section, an executable section, and an optional exception handling section.
- A procedure can be compiled and stored in the data base as a schema object.
- Procedures promote reusability and maintainability. When validated they can be used an any number of applications.
- If the requirements change, only the procedure needs to update.
157. What is an exception? What are types of exception.
An exception is an identifier in pl/sql block that is raised during the execution of a block that terminates its main body of actions. A block always terminates when pl/sql raises an exception, but can you perform an exception handler to perform final actions.
There are two types of exceptions:
Implicitly raised
✓ Predefined oracle server: One of approximately 20 errors that occur most often in pl/sql code.
✓ Non-predefined oracle server: Any other standard orale server error.
Explicity raised
✓ User defined: A condition that the developer determines is abnormal.
158. Where are predefined exception are stored.
Predefined exceptions were stored in the oracle server.
159. What is pragma_exception_init explain the usage.
In pl/sql,the pragma_exception_init tells the compiler to assiociate an exception name with an oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.
160. What are the return values of function sqlcode and sqlerm ?
Sqlcode returns the numeric value for the error code or error message by using two functions.Based on the value of the code or message,you can decide which subsequent action to take based on the error.
Sqlerrm returns character data containing the message associated with the error number.
