week9_hands.pks 3.92 KB
Newer Older
1
/*
michael lundquist's avatar
michael lundquist committed
2
3
# hands on 3

4
5
6
7
Before you start working on each chapter's exercises, you need to run a script to rebuild the database(s). This step ensures that the database(s) is in the correct state. Use the script above to rebuild the DoGood Donor database (DD_create). Use the DoGood Donor database for this assignment.


Instructions: Write appropriate PL/SQL programs to complete Hands-On Assignments 3.9-3.11 (Part II) for Chapter 3. For each program:
michael lundquist's avatar
michael lundquist committed
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
*/

/*
## 3_9 Retrieving Pledge Totals

Create a PL/SQL block that retrieves and displays information for a specific project based on Project ID. Display the following on a single row of output: project ID, project name, number of pldeges mads, total dollars pledged, and the average pledge amount.
*/

-- dd_pledge


declare
    lv_projid_num dd_project.idproj%TYPE := 501;
    lv_pledgesum_num dd_pledge.pledgeamt%TYPE;
    lv_pledgeavg_num dd_pledge.pledgeamt%TYPE;
    lv_pledgecount_num NUMBER(8);
    lv_projname_txt dd_project.projname%TYPE;

begin

select count(*), sum(pledgeamt), avg(pledgeamt)
    into lv_pledgecount_num, lv_pledgesum_num, lv_pledgeavg_num
    from dd_pledge join dd_project using (idproj)
    where idproj = lv_projid_num
    group by idproj;

select projname
    into lv_projname_txt
    from  dd_project
    where idproj = lv_projid_num
    ;

dbms_output.put_line( 'project name: ' || lv_projname_txt );
dbms_output.put_line( 'pledge count: ' || lv_pledgecount_num );
dbms_output.put_line( 'pledge average: ' || lv_pledgeavg_num );
dbms_output.put_line( 'pledge total for project: ' || lv_pledgesum_num );
end;
/

/*
## 3.10

Create a PL/SQL block to handle adding a new project. Create and use a sequence named `DD_PROJID_SEQ` to handle generating and populating the project ID. The first number issued by this sequence should be 530, and no caching should be used. Use a record variable to handle the data to be added. Data for the new row should be the following: project name = HK Animal Shelter Extension, start = 1/1/2013, end = 5/31/2013, and fundraising goal = $65,000. Any columns not addressed in the data list are currently unnown
*/

--DOESN'T WORK IN THE BLOCK
CREATE SEQUENCE DD_PROJID_SEQ
MINVALUE 530
START WITH 530
INCREMENT BY 1;

declare
    first_proj_var dd_project%ROWTYPE;
begin
    
    select DD_PROJID_SEQ.NEXTVAL
    into first_proj_var.idproj
    FROM DUAL;

    first_proj_var.projname := 'HK Animal Shelter Extension';
    first_proj_var.PROJSTARTDATE := '1-JAN-2013';
    first_proj_var.PROJENDDATE := '31-MAY-2013';
    first_proj_var.projfundgoal := 65000;

    INSERT INTO dd_project
    VALUES first_proj_var;
end;
/

/*
## 3.11 Retrieving and Displaying Pledge Data

Create a PL/SQL block to retrieve and display data for all pledges made in a specified month. One row of output should be displayed for each pledge. Include the following in each row of output:

- Pledge ID, donor ID, and pledge amount
- If the pledge is being paid in a lump sum, display "Lump Sum."
- If the pledge is being paid in monthly payments, display "Monthly - #"
- The list should be sorted to display all lump sum pledges first.
*/

declare
    --using a table of records
    TYPE type_pledge IS TABLE OF dd_pledge%ROWTYPE
    index by binary_integer;
    lv_pledges_var type_pledge;
    lv_month_date number(2) := 10; -- a month to select pledges from
    lv_paymentmonths_txt varchar2(30) := 'one lump Sum.';
begin
    --getting info
    select * BULK COLLECT
    into lv_pledges_var
    from dd_pledge
    where extract(month from pledgedate) = lv_month_date;

    --displaying it
    FOR i in lv_pledges_var.first..lv_pledges_var.last loop
        if lv_pledges_var(i).paymonths > 0 
            then lv_paymentmonths_txt := 'Monthly - ' || lv_pledges_var(i).paymonths;
        end if;
        dbms_output.put_line( 'pledge ID: ' || lv_pledges_var(i).IDPLEDGE || ' donor id ' || lv_pledges_var(i).IDDONOR || ' pledge amount ' || lv_pledges_var(i).pledgeamt || ' paid in ' || lv_paymentmonths_txt);
    end loop;
end;
/