From time to time, I just repeat old exercises. I do this to maintain my knowledge before it gets lost in the mist of time. One such areas is PLSQL. You never forget that PL SQL is used to maintain data on an Oracle database. But I do forget tiny details such as how to insert a parameter.
I like to insert the PLSQL code in the client tool SQL Developper. This tool clearly shows where the code is created. It also shows how the PLSQL functions and procedures are related to other database objects like schemes, tables and views.
Let me show the example. I created one procedure that calls a function. The function looks like:
create or replace PROCEDURE MAIN AS terug int; BEGIN dbms_output.put_line(' Hello World'); terug := test(110221); END MAIN;
This procedure is quite simple. It writes something to screen (‘Hello World’) and it calls a function with a parameter (110221). It can be invoked from the command line by the command “exec main”. in this case “main” is the name of the procedure and “exec” indicates that a procedure must be run.
Starting this from sqlplus looks like:
We see that ‘Hello World’ is returned.
The procedure calls a function. This function looks like:
create or replace function TEST
INVOER IN INT
) RETURN INT AS
select curptw into v_name
from CUSTOMER_IND where CUNOCU=invoer;
SELECT COUNT(*) INTO n_table FROM user_tables WHERE table_name = UPPER(p_table_name);
IF (n_table = 0) THEN
EXECUTE IMMEDIATE 'create table ' || p_table_name || '(teller int, naam varchar(50))';
EXECUTE IMMEDIATE 'truncate table ' || p_table_name;
teller := teller + 1;
FETCH cur_chief INTO r_chief;
EXIT WHEN cur_chief%NOTFOUND;
naam := r_chief.CURPTW;
stmt := 'insert into '||p_table_name || ' (teller, naam) values(' || teller ||','''||naam||''')';
EXECUTE IMMEDIATE stmt;
This function contains some ideas that I like. The first idea is the creation of a cursor that contains an outcome set. This cursor is based on a table for which the code is given here. This outcome is made dependent on a parameter. The set of outcomes will be translated into a series of records that will be translated one by one. Such a record is indicated by variable “r_chief”.
Another trick is a check whether a certain table already exists. This is done by checking the user_tables. If it is already present, the count will be larger than 0. In that case a command will be executed that truncates the table.
A final trick is looping through the cursor. Each loop writes one record to “r_chief”. From that record, some values are retrieved. The values are then stored in an insert statement. The most tricky part is getting an apostrophe around the value of a string. The apostrophe is also used to terminate a string in the PLSQL code.