February 2019 ~ PLSQL TUTORIAL/PLSQL PROGRAMS/ Implicit, Explicit, Cursor FOR Loop

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>

12 .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

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.

13 .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.

Examples:-

1.Write a program to print Hello World.

SQL> begin

2 dbms_output.put_line('Hello World');

3 End;

4 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

Hello World

PL/SQL procedure successfully completed.

2. Write a program to find sum of two numbers from 1 to 100

DECLARE

N NUMBER(3):=1;

S NUMBER(4):=0;

BEGIN

WHILE N<=100

LOOP

S := S+N;

N :=N+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('THE SUM IS '||S);

END;

Output:-

The sum of Two Numbers is 15

3. Print Fibonnaci series using for loop.

declare

a number(3) := 0;

b number(3) :=1;

c number(3) :=0;

begin

dbms_output.put_line('Fibonacci series are upto 10 ');

for m in 1..10

loop

c:=a+b;

dbms_output.put_line(c);

a:=b;

b:=c;

end loop;

end;

Example 4:- Write a pl sql function for calculate a factorial of given number.

A Recursive function is one that calls itself.Each recursive creates a new instance of any

items declared in the subprogram.

Example:-

create or replace function fac_number(num number)

return number as

begin

if num=1 then

return 1;

else

return(num*fac_number(num-1));

end if;

end;

SQL> select fac_number(5) from dual;

FAC_NUMBER(5)

-------------

120

Example 5:

--WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100

DECLARE

N NUMBER(3) :=0;

BEGIN

WHILE N<=100

LOOP

N :=N+2;

DBMS_OUTPUT.PUT_LINE(N);

END LOOP;

END;

Example 6 .--Write a program to calculate the sum of 10 natural numbers.

Declare

a number;

tot number default 0;

Begin

a:=1;

loop

tot:=tot+a;

exit when (a=10);

a:=a+1;

end loop;

dbms_output.put_line('Sum between 1 to 100 is '||tot);

End;

Example 7 .Write a program to accept a number and print it in reverse order

DECLARE

x NUMBER(5):=&x;

REV NUMBER(5):=0;

R NUMBER(5):=0;

BEGIN

WHILE x !=0

LOOP

R:=MOD(x,10);

REV:=REV*10+R;

x:=TRUNC(x/10);

END LOOP;

DBMS_OUTPUT.PUT_LINE('THE REVERSE OF A GIVEN NUMBER IS '||REV);