Oracle 101
Oracle DDL and PL-SQL
Information here is useful for all Oracle databases ; some examples are geared
toward Oracle 8 on Solaris, AIX, and Linux systems.
Back to top page
email ...
js99@rocket99.com
Copyright 1998-2006 © Citisoft, Inc. All Rights Reserved.
Oracle data types
====== DDL ======
Type Storage Range/Length Comments
----------------- ---------- -------------- -------------------
NUMBER 16 40 digit floating point
FLOAT 16 40 digit floating point
SMALLINT 16 40 digit floating point
NUMBER(a,b) varies a digits, b precision
FLOAT(a,b) varies a digits, b precision
DECIMAL 16 40 digit
INTEGER 16 40 digits
INTEGER(a) varies a digits
CHAR(a) a a=(1-255)
VARCHAR(a) varies 1 - 255
VARCHAR2(a) varies 1 - 2000
DATE 8 1/1/4217BC - 12/31/4712AD precision to minutes
LONG varies 0 - 2 GB stored inline, obsolete *
LONG RAW varies 0 - 2 GB stored inline, obsolete *
LONG VARCHAR varies 0 - 2 GB stored inline, obsolete *
BLOB varies 0 - 4 GB stored separate from table
CLOB varies 0 - 4 GB stored separate from table
NCLOB varies 0 - 4 GB stored separate from table
BFILE ?? ?? pointer to O/S file
ROWID 8 n/a row identifier within block
* Long datatypes are discouraged in Oracle 8. Note that are long and blob
datatypes are incompatible.
====== PL-SQL data types (differences) ======
Type Storage Range/Length Comments
----------------- ---------- -------------- ----------------------------
NUMERIC
VARCHAR
VARCHAR2
BLOB must be read in 32k chunks
CLOB
NCLOB
Creating a table
PCTFREE = Amount of space to leave in block during insert operations.
Allows room for records to grow within the same area.
PCUSED = The threshold at which the block is placed back on the free block list.
INITIAL/NEXT = The initial disk allocated, and the next extent size.
LOGGING = Indicates whether operations are written to the redo logs.
CREATE TABLE EMPLOYEE (
EMP_ID NUMBER(8),
LNAME VARCHAR2(30),
FNAME VARCHAR2(15),
HIRE_DT DATE,
SALARY NUMBER(8,2) )
PCTFREE 20
PCTUSED 50
STORAGE (
INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE ts01
LOGGING ;
/* Free table unallocated table blocks */
ALTER TABLE COMPANY DEALLOCATE UNUSED ;
Creating indexes
CREATE UNIQUE INDEX EMP_IDX ON EMPLOYEE (EMP_ID) ;
/* index create - table has sorted data */
CREATE UNIQUE INDEX IDX_INVOICE_ITEMS ON INVOICE_ITEMS
(INVOICE_ID,YEAR,FREQ_CODE,FREQ_NUMBER,FIELD_NUMBER,RECORD_SEQ)
TABLESPACE TS_07
NOLOGGING
NOSORT ;
/* create index - constraint */
ALTER TABLE INVOICE_FORMAT
ADD CONSTRAINT PK_INVOICE_FORMAT PRIMARY KEY(INVOICE_ID)
USING INDEX TABLESPACE PROD_IDX_01;
/* Get index information */
select
a.column_name, a.column_position,a.index_name, b.uniqueness
from user_ind_columns a, user_indexes b
where a.index_name=b.index_name and
a.table_name ='IDX_INVOICE_ITEMS'
order by a.index_name, a.column_position;
/* create bitmap index - table is fairly static,
and has less than 1000 distinct values in the indexed column */
CREATE BITMAP INDEX BIX_INVOICE_ARCHIVE
ON INVOICE_ARCHIVE (SALES_ID)
TABLESPACE PROD_IDX_01;
Creating constraints
/* primary key constraint */
ALTER TABLE EMPLOYEE (
CONSTRAINT EMP_PK
PRIMARY KEY (EMP_ID)
);
ALTER TABLE REPORT_FORMAT
ADD CONSTRAINT PK_REPORT_FORMAT PRIMARY KEY(REPORT_ID)
USING INDEX TABLESPACE PROD_IDX_01;
/* foreign key constraint */
ALTER TABLE EMPLOYEE ADD (
CONSTRAINT EMP_LOC_ASSIGN_FK
FOREIGN KEY (EMP_ID,
LOC_CD)
REFERENCES LOC_REGISTRY (
EMP_ID,
LOC_CD)
);
Creating and using a sequence
/* create a sequence for employee ids */
CREATE SEQUENCE EMP_ID_SEQ
INCREMENT BY 1
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER ;
/ * use the next emp id, and increment the sequence */
INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME)
VALUES (EMP_ID_SEQ.NEXTVAL, 'SMITH', 'JIM') ;
/* get the current value of the sequence */
INSERT INTO EMPLOYEE(EMP_ID, LNAME, FNAME)
VALUES (EMP_ID_SEQ.CURRVAL, 'SMITH', 'JIM') ;
Creating triggers
The example below illustrates versioning of the EMP_RESUME
table, which contains a blob field.
CREATE OR REPLACE TRIGGER EMP_RES_INS_TR
AFTER INSERT ON EMP_RES
FOR EACH ROW
DECLARE
VER1 NUMBER ;
EBLOB BLOB ;
VBLOB BLOB ;
BEGIN
EBLOB := EMPTY_BLOB();
SELECT (COUNT(*) + 1) INTO VER1
FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID ;
VBLOB := :NEW.RESUME ;
INSERT INTO VEMP_RES
( EMP_ID, DOC_URL,
A_USERID, D_MODIFIED, VER_NO, RESUME)
VALUES (
:NEW.EMP_ID, :NEW.DOC_URL,
USER, SYSDATE, VER1, EBLOB ) ;
SELECT RESUME
INTO EBLOB
FROM VEMP_RES
WHERE EMP_ID =:NEW.EMP_ID AND
VER_NO = VER1
FOR UPDATE ;
UPDATE VEMP_RES
SET RESUME = VBLOB
WHERE EMP_ID =:NEW.EMP_ID AND
VER_NO = VER1 ;
END;
Renaming a table
RENAME COMPANY TO CORPORATION ;
Synonyms and Database Links
-- Synonym Creation
GRANT SELECT ON USER5.COMPANY TO USER6 ;
CREATE SYNONYM USER6.COMPANY5 FOR USER5.COMPANY ;
-- Database Link
CREATE DATABASE LINK ARCHIVE_DATA CONNECT TO USER5 IDENTIFIED BY TIGER USING 'SERVER5' ;
/* user within this system can now reference tables using ARCHIVE_DATA.tablename */
Changing a column's type or name
-- Change Type
ALTER TABLE CORPORATION MODIFY (COMPANY_NM VARCHAR2(100));
alter table employee modify ( last_name varchar2(40) );
-- Change Name
alter table employee rename column last_name to last_nm ;
Moving a table
ALTER TABLE COMPANY MOVE
STORAGE (
INITIAL 200K
NEXT 200K
PCTINCREASE 0
MAXEXTENTS 50 )
TABLESPACE TS_01 ;
Partitioned Tables
Table partitioning is the best feature ever added to the Oracle RDBMS.
Chunks of data can be appended, replaced, or purged very easily when using
table partitioning. When you have more than 20 million rows in a non-static table,
partitioning is recommended. We found that bitmap indexes work better
than standard indexes on partitioned tables.
Add a partition
ALTER TABLE PHONE_DATA ADD PARTITION
p2004JUL VALUES (200407) TABLESPACE TS01 ;
Populate a partition, replaces existing data, if it exists
ALTER TABLE PHONE_DATA EXCHANGE PARTITION
p2004JUL WITH TABLE TMP_SWITCH_DATA ;
Move a partition
alter table PHONE_DATA move partition P2004JUL tablespace TS01 nologging ;
Truncate a partition
ALTER TABLE PHONE_DATA TRUNCATE PARTITION ;
Drop a partition
ALTER TABLE PHONE_DATA DROP PARTITION p2004JUL update global indexes ;
Get partition information
set pagesize 0
set linesize 120
select table_name, partition_name, blocks, tablespace_name from user_tab_partitions
order by table_name, partition_name ;
Create a local partition index
CREATE BITMAP INDEX BIX_PHONE_DATA ON PHONE_DATA
(PERIOD_KEY)
TABLESPACE TS02
LOCAL
;
exec dbms_stats.gather_table_stats ('USER77','PHONE_DATA',granularity=>'ALL',estimate_percent => 25, degree=>3 );
-- rebuild
ALTER TABLE PHONE_DATA MODIFY PARTITION
p2004JUL REBUILD UNUSABLE LOCAL INDEXES;
Using SQL-Plus
SQL-Plus is a query / command line utility which has some powerful formatting
capabilities.
Getting Started
; Command line terminator
/ Execute the current batch of commands
SET SERVEROUTPUT ON Allow messages from PL-SQL to be displayed
SHOW ERRORS Show errors from last batch
EDIT Run editor, and load buffer
CLEAR BUFFER Clear buffer commands
& Prompt for value
@ Run commands in @filename
/**** Examples ****/
/* prompt for process id, and kill */
alter system kill session '&Victim'
/
/* run commands in tables.sql */
@tables.sql
/
Creating a stored procedure
Below is a simple stored procedure which deletes an invoice.
Note:
- variable declaration placement
- the syntax for comments /* --- */
- ALL select statements must have an into statement for the
result set. Oracle stored procedures must use "out" variables to return
results to client programs.
- the declaration of INV_ID1 uses the column def as a prototype
CREATE OR REPLACE PROCEDURE PROC_DELETE_INVOICE
( USERID1 VARCHAR2, INV_ID1 INVOICE.INV_ID%TYPE )
AS
INV_COUNT NUMBER ;
BEGIN
INV_COUNT := 0;
/* check if invoice exists */
SELECT COUNT(*)
INTO INV_COUNT
FROM INVOICE
WHERE INV_ID = INV_ID1 ;
IF INV_COUNT > 0 THEN
DELETE FROM INVOICE WHERE INV_ID = INV_ID1 ;
COMMIT ;
END IF ;
END ;
Displaying output
All SELECT statements in PL-SQL must have an INTO clause; therefore another
method is needed to display output to the console.
DBMS_OUTPUT.PUT_LINE('TEST OUTPUT');
salary := 24000;
dbms_output.put_line(salary);
Output variables
Output variables are used to return data to another procedure, or to an external
application which has invoked the stored procedure.
/* sample procedure header using output variables */
TYPE INV_ARRAY IS TABLE OF NUMBER(8)
INDEX BY BINARY_INTEGER ;
CREATE OR REPLACE PROCEDURE PROC_GET_INV_NOS
( USERID1 IN VARCHAR2, INV_IDS OUT INV_ARRAY)
AS
...
Arrays and structures
Arrays and structures are implemented thought the use of "tables" and "records"
in PL-SQL.
/* EXAMPLE OF A SIMPLE RECORD TYPE */
TYPE INVOICE_REC_TYPE IS RECORD
(INV_ID INVOICE.INV_ID%TYPE,
INV_DT INVOICE.INV_DT%TYPE ) ;
/* ARRAY DECLARATION */
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER ;
NAME_TABLE NAME_TABLE_TYPE ;
/* ARRAY SUBSCRIPTING */
I := I + 1;
NAME_TABLE(I) := 'JSMITH';
Conditionals
Sample formats of conditional branching are given below:
IF <condition> THEN <statement> ;
IF <condition> THEN
<statements> ;
END IF;
/* sample statement, note the pipes for concatenation */
IF (COUNT1 = 0) AND (COUNT2 > 0) THEN
RETMSG := 'Security attributes have not been assigned, ' ||
'you are restricted.';
ELSE
RETMSG := 'You are OK';
END IF;
Looping
WHILE (I < 10)
LOOP
/* ... SOME CMDS ... */
I = I + 1;
END LOOP;
Cursors
The first example depicts dbase-style row processing ; the second a more
traditional "fetch" approach.
PROCEDURE PROC_SCAN_INVOICES (EXPIRE_DT IN DATE)
IS
CURSOR INVOICE_CUR IS
SELECT INV_ID, INV_DT FROM INVOICE ;
TYPE INVOICE_REC_TYPE IS RECORD
(INV_ID INVOICE.INV_ID%TYPE,
INV_DT INVOICE.INV_DT%TYPE ) ;
INVOICE_REC INVOICE_REC_TYPE ;
BEGIN
FOR INVOICE_REC1 IN INVOICE_CUR
LOOP
IF INVOICE_REC.INV_DT < EXPIRE_DT THEN
DELETE FROM INVOICE
WHERE INV_ID = INV_REC.INV_ID ;
DBMS_OUTPUT.PUT_LINE('INVOICE DELETETED:');
DBMS_OUTPUT.PUT_LINE(INV_REC.INV_ID);
END
END LOOP;
END;
/* ======================================= */
CREATE OR REPLACE PROCEDURE PROC_DOCEXPIRE_RPT
( RPT_BODY OUT LONG RAW )
IS
RPT_LINE VARCHAR2(1900);
RPT_PART VARCHAR2(1900);
RPT_LEAD VARCHAR2(200);
GLIB_ID1 NUMBER ;
GLIB_ID2 VARCHAR(12);
ORIG_LOC_CD1 VARCHAR2(12);
AUTHOR_ID1 VARCHAR2(30);
CONTRIBUTORS1 VARCHAR2(80);
TOPIC1 VARCHAR2(80);
NBR_ACCESS1 NUMBER ;
NBR_ACCESS2 VARCHAR2(12);
TOT_EXPIRED1 NUMBER ;
TOT_EXPIRED2 VARCHAR2(12);
COUNT1 NUMBER ;
RPT_BODY_PART LONG ;
CURSOR CUR1 IS
SELECT GLIB_ID, ORIG_LOC_CD, AUTHOR_ID, CONTRIBUTORS, TOPIC, NBR_ACCESS
FROM GEN_DOC
WHERE EXPIRE_DT < (SYSDATE + 30)
ORDER BY ORIG_LOC_CD, GLIB_ID ;
BEGIN
SELECT COUNT(*)
INTO TOT_EXPIRED1
FROM GEN_DOC
WHERE STAT_CD='90';
TOT_EXPIRED2 := TO_CHAR(TOT_EXPIRED1);
RPT_LEAD := '<H5>TOTAL EXPIRED DOCUMENT COUNT TO DATE: ... ' ||
TOT_EXPIRED2 || '</H5><HR>' ;
RPT_LINE := '<HTML><BODY BGCOLOR=#FFFFFF>' ||
'<H6>ABC Corporation</H6>' ||
'<H2>Gen Doc System - Documents Expiring Within 30 Days</H2><HR>' ||
RPT_LEAD ;
COUNT1 := 0;
OPEN CUR1;
RPT_LINE := RPT_LINE || '<TABLE>' ||
'<TD><U>No. Accesses</U></TD>' ||
'<TD><U>Document #</U></TD>' ||
'<TD><U>Topic</U></TD>' ||
'<TD><U>Author</U></TD>' ;
RPT_BODY := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_LINE := '';
LOOP
COUNT1 := COUNT1 + 1;
EXIT WHEN (COUNT1 > 500);
EXIT WHEN (UTL_RAW.LENGTH(RPT_BODY) > 32000);
FETCH CUR1 INTO
GLIB_ID1, ORIG_LOC_CD1, AUTHOR_ID1, CONTRIBUTORS1, TOPIC1, NBR_ACCESS1 ;
EXIT WHEN CUR1%NOTFOUND ;
RPT_PART := '<TR><TD>';
NBR_ACCESS2 := TO_CHAR(NBR_ACCESS1);
RPT_PART := CONCAT(RPT_PART,NBR_ACCESS2);
RPT_PART := CONCAT(RPT_PART,'</TD><TD>');
GLIB_ID2 := TO_CHAR(GLIB_ID1);
RPT_PART := RPT_PART || ORIG_LOC_CD1 || '-' || GLIB_ID2 ||
'</TD><TD>' || TOPIC1 || '</TD><TD>' ||
AUTHOR_ID1 || '</TD><TR>' ;
RPT_LINE := CONCAT(RPT_LINE, RPT_PART);
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY,RPT_BODY_PART);
-- RPT_BODY := RPT_BODY || RPT_LINE;
RPT_LINE := '';
END LOOP;
CLOSE CUR1 ;
RPT_LINE := '</TABLE></BODY></HTML>';
RPT_BODY_PART := UTL_RAW.CAST_TO_RAW(RPT_LINE);
RPT_BODY := UTL_RAW.CONCAT(RPT_BODY, RPT_BODY_PART);
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('ERROR: PROC_DOCSTAT_RPT');
GLIB_ID1 := UTL_RAW.LENGTH(RPT_BODY);
DBMS_OUTPUT.PUT_LINE(GLIB_ID1);
END;
END;
Packages
A package is a construct which bounds related procedures and functions together.
Variables declared in the declaration section of a package can be shared among the
procedures/functions in the body of the package.
/* package */
CREATE OR REPLACE PACKAGE INVPACK
IS
FUNCTION COUNTINV (SALESREP IN VARCHAR2) RETURN INTEGER;
PROCEDURE PURGEINV (INV_ID IN INTEGER) ;
END INVPACK;
/* package body */
CREATE OR REPLACE PACKAGE BODY INVPACK
IS
COUNT1 NUMBER;
FUNCTION COUNTINV (SALESREP IN VARCHAR2) RETURN INTEGER
IS
BEGIN
SELECT COUNT(*)
INTO COUNT1
FROM INVOICE
WHERE SALES_REP_ID = SALESREP ;
RETURN COUNT1 ;
END COUNTINV;
PROCEDURE PURGEINV (INV_ID1 IN INTEGER)
IS
BEGIN
DELETE FROM INVOICE
WHERE INV_ID = INV_ID1
END PURGEINV;
/* initialization section for package */
BEGIN
COUNT1 := 0 ;
END INVPACK;
Exception Handling
The following block could appear at the end of a stored procedure:
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data !!);
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('OTHER CONDITION OCCURRED !');
END;
Using Blobs
Blob variables require special handling in PL-SQL. When reading from a
file to a blob, only one statement is required. When reading from a
blob field to a PL-SQL variable, only 32k blocks can be processed, thus
necessitating a loop construct.
/*---------------------------------------*/
/* Read a blob from a file, and write */
/* it to the database. */
/*---------------------------------------*/
set serveroutput on size 500000 ;
truncate table image_test ;
create or replace directory image_dir as '/apps/temp/images' ;
create or replace procedure proc_imp_jpg
(fname1 in varchar2, image_id1 in numeric) is
file1 bfile ;
lblob blob ;
len int ;
e_blob blob ;
begin
file1 := bfilename('IMAGE_DIR',fname1);
e_blob := empty_blob();
insert into image_test (image_id, image_data)
values (image_id1, e_blob )
returning image_data into lblob ;
dbms_lob.fileopen(file1);
len := dbms_lob.getlength(file1) ;
dbms_lob.loadfromfile(lblob,file1,len);
dbms_lob.filecloseall();
commit;
exception
when others then
begin
dbms_output.put_line(sqlerrm);
dbms_lob.filecloseall();
commit;
end;
end ;
/
call proc_imp_jpg('jada.jpg',101)
/
/*-----------------------------*/
/* determine the length of */
/* a blob field */
/* by reading it */
/*-----------------------------*/
CREATE OR REPLACE PROCEDURE PROC_BLOB_LENGTH
(PART_ID1 NUMBER)
IS
SRC_LOB BLOB;
BUFFER RAW(100);
AMT BINARY_INTEGER := 100;
POS INTEGER := 1;
COUNTER INTEGER :=0;
BEGIN
SELECT PART_PHOTO INTO SCR_LOB
FROM PARTS
WHERE PART_ID=PART_ID1 ;
IF (SRC_LOB IS NOT NULL) THEN
LOOP
DBMS_LOB.READ (SRC_LOB, AMT, POS, BUFFER);
POS := POS + AMT;
COUNTER:=COUNTER+1;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('** Source is null');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data, total bytes:');
DBMS_OUTPUT.PUT_LINE(POS);
END;
/* ============ Other blob length examples ====== */
/**** Note ! These functions may return null, if the column is null ... an Oracle bug */
X := UTL_RAW.LENGTH(RPT_BODY) ;
Y := DBMS_LOB.GETLENGTH(LONG_RAW_COL);
Using the Context cartridge
Managing Context is an arduous task. The best approach is to use the
command line utility as much as possible. Below is a code sample which creates
a policy (a policy is a construct which informs context which column on
what table to scan during a search operation). The next example illustrates how
to perform a search, and stored the result keys in a table.
/* create a policy, on the emp_resume table */
ctx_svc.clear_all_errors;
dbms_output.put_line('Creating Policy ...');
ctx_ddl.create_policy(
POLICY_NAME => 'EMP_RES_POLICY',
COLSPEC => 'EMP_RES.RESUME',
SOURCE_POLICY => 'CTXSYS.DEFAULT_POLICY',
DESCRIPTION => 'EMP Policy',
TEXTKEY => 'EMP_ID',
DSTORE_PREF => 'CTXSYS.DEFAULT_DIRECT_DATASTORE',
FILTER_PREF => 'CTXSYS.HTML_FILTER',
LEXER_PREF => 'CTXSYS.DEFAULT_LEXER'
);
dbms_output.put_line('Indexing Policy ...');
ctx_ddl.create_index('EMP_POLICY');
/* Run a Context query, place the result key values in a table */
/* first, this table needs to be created */
CREATE TABLE EMP_CTX_RESULTS(
TEXTKEY VARCHAR2(64),
TEXTKEY2 VARCHAR2(64),
SCORE NUMBER,
CONID NUMBER );
/* this code can go in a stored proc */
POLICY1 := 'EMP_POLICY';
TABLE1 := 'EMP_CTX_RESULTS';
ID1 := 100 ;
QUERY1 := 'COBOL|FORTRAN';
CTX_QUERY.CONTAINS(POLICY1, QUERY1, TABLE1, 1, ID1);
/* the table will contain records with a CONID of 100 */
/* ... you can use ampersand or pipe as the conditional */
Sleep and Wait
Sometimes it is necessary to delay the execution of commands, for debugging,
or batch runs.
/* Sleep 60 seconds */
execute dbms_lock.sleep(60);
/* Sleep one hour */
execute dbms_lock.sleep(3600);
Date Manipulation
/* display current time */
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
as "SYSDATE"
from dual;
/* insert specific date/time into table */
insert into game_schedule
( sched_id, location, game_date )
values(2982, 'Chicago', to_date('2001/10/31:03:00:00PM', 'yyyy/mm/dd:hh:mi:ssam')) ;
Complex IF/THEN Processing
Submitted by: JP Vijaykumar, Oracle DBA
FOR PROCESSING DATA IN A PROCEDURE THE FOLLWOING CONDITONS
ARE TO BE SATISFIED:
01 CONDITION
TABLE TABA SHOULD HAVE MATCHING RECORDS IN TABLE TABB
(WHEN TABA.TRAINS = 'Y' AND TABB.TYPE = 'S'
AND TABA.CID = TABB.CID)
OR
(WHEN TABA.CARS = 'Y' AND TABB.TYPE = 'A'
AND TABA.CID = TABB.CID)
OR
(WHEN TABA.BIKES = 'Y' AND TABB.TYPE = 'G'
AND TABA.CID = TABB.CID)
02 CONDITION
TABLE TABB SHOULD HAVE MATCHING RECORDS IN TABLE TABC
TABB.PROD_NO = TABC.PROD_NO
03 IF ATLEAST ANY ONE COLUMN(TRAINS/CARS/BIKES) OF TABLE
TABA IS SET TO 'Y AND HAGING MATCHING RECORDS IN TABLE
TABB, ALL TABB'S RECORDS IN TURN HAVE MATCHING RECORDS IN TABC.
(LET US SAY TABB HAS 10 RECORDS FOR A PARTICULAR CID AND
TYPE FROM TABLE TABA. OUT OF WHICH ONLY 9 RECORDS ARE HAVING
MATCHES IN TABLE TABC. THEN THAT CID FROM TABLE TABA
SHOULD NOT BE PROCESSED.)
***********************************************************************/
declare
v_cnt number(10);
v_te number(10);
v_me number(10);
v_ge number(10);
v_status varchar2(10):='Y';
/***************************
TRAINS CARS BIKES
---------------------
Y Y Y
Y Y N
Y N N
N N N
N N Y
N Y Y
N Y N
Y N Y
S A G
-----------------
Y Y Y
Y Y N
Y N N
N N N
N N Y
N Y Y
N Y N
Y N Y
create table taba(cid number,TRAINS char(1),CARS char(1),BIKES char(1));
insert into taba values(1,'Y','Y','Y');
insert into taba values(2,'Y','Y','N');
insert into taba values(3,'Y','N','N');
insert into taba values(4,'N','Y','Y');
insert into taba values(5,'N','N','Y');
insert into taba values(6,'Y','N','Y');
insert into taba values(7,'N','Y','N');
insert into taba values(8,'N','N','N');
create table tabb(cid number,prod_no number,type char(1));
insert into tabb values(1,101,'S');
insert into tabb values(1,102,'A');
insert into tabb values(1,103,'G');
insert into tabb values(2,201,'S');
insert into tabb values(2,202,'A');
insert into tabb values(2,203,'G');
insert into tabb values(3,301,'S');
insert into tabb values(3,302,'A');
insert into tabb values(3,303,'G');
insert into tabb values(4,401,'S');
insert into tabb values(4,402,'A');
insert into tabb values(4,403,'G');
insert into tabb values(5,501,'S');
insert into tabb values(5,502,'A');
insert into tabb values(5,503,'G');
insert into tabb values(6,601,'S');
insert into tabb values(6,602,'A');
insert into tabb values(6,603,'G');
insert into tabb values(7,701,'S');
insert into tabb values(7,702,'A');
insert into tabb values(7,703,'G');
insert into tabb values(8,801,'S');
insert into tabb values(8,802,'A');
insert into tabb values(8,804,'G');
create table tabc(prod_no number,prod_type varchar2(10));
insert into tabc values(101,'BAN');
insert into tabc values(102,'BEN');
insert into tabc values(103,'BUN');
insert into tabc values(201,'DAN');
insert into tabc values(202,'DEN');
insert into tabc values(203,'DON');
insert into tabc values(301,'FAN');
insert into tabc values(302,'FAT');
insert into tabc values(303,'FEW');
insert into tabc values(401,'GEL');
insert into tabc values(402,'GET');
insert into tabc values(403,'GUN');
insert into tabc values(501,'HAM');
insert into tabc values(502,'HAT');
insert into tabc values(503,'HEN');
insert into tabc values(601,'LAN');
insert into tabc values(602,'LET');
insert into tabc values(603,'LUN');
insert into tabc values(701,'MAN');
insert into tabc values(702,'MEN');
insert into tabc values(703,'MOM');
insert into tabc values(801,'NET');
insert into tabc values(802,'NEW');
insert into tabc values(803,'NUN');
***************************/
begin
for c100 in (select
cid,TRAINS,CARS,BIKES
from taba a) loop
v_cnt :=0;
v_te :=0;
v_me :=0;
v_ge :=0;
if (upper(c100.TRAINS) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S';
if (v_cnt = 0) then
v_te:=v_te+1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_te:=v_te+1;
v_te:=v_te+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.CARS) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A';
if (v_cnt = 0) then
v_me:=v_me+1;
--v_num2:= v_num2 + 1;
--dbms_output.put_line('v_num2: '||v_num2 );
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_me:=v_me+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.BIKES) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G';
if (v_cnt = 0) then
v_ge:=v_ge+1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_ge:=v_ge+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if
(
((upper(c100.TRAINS)='N') and
(upper(c100.CARS)='N') and
(upper(c100.BIKES)='N')) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me=1)) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='N')))) then
dbms_output.put_line(c100.cid||' FAILURE TO PROCESS RECORDS');
elsif (
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N')))) then
dbms_output.put_line(c100.cid||' RECORDS PROCESSING SUCCESS');
end if;
end loop;
end;
declare
v_cnt number(10);
v_num1 number(10);
v_num2 number(10);
v_status varchar2(10):='Y';
/***************************
TRAINS CARS BIKES
-------------------
y y y
y y n
y n n
n y y
n n y
y n y
n y n
n n n
S A G
-------------------
1 1 1
1 1 0
1 0 0
0 1 1
0 0 1
1 0 1
0 1 0
0 0 0
create table taba(cid number,trains char(1),cars char(1),bikes char(1));
insert into taba values(1,'Y','Y','Y');
insert into taba values(2,'Y','Y','N');
insert into taba values(3,'Y','N','N');
insert into taba values(4,'N','Y','Y');
insert into taba values(5,'N','N','Y');
insert into taba values(6,'Y','N','Y');
insert into taba values(7,'N','Y','N');
insert into taba values(8,'N','N','N');
create table tabb(cid number,prod_no number,type char(1));
insert into tabb values(1,101,'S');
insert into tabb values(1,102,'A');
insert into tabb values(1,103,'G');
insert into tabb values(2,201,'S');
insert into tabb values(2,202,'A');
insert into tabb values(2,203,'G');
insert into tabb values(3,301,'S');
insert into tabb values(3,302,'A');
insert into tabb values(3,303,'G');
insert into tabb values(4,401,'S');
insert into tabb values(4,402,'A');
insert into tabb values(4,403,'G');
insert into tabb values(5,501,'S');
insert into tabb values(5,502,'A');
insert into tabb values(5,503,'G');
insert into tabb values(6,601,'S');
insert into tabb values(6,602,'A');
insert into tabb values(6,603,'G');
insert into tabb values(7,701,'S');
insert into tabb values(7,702,'A');
insert into tabb values(7,703,'G');
insert into tabb values(8,801,'S');
insert into tabb values(8,802,'A');
insert into tabb values(8,803,'G');
create table tabc(prod_no number,type varchar2(10),);
insert into tabc values(101,'BAN');
insert into tabc values(102,'BEN');
insert into tabc values(103,'BUN');
insert into tabc values(201,'DAN');
insert into tabc values(202,'DEN');
insert into tabc values(203,'DON');
insert into tabc values(301,'FAN');
insert into tabc values(302,'FAT');
insert into tabc values(303,'FEW');
insert into tabc values(401,'GEL');
insert into tabc values(402,'GET');
insert into tabc values(403,'GUN');
insert into tabc values(501,'HAM');
insert into tabc values(502,'HAT');
insert into tabc values(503,'HEN');
insert into tabc values(601,'LAN');
insert into tabc values(602,'LET');
insert into tabc values(603,'LUN');
insert into tabc values(701,'MAN');
insert into tabc values(702,'MEN');
insert into tabc values(703,'MOM');
insert into tabc values(801,'NET');
insert into tabc values(802,'NEW');
insert into tabc values(803,'NUN');
***************************/
begin
for c100 in (select
cid,trains,cars,bikes
from taba a) loop
v_cnt :=0;
v_num1 :=0;
v_num2 :=0;
if (upper(c100.trains) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S';
v_num1:= v_num1 + 1;
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.cars) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A';
v_num1:= v_num1 + 1;
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.bikes) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G';
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if ((v_num1 > 0) and (v_num1 > v_num2))
then
dbms_output.put_line(c100.cid||' RECORDS PROCESSING SUCCESS');
elsif ((v_num1 = 0) or (v_num1 = v_num2)) then
dbms_output.put_line(c100.cid||' FAILURE TO PROCESS RECORDS');
end if;
end loop;
end;
Back to top page
email ...
js99@rocket99.com
Copyright 2006 © Citisoft, Inc. All Rights Reserved.