UNC
School of Information and Library Science 		INLS723 – Database Systems II, Fall 2007
INLS 723

Main Page

Announcements

Calendar/Notes

Assignments

Links

Course Policies

Grades

PL/SQL Notes 1

Before you start

If you are using SQL*Plus...  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

If you are using Oracle SQL Developer...  Enter your PL/SQL programs into the window where you normally enter your SQL statements. To enable output, click on the "DBMS Output" tab and then click the "Enable DBMS Output" button (to the left of the pencil eraser button). Run the PL/SQL program by clicking the "Run Script" button or pressing F5. After the script is run, in the lower part of the screen, the "Script Output" window will show "anonymous block completed" to indicate that the script has finished running. To see the output, click on the "DBMS Output" tab.

Simple Program

DECLARE
	hello VARCHAR2(20);
	world VARCHAR2(20);
BEGIN
	hello := 'Hello';
	world := 'world';
	DBMS_OUTPUT.PUT_LINE(hello || ' ' || world || '!');
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.

Simple Program #2

DECLARE
	hello VARCHAR2(20);
	world VARCHAR2(20);
	a NUMBER(2);
	b NUMBER(2);
	c NUMBER(3);
	today DATE;
BEGIN
	hello := 'Hello';
	world := 'world';
	DBMS_OUTPUT.PUT_LINE(hello || ' ' || world || '!');
	a := 5;
	b := 10;
	c := a + b;	
	DBMS_OUTPUT.PUT_LINE(a || '+' || b || '=' || c);
	today := SYSDATE;
	DBMS_OUTPUT.PUT_LINE('Today is ' || today);
END;

IF...THEN...ELSE

DECLARE
	a NUMBER(2);
	b NUMBER(2);
	c NUMBER(3);
BEGIN
	a := 5;
	b := 10;
	c := a + b;	
	DBMS_OUTPUT.PUT_LINE('a+b=c is ' || a || '+' || b || '=' || c);
	IF c < 10 THEN
		DBMS_OUTPUT.PUT_LINE('c is less than 10');
	ELSE
		DBMS_OUTPUT.PUT_LINE('c is greater than or equal to 10');
	END IF;
END;

LOOP..EXIT

DECLARE
	a NUMBER(2);
BEGIN
	a := 0;
	LOOP
		DBMS_OUTPUT.PUT_LINE('a = ' || a);
		a := a + 1;
		IF a > 8 THEN EXIT;
		END IF;
	END LOOP;
END;

EXIT..WHEN

DECLARE
	a NUMBER(2);
BEGIN
	a := 0;
	LOOP
		DBMS_OUTPUT.PUT_LINE('a = ' || a);
		a := a + 1;
		EXIT WHEN a=8;
	END LOOP;
END;

WHILE..LOOP

DECLARE
	a NUMBER(2);
BEGIN
	a := 0;
	WHILE a < 8 LOOP
		DBMS_OUTPUT.PUT_LINE('a = ' || a);
		a := a + 1;
	END LOOP;
END;

FOR..LOOP

DECLARE
BEGIN
	FOR a IN 0..8 LOOP
		DBMS_OUTPUT.PUT_LINE('a = ' || a);
	END LOOP;
END;

NESTED LOOPS

DECLARE
BEGIN
	FOR a IN 0..8 LOOP
		FOR b in 0..3 LOOP
			DBMS_OUTPUT.PUT_LINE('a = ' || a || ' b = ' || b);
		END LOOP;
	END LOOP;
END;

References and Links