ORACLE OBJECT TABLES
We have defined book_type in this post User Defined Types
DESC book_type --here our type definition user type definition ------------------------------------- TYPE book_type AS OBJECT ( ISBN_no NUMBER(12), published_year NUMBER(4) ) NOT FINAL 4 rows selected --now we are creating an object table which consists many book_types in it --object tables can only store types in it there are VARCHARS,NUMBERS in it CREATE TABLE object_table_books OF book_type; --However we can reference an object table in our standard table with REF -- keyword and SCOPE keyword CREATE TABLE borrows ( borrow_id NUMBER PRIMARY KEY, borrower VARCHAR2(100), book REF book_type SCOPE IS object_table_books -- reference to an object table ); INSERT INTO object_table_books VALUES (book_type(1111111,2009)); --we can insert book_type in this object table --with this syntax 1 rows inserted. INSERT INTO object_table_books VALUES (book_type(2222222,2009)); 1 rows inserted. SELECT * FROM object_table_books; --get newly inserted data ISBN_NO PUBLISHED_YEAR ---------------------- ---------------------- 1111111 2009 2222222 2009 2 rows selected INSERT INTO borrows (borrow_id, borrower, book) VALUES (1,'ANUTWALIDERA', (SELECT REF(otb) FROM object_table_books otb WHERE isbn_no = 1111111) ); -- we must use REF keyword to map a book_type between borrow and object_table_books --think this as a foreign key 1 rows inserted. SELECT borrow_id, borrower,DEREF(book).isbn_no FROM borrows; BORROW_ID BORROWER DEREF(BOOK).ISBN_NO ---------------------- --------------------------- ---------------------- 1 ANUTWALIDERA 1111111 1 rows selected --when selecting data from a standard table which contains a REF column data -- we should use DEREF keyword to get the data of REF column after getting -- object we can access its properties with dot notation --on the other hand object table objects values can be get with --VALUE keyword (alternative syntax) It seems unnecessary SELECT VALUE(otb) FROM object_table_books otb; VALUE(OTB) ------------------------------- HR.BOOK_TYPE(1111111,2009) HR.BOOK_TYPE(2222222,2009) 2 rows selected SELECT VALUE(otb).isbn_no FROM object_table_books otb; VALUE(OTB).ISBN_NO ---------------------- 1111111 2222222 2 rows selected
leave a comment
ORACLE USER DEFINED TYPES
With oracle users can defined their own type. By using type declaration, users can use their own type like using when creating table column by VARHCHAR, NUMBERS …etc.
TYPEs are oracle objects and store data inside of it. Here it is syntax;
--this is a book_type which we will use it in out table columns --it has two property one is isbn no the other one is published year CREATE OR REPLACE TYPE book_type AS OBJECT ( ISBN_no NUMBER(12), published_year NUMBER(4) ) NOT FINAL; --we mean this type can be inherited by other type --default value is FINAL which means we can not inherit another type from this type --inheritance gives us code reuse --now we are creating a table for these books CREATE TABLE shelves ( id NUMBER (8), book book_type --we are using our own data type ); INSERT INTO shelves VALUES (1,book_type(111111,2008)); --we can insert with this syntax SELECT * FROM shelves; ID BOOK ------------- --------------------------------- 1 HR.BOOK_TYPE(111111,2008) 1 rows selected --here we can make update UPDATE shelves SET book = book_type(222222222,2009) --book type can be assigned to a book WHERE id = 1; SELECT * FROM shelves; ID BOOK ----- --------------- 1 HR.BOOK_TYPE(222222222,2009) 1 rows selected --another usage UPDATE shelves SET book = book_type(3333,2009) WHERE book = book_type(222222222,2009); SELECT * FROM shelves; ID BOOK ----- --------------- 1 HR.BOOK_TYPE(3333,2009) 1 rows selected
Here we can use SELECT operation with the help of dot access to elements
SELECT * FROM shelves s
WHERE s.book.ISBN_no = 3333;
ID BOOK
----- ---------------
1 HR.BOOK_TYPE(3333,2009)
1 rows selected
--now we are creating a sub type by using book_type (super type)
CREATE OR REPLACE TYPE science_book_type UNDER book_type (
--under means sub type
description VARCHAR2(30),
MEMBER PROCEDURE do_someOperation(param1 IN NUMBER),
--this procedure is belong to science_book_type
MEMBER FUNCTION get_todayDate RETURN DATE
--this function is belong to science_book_type
)NOT FINAL;
--HERE we define body for function and procedure
CREATE OR REPLACE TYPE BODY science_book_type AS
MEMBER PROCEDURE do_someOperation (param1 IN NUMBER) IS
BEGIN
dbms_output.put_line('input parameter is :' || param1);
END;
MEMBER FUNCTION get_todayDate RETURN DATE IS
dtmToday DATE;
BEGIN
SELECT SYSDATE
INTO dtmtoday
FROM dual;
RETURN dtmToday;
END;
END;
--we can create a test table for new subtype like we use before
CREATE TABLE science_shelves
(
id NUMBER (8),
sbook science_book_type --our own data type which has one fuction and one procedure inside of its structure
);
INSERT INTO science_shelves
VALUES (1,science_book_type(1,1,'test for description'));
--here we insert a science book it has one more column for description
--the other 2 properties come from book type
--here we can make SELECT operations
SELECT s.sbook.description
FROM science_shelves s; -- we must use table alias here otherwise we got errors like below
SBOOK.DESCRIPTION
------------------------------
test for description
1 rows selected
-- to call a MEMBER FUNCTION
SELECT s.sbook.get_todayDate() -->member function
FROM science_shelves s;
S.SBOOK.GET_TODAYDATE()
-------------------------
28-JUN-09
1 rows selected
--to call a MEMBER PROCEDURE
DECLARE
science_book science_shelves.sbook%TYPE;
BEGIN
SELECT s.sbook
INTO science_book
FROM science_shelves s
WHERE s.sbook.isbn_no= 1;
science_book.do_someOperation(123); -->member procedure
END;
input parameter is :123
Multiple INSERT, INSERT ALL , INSERT WHEN, INSERT INTO RETURNING INTO
In this post I would like to explain SQL INSERT usages.
First we are starting with a simple INSERT clause ;
Create 2 simple tables to insert data in them;
CREATE TABLE TESTTABLE1 ( COLUMN1 VARCHAR2(20), COLUMN2 VARCHAR2(20) ); CREATE TABLE TESTTABLE2 ( COLUMN1 VARCHAR2(20), COLUMN2 VARCHAR2(20), COLUMN3 VARCHAR2(20) ); CREATE TABLE succeeded. CREATE TABLE succeeded.
Here we can see simple SQL Insert;
--Simple insert
INSERT INTO TESTTABLE1 (column1,column2) VALUES ('test1','test1');
--We can insert without specifying column names
INSERT INTO TESTTABLE1 VALUES ('test2','test2');
SELECT * FROM TESTTABLE1;
COLUMN1 COLUMN2
-------------------- --------------------
test1 test1
test2 test2
2 rows selected
Now I want to demonstrate INSERT ALL usage; With the syntax below you can insert multiple values to different tables at the same time;
TRUNCATE TABLE TESTTABLE1; --Clear all table content
TRUNCATE TABLE TESTTABLE2;
INSERT ALL
INTO TESTTABLE1 (column1,column2) -- TESTTABLE1 is used
VALUES ('Test1','Test1')
INTO TESTTABLE2 (column1,column2,column3) -- TESTTABLE2 is used
VALUES ('Test2','Test2','Test2')
INTO TESTTABLE2(column1,column2,column3) -- again TESTTABLE2 is used
VALUES ('Test2','Test2','Test2')
SELECT * FROM DUAL; -- a dummy select is required here for the syntax
--after that
SELECT * FROM TESTTABLE1;
COLUMN1 COLUMN2
-------------------- --------------------
Test1 Test1
1 rows selected
SELECT * FROM TESTTABLE2;
COLUMN1 COLUMN2 COLUMN3
-------------------- -------------------- --------------------
Test2 Test2 Test2
Test2 Test2 Test2
2 rows selected
-- as you can see we can insert into different tables
We can use WHEN THEN clause in INSERT SQL
--first here is regions data in regions table
SELECT * FROM REGIONS;
REGION_ID REGION_NAME
---------------------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
4 rows selected
--Then we can use INSERT ALL with WHEN
--create an empty table
CREATE TABLE TESTTABLE3
(
COLUMN1 VARCHAR2(20),
COLUMN2 VARCHAR2(20),
COLUMN3 VARCHAR2(20)
);
CREATE TABLE succeeded.
INSERT ALL
WHEN(region_name = 'Europe') THEN -- if region_name is equal to Europe
INTO TESTTABLE1 (column1,column2) -- we are inserting data to Testtable1
VALUES ('Europe','Europe')
WHEN(region_name = 'Asia') THEN -- if region_name is equal to Asia
INTO TESTTABLE2 (column1,column2,column3) -- we are inserting data to Testtable2
VALUES ('Asia','Asia','Asia')
ELSE --for other region_names in regions table we are inserting data into Testtable3
INTO TESTTABLE3(column1,column2,column3)
VALUES ('Others','Others','Others')
SELECT * FROM REGIONS;
-- all of the when clauses are evaluated like a case or if in JAVA programs, They don't mean else if in this structure
--we can see it like
TRUNCATE TABLE TESTTABLE1;
INSERT ALL
WHEN(region_name = 'Europe') THEN
INTO TESTTABLE1 (column1,column2)
VALUES ('Europe','Europe')
WHEN(region_id = 1) THEN --Europe's regionid is 1 in Regions table so that this is executed again
INTO TESTTABLE1 (column1,column2)
VALUES ('Europe','Europe')
SELECT * FROM REGIONS;
SELECT * FROM TESTTABLE1;
COLUMN1 COLUMN2
-------------------- --------------------
Europe Europe
Europe Europe
2 rows selected
-- we see two records as we expected
Here we can examine INSERT FIRST ;
It is really similar to INSERT ALL WHEN but by saying FIRST only the first matching WHEN is executing and the other whens are ignored
TRUNCATE TABLE TESTTABLE1;
INSERT FIRST
WHEN(region_name = 'Europe') THEN
INTO TESTTABLE1 (column1,column2)
VALUES ('Europe','Europe')
WHEN(region_id = 1) THEN --Europe's regionid is 1 in Regions table so that this is executed again
INTO TESTTABLE1 (column1,column2)
VALUES ('Europe','Europe')
SELECT * FROM REGIONS;
SELECT * FROM TESTTABLE1;
COLUMN1 COLUMN2
-------------------- --------------------
Europe Europe
1 rows selected
--second when is also true but by providing FIRST the second is never executed so we see only one record in Testtable1
Also you can insert into a SQL select
INSERT INTO ( SELECT region_id,region_name from REGIONS ) VALUES (999,'new value'); SELECT * FROM REGIONS; REGION_ID REGION_NAME ---------------------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 999 new value 5 rows selected
The last example is with INSERT INTO RETURNING
With this syntax for example you can get your last inserted data from INSERT statement
CREATE SEQUENCE TESTSEQUENCE INCREMENT BY 1
START WITH 501 MAXVALUE 9999999999 MINVALUE 500; -- create a sequence for demonstration purpose
DECLARE
ret VARCHAR2(15) :='';
BEGIN
INSERT INTO TESTTABLE1 (column1,column2)
VALUES (TESTSEQUENCE.nextval,'1') RETURNING column1 INTO ret; --here we get newly inserted sequence value
dbms_output.put_line('inserted sequence is >>> ' || ret);
INSERT INTO TESTTABLE1 (column1,column2)
VALUES (TESTSEQUENCE.nextval,'1') RETURNING column1 INTO ret; --here we get newly inserted sequence value
dbms_output.put_line('inserted sequence is >>> ' || ret);
END;
inserted sequence is >>> 501
inserted sequence is >>> 502
With this last example, we can insert into a destination table which has the same structure (I mean columns)
as source table.
DESC TESTTABLE3 Name Null Type ------------------------------ -------- --------------- COLUMN1 VARCHAR2(20) COLUMN2 VARCHAR2(20) COLUMN3 VARCHAR2(20) 3 rows selected DESC TESTTABLE2 Name Null Type ------------------------------ -------- ----------------- COLUMN1 VARCHAR2(20) COLUMN2 VARCHAR2(20) COLUMN3 VARCHAR2(20) 3 rows selected SELECT * FROM TESTTABLE2; COLUMN1 COLUMN2 COLUMN3 -------------------- -------------------- -------------------- Test2 Test2 Test2 Test2 Test2 Test2 2 rows selected SELECT * FROM TESTTABLE3; COLUMN1 COLUMN2 COLUMN3 -------------------- -------------------- -------------------- 0 rows selected -- after here INSERT INTO TESTTABLE3 --TESTTABLE3 get all rows in TESTTABLE3 SELECT * FROM TESTTABLE2 --This syntax is really handy for replication purposes SELECT * FROM TESTTABLE3; COLUMN1 COLUMN2 COLUMN3 -------------------- -------------------- -------------------- Test2 Test2 Test2 Test2 Test2 Test2 2 rows selected --it works --all the data in TESTTABLE2 is now transfered to TESTTABLE3
Oracle TRIM usages
Here are example usages;
--default behavior is trimming SPACE char SELECT '----' || TRIM (BOTH FROM ' test string for removing ') ||'----' as TRIM_RESULT FROM dual; TRIM_RESULT -------------------------------- ----test string for removing---- 1 rows selected
--here we define another char '%' SELECT '----' || TRIM (BOTH '%' FROM '%%%%%%test string for removing%%') ||'----' as TRIM_RESULT FROM dual; TRIM_RESULT -------------------------------- ----test string for removing---- 1 rows selected
--here we define another char '%' and only leading chars are removed SELECT '----' || TRIM (LEADING '%' FROM '%%%%%%test string for removing%%') ||'----' as TRIM_RESULT FROM dual; TRIM_RESULT ---------------------------------- ----test string for removing%%---- 1 rows selected
--here we define another char '%' and only trailing chars are removed SELECT '----' || TRIM (TRAILING '%' FROM '%%%%%%test string for removing%%') ||'----' as TRIM_RESULT FROM dual; TRIM_RESULT -------------------------------------- ----%%%%%%test string for removing---- 1 rows selected
--LEFT TRIM == LEADING
SELECT '----' || LTRIM ('%%%%%%test string for removing%%','%') ||'----'
as TRIM_RESULT
FROM dual;
TRIM_RESULT
----------------------------------
----test string for removing%%----
1 rows selected
--RIGHT TRIM == TRAILING
SELECT '----' || RTRIM ('%%%%%%test string for removing%%','%') ||'----'
as TRIM_RESULT
FROM dual;
TRIM_RESULT
--------------------------------------
----%%%%%%test string for removing----
1 rows selected
Oracle FOR IN LOOP Power
Oracle’s for in loop can be effectively used in simple SQL queries instead of writing cursors for select queries. Today I have coded such a for loop to DROP around 70 tables generated with a tool mistakenly (which starts with BS_) . Now I want to explain it with a basic example;
Here is the syntax
FOR reference IN ( SQL resultset ) --Here we can reference any rowLOOP END LOOP;
My very simple first example is trying to increase salaries (+500) of all employees who work as an IT programmer except
Alexander Hunold (may be he does not deserve it);
SELECT employee_id , first_name, last_name , job_id , salary
FROM employees
WHERE job_id = 'IT_PROG'
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
---------------------- -------------------- ------------------------- ---------- ----------------------
103 Alexander Hunold IT_PROG 9000
104 Bruce Ernst IT_PROG 6000
105 David Austin IT_PROG 4800
106 Valli Pataballa IT_PROG 4800
107 Diana Lorentz IT_PROG 4200
5 rows selected
--after this we wrote a FOR IN LOOP like below
BEGIN
FOR IT_PROGRAMMERS IN
(
SELECT employee_id , first_name, last_name , job_id , salary
FROM employees
WHERE job_id = 'IT_PROG'
-- here we took a resultset and reference all columns with IT_PROGRAMMERS reference
)
LOOP
IF NOT (IT_PROGRAMMERS.employee_id = 103) THEN --we mean not Alexander Hunold
UPDATE employees
SET employees.salary = employees.salary + 500 --increase it 500 $ good money
WHERE employees.employee_id = IT_PROGRAMMERS.employee_id; --reference from the original resultset
END IF;
END LOOP;
COMMIT;
END;
SELECT employee_id , first_name, last_name , job_id , salary
FROM employees
WHERE job_id = 'IT_PROG';
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID SALARY
---------------------- -------------------- ------------------------- ---------- ----------------------
103 Alexander Hunold IT_PROG 9000
104 Bruce Ernst IT_PROG 6500
105 David Austin IT_PROG 5300
106 Valli Pataballa IT_PROG 5300
107 Diana Lorentz IT_PROG 4700
5 rows selected
--yes it works
Here is another handy example which I used it for dropping around 70 tables in my database
DECLARE countval NUMBER:=0; --use it know how many drop operation would be perform sqlString VARCHAR(100); -- dynamic sqlString to produce sqlString on the fly BEGIN FOR alltables IN ( SELECT table_name FROM user_tables -- I got all user table names from this resultset ) LOOP -----------------------LOOPING BETWEEN THESE LINES---->LOOP STARTS IF(alltables.table_name LIKE 'BS_%') THEN -- reference it with alltables name and trying to learn if there is a BS_ in front of table names BEGIN countval := countval + 1; sqlString := 'DROP TABLE ' || alltables.table_name || ' CASCADE CONSTRAINTS' ; -- using cascade contraints to escape from contraints exceptions EXECUTE IMMEDIATE ''|| sqlString || ''; -- executing dynamic sql here END; END IF; -----------------------LOOPING BETWEEN THESE LINES---->LOOP ENDS END LOOP; dbms_output.put_line( countval || ' TABLE(S) IS DROPED!'); -- how many table is dropped! END;
