Oracle PL/SQL/PL SQL/Variable

Материал из SQL эксперт
Версия от 09:59, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/PL_SQL/Variable&oldid=1828»