INLS 723 Database Systems II, Fall 2006
INLS 723
Main Page
Announcements
Calendar/Notes
Assignments
DB Links
Course Policies
Grades

PL/SQL Notes 2

Before you start

Before you can see the output of PL/SQL programs in SQL*Plus, you need to set the size of the output buffer. Do this by typing the following command into SQL*Plus. Notice that there is no semi-colon at the end of the command. 
        SET SERVEROUTPUT ON SIZE 4096

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;

Running PL/SQL programs in SQL*Plus

Save the program to a file. For example, test.sql. Then, in SQL*Plus you can load the program using the start command, and run it using the forward-slash (/): 
	@test;
	/
Remember that you need to: 
        SET SERVEROUTPUT ON SIZE 4096
or you will not see any output.

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;
Then to create the procedure, type: 
/
If there were compilation errors, you may need to drop the procedure: 
drop procedure addone;
To use the procedure, you call it from another program: 
DECLARE
	a NUMBER(2);
BEGIN
	a := 4;
	addone(a);
END;

References and Links