PL/SQL Function that transforms a row into a BSON Document. Task 1) Implement a
ID: 3756115 • Letter: P
Question
PL/SQL Function that transforms a row into a BSON Document.
Task 1) Implement a stored PL/SQL function that transforms a trip selected from TRIP table and trip legs selected from TRIPLEG table into a single BSON document. The document must have a hierarchical structure must not contain any redundancies. A BSON document must be returned by the function as a string of characters. The function must have one input parameter to pass information about a trip number. If a trip with a given number does not exist then the function must return an empty BSON document. A name of the stored function is up to you. Create SQL script solution1.sql with CREATE OR REPLACE FUNCTION statement that compiles the function and saves it in a data dictionary Next, include into a script solution1.sql three SELECT statements that test the stored function. One of the statements must return an empty BSON document and one of the statements must return BSON document with a trip that has at least two legs. When ready process SQL script solution1.sql and record the results of processing in a file solution1.lst. You can use a command line interface MongoDB system to verify the correctness of your transformation, however, no report from the verification is required Your report from processing of SQL script solution1.sql must include a listing of all PL/SQL statements processed. To achieve that put the following SQL Plus commands: SPOOL file-name SET ECHO ON SET FEEDBACK ON SET LINESIZE 100 SET PAGESIZE 200 SET SERVEROUTPUT ON at the beginning of SQL script and SPOOL OFF at the end of SQL script.Explanation / Answer
CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE; TYPE outrec_typ IS RECORD ( var_num NUMBER(6), var_char1 VARCHAR2(30), var_char2 VARCHAR2(30) ); TYPE outrecset IS TABLE OF outrec_typ; FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED; END refcur_pkg; / CREATE OR REPLACE PACKAGE BODY refcur_pkg IS FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS out_rec outrec_typ; in_rec p%ROWTYPE; BEGIN LOOP FETCH p INTO in_rec; -- input row EXIT WHEN p%NOTFOUND; out_rec.var_num := in_rec.employee_id; out_rec.var_char1 := in_rec.first_name; out_rec.var_char2 := in_rec.last_name; PIPE ROW(out_rec); -- first transformed output row out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); -- second transformed output row END LOOP; CLOSE p; RETURN; END f_trans; END refcur_pkg; / SELECT * FROM TABLE ( refcur_pkg.f_trans ( CURSOR (SELECT * FROM employees WHERE department_id = 60) ) DROP TABLE gradereport; CREATE TABLE gradereport ( student VARCHAR2(30), subject VARCHAR2(30), weight NUMBER, grade NUMBER ); INSERT INTO gradereport (student, subject, weight, grade) VALUES ('Mark', 'Physics', 4, 4); INSERT INTO gradereport (student, subject, weight, grade) VALUES ('Mark','Chemistry', 4, 3); INSERT INTO gradereport (student, subject, weight, grade) VALUES ('Mark','Maths', 3, 3); INSERT INTO gradereport (student, subject, weight, grade) VALUES ('Mark','Economics', 3, 4); CREATE PACKAGE pkg_gpa AUTHID DEFINER IS TYPE gpa IS TABLE OF NUMBER; FUNCTION weighted_average(input_values SYS_REFCURSOR) RETURN gpa PIPELINED; END pkg_gpa; / CREATE PACKAGE BODY pkg_gpa IS FUNCTION weighted_average (input_values SYS_REFCURSOR) RETURN gpa PIPELINED IS grade NUMBER; total NUMBER := 0; total_weight NUMBER := 0; weight NUMBER := 0; BEGIN LOOP FETCH input_values INTO weight, grade; EXIT WHEN input_values%NOTFOUND; total_weight := total_weight + weight; -- Accumulate weighted average total := total + grade*weight; END LOOP; PIPE ROW (total / total_weight); RETURN; -- returns single result END weighted_average; END pkg_gpa; /
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.