PL/SQL Notes 3

Before you start

Data Conversion

DECLARE
	sa VARCHAR2(2);
	sb VARCHAR2(2);
	c NUMBER(4);
BEGIN
	sa := '5';
	sb := '10';
	c := TO_NUMBER(sa) + TO_NUMBER(sb);	
	DBMS_OUTPUT.PUT_LINE(sa || '+' || sb || '=' || c);
END;

LTRIM, RTRIM, and LENGTH

DECLARE
	first VARCHAR2(20);
	last  VARCHAR2(20);
	flen  NUMBER(3);
	llen  NUMBER(3);
BEGIN
	first := '    john';
	last  := 'smith  ';

	DBMS_OUTPUT.PUT_LINE('Before TRIMs');
	flen := LENGTH(first);
	llen := LENGTH(last);
	DBMS_OUTPUT.PUT_LINE('first = ##' || first || '##, ' || flen || ' chars');
	DBMS_OUTPUT.PUT_LINE('last  = ##' || last  || '##, ' || llen || ' chars');

	first := LTRIM(first);
	last  := RTRIM(last);

	DBMS_OUTPUT.PUT_LINE('After TRIMs');
	flen := LENGTH(first);
	llen := LENGTH(last);
	DBMS_OUTPUT.PUT_LINE('first = ##' || first || '##, ' || flen || ' chars');
	DBMS_OUTPUT.PUT_LINE('last  = ##' || last  || '##, ' || llen || ' chars');
END;

UPPER, LOWER, and INITCAP

DECLARE
	first  VARCHAR2(20);
	middle VARCHAR2(20);
	last   VARCHAR2(20);
BEGIN
	first  := 'john';
	middle := 'DOE';
	last   := 'sMITh';

	DBMS_OUTPUT.PUT_LINE('Before');
	DBMS_OUTPUT.PUT_LINE('first  = ' || first);
	DBMS_OUTPUT.PUT_LINE('middle = ' || middle);
	DBMS_OUTPUT.PUT_LINE('last   = ' || last );

	first  := UPPER(first);
	middle := LOWER(middle);
	last   := INITCAP(last);

	DBMS_OUTPUT.PUT_LINE('After');
	DBMS_OUTPUT.PUT_LINE('first  = ' || first);
	DBMS_OUTPUT.PUT_LINE('middle = ' || middle);
	DBMS_OUTPUT.PUT_LINE('last   = ' || last );
END;

INSTR and SUBSTR

DECLARE
	course  VARCHAR2(40);
	cnum  NUMBER(3);
	pos NUMBER(2);
BEGIN
	course := 'Fall 2006 INLS 723 Database Systems II';
	DBMS_OUTPUT.PUT_LINE('Course = ' || course);

	pos := INSTR(course,'INLS');
	DBMS_OUTPUT.PUT_LINE('string INLS found at pos = ' || pos);

	cnum := TO_NUMBER(SUBSTR(course,pos+5,3));
	cnum := cnum + 1;
	DBMS_OUTPUT.PUT_LINE('cnum = ' || cnum);
END;

Using PL/SQL to insert individual table rows

DECLARE
	sid sailors.sid%TYPE;
	sname sailors.sname%TYPE;
	rating sailors.rating%TYPE;
	age sailors.age%TYPE;
BEGIN
	sid := '8';
	sname := 'fredcolumbus';
	rating := '3';
	age := '32';

	INSERT INTO sailors VALUES (sid, sname, rating, age);
	COMMIT;
END;

Handling Exceptions

DECLARE
	sid sailors.sid%TYPE;
	sname sailors.sname%TYPE;
	rating sailors.rating%TYPE;
	age sailors.age%TYPE;
	error_msg VARCHAR2(512);
BEGIN
	sid := '8';
	sname := 'fredcolumbus';
	rating := '3';
	age := '32';

	INSERT INTO sailors VALUES (sid, sname, rating, age);
	COMMIT;

	EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN
			DBMS_OUTPUT.PUT_LINE('Error:  Attempted to insert a duplicate key for');
			DBMS_OUTPUT.PUT_LINE('sid = ' || sid || ' sname = ' || sname);
		WHEN OTHERS THEN
			error_msg := SQLERRM;
			DBMS_OUTPUT.PUT_LINE('This error occurred:');
			DBMS_OUTPUT.PUT_LINE(error_msg);
END;

Procedures

CREATE PROCEDURE addone(i IN NUMBER) AS
	j NUMBER(10);
BEGIN
	j := i + 1;
	DBMS_OUTPUT.PUT_LINE(j);
END addone;

/

drop procedure addone;

DECLARE
	a NUMBER(2);
BEGIN
	a := 4;
	addone(a);
END;

References and Links

• Morrison, M. and Morrison, J. (2003).  Chapter 4, "Guide to Oracle 9i".  Thomson Course Technology, Boston, Massachusetts.
• Ullman, "Using Oracle PL/SQL"
• Time and Date functions and calculations