http://sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL%2FPL_SQL%2FVariable
Oracle PL/SQL/PL SQL/Variable - История изменений
2026-04-04T10:29:01Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=Oracle_PL/SQL/PL_SQL/Variable&diff=1827&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:19Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=Oracle_PL/SQL/PL_SQL/Variable&diff=1828&oldid=prev
Admin: 1 версия
2010-05-26T09:59:08Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==Define variable and use it in SQL statement in PL/SQL==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> CREATE TABLE session (<br />
2 department CHAR(3),<br />
3 course NUMBER(3),<br />
4 description VARCHAR2(2000),<br />
5 max_lecturer NUMBER(3),<br />
6 current_lecturer NUMBER(3),<br />
7 num_credits NUMBER(1),<br />
8 room_id NUMBER(5)<br />
9 );<br />
Table created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);<br />
1 row created.<br />
SQL><br />
SQL> select * from session;<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
HIS 101.00<br />
History 101<br />
30.00 11.00 4.00 ########<br />
HIS 301.00<br />
History 301<br />
30.00 .00 4.00 ########<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
CS 101.00<br />
Computer Science 101<br />
50.00 .00 4.00 ########<br />
ECN 203.00<br />
Economics 203<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
15.00 .00 3.00 ########<br />
CS 102.00<br />
Computer Science 102<br />
35.00 3.00 4.00 ########<br />
MUS 410.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
Music 410<br />
5.00 4.00 3.00 ########<br />
ECN 101.00<br />
Economics 101<br />
50.00 .00 4.00 ########<br />
<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
NUT 307.00<br />
Nutrition 307<br />
20.00 2.00 4.00 ########<br />
MUS 100.00<br />
Music 100<br />
100.00 .00 3.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
<br />
9 rows selected.<br />
SQL><br />
SQL><br />
SQL><br />
SQL> DECLARE<br />
2 v_Department VARCHAR2(3);<br />
3 BEGIN<br />
4 v_Department := "CS";<br />
5 DELETE FROM session<br />
6 WHERE department = v_Department;<br />
7 END;<br />
8 /<br />
PL/SQL procedure successfully completed.<br />
SQL><br />
SQL><br />
SQL> select * from session;<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
HIS 101.00<br />
History 101<br />
30.00 11.00 4.00 ########<br />
HIS 301.00<br />
History 301<br />
30.00 .00 4.00 ########<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
CS 101.00<br />
Computer Science 101<br />
50.00 .00 4.00 ########<br />
ECN 203.00<br />
Economics 203<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
15.00 .00 3.00 ########<br />
CS 102.00<br />
Computer Science 102<br />
35.00 3.00 4.00 ########<br />
MUS 410.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
Music 410<br />
5.00 4.00 3.00 ########<br />
ECN 101.00<br />
Economics 101<br />
50.00 .00 4.00 ########<br />
<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
NUT 307.00<br />
Nutrition 307<br />
20.00 2.00 4.00 ########<br />
MUS 100.00<br />
Music 100<br />
100.00 .00 3.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
<br />
9 rows selected.<br />
SQL><br />
SQL> drop table session;<br />
Table dropped.<br />
SQL><br />
</source><br />
<br />
<br />
<br />
<br />
==Use block name to reference a variable name==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL><br />
SQL> CREATE TABLE session (<br />
2 department CHAR(3),<br />
3 course NUMBER(3),<br />
4 description VARCHAR2(2000),<br />
5 max_lecturer NUMBER(3),<br />
6 current_lecturer NUMBER(3),<br />
7 num_credits NUMBER(1),<br />
8 room_id NUMBER(5)<br />
9 );<br />
Table created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);<br />
1 row created.<br />
SQL><br />
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)<br />
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);<br />
1 row created.<br />
SQL><br />
SQL> select * from session;<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
HIS 101.00<br />
History 101<br />
30.00 11.00 4.00 ########<br />
HIS 301.00<br />
History 301<br />
30.00 .00 4.00 ########<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
CS 101.00<br />
Computer Science 101<br />
50.00 .00 4.00 ########<br />
ECN 203.00<br />
Economics 203<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
15.00 .00 3.00 ########<br />
CS 102.00<br />
Computer Science 102<br />
35.00 3.00 4.00 ########<br />
MUS 410.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
Music 410<br />
5.00 4.00 3.00 ########<br />
ECN 101.00<br />
Economics 101<br />
50.00 .00 4.00 ########<br />
<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
-----------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
NUT 307.00<br />
Nutrition 307<br />
20.00 2.00 4.00 ########<br />
MUS 100.00<br />
Music 100<br />
100.00 .00 3.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
-----------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
<br />
9 rows selected.<br />
SQL><br />
SQL><br />
SQL> <<l_DeleteBlock>><br />
2 DECLARE<br />
3 Department CHAR(3);<br />
4 BEGIN<br />
5 Department := "CS";<br />
6 DELETE FROM session<br />
7 WHERE department = l_DeleteBlock.Department;<br />
8 END;<br />
9 /<br />
PL/SQL procedure successfully completed.<br />
SQL><br />
SQL><br />
SQL> select * from session;<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
-----------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
HIS 101.00<br />
History 101<br />
30.00 11.00 4.00 ########<br />
HIS 301.00<br />
History 301<br />
30.00 .00 4.00 ########<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
-----------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
ECN 203.00<br />
Economics 203<br />
15.00 .00 3.00 ########<br />
MUS 410.00<br />
Music 410<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
-----------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
5.00 4.00 3.00 ########<br />
ECN 101.00<br />
Economics 101<br />
50.00 .00 4.00 ########<br />
NUT 307.00<br />
DEP COURSE<br />
--- --------<br />
DESCRIPTION<br />
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------<br />
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID<br />
------------ ---------------- ----------- --------<br />
Nutrition 307<br />
20.00 2.00 4.00 ########<br />
MUS 100.00<br />
Music 100<br />
100.00 .00 3.00<br />
<br />
7 rows selected.<br />
SQL><br />
SQL> drop table session;<br />
Table dropped.<br />
SQL><br />
SQL><br />
</source><br />
<br />
<br />
<br />
<br />
==Use VARIABLE command to define variable==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> -- Typing the VARIABLE command with no arguments provides a list of all <br />
variables created with the<br />
SQL> -- variable command:<br />
SQL><br />
SQL> VARIABLE my_student NUMBER;<br />
SQL><br />
SQL> VARIABLE<br />
variable my_student<br />
datatype NUMBER<br />
SQL><br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Variable command: define a variable==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL><br />
SQL> -- Print command<br />
SQL><br />
SQL><br />
SQL> VARIABLE g_result VARCHAR2(100)<br />
SQL> BEGIN<br />
2 :g_result :="Hello";<br />
3 END;<br />
4 /<br />
PL/SQL procedure successfully completed.<br />
SQL><br />
SQL> PRINT g_result<br />
G_RESULT<br />
-------------------<br />
Hello<br />
SQL><br />
SQL><br />
<br />
</source></div>
Admin