Procedures |
  
|
Procedures |
|
Procedures are stored Subroutine which contains set of Sql code performing certain task. A procedure can be created once and can be called anytime for future use. Procedures return values as well. Users can Create Procedures in Appexe. Follow below steps to know more.
Steps to Create Procedures:-
Step 1: Create a Remote DB Table. Insert data into the csv file and upload csv data into the table. To know more about Remote DB Table click here.
Once Remote DB table is created Click on "Create Procedure" as shown in the image below.
e.g
Table name: emp
Fieldnames: id, empname, salary, last_date, last_user
Step 2: Procedure Manager window opens.“Procedure Schema” can be seen on the window where User need to write the procedure.
Step 3: Enter Procedure text in the Text Area.
Procedure Format:
create function emp_stamp() returns trigger as $emp_stamp$
begin
-- check that empname and salary are given
if new.empname is null then
raise exception 'empname cannot be null';
end if;
if new.salary is null then
raise exception ' % cannot have null salary', new.empname;
end if;
-- who works for us when she must pay for it?
if new.salary < 0 then
raise exception ' % cannot have a negative salary', new.empname;
end if;
-- remember who changed the payroll when
new.last_date := current_timestamp;
new.last_user := current_user;
return new;
end;
$emp_stamp$ language plpgsql;
Function of Procedure:-
1) Raises Error message if the empname field is empty
2) Raises Error message if the salary field is empty.
3) Raises Error message if the salary field is negative.
4) It stores the last date and last user.
Step 4: The Procedure created can be seen listed out as shown in the image below. Users can click on the Edit icon under Actions column to edit the Procedure.
Click here to know How to call the Procedure.