Get Codes: PL/SQL Code examples

PL/SQL

---------PL/SQL Code examples---------

First Create Table and Insert some values

CREATE TABLE report(seat_num
number(4),sname VARCHAR(15),total number(3),sgpi decimal(4,2),report
varchar(5));

insert into report values
(6001,'Prashant',484,7.17,'pass');

insert into report values (6002,'deepak',447,6.33,'pass');

PL/SQL Ex1 Declaring a variable and using “select-into”:

declare si varchar(10);

dm number(10);

begin



dm:=6001;

select sname into si from report where
seat_num=dm;

dbms_output.put_line('Hello '||si);

end;

output: Hello prashant

PL/SQL Ex2 Anchored variable.

declare

si varchar(10);

mp report.sgpi%type;



begin

select sname,sgpi into si,mp from report where
seat_num=6002;

dbms_output.put_line('Hello '||si||' Your Sgpi Is '||mp);

end;

output: Hello deepak Your Sgpi Is 6.33

PL/SQL Ex3. Using Constant variable.

declare

r number(10);

pi constant number(10,2):=3.142;



area number(10,2);

begin

r:=3;

area:=pi*r*r;

dbms_output.put_line(area);

end;

output: 28.26

PL/SQL Ex4. Declaring Bind Variables(Variables of sql) and using in PL/SQL

--Variables declared in sql* are known as bind variable and can be used in PL/SQL

--NOTE—Add colon(:) when using bind variable in PL/SQL block

--Declare two variables ‘s’ and ‘r’ in Sql *

variable s number;

variable r number;

---Start Plsql block and use above two
variables



begin

:s:=10;

:r:=:s*:s;

dbms_output.put_line(:r);

end;

output: 100

Stay Tuned for more such example

Get Codes