1.How Many triggers can be applied to a table?
There are maximum 12 triggers can be applied to a table.
2. Difference between Syntax error & runtime error in PLSQL?
A Syntax error is easily identified by a PLSQL compiler.For example Incorrect spelling.
but runtime error can be handled with the help of Exception Section block in PLSQL.
3.How to disabled multiple triggers of a table at a time ?
Alter table Table_name disable all triggers.
4. Where is the pre defined procedures stored?
In oracle standard package,procedures,functions.
5.What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable
& it closes when all the records have been carried out process.
6.What is the difference between Procedure and function?
Function must return value and procedure does not.
Function can be used in sql with some restrictions. where as procedure can not be called directly from sql.
7.Explain Boolean Datatype with Example In Oracle
DECLARE
V_SAL NUMBER;
V_FLAG BOOLEAN;
BEGIN
SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;
V_FLAG := (V_SAL>=10000);
IF V_FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
ELSE
DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
END IF;
END;
8.Find out the First_name of the employee and the name of the departent for the employee who is managing for worker employee_id is 124.
declare
fname employees.first_name%type;
dname departments.department_name%type;
begin
select first_name , department_name into
fname, dname
from employees join departments using
(department_id)
where employee_id = ( select manager_id from
employees where employee_id= 124);
dbms_output.put_line(fname);
dbms_output.put_line(dname);
end;
/
9.Write a PL Sql Block that display Top 10 employees salaries from Employees Table.
DECLARE
CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
EMPREC EMPLOYEES%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO EMPREC;
DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
EXIT WHEN EMPCUR%ROWCOUNT >= 10 OR EMPCUR%NOTFOUND;
END LOOP;
CLOSE EMPCUR;
END;
10.What is transaction?
A transaction is a set of sql statements like data manipulation that works with logical unit .
You can use commit,Rollback Savepoint command to control the transaction.
At the end of the transaction that makes database changes,oracle makes all the changes permanent save or may be undone.
if your program fails in the middle of a transaction,oracle detect the error and rollback the transaction and restoring the database.
1.Commit : Commit command to make changes permanent save to a database during the current transaction.
2. Rollback : Rollback command executes at the end of current transaction and undo any changes made since the begin transaction.
3.Savepoint : Savepoint command save the current point with the unique name in the processing of a transaction.
4.Autocommit : Set Autocommit on to execute commit statement automatically.
5.Set transaction : PL/SQL set transaction command set the transaction properties such as write/read access.
Savepoint Savepoint_names is marks the current point in the processing of a transaction.Savepoint let you rollback part of a transaction instead of the whole transaction.
SQL> create table temp (id number,fname varchar2(30));
Table created.
SQL> insert into temp values(1,'Konal');
1 row created.
SQL> savepoint a;
Savepoint created.
SQL> insert into temp values(2,'Santosh');
1 row created.
SQL> insert into temp values(3,'Nikita');
1 row created.
SQL> insert into temp values(4,'Shalini');
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL> insert into temp values(5,'Ahmad');
1 row created.
SQL> rollback to b;
Rollback complete.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL>
8.In which case do we need to use for update nowait in cursors.?
Using for update nowait will cause the rows to be busy and acquires a lock until a commit or rollback is executed.
Any other session that tries to acquire a lock will get an Oracle error message like ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired instead of waiting the lock to release.
Session1:
CURSOR abc_cur
IS
select * from dept where deptno =10 for update nowait;
Here the rows are locked until the cursor is closed or a commit/rollback gets executed. If, meanwhile, another user from session 2 tries to access the same records then this will throw an error as shown below:
Session2:
select * from dept where deptno =10 for update nowait;
This user cannot even update or delete the same records that have been locked by the first session.
Usage: Now if you want to do some manipulation on certain sets of records and you don't want another user from another session to override your data then you must first lock the records(using for update nowait) and then do your manipulation. After you're done with your manipulation, close the cursor and commit.
11..What is the differance between a procedure and Function.
Both Procedures and functions have the capability of accesing parameters but function returns a value
to the PLSQL block where as procedures do not return any value.
You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.
12. Exaplin with example Nested table
DECLARE
V_DEPT EMPLOYEES.DEPARTMENT_ID%TYPE;
V_NAME EMPLOYEES.LAST_NAME%TYPE;
V_SAL EMPLOYEES.SALARY%TYPE;
V_BONUS V_SAL%TYPE;
BEGIN
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
INTO V_NAME,V_SAL,V_DEPT
FROM EMPLOYEES
WHERE EMPLOYEE_ID=&EMPNO;
IF V_DEPT=80 THEN
V_BONUS :=0;
ELSE
IF V_SAL >= 20000 THEN
V_BONUS := V_SAL*.2;
ELSIF V_SAL>=15000 THEN
V_BONUS := V_SAL*.3;
ELSIF V_SAL>= 10000 THEN
V_BONUS := V_SAL *.4;
ELSE
V_BONUS := V_SAL*.5;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('BONUS OF '||V_NAME||' IS '||V_BONUS);
END;
13.5) Write a program to accept a number and print it in reverse order
DECLARE
N
NUMBER(5):=&N;
REV
NUMBER(5):=0;
R
NUMBER(5):=0;
BEGIN
WHILE
N !=0
LOOP
R:=MOD(N,10);
REV:=REV*10+R;
N:=TRUNC(N/10);
END
LOOP;
DBMS_OUTPUT.PUT_LINE('THE
REVERSE OF A GIVEN NUMBER IS '||REV);
END;
0 comments:
Post a Comment