Oracle PL/SQL/Data Type/PLS INTEGER

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

Assign loop counter to pls_integer type variable

  
SQL> create or replace procedure num_test_as_pls is
  2   x pls_integer;
  3   t number := dbms_utility.get_time;
  4   begin
  5       for i in 1 .. 1000 loop
  6          x := i;
  7       end loop;
  8       dbms_output.put_line((dbms_utility.get_time-t)||"cs");
  9   end;
 10  /
Procedure created.
SQL>
SQL>
SQL>



Calculate a grand total

   
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> declare
  2      cnt pls_integer := 0;
  3  begin
  4    for i in (select ename from emp) loop
  5      cnt := cnt + 1;
  6    end loop;
  7    dbms_output.put_line(cnt);
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.



Calculation with pls_integer

  

SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> declare
  2      cnt pls_integer := 0;
  3  begin
  4    for i in (select ename from emp) loop
  5      cnt := cnt + 1;
  6    end loop;
  7    dbms_output.put_line(cnt);
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL> drop table emp;
Table dropped.



Check value of PLS_INTEGER type variable

   
SQL>
SQL>
SQL> DECLARE
  2     lv_test_num1 PLS_INTEGER := 5;
  3     lv_test_num2 PLS_INTEGER := 10;
  4     lv_test_num3 PLS_INTEGER := 15;
  5     lv_test_num4 PLS_INTEGER := 5;
  6  BEGIN
  7     IF lv_test_num1 = lv_test_num2 THEN
  8        DBMS_OUTPUT.PUT_LINE("Test 1 and Test 2 Equal");
  9     ELSIF lv_test_num1 = lv_test_num3 THEN
 10        DBMS_OUTPUT.PUT_LINE("Test 1 and Test 3 Equal");
 11     ELSIF lv_test_num1 = lv_test_num4 THEN
 12        DBMS_OUTPUT.PUT_LINE("Test 1 and Test 4 Equal");
 13     ELSE
 14        DBMS_OUTPUT.PUT_LINE("Test 1 Not Equal to Test 2,3 or 4");
 15     END IF;
 16  END;
 17  /
Test 1 and Test 4 Equal
PL/SQL procedure successfully completed.
SQL>



Count with pls_integer type variable

  
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL>
SQL> declare
  2   cnt pls_integer := 0;
  3   begin
  4       for i in (select ename from emp) loop
  5           cnt := cnt + 1;
  6       end loop;
  7       dbms_output.put_line(cnt);
  8   end;
  9  /
9
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.



PLS_Integer type variable

  
SQL> CREATE TABLE myHotel(
  2     room_id INTEGER,
  3     resident_count INTEGER,
  4     room_capacity INTEGER,
  5     name VARCHAR2(20)
  6  );
Table created.
SQL>
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(101, 20, 20, "First Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(102, 19, 20, "Second Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(103, 10, 20, "Third Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(104, 0, 20, "Fourth Room");
1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
  2  IS
  3     roomname       VARCHAR2 (20);
  4     residentcount   PLS_INTEGER;
  5     capacity       PLS_INTEGER;
  6     noroom         EXCEPTION;
  7  BEGIN
  8     SELECT resident_count, room_capacity, name
  9       INTO residentcount, capacity, roomname
 10       FROM myHotel
 11      WHERE room_id = roomin;
 12
 13     IF residentcount > capacity - 1
 14     THEN
 15        RAISE noroom;
 16     ELSE
 17        UPDATE myHotel
 18        SET resident_count = residentcount + 1
 19        WHERE room_id = roomin;
 20        COMMIT;
 21        DBMS_OUTPUT.put_line ("Student count:"||residentcount||" in "|| roomname);
 22     END IF;
 23  EXCEPTION
 24     WHEN noroom
 25     THEN
 26        DBMS_OUTPUT.put_line ("There is no room in " || roomname);
 27     WHEN OTHERS
 28     THEN
 29        DBMS_OUTPUT.put_line ("Error " || SQLERRM || " occurred.");
 30  END;
 31  /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myHotel;
Table dropped.
SQL>
SQL>
SQL>
SQL>



PLS_INTEGER variable

   
SQL>
SQL> DECLARE
  2    v_PLSInt PLS_INTEGER;
  3  BEGIN
  4    v_PLSInt := 2147483647;
  5
  6    v_PLSInt := v_PLSInt + 1 - 1;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 6

SQL>



The index variable is a PLS_INTEGER datatype number.

  
SQL> BEGIN
  2    FOR i IN 1..10 LOOP
  3      dbms_output.put_line("The index value is ["||i||"]");
  4    END LOOP;
  5  END;
  6  /
The index value is [1]
The index value is [2]
The index value is [3]
The index value is [4]
The index value is [5]
The index value is [6]
The index value is [7]
The index value is [8]
The index value is [9]
The index value is [10]
PL/SQL procedure successfully completed.
SQL>
SQL>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Data_Type/PLS_INTEGER&oldid=1580»