-
Notifications
You must be signed in to change notification settings - Fork 58
/
Day-40_ORACLE_PLSQL_BLOCKS.txt
195 lines (140 loc) · 5.5 KB
/
Day-40_ORACLE_PLSQL_BLOCKS.txt
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
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
"Welcome To Ashok IT"
"Oracle Database"
Topic : Introduction To PL/SQL-Blocks
Date : 17/01/2023
(Session - 40)
_____________________________________________________________________________________________________________________________
Important Information
*********************
>> Oracle Class Notes ::: https://github.com/ashokitschool/ORACLE_CLASS_NOTES
>> Class Recording ::: Will be available through Ashok IT Portal
>> Class Related Updates "Join In WhatsApp Group" check with Admin Team.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yesterday Session
==================
* We Just started PL/SQL Introduction.
* SQL Datatypes = PLSQL Datatypes
* SQL Operators = PLSQL Operators ("=", ":=")
* Output Statement for PL/SQL i.e.,dbms_output.put_line('Message'); >>>>> System.out.println("Message");
dbms_output.put_line('Message' || a); >>>>> System.out.println("Message:::" + a);
* Input Statement For PL/SQL i.e.,&
* PL/SQL Program Structure
========================
1) declaration section >>>>>>>>> Optional Section
2) begin section >>>>>>>>> Mandatory Section
3) end section >>>>>>>>> Mandatory Section
4) Exception Section >>>>>>>>> Optional Section
5) / >>>>>>>>> Mandatory section
* We can define the business logic between the begin section and end section of an PL/SQL block.
Today Session
=============
First Example
=============
Develop the Pl/SQL Block to display the welcome message.
begin
dbms_output.put_line('Welcome To Ashok IT PL/SQL Programming.....');
end;
/
PL/SQL Procedure Completed Successfully
SQL> set serveroutput on (Enabling the Pl/SQL Environment)
SQL> / (Executing the previous PL/SQL block)
Welcome To Ashok IT PL/SQL Programming.....
PL/SQL Procedure Completed Successfully
Second Example
==============
Develop the Pl/SQL Block to display value of declared variable.
declare
a number;
begin
-- This is for Declaration
a := 150;
dbms_output.put_line('Defined Variable value::::' || a);
end;
/
OUTPUT
======
Defined Variable value:::::150
PL/SQL procedure successfully completed.
Third Example
=============
Develop the PL/SQL Block to perform addition of two numbers
declare
a number := 150;
b number := 250;
c number;
begin
c := a + b;
dbms_output.put_line('Addition Of Two Numbers:::::' || c);
end;
/
Addition Of Two Numbers:::::400
PL/SQL procedure successfully completed.
Fourth Example
================
Develop the PL/SQL Block to perform addition of two numbers using dynamic values
declare
a number := &firstNumber;
b number := &secondNumber;
c number;
begin
c := a + b;
dbms_output.put_line('Addition Of Two Numbers:::::' || c);
end;
/
Enter value for firstnumber: 254
old 2: a number := &FirstNumber;
new 2: a number := 254;
Enter value for secondnumber: 547
old 3: b number := &SecondNumber;
new 3: b number := 547;
Addition of Two Numbers:::::801
PL/SQL procedure successfully
Fifth Example
=============
Develop the PL/SQL block to get the customer details based on given customer Id ?
1) ashokit_customers >>>>>>>>> Table Name
2) customer_id,customer_name,customer_location,bill_amount >>>>>>>>> Columns
declare
customerId number := &customerId;
customerName varchar2(30);
customerLocation varchar2(30);
billAmount number
begin
select customer_name,customer_location,bill_amount into customerName,customerLocation,billAmount
from ashokit_customers where customer_id = customerId;
dbms_output.put_line(customerId || customerName || customerLocation || billAmount);
end;
/
OUTPUT
======
Enter value for customerid: 123
old 2: customerId number := &customerId;
new 2: customerId number := 123;
123 Mahesh Hyderabad 15000
PL/SQL procedure successfully completed.
%row type
=========
* By using this object we can able to declare plsql variable according to the collection of columns in sql table.
* This variable can store a value from any column in that table.
Example code
============
declare
customerId number := &customerId;
customer_record ashokit_customers%rowtype >>>>>>>>>>This is for holding the particular row of information from table
begin
select * into customer_record from ashokit_customers where customer_id=customerId;
dbms_output.put_line(customer_record.customer_name || customer_record.bill_amount);
end;
/
OUTPUT
======
Enter value for customerid: 123
old 2: customerId number := &customerId;
new 2: customerId number := 123;
Mahesh15000
PL/SQL procedure successfully completed.
Assignments
============
1) Develop the Plsql block to check whether given number is even number or odd number.
2) Develop the Plsql block to get the emp details from emp table.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++