PLSQL Procedure in Erlang
Functions/Procedures from Erlang:
Prepare your DB:
drop table employee;
CREATE TABLE EMPLOYEE
( "NR" NUMBER,
"FIRSTNAME" VARCHAR2(20),
"LASTNAME" VARCHAR2(20),
"GENDER" CHAR(1),
"EXPERIENCE" NUMBER
) ;
ALTER TABLE EMPLOYEE ADD PRIMARY KEY
("NR");
DROP SEQUENCE EMPLOYEE_SEQ;
CREATE SEQUENCE EMPLOYEE_SEQ INCREMENT
BY 1 START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1
CACHE 20;
create or replace TRIGGER
EMPLOYEE_SEQ_TRG
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.NR IS NULL
THEN
SELECT EMPLOYEE_SEQ.NEXTVAL INTO
:NEW.NR FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
insert into EMPLOYEE(FIRSTNAME,
LASTNAME, GENDER, EXPERIENCE) values ('Richa','Vaidya','F',4);
select * from EMPLOYEE;
and check the output.
Lets create a procedure for inserting:
create or replace procedure
insert_employee (V_FIRSTNAME IN EMPLOYEE.FIRSTNAME%TYPE,
V_LASTNAME IN EMPLOYEE.LASTNAME%TYPE,
V_GENDER IN EMPLOYEE.GENDER%TYPE ,
V_EXPERIENCE IN
EMPLOYEE.EXPERIENCE%TYPE, V_NR OUT EMPLOYEE.NR%TYPE) as
begin
INSERT INTO EMPLOYEE (FIRSTNAME,
LASTNAME, GENDER, EXPERIENCE) VALUES
(V_FIRSTNAME, V_LASTNAME, V_GENDER,
V_EXPERIENCE) RETURNING NR INTO V_NR;
end;
To get the output and confirm that your
procedure is working fine:
exec INSERT_EMPLOYEE('Foo', 'Bar', 'M',
5, :V_NR);
print :V_NR;
Your Employee table looks like this
now:
Erl program: (Here I had problems while saving numbers in db from erl, so i formatted the callable)
Save the file as dbInsert.erl:
-module(dbInsert).
-export([save_to_db/4]).
save_to_db(FirstName, LastName, Gender,
Experience) ->
odbc:start(),
{ok,
Ref}=odbc:connect("DSN=****;UID=****;PWD=****",
[{scrollable_cursors, off},{time_out, infinity}]),
Formated_Sql=io_lib:format("{call INSERT_EMPLOYEE(?,?,?,~p,?)}",
[Experience]),
SQL=binary_to_list(binary:list_to_bin(Formated_Sql)),
{executed,1,[ID]}=odbc:param_query(Ref, SQL ,[{{sql_varchar, 20}, in,
[FirstName]}, {{sql_varchar, 20}, in, [LastName]}, {{sql_varchar, 1},
in, [Gender]}, {sql_integer, out, [1]}], infinity),
odbc:disconnect(Ref),
io:fwrite("Generated ID
~w~n", [ID]),
odbc:disconnect(Ref),
ok.
Ouput:
Eshell V7.0 (abort with ^G)
1> c(dbInsert).
{ok,dbInsert}
2>
dbInsert:save_to_db("A","B","M",10).
Generated ID {5}
ok
And your DB looks like:
Comments