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:![\"\"](\"http:\/\/62.131.51.129\/wp-content\/uploads\/2017\/12_01\/Untitled.png\")
<\/p>\n
We see that \u2018Hello World\u2019 is returned.
The procedure calls a function. This function looks like:<\/p>\n\n\n
create or replace function TEST
(
INVOER IN INT
) RETURN INT AS
v_name HR.CUSTOMER_IND.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 curptw into v_name
from CUSTOMER_IND 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.CURPTW;
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;<\/pre>\n\n\n\n