SQL Code Samples

Triggers

Stored Procedures

Join and Sum Function

SQL Stored Procedure Code Example

—SQL Stored Procedure Code Example





create or replace procedure projtask (custnum in customer.custno%type) is

--************************************************
-- This procedure lists projects and tasks
-- belonging to a given customer.
--************************************************

-- program variables used for retrieving table data

   cname char(20);
   projnum number(4);
   temp_projnum number(4);
   projtitle varchar2(25);
   tasknum number(4);
   description varchar2(25);
   hours number(4,1);
   sdate date;
   tcount number;
   temp_tcount number;
   hrcount number(4,1);
   totalprojects number;
   totaltasks number;
   totalhours number;

-- Define retrieval cursor to get single customer's projects and tasks
    cursor nexttask is
    select c.custname, p.pjno, p.pjtitle, t.taskno, t.descrip, t.hrs, t.start_date
    from customer c left join project p on custnum=p.custno left join task t on p.pjno=t.pjno
    where c.custno=custnum
    order by p.pjno, t.taskno;
 
begin

   open nexttask;

-- Retrieve first task row
   fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;

-- Start output display
   dbms_output.put_line('****************************************');
   dbms_output.put_line(cname||' Projects:');
   dbms_output.put_line(chr(10));

-- Retrieve the task count to determine if a project exists in project table
   select count(taskno) into temp_tcount from task where pjno=projnum;

-- test %notfound cursor attribute to see if row returned
   if nexttask%notfound or temp_tcount=0 then
      dbms_output.put_line('No projects currently on file');
      dbms_output.put_line(chr(10));

-- list first project with task descriptions
   else
       dbms_output.put_line('Project: '||projnum||' '||projtitle);
       dbms_output.put_line(chr(10));
       dbms_output.put_line('TaskNo  Description Hours  StartDate');
       dbms_output.put_line('________________________________________');

   -- set task count equal to zero
       tcount:=0;
   -- set total tasks equal to zero
       totaltasks:=0;
   -- temp storage for project number
       temp_projnum:=projnum;
   --set total projects to zero
       totalprojects:=0;
   -- add 1 to project total
       totalprojects:=totalprojects+1;
   -- set total hours to zero
       totalhours:=0;

       loop
           dbms_output.put_line(tasknum|| '  ' ||description||'  '||hours||' '||sdate);
           fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;
       
           -- test %notfound cursor attribute to see if row returned
           if nexttask%notfound then
              dbms_output.put_line('________________________________________');
              dbms_output.put_line('No tasks currently on file');
              dbms_output.put_line('________________________________________');
           end if;
           exit when nexttask%notfound;

           --increment task counter
           tcount:=tcount+1;
       
           -- if the project number changes, start new task description table
           if temp_projnum!=projnum then
              -- bottom of task description table for previous project
              dbms_output.put_line('________________________________________');
              dbms_output.put_line(chr(10));
         
              -- totals for previous project
              dbms_output.put_line('Number of Tasks: '||tcount);
              dbms_output.put_line('Total Project Hours: '||hrcount);
              dbms_output.put_line('________________________________________');
              dbms_output.put_line(chr(10));
         
     --get total tasks for previous project
              totaltasks:=totaltasks+tcount;
              -- calculate total hours
              totalhours:=totalhours+hrcount;
              -- reset task counter for next project
              tcount:=0;
              -- assign new project number to temp storage variable for projnum
              temp_projnum:=projnum;
              --add 1 to project total
              totalprojects:=totalprojects+1;
         
              -- display next project number and title
              dbms_output.put_line('Project: '||projnum||' '||projtitle);
              dbms_output.put_line(chr(10));

              --determine if tasks exist for project
 
              select count(taskno) into temp_tcount from task where pjno=projnum;

              if temp_tcount>0 then
                 dbms_output.put_line('TaskNo  Description Hours  StartDate');
                 dbms_output.put_line('________________________________________');
              end if;
         
              dbms_output.put_line(tasknum|| '  ' ||description||'  '||hours||' '||sdate);
              fetch nexttask into cname, projnum, projtitle, tasknum, description, hours, sdate;
         
              -- test %notfound cursor attribute to see if row returned
              if nexttask%notfound then
                 dbms_output.put_line('________________________________________');
                 dbms_output.put_line(chr(10));
                 dbms_output.put_line('No tasks currently on file');
                 dbms_output.put_line('________________________________________');
              end if;
              exit when nexttask%notfound;
         
              -- increment task counter for next project   
              tcount:=tcount+1;
           end if;
           select sum(hrs) into hrcount
           from task
           where projnum=pjno;
       end loop;
   end if;
-- add up totals and close display
   dbms_output.put_line('****************************************');

-- only display totals if projects exist
   if totalprojects>0 then
      dbms_output.put_line('Number of Projects: '||totalprojects);
      dbms_output.put_line('Number of Tasks: '||totaltasks);
      dbms_output.put_line('Total Hours: '||totalhours);
      dbms_output.put_line(chr(10));
   end if;
   close nexttask;
end projtask;
/

SQL Trigger Code Example

—SQL Trigger Code Example


create or replace trigger BeforeInsertTask
  Before insert on task
  for each row
--
-- This PL/SQL trigger will calculate and report the new current balance
-- for a given customer after insertion of a new task and it will then
-- update the customer table with the new current balance

DECLARE
-- variables to hold rate of employee and calculated total charge for task

   v_rate        number(7,2);
   v_projnum     number(4);
   v_old_balance number(9,2);
   v_new_balance number(9,2);
   v_task_charge number(7,2);
   v_custnum     number(4);

BEGIN
--retrieve employee rate

select rate into v_rate
        from employee
where empid = :new.empid;

--calculate task cost

v_task_charge := v_rate * :new.hrs;

--retrieve current balance

select curr_balance into v_old_balance
from customer c join project p on c.custno=p.custno
where p.pjno=:new.pjno;

--add task charge to old balance to get new balance

v_new_balance:=v_old_balance+v_task_charge;

--display new balance
dbms_output.put_line('The new customer total is: $'||v_new_balance);

--retrieve customer number

select c.custno into v_custnum
from customer c join project p on c.custno=p.custno
where p.pjno=:new.pjno;

--update customer balance

update customer
set curr_balance=v_new_balance
where custno=v_custnum;

END BeforeInsertTask;
/

--select old balance

select curr_balance from customer where custno=123;

--insert task

INSERT INTO task (pjno, taskno, descrip, hrs, start_date, compl_date, empid)
VALUES (7777, 81, 'Install PT-Sale Package', 75.5, '01-JUN-06', '01-JUL-06', 'JOC');

--select new balance

select curr_balance from customer where custno=123;

SQL Join and Sum Function Code Example

 --SQL Join and Sum Function Code Example

                        

 --#1                                                             
 SELECT SELECTEDITEM.ROOMID, MENUITEM.ITEMNO,                     
  (SELECTEDITEM.UNIT_COUNT*MENUITEM.EST_SERVINGS) AS TOTALSERVINGS,
  SELECTEDITEM.DELIVERY_TIME,                                     
  RESERVATION.START_TIME, RESERVATION.EVENTID                     
  FROM MENUITEM                                                   
  JOIN SELECTEDITEM                                               
  ON MENUITEM.ITEMNO = SELECTEDITEM.ITEMNO                        
  JOIN RESERVATION                                                
  ON SELECTEDITEM.EVENTID = RESERVATION.EVENTID                   
  AND                                                             
  SELECTEDITEM.ROOMID = RESERVATION.ROOMID                        
  WHERE RESERVATION.EVENTID = 2201                                
  ORDER BY SELECTEDITEM.DELIVERY_TIME, RESERVATION.ROOMID,        
  MENUITEM.ITEMNO;                                                
 --#2          

  SELECT ROOM.ROOMID, RESERVATION.EVENTID, RESERVATION.EST_ATTENDANCE,
 ROOM.CAPACITY, (100*RESERVATION.EST_ATTENDANCE)/ROOM.CAPACITY       
 AS PCAPACITY                                                        
 FROM ROOM                                                           
 JOIN RESERVATION                                                    
 ON ROOM.ROOMID = RESERVATION.ROOMID                                 
 WHERE (100*RESERVATION.EST_ATTENDANCE)/ROOM.CAPACITY >=80;          
 --#3                                                                
   SELECT MENUITEM.ITEMNO, MENUITEM.DESCRIPTION, MENUITEM.UNIT_PRICE,
   SELECTEDITEM.EVENTID, SELECTEDITEM.ROOMID, SELECTEDITEM.UNIT_COUNT,
  (MENUITEM.UNIT_PRICE*SELECTEDITEM.UNIT_COUNT) AS EXTENDCOST        
   FROM MENUITEM LEFT JOIN SELECTEDITEM ON MENUITEM.ITEMNO =         
   SELECTEDITEM.ITEMNO                                               
   WHERE MENUITEM.UNIT_PRICE >= 25;                                  
 --#4                                                                
 SELECT AVG(UNIT_PRICE / EST_SERVINGS) AS AVGPRICE                   
   FROM MENUITEM;                                                    
 --#5                                                      

 SELECT MAX(RESERVATION.EST_ATTENDANCE), MIN(RESERVATION.EST_ATTENDANCE)
  FROM EVENT                                                          
  JOIN RESERVATION                                                    
  ON EVENT.EVENTID = RESERVATION.EVENTID                              
  WHERE EVENT.EVENT_DATE BETWEEN '8/31/2005' AND '11/1/2005';         
--#6                                                                  
SELECT EVENTID,SUM(HRS_USED) AS SUMHRS , AVG(EST_ATTENDANCE)          
  AS ESTATTENDANCE                                                    
  FROM RESERVATION                                                    
  GROUP BY EVENTID;