0 Replies - 569 Views - Last Post: 16 July 2011 - 06:59 PM

#1 hash020  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 21-January 11

Simple Pl/SQL Applications part 3

Posted 16 July 2011 - 06:59 PM

using this code


DROP TABLE MM_MOVIE_TYPE CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_pay_type CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_member CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_movie CASCADE CONSTRAINTS PURGE;
DROP TABLE mm_rental CASCADE CONSTRAINTS PURGE;
DROP SEQUENCE mm_rental_seq;


CREATE TABLE mm_movie_type
     (movie_cat_id   NUMBER(2),
      movie_category VARCHAR(12),
      CONSTRAINT movie_cat_id_pk PRIMARY KEY (movie_cat_id));
CREATE TABLE mm_pay_type
     (payment_methods_id  NUMBER(2),
      payment_methods     VARCHAR(14),
      CONSTRAINT payment_methods_id_pk PRIMARY KEY (payment_methods_id));
CREATE TABLE mm_member
   (member_id  NUMBER(4),
   last         VARCHAR(12),
   first        VARCHAR(8),
   license_no   VARCHAR(9),
   license_st   VARCHAR(2),
   credit_card  VARCHAR(12),
   suspension   VARCHAR(1) DEFAULT 'N',
   mailing_list VARCHAR(1),
   CONSTRAINT cust_custid_pk PRIMARY KEY (member_id),
   CONSTRAINT cust_credcard_ck CHECK (LENGTH(credit_card) = 12));
CREATE TABLE mm_movie
     (movie_id     NUMBER(4),
      movie_title  VARCHAR(40),
      movie_cat_id   NUMBER(2) NOT NULL,
      movie_value  DECIMAL(5,2),
      movie_qty NUMBER(2),
      CONSTRAINT movies_id_pk PRIMARY KEY (movie_id),
      CONSTRAINT movie_type_fk FOREIGN KEY (movie_cat_id)
            REFERENCES mm_movie_type(movie_cat_id),
      CONSTRAINT movies_value_ck CHECK (movie_value BETWEEN 5 and 100));
CREATE TABLE mm_rental
      (rental_id NUMBER(4),
       member_id   NUMBER(4),
       movie_id      NUMBER(4),
       checkout_date DATE DEFAULT SYSDATE,
       checkin_date  DATE,
       payment_methods_id  NUMBER(2),
       CONSTRAINT rentals_pk PRIMARY KEY (rental_id),
       CONSTRAINT member_id_fk FOREIGN KEY (member_id)
            REFERENCES mm_member(member_id),
       CONSTRAINT movie_id_fk FOREIGN KEY (movie_id)
            REFERENCES mm_movie(movie_id),
       CONSTRAINT pay_id_fk FOREIGN KEY (payment_methods_id)
            REFERENCES mm_pay_type(payment_methods_id));
Create sequence mm_rental_seq  start with 13;
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card)
   VALUES (10, 'Tangier', 'Tim', '111111111', 'VA', '123456789111');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
   VALUES (11, 'Ruth', 'Babe', '222222222', 'VA', '222222222222', 'Y');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
   VALUES (12, 'Maulder', 'Fox', '333333333', 'FL', '333333333333', 'Y');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card)
   VALUES (13, 'Wild', 'Coyote', '444444444', 'VA', '444444444444');
INSERT INTO mm_member (member_id, last, first, license_no, license_st, credit_card, mailing_list)
   VALUES (14, 'Casteel', 'Joan', '555555555', 'VA', '555555555555', 'Y');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
  VALUES ( '1', 'SciFi');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
  VALUES ( '2', 'Horror');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
  VALUES ( '3', 'Western');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
  VALUES ( '4', 'Comedy');
INSERT INTO mm_movie_type (movie_cat_id, movie_category)
  VALUES ( '5', 'Drama');
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (1, 'Alien', '1', 10.00, 5);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (2, 'Bladerunner', '1', 8.00, 3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (3, 'Star Wars', '1', 15.00, 11);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (4,'Texas Chainsaw Masacre', '2', 7.00, 2);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (5, 'Jaws', '2', 7.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (6, 'The good, the bad and the ugly', '3', 7.00,2);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (7, 'Silverado', '3', 7.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (8, 'Duck Soup', '4', 5.00,1);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (9, 'Planes, trains and automobiles', '4', 5.00,3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (10, 'Waking Ned Devine', '4', 12.00,4);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (11, 'Deep Blue Sea', '5', 14.00,3);
INSERT INTO mm_movie (movie_id, movie_title, movie_cat_id, movie_value, movie_qty)
  VALUES (12, 'The Fifth Element', '5', 15.00,5);
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
  VALUES ('1', 'Account');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
  VALUES ('2', 'Credit Card');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
  VALUES ('3', 'Check');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
  VALUES ('4', 'Cash');
INSERT INTO mm_pay_type (payment_methods_id, payment_methods)
  VALUES ('5', 'Debit Card');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (1,'10', '11', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (2,'10', '8', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (3,'12', '6', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (4,'13', '3', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (5,'13', '5', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (6,'13', '11', '5');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (7,'14', '10', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (8,'14', '7', '2');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (9,'12', '4', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (10,'12', '12', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (11,'12', '3', '4');
INSERT INTO mm_rental (rental_id, member_id, movie_id, payment_methods_id)
  VALUES (12,'13', '4', '5');
UPDATE mm_rental 
 SET checkout_date = '04-JUN-03';
COMMIT;




and this


alter table mm_movie
add(stk_flag CHAR(1));







SET SERVEROUTPUT ON 

DECLARE 

v_count NUMBER; 
v_title mm_movie.movie_title%TYPE; 
v_movie_id NUMBER;
v_step NUMBER;

BEGIN 

v_movie_id := &movie_id;

v_step := 1;
SELECT count(*) INTO v_count FROM mm_movie WHERE movie_id = v_movie_id;

v_step := 2;
SELECT count(*) INTO v_count FROM mm_rental WHERE movie_id = v_movie_id;

SELECT m.movie_title, COUNT(r.rental_id) INTO v_title, v_count 
FROM mm_movie m, mm_rental r 
WHERE m.movie_id = r.movie_id 
AND m.movie_id = v_movie_id 
GROUP BY m.movie_title; 

DBMS_OUTPUT.PUT_LINE('For title '|| v_title || ' the count = ' || v_count); 

EXCEPTION

WHEN NO_DATA_FOUND THEN
IF (v_step = 1) THEN
DBMS_OUTPUT.PUT_LINE('Movie ID '||v_movie_id||' was not found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Movie ID '||v_movie_id||' was never rented.');
END IF;

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END; 
/






fix these errors


 

Enter value for movie_id: 
old  10: v_movie_id := &movie_id;
new  10: v_movie_id := ;
v_movie_id := ;
              *
ERROR at line 10:
ORA-06550: line 10, column 15:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted S




This post has been edited by hash020: 16 July 2011 - 07:01 PM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1