pl sql templates

As you may know, a lot of the code we write is repetitive. Yes, it is much better if you know exactly what most of the code is doing, but in general a lot of the code follows a similar pattern or template. Here are some pl sql templates I have used over the years.

Basic pl sql block §

SET SERVEROUTPUT ON
 
DECLARE
BEGIN
	-- print
	dbms_output.put_line('hello world');
END;
/

Select into §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare variables
	v_name VARCHAR(20);
BEGIN
	-- get last name of one record
	SELECT col1
	  INTO v_name
	  FROM tbl1
	 WHERE ROWNUM < 2;
	 
	-- print
	dbms_output.put_line('last name is: ' || v_name);
END;
/

Select into exception §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare variables
	v_name VARCHAR(20);
BEGIN
	-- get last name of one record
	SELECT col1
	  INTO v_name
	  FROM tbl1
	 WHERE col2 = 5;
 
	EXCEPTION 
	WHEN NO_DATA_FOUND THEN
		v_name := NULL;
	 
	-- print
	dbms_output.put_line('last name is: ' || COALESCE(v_name, 'MISSING'));
END;
/

Simple for loop §

SET SERVEROUTPUT ON
 
DECLARE
BEGIN
	-- loop through 9 records in tbl1
	FOR REC IN (SELECT col1
				  FROM tbl1
				 WHERE ROWNUM < 10)
		LOOP
			-- print
			dbms_output.put_line('last name is: ' || REC.col1);
		END LOOP;
END;
/

For loop with cursor §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare cursor
	CURSOR c1 IS
		SELECT col1
		  FROM tbl1
		 WHERE ROWNUM < 10;
BEGIN
	-- loop through 9 records in tbl1
	FOR REC IN c1
		LOOP
			-- print
			dbms_output.put_line('last name is: ' || REC.col1);
		END LOOP;
END;
/

Simple function §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare function
	FUNCTION get_num(p_par1 IN NUMBER) RETURN NUMBER
	IS
		v_num NUMBER := NULL;
	BEGIN
		SELECT col1
		  INTO v_num
		  FROM tbl1
		 WHERE col2 = p_par1;
	RETURN v_num;
	END get_num;
BEGIN
	-- call function and print
	dbms_output.put_line('number is: ' || get_num(10));
END;
/

Simple procedure §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare variables
	v_out NUMBER;
 
	-- declare function
	PROCEDURE get_num(p_par1 IN NUMBER, p_par2 OUT NUMBER)
	IS
	BEGIN
		SELECT col1
		  INTO p_par2
		  FROM tbl1
		 WHERE col2 = p_par1;
	END;
BEGIN
	-- call proc and print
	get_num(10, v_out);
	dbms_output.put_line('number is: ' || v_out);
END;
/

Commit every n rows §

SET SERVEROUTPUT ON
 
DECLARE
	-- declare variables
	v_LIMIT_IN PLS_INTEGER DEFAULT 1000; -- commit every 1000 updates
 
	-- declare cursor
	CURSOR c1 IS
		SELECT col1
		  FROM tbl1
		 WHERE ROWNUM < 10;
 
	TYPE tt_tbl1 IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
	t_tbl1 tt_tbl1;
BEGIN
OPEN c1;
	LOOP
		FETCH c1
		BULK COLLECT INTO t_tbl1 LIMIT v_LIMIT_IN;
	
		FOR indx IN 1 .. t_tbl1.COUNT
		LOOP
			-- do stuff
			-- here is how you get your data: t_tbl1(indx).col1
		END LOOP;
 
		commit;
 
		EXIT WHEN c1%NOTFOUND;
	END LOOP;
CLOSE c1;
END;
/

record table §

DECLARE
 
	-- declare variables
	v_id NUMBER :=0;
 
	-- declare cursor
	CURSOR c2 IS
		SELECT col1
		  FROM tbl1
		 WHERE ROWNUM < 10;
		 
	-- we delcare our table columns here
	TYPE rec is RECORD (
		a NUMBER;
		b NUMBER;
	);
	
	-- we declare our table type
	TYPE t_rec IS TABLE of rec INDEX BY BINARY_INTEGER;
 
	initrec rec;
	c_rec t_rec;
BEGIN
	FOR REC in c2
		LOOP
			-- initialize table
			IF NOT c_rec.exists(REC.col1) THEN
				c_rec(REC.col1) := initrec;
			END IF;
 
			-- add data to table
			c_rec(REC.col1) := REC.col2;
		END LOOP;
 
	-- loop through the table if it has data
	IF c_rec.FIRST IS NOT NULL THEN
		v_id := c_rec.FIRST;
 
		LOOP
			EXIT WHEN v_id IS NULL;
				DBMS_OUTPUT.PUT_LINE(c_rec(v_id).col1);
			v_id := c_rec.NEXT(v_id);
		END LOOP;
	END IF;
END;
/