learning_SQL_Oracle/Database-textbook/PLSQL/PLSQL.txt at main · ShubhagamanSingh/learning_SQL_Oracle · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
PL/SQL:-

PL/SQL IS STAND FOR PROCESSERAL LANGUAGE, STRUCTURED QUERY LANGUAGE DEVELOPED BY ORACLE CORPORATION IN 1980.
IT IS PORTABLE HIGH PERFORMANCE, TRANSACTION PROCESSING LANGUAGE.
IT IS OS INDIPENDENT. IT IS ALSO INTERPRITED.

SYNTAX OF PL/SQL CODE:-

SECTIONS:-

	declare
	/*In this section we declear variables.
	This is not maindatory.*/

	begin
	/*Here you write programming and sql statements*/
	end;
	/


code:-

begin
dbms_output.put_line('Hello Word');
end;
/

CODE OF SIMPLE CALCULATER:-			-------------------------------------

declare
a int;
b int;
begin
a:=&a;
b:=&b;
dbms_output.put_line('Summation='||(a+b));
dbms_output.put_line('Substration='||(a-b));
dbms_output.put_line('Multiplication='||(a*b));
dbms_output.put_line('Division='||(a/b));
end;
/

a:=&a---->Enter value for a // a:=<value>
a:=&b---->Enter value for b // b:=<value>

IF STATEMENT:-

if condition then
/* code of if */
end if;

IF-ELSE STATEMENT:-

if condition then
/* code of if */
else
/* code of else */
end if;

LADDER IF-ELSE STATEMENT:-

if condition1 then
/* code1 */
elsif condition2 then
/* code2 */
else
/* code3 */
end if;

WRITE A PL/SQL CODE TO CHECK GIVEN NUMBER IS EVEN OR ODD -------------------------------

declare
a int;
begin
a:=&a;
if a mod 2=0 then
dbms_output.put_line('Even');
else
dbms_output.put_line('Odd');
end if;
end;
/

LOOP IN PL/SQL:-

LOOP CONTROL:-

		IF YOU HAVE A BLOCK OF CODE WHICH YOU WANT TO REPEATEDLY UP TO GIVEN CONDOTION IS TRUE THEN YOU CAN USE LOOP CONTROL.

		IN PL/SQL THERE ARE THREE TYPES OF LOOP CONTROL:-

		1. BASIC LOOP;
		2. WHILE LOOP;
		3. FOR LOOP;

BASIC LOOP:-

	SYNTAX:-

	loop
	/* code */
	end loop;

PL/SQL CODE TO PRINT NUMBER 1 TO 5:-

declare
a int;
begin
a:=1;
loop
dbms_output.put_line(a);
a:=a+1;
exit when a>5;
end loop;
end;
/

WHILE LOOP:-

		WHILE LOOP IS WORK LIKE ENTRY CONTROL. IN WHILE LOOP FIRST CONDITION IS CHECKED THEN CODE WILL EXECUTED.

	SYNTAX:-

	while(condition)
	loop
	/* code */
	end loop;

PL/SQL CODE TO PRINT TABLE OF GIVEN NUMBER:-

declare
n int;
i int;
begin
n:=&n;
i:=1;
while(n<=10)
loop
dbms_output.put_line(n||'*'||i||'='||(n*i));
i:=i+1;
end loop;
end;
/

FOR LOOP:-

		FOR LOOP STATEMENT IS BEST SUTABLE WHEN YOU WANT TO EXECUTE A CODE FOR A KNOWN NUMBER OF TIME RATHER THAN BAISED ON SOME OTHER CONDITION.

	SYNTAX:-

	for <loop variable> in <lower limit>..<upper limit>
	loop
	/* code */
	end loop;
	end;
	/


PL/SQL CODE TO PRINT NUMBER 1 TO 10 USING FOR LOOP:-

<no need of declare section>

begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
/