Oracle PL/SQL/Data Type/PLS INTEGER
Содержание
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>