Oracle PL/SQL/Data Type/Number Type

Материал из SQL эксперт
Перейти к: навигация, поиск

Add one to current number type value of counter

   <source lang="sql">
  

SQL> declare

 2      i   number := 0;
 3  begin
 4      i := i + 1;
 5  exception
 6      when others then
 7      raise_application_error (-20100, "error#" || sqlcode || " desc: " || sq

lerrm);

 8  end;
 9  /

PL/SQL procedure successfully completed. SQL>


 </source>
   
  


An if-then statement comparing two numeric literals

   <source lang="sql">
  

SQL> SQL> BEGIN

 2    IF 1 = 1 THEN
 3      dbms_output.put_line("Condition met!");
 4    END IF;
 5  END;
 6  /

Condition met! PL/SQL procedure successfully completed.


 </source>
   
  


A numeric variable with no specified size, no initial value

   <source lang="sql">
  

SQL> SQL> -- A numeric variable with no specified size. SQL> -- No initial value. SQL> -- x has the value NULL. SQL> SQL> set serverout on; SQL> SQL> DECLARE

 2     X NUMBER;
 3  BEGIN
 4
 5     DBMS_OUTPUT.PUT_LINE(x);
 6  END;
 7  /

PL/SQL procedure successfully completed. SQL>



 </source>
   
  


Character Data Type Conversion Chart

   <source lang="sql">
   

Alternative SQL Data type Oracle Equivalent Data type CHARACTER ( size ) CHAR ( size ) CHAR ( size ) CHAR ( size ) CHARACTER VARYING ( size ) VARCHAR ( size ) CHAR VARYING ( size ) VARCHAR ( size ) NATIONAL CHARACTER ( size ) NCHAR ( size ) NATIONAL CHAR ( size ) NCHAR ( size ) NCHAR ( size ) NCHAR ( size ) NATIONAL CHARACTER VARYING ( size ) NVARCHAR2 ( size ) NATIONAL CHAR VARYING ( size ) NVARCHAR2 ( size ) NCHAR VARYING ( size ) NVARCHAR2 ( size )


 </source>
   
  


Compare number value in if statement

   <source lang="sql">
  

SQL> CREATE TABLE myTable(

 2      e INTEGER,
 3      f INTEGER
 4  );

Table created. SQL> SQL> DELETE FROM myTable; 0 rows deleted. SQL> INSERT INTO myTable VALUES(1, 3); 1 row created. SQL> INSERT INTO myTable VALUES(2, 4); 1 row created. SQL> SQL> DECLARE

 2      a NUMBER;
 3      b NUMBER;
 4  BEGIN
 5      SELECT e,f INTO a,b FROM myTable WHERE e>1;
 6      IF b=1 THEN
 7          INSERT INTO myTable VALUES(b,a);
 8      ELSE
 9          INSERT INTO myTable VALUES(b+10,a+10);
10      END IF;
11  END;
12  /

PL/SQL procedure successfully completed. SQL> drop table myTable; Table dropped. SQL>


 </source>
   
  


Compare number value with >

   <source lang="sql">
  

SQL> CREATE TABLE customers

 2  (
 3     id                NUMBER,
 4     address    VARCHAR2(40),
 5     postal_code       VARCHAR2(10),
 6     city              VARCHAR2(30),
 7     state_province    VARCHAR2(40),
 8     income_level      VARCHAR2(30),
 9     credit_limit      NUMBER
10  );

Table created. SQL> SQL> SQL> select id, state_province, credit_limit

 2  from   customers
 3  where  state_province = "UT"
 4  and    credit_limit > 10000;

no rows selected SQL> SQL> SQL> drop table customers; Table dropped.


 </source>
   
  


Compare number value with "between ... and"

   <source lang="sql">
  

SQL> SQL> SQL> CREATE TABLE customers

 2  (
 3     id                NUMBER,
 4     gender            CHAR(1),
 5     year_of_birth     NUMBER(4),
 6     marital_status    VARCHAR2(20),
 7     address           VARCHAR2(40),
 8     state_province    VARCHAR2(40),
 9     email             VARCHAR2(30)
10  );

Table created. SQL> select id, gender, year_of_birth

 2  from   customers
 3  where  state_province = "CT"
 4  and    gender = "M"
 5  and    year_of_birth between 1936 and 1939;

no rows selected SQL> SQL> drop table customers; Table dropped.


 </source>
   
  


Compare number value with =(equals)

   <source lang="sql">
  

SQL> CREATE TABLE products(

 2    id              NUMBER(6),
 3    name            VARCHAR2(50),
 4    category        VARCHAR2(50),
 5    list_price      NUMBER(8,2),
 6    min_price       NUMBER(8,2)
 7  );

Table created. SQL> SQL> SQL> select id, name, category, list_price

 2  from   products
 3  where  id = 117;

no rows selected SQL> SQL> drop table products; Table dropped. SQL>


 </source>
   
  


Compare number value with != (not equals)

   <source lang="sql">
  

SQL> CREATE TABLE products

 2  (
 3    id              NUMBER(6),
 4    name            VARCHAR2(50),
 5    descr            VARCHAR2(4000),
 6    subcategory     VARCHAR2(50),
 7    subcat_desc     VARCHAR2(2000),
 8    category        VARCHAR2(50),
 9    weight_class    NUMBER(2)
10  );

Table created. SQL> SQL> SQL> SQL> select id, category, weight_class

 2  from   products
 3  where  weight_class != 1;

no rows selected SQL> SQL> SQL> drop table products; Table dropped. SQL>


 </source>
   
  


Comparison operator with number value and order by

   <source lang="sql">
   

SQL> SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SQL> SQL> SELECT * FROM product WHERE quantity_on_hand = 1; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 1 row selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand < 500; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Product 1 50 100 15-JAN-03 2 rows selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand < 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Product 1 50 100 15-JAN-03 2 rows selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand <= 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Widget 75 1000 15-JAN-02 Product 1 50 100 15-JAN-03 3 rows selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand > 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Product 2 25 10000 1 row selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand >= 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Widget 75 1000 15-JAN-02 Product 2 25 10000 2 rows selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand > 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Product 2 25 10000 1 row selected. SQL> SQL> SELECT * FROM product WHERE quantity_on_hand >= 1000; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Widget 75 1000 15-JAN-02 Product 2 25 10000 2 rows selected. SQL> SQL> SELECT * FROM product WHERE product_price IN (50, 99); PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Product 1 50 100 15-JAN-03 2 rows selected. SQL> SQL> SELECT * FROM product ORDER BY product_price DESC; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Widget 75 1000 15-JAN-02 Product 1 50 100 15-JAN-03 Product 2 25 10000 4 rows selected. SQL> SQL> DROP TABLE product; Table dropped. SQL>


 </source>
   
  


Create a table with number type column: number(4)

   <source lang="sql">
   

SQL> SQL> create table inventory(

 2  partno number(4) constraint invent_partno_pk primary key,
 3  partdesc varchar2(35) constraint invent_partdesc_uq unique);

Table created. SQL> SQL> SQL> drop table inventory; Table dropped. SQL>


 </source>
   
  


declare number variable

   <source lang="sql">
   

SQL> SQL> SQL> declare

 2      myNumber_variable number;
 3    begin
 4      myNumber_variable := 50;
 5    end;
 6    /

PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


Declare number variable and assign value

   <source lang="sql">
  

SQL> SQL> -- declare number and assign value SQL> SQL> declare

 2      myNumber number;
 3    begin
 4      myNumber := 50;
 5
 6      dbms_output.put_line(myNumber);
 7    end;
 8    /

50 PL/SQL procedure successfully completed. SQL> SQL>



 </source>
   
  


Do calculation in select statement with number type column

   <source lang="sql">
   

SQL> SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

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> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected. SQL> SQL> select ename, sal, sal * 1.1 from emp; ENAME SAL SAL*1.1


---------- ----------

SMITH 800 880 ALLEN 1600 1760 WARD 1250 1375 JONES 2975 3272.5 MARTIN 1250 1375 BLAKE 2850 3135 CLARK 2450 2695 SCOTT 3000 3300 KING 5000 5500 TURNER 1500 1650 ADAMS 1100 1210 ENAME SAL SAL*1.1


---------- ----------

JAMES 950 1045 FORD 3000 3300 MILLER 1300 1430 14 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


Identical declarations using NUMBER subtypes

   <source lang="sql">
  

SQL> SQL> SQL> -- Identical declarations using NUMBER subtypes. SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE

 2     -- all these declarations are identical.
 3     num_dec     DECIMAL(5,2);
 4     num_int     INTEGER(5,2);
 5     num_dbl     DOUBLE PRECISION(5);
 6     num_num     NUMERIC(5,2);
 7     num_real    REAL(5);
 8     num_sint    SMALLINT(5,2);
 9     num_flt     FLOAT(17);
10
11  BEGIN
12
13     num_dec := 123.456;
14     num_int := 123.456;
15     num_dbl := 123.456;
16     num_num := 123.456;
17     num_real := 123.456;
18     num_sint := 123.456;
19     num_flt := 123.456;
20
21     DBMS_OUTPUT.PUT_LINE(num_dec);
22     DBMS_OUTPUT.PUT_LINE(num_int);
23     DBMS_OUTPUT.PUT_LINE(num_dbl);
24     DBMS_OUTPUT.PUT_LINE(num_num);
25     DBMS_OUTPUT.PUT_LINE(num_real);
26     DBMS_OUTPUT.PUT_LINE(num_sint);
27     DBMS_OUTPUT.PUT_LINE(num_flt);
28   END;
29  /

123.46 123.46 120 123.46 120 123.46 123.456 PL/SQL procedure successfully completed. SQL> SQL> SQL>



 </source>
   
  


Initiate number value to 0

   <source lang="sql">
  

SQL> declare

 2      i   number := 0;
 3  begin
 4      i := i + 1;
 5  exception
 6      when others then
 7      raise_application_error (-20100, "error#" || sqlcode || " desc: " || sqlerrm);
 8  end;
 9  /

PL/SQL procedure successfully completed. SQL> SQL>


 </source>
   
  


NUMBER(m,n)

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name  VARCHAR2(25),
 3       product_price NUMBER(4,2)
 4       );

Table created. SQL> SQL> INSERT INTO product VALUES ("Product Name 1", 1); 1 row created. SQL> INSERT INTO product VALUES ("Product Name 2", 2.5); 1 row created. SQL> INSERT INTO product VALUES ("Product Name 3", 50.75); 1 row created. SQL> INSERT INTO product VALUES ("Product Name 4", 99.99); 1 row created. SQL> SQL> SELECT * FROM product; PRODUCT_NAME PRODUCT_PRICE


-------------

Product Name 1 1 Product Name 2 2.5 Product Name 3 50.75 Product Name 4 99.99 SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL>

17     l_iv := utl_i18n.string_to_raw (p_iv, "AL32UTF8");
18     l_key := utl_i18n.string_to_raw (p_key, "AL32UTF8");
19     l_enc_val :=
20        dbms_crypto.encrypt (src      => l_in,
21                             KEY      => l_key,
22                             iv       => l_iv,
23                             typ      =>   l_enc_algo
24                                         + dbms_crypto.chain_cbc
25                                         + dbms_crypto.pad_pkcs5
26                            );
27     l_ret := RAWTOHEX (l_enc_val);
28     RETURN l_ret;
29  END;
30  /

Function created. SQL> SQL> SQL>


 </source>
   
  


Number that exceeds precision

   <source lang="sql">
   

SQL> SQL> DECLARE

 2    v_TempVar NUMBER(2);
 3  BEGIN
 4    v_TempVar := 456;
 5  END;
 6  /

DECLARE

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 4

SQL>


 </source>
   
  


Numeric Data Type Conversion Chart

   <source lang="sql">
   

Alternative SQL Data type Oracle Equivalent Data type NUMERIC ( PRECISION, SCALE ) NUMBER ( PRECISION, SCALE ) DECIMAL ( PRECISION, SCALE ) NUMBER ( PRECISION, SCALE ) INTEGER NUMBER ( 38 ) INT NUMBER ( 38 ) SMALLINT NUMBER ( 38 ) FLOAT(b) NUMBER DOUBLE PRECISION NUMBER REAL NUMBER


 </source>
   
  


select 5.1d, 42f from dual

   <source lang="sql">
  

SQL> SQL> select 5.1d, 42f from dual;

     5.1D        42F

----------
 5.1E+000   4.2E+001

SQL> SQL>


 </source>
   
  


Update number type column with calculation

   <source lang="sql">
   

SQL> SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1008 Oracle                                             oracle1@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> SQL> SQL> select job_id, last_name, salary

 2        from employees
 3       where job_id in ("PROG", "MGR")
 4       order by job_id, last_name
 5      /

JOB_ID LAST_NAME SALARY


-------------------------------------------------- ----------

MGR Kyte 25000 MGR Lawson 30000 PROG Beck 20000 PROG Bliss 24000 PROG Java 20000 PROG Viper 20000 6 rows selected. SQL> update employees

 2         set salary = salary * 1.15
 3       where job_id = "PROG"
 4      /

4 rows updated. SQL> select job_id, last_name, salary

 2        from employees
 3       where job_id in ("PROG", "MGR")
 4       order by job_id, last_name
 5      /

JOB_ID LAST_NAME SALARY


-------------------------------------------------- ----------

MGR Kyte 25000 MGR Lawson 30000 PROG Beck 23000 PROG Bliss 27600 PROG Java 23000 PROG Viper 23000 6 rows selected. SQL> SQL> drop table employees; Table dropped. SQL> SQL> SQL>


 </source>
   
  


Use Case statement with number type value

   <source lang="sql">
   

SQL> SQL> declare

 2    l_num number := 3;
 3  begin
 4    case l_num
 5      when 1 then dbms_output.put_line("You selected one");
 6      when 2 then dbms_output.put_line("You selected two");
 7      when 3 then dbms_output.put_line("You selected three");
 8      when 4 then dbms_output.put_line("You selected four");
 9      when 5 then dbms_output.put_line("You selected five");
10      when 6 then dbms_output.put_line("You selected six");
11      when 7 then dbms_output.put_line("You selected seven");
12      when 8 then dbms_output.put_line("You selected eight");
13      when 9 then dbms_output.put_line("You selected nine");
14      when 0 then dbms_output.put_line("You selected zero");
15      --else dbms_output.put_line("You selected more than one digit...");
16    end case;
17  end;
18  /

PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Use number as the column type

   <source lang="sql">
   

SQL> SQL> create table retired_employees(

 2        employee_id number,
 3        last_name   varchar2(30)
 4      );

Table created. SQL> SQL> SQL> drop table retired_employees; Table dropped. SQL>


 </source>
   
  


use number value with in operator

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product WHERE product_price IN (50, 99); PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Product 1 50 100 15-JAN-03 2 rows selected. SQL> SQL> DROP TABLE product; Table dropped. SQL>


 </source>