Tutorial Guide > REST Tutorial > Creating Studio Resources for Use in this Tutorial > Creating the Procedures
 
Creating the Procedures
Now you create procedures within TDV to search, add to, and delete the play data.
Procedure
Used To
playdataByID
GET data
insertPlaydata
PUT data
deletePlaydata
DELETE data
Create the procedures needed to GET, PUT, and DELETE data
1. Open Studio.
2. For each of the three scripts, right-click to select New SQL Script from the Studio tree.
3. Name the scripts as follows:
— playdataByID
— insertPlaydata
— deletePlaydata
4. Copy the appropriate script contents into the editor.
 
For the script
Copy the following into the editor
deletePlaydata
PROCEDURE deletePlaydata(
IN pk VARCHAR(250),
OUT response VARCHAR(250)
)
BEGIN
INDEPENDENT TRANSACTION
delete from /shared/playdata/Vplaydata
where ID = pk;
set response='row '|| pk || ' deleted';
COMMIT;
END
insertPlaydata
PROCEDURE insertPlaydata(
IN id_val VARCHAR(250),
IN name_val VARCHAR(250),
IN author_val VARCHAR(250),
OUT response VARCHAR(250)
)
BEGIN
INDEPENDENT TRANSACTION
DECLARE r ROW (ID VARCHAR(250));
DECLARE c CURSOR(ID VARCHAR(250));
set response='row '|| id_val || ' already EXISTS!';
 
OPEN c FOR
SELECT cs.ID FROM /shared/playdata/Vplaydata cs
WHERE cs.ID=id_val;
FETCH c INTO r;
IF NOT c.FOUND
THEN
INSERT INTO /shared/playdata/Vplaydata (ID,name,author)
VALUES (id_val, name_val,author_val);
set response='row '|| id_val || ' inserted';
END IF;
COMMIT;
END
playdataByID
PROCEDURE playdataByID(
IN id_arg INTEGER,
OUT result CURSOR (
ID INTEGER,
name VARCHAR(32768),
author VARCHAR(32768)
)
)
BEGIN
DECLARE isnull varchar(12);
set isnull=nvl2(id_arg,''||id_arg,'true');
IF isnull <> 'true'
THEN
OPEN result FOR
SELECT
id_arg ID,
Vplaydata.name,
Vplaydata.author
FROM
/shared/playdata/Vplaydata Vplaydata
WHERE
id_arg = Vplaydata.ID;
ELSEIF isnull='true'
THEN
OPEN result FOR
SELECT
CAST(Vplaydata.ID as INTEGER) ID,
Vplaydata.name,
Vplaydata.author
FROM
/shared/playdata/Vplaydata Vplaydata;
END IF;
END
5. Save each procedure.