====== 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.
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 ;
Creating indexes
CREATE UNIQUE INDEX EMP_IDX ON EMPLOYEE (EMP_ID) ;
Creating constraints
/* primary key constraint */
ALTER TABLE EMPLOYEE (
CONSTRAINT EMP_PK
PRIMARY KEY (EMP_ID)
);
/* 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;
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
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;
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')) ;
No comments:
Post a Comment