Oracle PL/SQL/Data Type/Number Type
Содержание
- 1 Add one to current number type value of counter
- 2 An if-then statement comparing two numeric literals
- 3 A numeric variable with no specified size, no initial value
- 4 Character Data Type Conversion Chart
- 5 Compare number value in if statement
- 6 Compare number value with >
- 7 Compare number value with "between ... and"
- 8 Compare number value with =(equals)
- 9 Compare number value with != (not equals)
- 10 Comparison operator with number value and order by
- 11 Create a table with number type column: number(4)
- 12 declare number variable
- 13 Declare number variable and assign value
- 14 Do calculation in select statement with number type column
- 15 Identical declarations using NUMBER subtypes
- 16 Initiate number value to 0
- 17 NUMBER(m,n)
- 18 Number that exceeds precision
- 19 Numeric Data Type Conversion Chart
- 20 select 5.1d, 42f from dual
- 21 Update number type column with calculation
- 22 Use Case statement with number type value
- 23 Use number as the column type
- 24 use number value with in operator
Add one to current number type value of counter
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>
An if-then statement comparing two numeric literals
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.
A numeric variable with no specified size, no initial value
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>
Character Data Type Conversion Chart
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 )
Compare number value in if statement
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>
Compare number value with >
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.
Compare number value with "between ... and"
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.
Compare number value with =(equals)
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>
Compare number value with != (not equals)
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>
Comparison operator with number value and order by
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>
Create a table with number type column: number(4)
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>
declare number variable
SQL>
SQL>
SQL> declare
2 myNumber_variable number;
3 begin
4 myNumber_variable := 50;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Declare number variable and assign value
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>
Do calculation in select statement with number type column
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.
Identical declarations using NUMBER subtypes
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>
Initiate number value to 0
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>
NUMBER(m,n)
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>
Number that exceeds precision
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>
Numeric Data Type Conversion Chart
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
select 5.1d, 42f from dual
SQL>
SQL> select 5.1d, 42f from dual;
5.1D 42F
---------- ----------
5.1E+000 4.2E+001
SQL>
SQL>
Update number type column with calculation
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>
Use Case statement with number type value
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>
Use number as the column type
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>
use number value with in operator
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>