|
|
PL/SQL Notes
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
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;
Using PL/SQL to read and process table data -- One Field
In Oracle PL/SQL, cursors are used to help read
and process data from tables.
This example uses a cursor to read one field from a table.
DECLARE
CURSOR sailors_cursor IS
SELECT DISTINCT sname
FROM reserves r, sailors s
WHERE (day - TO_DATE('04-MAY-06') > 0) AND
r.sid = s.sid;
current_sname sailors.sname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sailors who have reserved boats after May 4, 2006:');
OPEN sailors_cursor;
LOOP
FETCH sailors_cursor INTO current_sname;
EXIT WHEN sailors_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(current_sname);
END LOOP;
CLOSE sailors_cursor;
END;
Using PL/SQL to read and process table data -- Whole Rows
This example uses a cursor to read an entire row from a table.
DECLARE
CURSOR sailors_cursor IS
SELECT sname, day
FROM reserves r, sailors s
WHERE (day - TO_DATE('04-MAY-06') > 0) AND
r.sid = s.sid;
sailors_row sailors_cursor%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sailors who have reserved boats after May 4, 2006:');
DBMS_OUTPUT.PUT_LINE('Name' || ' ' || 'Reservation Date');
OPEN sailors_cursor;
LOOP
FETCH sailors_cursor INTO sailors_row;
EXIT WHEN sailors_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sailors_row.sname || ' ' || sailors_row.day);
END LOOP;
CLOSE sailors_cursor;
END;
References and Links
|