sql programming 0 Comments

Third type of sequence statement

1) goto statement: it transfer the flow of control to the label unconditionaly.

syntex: goto ;
<>
<--------------------------------------------------------------------------------->
Q:wap to find larger of two no by using goto statement?
Ans:
set serveroutput on;
declare
num1 number(2);
num2 number(2);
begin
num1:=&num1;
num2:=&num2;
if num1>num2 then
goto p1;
else
goto p2;
end if;
<>
dbms_output.put_line('num1 is larger');
<>
dbms_output.put_line('num2 is larger');
end;

OUTPUT:
old 5: num1:=&num1;
new 5: num1:=2;
old 6: num2:=&num2;
new 6: num2:=3;
num2 is larger
PL/SQL procedure successfully completed.
<----------------------------------------------------------------------------------------->

TRIGGER:
1)it is a store processure which execute automatically when a triggring event occur in the database.
2)it is in the form of INSERT /UPDATE/DELETE.
3) it is used to maintain the intigrity in the database.

DIFFERNCE BETWEEN TRIGGER AND PROCEDURE
1)trigger do not accept parameter
2)trigger are exicuted automaticaly without user calling.
example:

=>Parts of the trigger
1) triggering event or statement:
2) trigring restriction: true/false
3) triggering action: if statement is true then triggering event occur in the database in the form of PL/SQL

<-------syntex--------->
(create and replace trigger trigger name
BEFORE /AFTER
INSERT/UPDATE/DELETE of column name
ON TABLE NAME
REFRENCING OLD AS OLD ,NEW AS NEW
FOR EACH ROW
WHEN CONDITION
declare
constants and variable declaration
begin
PL/SQL statement
end;
<----end data-blogger-escaped-of="" data-blogger-escaped-syntex-------="">
BEFORE: it execute its action before the triggering statements
AFTER:it execute its action after triggering statements.
WHEN TRIGGER: it is triggering restriction.
Q:WAP to create PL/SQL trigger which will tell about the operation perform on databaase?
ANS:
create table student350
(
rollno int,
name char(20),
marks int
);
set serveroutput on;
create or replace trigger t34
before insert or update or delete
on student350
begin
if inserting then
dbms_output.put_line('operation perform in insertion');
else if updating then
dbms_output.put_line('operation perform in updation');
else
dbms_output.put_line('operation perform in deletion');
end if;
end if;
end;
insert into student350 values(01,'rahul',75);
insert into student350 values(02,'sumit',85);
insert into student350 values(03,'aditya',75);
insert into student350 values(04,'amit',85);

OUTPUT:
operation perform in insertion
1 row created.

operation perform in insertion
1 row created.

operation perform in insertion
1 row created.

operation perform in insertion
1 row created.

Q: create the PL/SQl trigger which will convert name in uppercase before inserting into table?
ANS:
create table student350
(
rollno int,
name char(20),
marks int
);
set serveroutput on;
create or replace trigger t36
before insert or update of name on student350
for each row
begin
:new.name:=upper(:new.name);
end;
insert into student350 values(01,'rahul',75);
insert into student350 values(02,'sumit',85);
insert into student350 values(03,'aditya',75);
insert into student350 values(04,'amit',85);

OUTPUT:

TO drop TRIGGER: drop trigger trigger name;

Q: create a trigger that insert a tupple into t5when the tupple into t4 spaceficaly the trigger chacks w

hether the new tupple has a first coponent 10 or less and if show the reverse tupple into t5.

ANS:

create table t41

(

a int,

b char(10)

);

create table t51

(

c char(10),

d int

);

set serveroutput on;

create or replace trigger t41

after insert on t41

for each row

when(new.a<=10) //trigger restriction

begin

insert into t51 values(:new.b,:new.a);

end;

insert into t41 values(5,'asd')

https://www.mediafire.com/folder/9z3cmblhwymm0/ds_pracicle

Q:create a trigger that displays the number of employees after every delete in emp table

Ans:

create table emp0248

(

empno number(20),

name char(20)

);

create or replace trigger t0248

after delete on emp0248

declare

n int;

begin

select count(n) into n from emp;

dbms_output.put_line('Employee Remaining'||n||'after deletion');

end;

Q:stop the transection if the salary enter by the user exceed 50000 in table employee?

Ans:

create or replace trigger t0248

before inserte and delete of sal in emp

for each row

when(new.sal>50000)

begin

RAISE_APPLICATION_ERROR(20001,''MSC'); //use to raise problem in program

end;

server: http://172.17.14.100/isqlplus dated:- 4/4/14

subprograms:-

1)Procedures

2)Stored Procedure

3)Functions

Procedure-Syntex

set serverouput on;

declare

global variables

procedure procedurename(arguments IN/OUT/INOUT detatype)

IN/AS

begin

PL/SQL statements;

End procedurename

begin

exwcute start;

procedure calling;

end;

IN: this type of parameterwill accept the value from the user.

OUT: this type of parameter will return th value to the user.

IN/OUT:this type of parameter will accept as well as return the value from the user.

//WAP to find the product of two number

set serveroutput on;

declare

num1 number(2);

num2 number(2);

mul number(4);

procedure multiplication

(num1 IN number,

num2 IN number,

mul OUT number

)

begin

mul:=num1*num2;

end multiplication;

begin

num1:=&num1;

num2:=&num2;

multiplication(num1,num2,mul);

dbms_output.put_line(mul);

end;

//Write a stored procedure to find sum of two number

set serveroutput on;

declare

num1 number(2);

num2 number(2);

mul number(4);

procedure multiplication

(num1 IN number,

num2 IN number,

mul OUT number

)

begin

mul:=num1+num2;

end multiplication;

begin

num1:=&num1;

num2:=&num2;

multiplication(num1,num2,mul);

dbms_output.put_line(mul);

end;

// create or replace procedure->

create or replace procedure as321(num1 in number,num2 in number, asd out number)

begin

asd:=num1+num2;

end;

set serveroutput on;

declare

num1 number(2);

num2 number(2);

asd number(2);

begin

num1:=&num1;

num2:=&num2;

as321(num1,num2, asd);

dbms_output.put_line(asd);

end;

<---------------function---------------------->
syntex:
set serveruotput on;
declare
global variable
function functionname(argument
return datatype
is
begin
pl/sql statement;
return(variable);
end functionname;
begin
executable statements;
end;
mul:=multiply(num1,num2,mul);
dbms_output.put_line(mul);
end;

//Write a function to multiply two number
set serveroutput on;
declare
num1 number(2);
num2 number(2);
mul number(4);
function multiplication
(num1 IN number,
num2 IN number,
mul OUT number
)
return number
IS
begin
mul:=num1*num2;
return(mul);
end multiplication;
begin
num1:=&num1;
num2:=&num2;
mul:=multiplication(num1,num2,mul);
dbms_output.put_line(mul);
end;