Scribd
Upload
3 views3 pages

PL/SQL Basics: Variables, Loops, Triggers

The document provides examples of PL/SQL programming, including variable declaration, arithmetic operations, string concatenation, conditional statements, loops, and creating sequences. It also covers the creation of a trigger for maintaining historical data upon deletion. Overall, it serves as a guide for basic PL/SQL syntax and functionalities.

Uploaded by

meprojects.gg
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views3 pages

PL/SQL Basics: Variables, Loops, Triggers

The document provides examples of PL/SQL programming, including variable declaration, arithmetic operations, string concatenation, conditional statements, loops, and creating sequences. It also covers the creation of a trigger for maintaining historical data upon deletion. Overall, it serves as a guide for basic PL/SQL syntax and functionalities.

Uploaded by

meprojects.gg
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

===############# PL/SQL ##############===

=========>>>> 1)
declare
a number(4);
b number(4);
begin
a:=4;
b:=6;
DBMS_OUTPUT.PUT_LINE(a+b);
DBMS_OUTPUT.PUT_LINE('Bhavin' || ' ' || 'Jariwala'); /*concatination of string
using pipe operator*/
end;

=========>>>> 2)
declare
a number(4);
b number(4);
begin

b:=6;
DBMS_OUTPUT.PUT_LINE(a+b);
/*If any one value is NULL than output will be NULL. Default value of variable is
NULL*/
end;

==========>>> CONDITIONAL STATEMENTS <<<============


DECLARE
AGE NUMBER(4);
BEGIN
AGE:=94;
IF AGE>=18 AND AGE<60 THEN
DBMS_OUTPUT.PUT_LINE('You are major now');
ELSIF AGE>=60 THEN
DBMS_OUTPUT.PUT_LINE('You are retired now');
ELSE
DBMS_OUTPUT.PUT_LINE('You are minor yet');
END IF;
END;

==========>>> LOOPS <<<===============


// FOR LOOP
/*
--->SYNTAX:
FOR index IN from..to LOOP
#code
END LOOP;
*/
1)
DECLARE
AC_COUNT [Link]%TYPE;
BEGIN
SELECT COUNT(ACCOUNTNO) INTO AC_COUNT FROM ACCOUNT;
FOR i IN 1..AC_COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ACCOUNT HOLDER:' || i);
END LOOP;
END;

2)
// LOOP
/*
--->SYNTAX
LOOP

#code
EXIT WHEN exit_condition
END LOOP;
// 'END LOOP' passes control to the 'LOOP'
*/
DECLARE
I NUMBER(6) := 0;
AC_NO [Link]%TYPE;
BEGIN
AC_NO := 105;
LOOP
AC_NO := AC_NO + 1;
INSERT INTO ACCOUNT VALUES(AC_NO,12000);
I := I + 1;
EXIT WHEN I>5;
END LOOP;
END;

SELECT * FROM ACCOUNT

=====================>>> CREATING SEQUENCE <<<================

/*
--->SYNTAX:
CREATE SEQUENCE sequence_name
STARTS WITH initial_value
INCREMENTED BY incrementation_value
MAXVALUE value till squence will be increment value
MINVALUE minimum value(uses if you decrement value or increment by -1)
CYCLE|NOCYCLE

CYCLE : if sequence reaches it's maximum value it will again starts from initial
value
NOCYCLE: if sequence exceeds its maximum value an exception will be thrown
*/
create sequence bankid
start with 1
increment by 1;
delete from account; //delete all the records from table

begin
insert into account values([Link],4500);
insert into account values([Link],2500);
insert into account values([Link],20000);
insert into account values([Link],45000);
insert into account values([Link],13000);
insert into account values([Link],15000);
insert into account values([Link],25000);
end;
select * from account

=================>>>>>>>>> TRIGGER <<<<<<<<<==================

CREATE TRIGGER STU_HISTORY


BEFORE DELETE ON STUD_DATA
FOR EACH ROW
BEGIN
INSERT INTO STU_HISTORY VALUES(:[Link], :[Link]);
END;

delete from stud_data;

select * from stu_history;

You might also like