Oracle PL/SQL/PL SQL/Variable
Содержание
Define variable and use it in SQL statement in PL/SQL
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> select * from session;
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
CS 101.00
Computer Science 101
50.00 .00 4.00 ########
ECN 203.00
Economics 203
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00
Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
NUT 307.00
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
9 rows selected.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_Department VARCHAR2(3);
3 BEGIN
4 v_Department := "CS";
5 DELETE FROM session
6 WHERE department = v_Department;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from session;
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
CS 101.00
Computer Science 101
50.00 .00 4.00 ########
ECN 203.00
Economics 203
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00
Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
NUT 307.00
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
9 rows selected.
SQL>
SQL> drop table session;
Table dropped.
SQL>
Use block name to reference a variable name
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> select * from session;
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
CS 101.00
Computer Science 101
50.00 .00 4.00 ########
ECN 203.00
Economics 203
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00
Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
-----------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
NUT 307.00
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
DEP COURSE
--- --------
DESCRIPTION
-----------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
9 rows selected.
SQL>
SQL>
SQL> <<l_DeleteBlock>>
2 DECLARE
3 Department CHAR(3);
4 BEGIN
5 Department := "CS";
6 DELETE FROM session
7 WHERE department = l_DeleteBlock.Department;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from session;
DEP COURSE
--- --------
DESCRIPTION
-----------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
-----------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
ECN 203.00
Economics 203
15.00 .00 3.00 ########
MUS 410.00
Music 410
DEP COURSE
--- --------
DESCRIPTION
-----------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
NUT 307.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
7 rows selected.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>
Use VARIABLE command to define variable
SQL>
SQL> -- Typing the VARIABLE command with no arguments provides a list of all
variables created with the
SQL> -- variable command:
SQL>
SQL> VARIABLE my_student NUMBER;
SQL>
SQL> VARIABLE
variable my_student
datatype NUMBER
SQL>
Variable command: define a variable
SQL>
SQL>
SQL> -- Print command
SQL>
SQL>
SQL> VARIABLE g_result VARCHAR2(100)
SQL> BEGIN
2 :g_result :="Hello";
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT g_result
G_RESULT
-------------------
Hello
SQL>
SQL>