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 v_name C##SCOTT.CUSTOMER.CURPNM%TYPE; n_table INT; teller INT:=0; l_uitvoer VARCHAR(100); p_table_name VARCHAR(100); stmt VARCHAR(250); naam VARCHAR(100); CURSOR cur_chief IS select CUDLTN into v_name from CUSTOMER where CUNOCU=invoer; r_chief cur_chief%ROWTYPE; BEGIN p_table_name :='ff'; 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))'; else EXECUTE IMMEDIATE 'truncate table ' || p_table_name; END IF; OPEN cur_chief; LOOP teller := teller + 1; FETCH cur_chief INTO r_chief; EXIT WHEN cur_chief%NOTFOUND; naam := r_chief.CUDLTN; stmt := 'insert into '||p_table_name || ' (teller, naam) values(' || teller ||','''||naam||''')'; DBMS_OUTPUT.PUT_LINE(stmt); EXECUTE IMMEDIATE stmt; END LOOP; RETURN 1; END TEST;
This function contains some ideas that I like. The first idea is the creation of a cursor that contains an outcome set. 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.