1.What is difference
between stored procedures and application procedures,stored function and
application function?
Difference between
Stored Procedure and Application Procedure:
Stored Procedure is a named PLSQL block that will be compiled and used for further use. If maintained across Servers, they can be used for different applications and Users. Application Procedures are the one which will be used for local apps, this can be called local block of code. Apart from technical characteristics, same will apply to the differences between Stored Functions and Application Functions.
Difference between Procedures and Functions:
a. Functions can be used in a select statement where as procedures cannot
b. Procedure takes both input and output parameters but Functions takes only input parameters
c. Functions cannot return values of type text, ntext, image & timestamps where as procedures can
d. Functions can be used as user defined datatypes in create table but procedures cannot
***Eg:-create table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get?s executed and the return
Type is returned as the result set.
Stored Procedure is a named PLSQL block that will be compiled and used for further use. If maintained across Servers, they can be used for different applications and Users. Application Procedures are the one which will be used for local apps, this can be called local block of code. Apart from technical characteristics, same will apply to the differences between Stored Functions and Application Functions.
Difference between Procedures and Functions:
a. Functions can be used in a select statement where as procedures cannot
b. Procedure takes both input and output parameters but Functions takes only input parameters
c. Functions cannot return values of type text, ntext, image & timestamps where as procedures can
d. Functions can be used as user defined datatypes in create table but procedures cannot
***Eg:-create table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get?s executed and the return
Type is returned as the result set.
The basic difference
between function and procedure is that a procedure can never be used in a sql
statement while a function can be used
Stored procedures
are sub programs stored in the database and can be called & execute
multiple times where in an application procedure is the one being used for a
particular application same is the way for function
Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine.Difference is Stored Procedure must return multiple value and function must return single value .
Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine.Difference is Stored Procedure must return multiple value and function must return single value .
2) What is
Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD
which allows to issue an user_defined error messages from stored sub-program or
database
trigger.
trigger.
RAISE_APPLICATION_ERROR is a
procedure of package DBMS which
allows to issue user_defined error
message.
procedure of package DBMS which
allows to issue user_defined error
message.
It is Its a STANDARD
procedure to communicate with a pre defined exception interactively by
returning a nonstandard error code and error message.
error_number : between -20000 to -20999
message : 2048 bytes
This can be used in both execution and exception setion;
Ex: 1 :
.....
BEGIN
...
delete from employees
where manager_id = v_mgr;
if SQL%NOTFOUND then
RAISE_APPLICATION_ERROR(-20020,'This is not a valid manager');
end if;
....
---------------------------
ex:2 :
......
EXCEPTION
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20201,'manager is not a valid employee');
END;
error_number : between -20000 to -20999
message : 2048 bytes
This can be used in both execution and exception setion;
Ex: 1 :
.....
BEGIN
...
delete from employees
where manager_id = v_mgr;
if SQL%NOTFOUND then
RAISE_APPLICATION_ERROR(-20020,'This is not a valid manager');
end if;
....
---------------------------
ex:2 :
......
EXCEPTION
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20201,'manager is not a valid employee');
END;
3) How we
can create a table in PL/SQL block. insert records
into it??? is it possible by some procedure or
function?? please give example...
we can create a table through
pl/sql block with dbms_sql package and execute immediate statement.
CREATE OR REPLACE PROCEDURE PROC_CREATE_NEW_TABLE(STMT VARCHAR2)
IS
AUTHID CURRENT_USER
BEGIN
EXECUTE IMMEDIATE STMT;s
END;
here AUTHID CURRENT_USER is used to create a table under current user privileges.
through this procedure we can add our own fields of a table on run time,even it is not depended on structure of an existing table.
Insertion record into the table, because it is DML operation so we can easily create a procedure with simple statements or execute immediate statement.
CREATE OR REPLACE PROCEDURE PROC_CREATE_NEW_TABLE(STMT VARCHAR2)
IS
AUTHID CURRENT_USER
BEGIN
EXECUTE IMMEDIATE STMT;s
END;
here AUTHID CURRENT_USER is used to create a table under current user privileges.
through this procedure we can add our own fields of a table on run time,even it is not depended on structure of an existing table.
Insertion record into the table, because it is DML operation so we can easily create a procedure with simple statements or execute immediate statement.
4)
Explian rowid,rownum?What are the pseduocolumns we have?
ROWID - Hexa decimal number each and every
row having unique.Used in searching
ROWNUM - It is a integer number also unique
for sorting Normally TOP N Analysys.
Other Psudo Column are NEXTVAL,CURRVAL Of
sequence are some examples
ROWID
: Every record in a database is uniquely identified by system generated value
called Rowid. It Is a 18 character hexma decimal value. These Rowid's are
physically existence.ROWNUM : It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.
NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS
5) State the difference between implicit
and explicit cursor's.
Implicit Cursor are declared and
used by the oracle internally. whereas the explicit cursors are declared and
used by the user. more over implicitly cursors are no need to declare oracle
creates and process and closes autometically. the explicit cursor should be
declared and closed by the user.
Implict cursor can be used to handle
single record (i.e) the select query used should not yield more than one row.
if u have handle more than one record then Explict cursor should be used.
Cursor:
It is automatically created by oracle for all sql dml statements including the
query that returns one row.if u have handle more than one record then Explict cursor should be used.
Explicit cursor: These are created and managed by the user.And used for multi row select statement.
Cursors are used for the purpose of storing the intermediate results when executing a SQL Query or a block of code...
Implicit Cursors are by default declared by ORacle itself and hence there is no scope of storing resultset with more than one record where as Explicit Cursors are declared by the User and hence can define the parameters with measure to store the no of records.
6) State the advantage and disadvantage
of Cursor?
Advantage :
In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.
disadvantage:
using implicit/explicit cursors are depended by sutiation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you should use exlicit cursors. other wise it will put burdon on cpu.
In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.
disadvantage:
using implicit/explicit cursors are depended by sutiation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you should use exlicit cursors. other wise it will put burdon on cpu.
Advantages:
Cursors are somewhat like Pointers of Oracle, Oracle executes a query and assign some name to that memory location, which is known as Cursor.
Cursors are used for storing data temperarorily.
Cursors are used for storing, managing and retrieving data.
Cursors stores in the RAM so the data access and retrieval is fast.
Disadvantage:
No such disadvantage. Only thing is that rather than storing data in an intermediate variable and fetching it one by one via cursor and then processing it sequentially, we can use Collections. By using collections, we can pass the bundle of Data at once.
Cursors are somewhat like Pointers of Oracle, Oracle executes a query and assign some name to that memory location, which is known as Cursor.
Cursors are used for storing data temperarorily.
Cursors are used for storing, managing and retrieving data.
Cursors stores in the RAM so the data access and retrieval is fast.
Disadvantage:
No such disadvantage. Only thing is that rather than storing data in an intermediate variable and fetching it one by one via cursor and then processing it sequentially, we can use Collections. By using collections, we can pass the bundle of Data at once.
7) Explain the usage of WHERE CURRENT OF
clause in cursors ?
Where CURRENT OF clause means
cursor
points to present row of
the cursor
cursor
points to present row of
the cursor
WHERE CURRENT OF clause in an UPDATE,DELETE
statement refers to the latest row fetched from a cursor.
DECLARE
CURSOR wip_cur IS
SELECT acct_no, enter_date FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_
date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
CURSOR wip_cur IS
SELECT acct_no, enter_date FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_
date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
8) What are the Restrictions on Cursor
Variables?
u cannot declare cursor in package
specification
not allowed when using db links
cannot use comparison operators
cannot assign NULL
cursor's values cannot be stored in table columns
cannot be used with associative array, nested tables and varray
cannot be use one where the other is expected
cannot reference a cursor variable in cursor FOR LOOP
cannot direclty goto any columns
not allowed when using db links
cannot use comparison operators
cannot assign NULL
cursor's values cannot be stored in table columns
cannot be used with associative array, nested tables and varray
cannot be use one where the other is expected
cannot reference a cursor variable in cursor FOR LOOP
cannot direclty goto any columns
9) Name the tables where characteristics
of Package, procedure and functions are stored ?
User_objects, User_Source and
User_error.
user_source,user_objects
10) What are two virtual tables
available during database trigger execution ?
The table columns are referred as
OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
Two tables are: OLD and NEW.
Insert Trigger :
OLD - no value.
NEW - inserted value.
UPDATE TRIGGER -
OLD- old value.
NEW- new updated value.
DELETE TRIGGER -
OLD - old value.
NEW - no value.
Insert Trigger :
OLD - no value.
NEW - inserted value.
UPDATE TRIGGER -
OLD- old value.
NEW- new updated value.
DELETE TRIGGER -
OLD - old value.
NEW - no value.
11) What is difference between % ROWTYPE
and TYPE RECORD ?
%
ROWTYPE is to be used whenever query returns a entire row of a table or view.TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
12) How many types of database triggers
can be specified on a table ? What are they ?
Baically there only 1 type of trigger which
can be fired on the table. .i.e., DML TRIGGER.
There are 14 types of DML TRIGGER
But we can fire only 12 types of triggers, because remaining 2 types of triggers fire on View.
1. Before insert on ROW LEVEL TRIGGER
2. 1. AFTER insert on ROW LEVEL TRIGGER
3. Before insert on STATEMENT LEVEL TRIGGER
4. After insert on STATEMENT LEVEL TRIGGER
5. Before update on ROW LEVEL TRIGGER
6. After update on ROW LEVEL TRIGGER
7. Before update on STATEMENT LEVEL TRIGGER.
8. After update on STATEMENT LEVEL TRIGGER.
9. Before Delete on STATEMENT LEVEL TRIGGER.
10 After Delete on STATEMENT LEVEL TRIGGER.
11. Before Delete on ROW LEVEL TRIGGER.
12 After Delete on ROW LEVEL TRIGGER
There are 14 types of DML TRIGGER
But we can fire only 12 types of triggers, because remaining 2 types of triggers fire on View.
1. Before insert on ROW LEVEL TRIGGER
2. 1. AFTER insert on ROW LEVEL TRIGGER
3. Before insert on STATEMENT LEVEL TRIGGER
4. After insert on STATEMENT LEVEL TRIGGER
5. Before update on ROW LEVEL TRIGGER
6. After update on ROW LEVEL TRIGGER
7. Before update on STATEMENT LEVEL TRIGGER.
8. After update on STATEMENT LEVEL TRIGGER.
9. Before Delete on STATEMENT LEVEL TRIGGER.
10 After Delete on STATEMENT LEVEL TRIGGER.
11. Before Delete on ROW LEVEL TRIGGER.
12 After Delete on ROW LEVEL TRIGGER
13) What is trigger,cursor,functions in
pl-sql and we need sample programs about it?
Trigger is an event driven PL/SQL block.
Event may be any DML transaction.
Cursor is a stored select statement for that current session. It will not be stored in the database, it is a logical component.
Function is a set of PL/SQL statements or a PL/SQL block, which performs an operation and must return a value.
Cursor is a stored select statement for that current session. It will not be stored in the database, it is a logical component.
Function is a set of PL/SQL statements or a PL/SQL block, which performs an operation and must return a value.
Cursor
cursor is a private sql work area.
Every sql statement executed
by oracle server has an individual
cursor associated with it.
Cursor are two types
1.Implicit cursor
2.Explicit cursor
Implicit cursor: Implicit cursors
are declared by pl/sql implicitly
at the time of DML statement and select statement in pl/sql including queries that returns single row.
cursor have four attributes
1. SQL%ROWCOUNT
2. SQL%ISOPEN
3. SQL%NOTFOUND
4. SQL%FOUND
SQL%ROWCOUNT-Basically it returns
number.
means number of rows
affected by present sql statement.
SQL%ISOPEN-Always evalutes false
because implicit cursor automatically closed after execution of sql statement.
SQL%FOUND-Always evalutes true
because one or more rows are affected by recent sql statement
SQL%NOTFOUND-Always evalutes true when no rows are affected by
present sql statement.
example of explicit cursor
DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cur IS
SELECT employee_id,last_name
FROM employees
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_name ;
EXIT WHEN emp_cur%ROWCOUNT>10 OR
emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('employee_id:'||TO_CHAR(v_empno) || 'employee_name:'||'v_name');
END LOOP;
CLOSE emp_cur;
END;
Trigger:-Trigger is pl/sql block or
procedure that is associated with table,view,schema and database.
Execute immidiately when particular event take place.
there are two types of trigger
1.Application trigger:fires automatically when event occurs with particular application.
2.Database trigger:Fires when
data such as DML oparation occured at that time.
DML triggers are two types
1.Statementlevel trigger
2.Rowlevel trigger
statement level trigger-statement level trigger means trigger body
execute once for the triggering event.this is default.A statement level trigger fire once even no rows are affected at all.
Row level- Trigger body execute
once for each row affected by triggering event.if no rows are
affected in that case trigger body
not executed.
trigger example
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
TO_CHAR(SYSDATE,'DY')IN('SUN','SAT') OR TO_CHAR((SYSDATE,'HH24:MI')NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROR(-20253,'u may insert employee information at business hrs');
END;
cursor is a private sql work area.
Every sql statement executed
by oracle server has an individual
cursor associated with it.
Cursor are two types
1.Implicit cursor
2.Explicit cursor
Implicit cursor: Implicit cursors
are declared by pl/sql implicitly
at the time of DML statement and select statement in pl/sql including queries that returns single row.
cursor have four attributes
1. SQL%ROWCOUNT
2. SQL%ISOPEN
3. SQL%NOTFOUND
4. SQL%FOUND
SQL%ROWCOUNT-Basically it returns
number.
means number of rows
affected by present sql statement.
SQL%ISOPEN-Always evalutes false
because implicit cursor automatically closed after execution of sql statement.
SQL%FOUND-Always evalutes true
because one or more rows are affected by recent sql statement
SQL%NOTFOUND-Always evalutes true when no rows are affected by
present sql statement.
example of explicit cursor
DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cur IS
SELECT employee_id,last_name
FROM employees
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_name ;
EXIT WHEN emp_cur%ROWCOUNT>10 OR
emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('employee_id:'||TO_CHAR(v_empno) || 'employee_name:'||'v_name');
END LOOP;
CLOSE emp_cur;
END;
Trigger:-Trigger is pl/sql block or
procedure that is associated with table,view,schema and database.
Execute immidiately when particular event take place.
there are two types of trigger
1.Application trigger:fires automatically when event occurs with particular application.
2.Database trigger:Fires when
data such as DML oparation occured at that time.
DML triggers are two types
1.Statementlevel trigger
2.Rowlevel trigger
statement level trigger-statement level trigger means trigger body
execute once for the triggering event.this is default.A statement level trigger fire once even no rows are affected at all.
Row level- Trigger body execute
once for each row affected by triggering event.if no rows are
affected in that case trigger body
not executed.
trigger example
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
TO_CHAR(SYSDATE,'DY')IN('SUN','SAT') OR TO_CHAR((SYSDATE,'HH24:MI')NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROR(-20253,'u may insert employee information at business hrs');
END;
14) What is a database trigger ? Name
some usages of database trigger ?
Trigger is a stored plsql program, which is
fired automatically when an event occur on the database.
Triggers can be created on
1.DML statements
2.DDL staments
3.Viwes(for views only insted of trigger is valid)
4.Any database event occures like database startup or shutdown.
Advantages of triggers:
1.Data auditing.
2.Enforce complex Business rules.
3.providing security.
*******Thank you for reading and All the best**************
Your comments will help me a lot...please give your comments which will encourage me to post more.....
Triggers can be created on
1.DML statements
2.DDL staments
3.Viwes(for views only insted of trigger is valid)
4.Any database event occures like database startup or shutdown.
Advantages of triggers:
1.Data auditing.
2.Enforce complex Business rules.
3.providing security.
*******Thank you for reading and All the best**************
Your comments will help me a lot...please give your comments which will encourage me to post more.....
No comments:
Post a Comment
Thanks for your valuable comments