Monday, February 16, 2015



Oracle PL/SQL Programming Fundamentals

Appendix A: Source Codes

When you try an example, but don’t want to type the source code, you can copy it from the listing in this appendix and paste it on your SQL Developer worksheet.
-- create_produce.sql
CREATE TABLE produce
(code INTEGER PRIMARY KEY,
name VARCHAR2(20),
type CHAR(6),
price NUMBER(4,2),
update_dt DATE);

-- insert_produce.sql
INSERT INTO produce VALUES(1, 'Apple','Fruit',1,to_date('1-MAY-2014','DD-MON-YYYY'));
INSERT INTO produce VALUES(2,'Broccoli','Veggie',2,to_date('2-MAY-2014','DD-MON-YYYY'));
INSERT INTO produce VALUES(3 ,'Carrot','Veggie',3,to_date('3-MAY-2014','DD-MON-YYYY'));
INSERT INTO produce VALUES(4,'Mango','Fruit',4,to_date('4-MAY-2014','DD-MON-YYYY'));
INSERT INTO produce VALUES(5,'Grape','Fruit',5,to_date('5-MAY-2014','DD-MON-YYYY'));

-- running_plsql.sql
DECLARE
  code_var       INTEGER;
  type_var       CHAR(6);
  name_var       VARCHAR2 (20);
  price_var      NUMBER(4,2);
  update_dt_var  DATE;
  price_too_high EXCEPTION;
BEGIN
  SELECT code,type,name,price,update_dt
  INTO code_var,type_var,name_var,price_var,update_dt_var
  FROM produce
  WHERE name   = 'Grape';
  IF price_var > 4.5 THEN
    RAISE price_too_high;
  END IF;
EXCEPTION
WHEN price_too_high THEN
  dbms_output.put_line ('Price is too high');
END;

-- Example 3-1
DECLARE
  code_var      INTEGER;
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Apple';
  dbms_output.put_line(code_var||' '||type_var||' '||name_var
  ||' '||price_var||' '||update_dt_var);
END;

-- Example 3-2
DECLARE
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Apple';
END;

-- Example 3-3
BEGIN
  dbms_output.put_line('Welcome to PL/SQL!');
END;

-- Example 3-4
DECLARE
  x VARCHAR2(5);
BEGIN
  x := 'abcdef';
  dbms_output.put_line('Error');
END;

-- Example 3-5
DECLARE
  x VARCHAR2(5);
BEGIN
  x := 'abcdef';
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error');
END;

-- Example 3-6
BEGIN
  dbms_output.put_line('Parent block');
  BEGIN
    dbms_output.put_line('  First child nested block');
  END;
  BEGIN
      dbms_output.put_line('  Second child nested block');
   BEGIN
     dbms_output.put_line('    Grandchild nested block of 1st child');
    END;
  END;
END;

-- Example 3-7
<< parent >>
BEGIN
  dbms_output.put_line('Parent block');
  << first_child >>
  BEGIN
    dbms_output.put_line('  First child nested block');
  END first_child;
  << second_child >>
  BEGIN
      dbms_output.put_line('  Second child nested block');
   << grandchild >>
    BEGIN
     dbms_output.put_line('    Grandchild nested block of 1st child');
    END grandchild;
  END second_child;
END parent;

-- Example 3-8
DECLARE
  code_var      INTEGER;
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Pineapple';
  dbms_output.put_line (code_var||' '||type_var||' '||
  name_var ||' '||price_var ||' '||update_dt_var);
EXCEPTION
WHEN OTHERS THEN
  << parent_block >>
  BEGIN
    IF SQLCODE IN(+100, -1422) THEN
      <<child_block>>
      BEGIN
        IF SQLCODE = +100 THEN
          dbms_output.put_line('there is no Pineapple');
        ELSE
          dbms_output.put_line('query returns more than one row');
        END IF;
      END child_block;
    END IF;
  END parent_block;
END;

-- Example 3-9
<< parent >>
DECLARE
parent_var VARCHAR2(10) := 'parent';
BEGIN
  dbms_output.put_line(parent_var);
  << first_child >>
  DECLARE
  first_child_var VARCHAR2(10) := 'child';
  BEGIN
    dbms_output.put_line(first_child_var);
    dbms_output.put_line(parent_var);
  END first_child;
END parent;

-- Example 3-10
<< parent >>
DECLARE
  parent_var VARCHAR2(10) := 'parent';
BEGIN
  dbms_output.put_line(parent_var);
  dbms_output.put_line(first_child_var);
  << first_child >>
  DECLARE
    first_child_var VARCHAR2(10) := 'child';
  BEGIN
    dbms_output.put_line(first_child_var);
    dbms_output.put_line(parent_var);
  END first_child;
  << second_child >>
  BEGIN
    dbms_output.put_line(first_child_var);
  END second_child;
END parent;

-- Example 3-11
<< parent >>
DECLARE
  same_name_var VARCHAR2(10) := 'parent';
BEGIN
  dbms_output.put_line(same_name_var);
  << child >>
  DECLARE
    same_name_var VARCHAR2(10) := 'child';
  BEGIN
    dbms_output.put_line(same_name_var);
    dbms_output.put_line(parent.same_name_var);
  END child;
END parent;

-- Example 3-12
-- Example 3-12: Comments
-- An example of declaring a constant and a variable
DECLARE
  fruit_con  CONSTANT VARCHAR2(20) := 'Fruit';  -- constant example
  veggie_var VARCHAR2(20)          := 'Veggie'; -- variable example
BEGIN
  /* the following SQL adds a produce
  into the produce table */
  INSERT
  INTO produce VALUES
    (99,'Tangerine',fruit_con, 9 , '1-JAN-2014' );
END;

-- Example 4-1
DECLARE
  code_var      INTEGER;
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Apple';
  dbms_output.put_line (code_var||' '||type_var||' '||name_var
  ||' '||price_var||' '||update_dt_var);
END;

-- Example 4-2
DECLARE
  code_var      INTEGER;
  name_var      VARCHAR2 (20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
  new_name_var VARCHAR2(20);
BEGIN
  SELECT code, name, price, update_dt
  INTO code_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Apple';
  new_name_var  := 'New ' || name_var;
  dbms_output.put_line
  (code_var||' '||new_name_var||' '||price_var||' '||update_dt_var);
  UPDATE produce SET name = new_name_var WHERE code = code_var;
END;

-- Example 4-3
DECLARE
  code_var      INTEGER;
  type_var      CHAR(10);
  name_var      VARCHAR2(20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
  new_name_var  VARCHAR2(20);
  new_var  VARCHAR2 (20) DEFAULT 'New ';
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name    = 'Grape';
  new_name_var := new_var || name_var;
  dbms_output.put_line (code_var||' '||new_name_var||' '||price_var||' '||
  update_dt_var);
  UPDATE produce SET name = new_name_var WHERE code = code_var;
END;

-- Example 4-4
DECLARE
  code_var      INTEGER;
  type_var      CHAR(10) := 'Fruit';
  name_var      VARCHAR2(20);
  price_var     NUMBER(4,2);
  update_dt_var DATE;
  new_name_var  VARCHAR(20);
  new_var       CHAR (4) DEFAULT 'New ';
BEGIN
  dbms_output.put_line('The value of type_var is: ' || type_var);
  SELECT *
  INTO code_var,type_var,name_var,price_var,update_dt_var
  FROM produce
  WHERE code = 99;
  dbms_output.put_line('The value of type_var is: ' || type_var);
END;

-- Example 4-5
DECLARE
  code_var      INTEGER;
  type_var      CHAR(10) NOT NULL := 'Fruit';
  name_var      VARCHAR2(20);
  price_var     NUMBER(6,2);
  update_dt_var DATE;
  new_name_var  VARCHAR(20);
  new_var       CHAR (4) DEFAULT 'New ';
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE code = 99;
  dbms_output.put_line('The value of type_var is: ' || type_var);
END;

-- Example 4-6
DECLARE
  fruit_con  CONSTANT VARCHAR2(20) := 'Fruit';
  veggie_con CONSTANT VARCHAR2(20) := 'Veggie';
BEGIN
  INSERT INTO produce VALUES
    (11, 'Tangerine', fruit_con ,11,'11-MAY-2014');
  INSERT INTO produce VALUES
    (12, 'Lettuce', veggie_con ,12,'12-MAY-2014');
END;

-- Example 4-7
DECLARE
  fruit_con  CONSTANT VARCHAR2(20) := 'Fruit';
  veggie_con CONSTANT VARCHAR2(20) := 'Veggie';
BEGIN
  fruit_con := 'New Fruit';
END;

-- Example 5-1
DECLARE
  code_var      INTEGER;
  type_var      CHAR(10) NOT NULL := 'Veggie';
  name_var      VARCHAR2(20);
  price_var     NUMBER(6,2) := 2.5;
  update_dt_var DATE;
BEGIN
  code_var  := 20;
  name_var  := 'Kale';
  price_var := price_var + 1.0;
  update_dt_var := CURRENT_DATE;
  INSERT INTO produce VALUES
    (code_var, type_var, name_var, price_var, update_dt_var) ;
END;

-- Example 5-2
DECLARE
  num NUMBER(6,2) := &num_input;
  greater_than_10 BOOLEAN     := num > 10;
BEGIN
  IF greater_than_10 THEN
    dbms_output.put_line(num || ' is greater than 10');
    num := ROUND(num);
    dbms_output.put_line(num || ' rounded' );
  END IF;
END;

-- Example 5-3
DECLARE
  num NUMBER(6,2) := &num_input;
  greater_than_10 BOOLEAN     := num > 10;
BEGIN
  IF greater_than_10 THEN
    dbms_output.put_line(num || ' is greater than 10');
    num := ROUND(num);
    dbms_output.put_line(num || ' rounded' );
  END IF;
END;

-- Example 5-4
DECLARE
  num             NUMBER(6,2) := &num_input;
  greater_than_10 BOOLEAN     := num > 10;
BEGIN
  IF greater_than_10 THEN
    dbms_output.put_line(num || ' is greater than 10');
  ELSE
    dbms_output.put_line(num || ' is equal or smaller than 10');
  END IF;
END;

-- Example 5-5
DECLARE
  num             NUMBER(6,2) := &num_input;
  greater_than_10 BOOLEAN     := num > 10;
BEGIN
  IF greater_than_10 THEN
    dbms_output.put_line(num || ' is greater than 10');
  ELSIF num = 10 THEN
    dbms_output.put_line(num || ' is equal to 10');
  ELSE
    dbms_output.put_line(num || ' is smaller than 10');
  END IF;
END;

-- Example 5-6
DECLARE
  clue VARCHAR2(1) := '&clue_input';
BEGIN
  CASE clue
  WHEN 'O' THEN
    DBMS_OUTPUT.PUT_LINE('Obvious');
  WHEN 'U' THEN
    DBMS_OUTPUT.PUT_LINE('Useless');
  WHEN 'N' THEN
    DBMS_OUTPUT.PUT_LINE('Not Sure');
  WHEN 'M' THEN
    DBMS_OUTPUT.PUT_LINE('Need More');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Input Not Valid');
  END CASE;
END;

-- Example 5-7
DECLARE
  sel         NUMBER(1)   := &sel_in;
  sel_value_1 VARCHAR2(2) := '&sel_in_1';
  sel_value_2 VARCHAR2(2) := '&sel_in_2';
BEGIN
  CASE (sel)
  WHEN sel_value_1 THEN
    DBMS_OUTPUT.PUT_LINE('sel = sel_value_1');
  WHEN sel_value_2 THEN
    DBMS_OUTPUT.PUT_LINE('sel = sel_value_2');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Input Not Valid ');
  END CASE;
END;

-- Example 5-8
DECLARE
  max_price DECIMAL(6,2);
  avg_price DECIMAL(6,2);
BEGIN
  SELECT MAX(price) INTO max_price FROM produce;
  SELECT AVG(price) INTO avg_price FROM produce;
  CASE
    -- reduce price
  WHEN max_price > 5 THEN
    UPDATE produce SET price = price - (price * .01);
    -- increase price
  WHEN avg_price < 3.5 THEN
    UPDATE produce SET price = price + (price * .01);
  END CASE;
END;

-- Example 5-9
DECLARE
  num NUMBER := 1;
BEGIN
  << basic_loop >>
  LOOP
    IF num > 3 THEN -- loop three times only
      EXIT;
    END IF;
    DBMS_OUTPUT.PUT_LINE ('In loop: num = ' || TO_CHAR(num));
    num := num + 1;
  END LOOP basic_loop;
  -- On EXIT, execute the following statement
  DBMS_OUTPUT.PUT_LINE('After loop: num = ' || TO_CHAR(num));
END;

-- Example 5-10
DECLARE
  counter1 NUMBER := 1;
  counter2 NUMBER := 1;
BEGIN
  LOOP
    IF counter1 > 2 THEN EXIT; -- loop twice
    END IF;
    DBMS_OUTPUT.PUT_LINE ('Outer loop: counter1 = ' || TO_CHAR(counter1));
    counter1 := counter1 + 1;
    LOOP
      IF counter2 > 2 THEN EXIT; -- loop twice
      END IF;
      DBMS_OUTPUT.PUT_LINE ('Inner loop: counter2 = ' || TO_CHAR(counter2));
      counter2 := counter2 + 1;
    END LOOP;
    counter2 := 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('After loop: counter1 = ' || TO_CHAR(counter1));
END;

-- Example 5-11
BEGIN
  FOR i IN 1..3
  LOOP
    dbms_output.put_line('Iteration number: '||i);
  END LOOP;
END;

-- Example 5-12
DECLARE
  i INTEGER := 1;
BEGIN
  WHILE i < 4
  LOOP
    dbms_output.put_line('Iteration number: '||i);
    i := i +1;
  END LOOP;
END;

-- Example 6-1
DECLARE
  code_var      INTEGER;
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(6,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Pineapple';
  dbms_output.put_line (code_var||' '||type_var||' '||name_var
  ||' '||price_var||' '||update_dt_var);
END;

-- Example 6-2
DECLARE
  code_var      INTEGER;
  type_var      CHAR(6);
  name_var      VARCHAR2 (20);
  price_var     NUMBER(6,2);
  update_dt_var DATE;
BEGIN
  SELECT code, type, name, price, update_dt
  INTO code_var, type_var, name_var, price_var, update_dt_var
  FROM produce
  WHERE name = 'Pineapple';
  dbms_output.put_line (code_var||' '||type_var||' '||name_var
  ||' '||price_var||' '||update_dt_var);
EXCEPTION
WHEN no_data_found THEN
  dbms_output.put_line('Our query does not find any Pineapple.');
END;

-- Example 6-3
<< parent >>
DECLARE
  code_v VARCHAR2(20);
BEGIN
  dbms_output.put_line ('Parent');
  << first_child >>
  BEGIN
    SELECT code INTO code_v FROM produce WHERE name = 'Pineapple';
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line ('First_child Exception');
  END first_child;
  EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line ('Parent Exception');
END parent;

-- Example 6-4
DECLARE
  code_v VARCHAR2(20);
BEGIN
  SELECT code INTO code_v FROM produce WHERE name = 'Pineapple';
EXCEPTION
WHEN TOO_MANY_ROWS OR no_data_found THEN
  dbms_output.put_line ('Error with the Query');
END;

-- Example 6-5
<< parent >>
DECLARE
  code_v VARCHAR2(20);
BEGIN
  dbms_output.put_line ('Parent');
  SELECT code INTO code_v FROM produce WHERE name = 'Guava';
  << first_child >>
  BEGIN
    SELECT code INTO code_v FROM produce WHERE name = 'Apple';
  EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line ('First_child Exception');
  END first_child;
  EXCEPTION
WHEN TOO_MANY_ROWS THEN
  dbms_output.put_line ('Parent Exception');
END parent;

-- Example 6-6
<< parent >>
DECLARE
  code_v VARCHAR2(20);
BEGIN
  dbms_output.put_line ('Parent');
  << first_child >>
  BEGIN
    SELECT code INTO code_v FROM produce WHERE name = 'Pineapple';
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line ('First_child Exception');
  END first_child;
  EXCEPTION
WHEN no_data_found THEN
  dbms_output.put_line ('Parent Exception');
END parent;

-- Example 6-7
DECLARE
  x VARCHAR2(5);
BEGIN
  x := 'abcdef';
  dbms_output.put_line('Error');
EXCEPTION
WHEN VALUE_ERROR THEN
  dbms_output.put_line
  ('We have an error related to data value');
END;

-- Example 6-8
DECLARE
  x VARCHAR2(5);
BEGIN
  x := 'abcdef';
EXCEPTION
WHEN VALUE_ERROR THEN
  dbms_output.put_line('The error code is: ' ||SQLCODE);
  dbms_output.put_line('The error message is: '||SQLERRM);
END;

-- Example 6-9
DECLARE
  pk_violation EXCEPTION;
  PRAGMA EXCEPTION_INIT(pk_violation, -1);
BEGIN
  UPDATE produce SET code = 1 WHERE code = 2;
EXCEPTION
WHEN pk_violation THEN
  dbms_output.put_line (SQLCODE);
  dbms_output.put_line (SQLERRM);
END;

-- Example 7-1
DECLARE
  avg_price NUMBER(6,2);
BEGIN
  SELECT AVG(price)
  INTO avg_price
  FROM produce;
  dbms_output.put_line('The average price is: ' || avg_price);
  UPDATE produce
  SET price   = price + 0.10 * avg_price
  WHERE price < avg_price;
END;

-- Example 7-2
DECLARE
  code_v  VARCHAR2(20);
  name_v  VARCHAR2(20);
  price_v NUMBER(6,2);
BEGIN
  SELECT code, name, price
  INTO code_v, name_v, price_v
  FROM produce
  WHERE name = 'Apple';
  dbms_output.put_line(
  'The price of our ' ||name_v||
  ' (its code is '||(code_v)|| ') is $'
  || price_v);
END;

-- Example 7-3
DECLARE
  code_v  VARCHAR2(20);
  name_v  VARCHAR2(20);
  price_v NUMBER(6,2);
BEGIN
  SELECT code, name, price
  INTO code_v, name_v, price_v
  FROM produce;
  dbms_output.put_line(
  'The price of our ' ||name_v||
  ' (its code is '||(code_v)|| ') is $'
  || price_v);
END;

-- Example 7-4
DECLARE
  code_v  VARCHAR2(20);
  name_v  VARCHAR2(20);
  price_v NUMBER(6,2);
BEGIN
  SELECT code, name, price
  INTO code_v, name_v, price_v
  FROM produce
  WHERE type = 'Veggie'; 
EXCEPTION
WHEN too_many_rows THEN 
dbms_output.put_line('Error: More than one row returned');
END;

-- Example 7-5
DECLARE
  avg_price produce.price%TYPE;
  p_row produce%ROWTYPE;
BEGIN
  SELECT *
  INTO p_row
  FROM produce
  WHERE name = 'Carrot';
  dbms_output.put_line('The price of our '||p_row.name
  ||' (its code is '||p_row.code|| ') is $'||p_row.price);
END;

-- Example 7-6
DECLARE
  p_row produce%ROWTYPE;
  price_count INTEGER := 0;
BEGIN
  SELECT * INTO p_row FROM produce
  WHERE name = 'Apple' FOR UPDATE OF price;
  UPDATE produce SET price  = 1 WHERE name = 'Apple';
END;

-- Example 7-7
DECLARE
  p_row produce%ROWTYPE;
  price_count INTEGER := 0;
BEGIN
  SELECT * INTO p_row FROM produce
  WHERE name = 'Apple' FOR UPDATE OF price;
  UPDATE produce SET price  = 1.5 WHERE name = 'Apple';
  SELECT COUNT(*) INTO price_count FROM produce
  WHERE price    = 1.5;
  IF price_count > 1 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;

-- Example 8-1
DECLARE
  CURSOR c
  IS
    SELECT * FROM produce;
  cur c%rowtype; -- cur is declared having the cursor's type
BEGIN
  OPEN c;
  FETCH c INTO cur;
  dbms_output.put_line(cur.code || ' ' || cur.name);
END;

-- Example 8-2
DECLARE
  price_increase NUMBER(2,2) := 0.01;
  CURSOR c
  IS
    SELECT price, (price + price_increase) new_price
    FROM produce;
  cr c%rowtype;
BEGIN
  OPEN c;
  FETCH c INTO cr;
  dbms_output.put_line('The current price of ' || cr.price ||
  ' will increase to ' || cr.new_price);
END;

-- Example 8-3
DECLARE
  CURSOR c
  IS
    SELECT * FROM produce;
  cr c%rowtype;
  i INTEGER := 1;
BEGIN
  OPEN c;
  WHILE i < 7
  LOOP
    FETCH c INTO cr;
    dbms_output.put_line(cr.code);
    i := i + 1;
  END LOOP;
  CLOSE c;
END;

-- Example 8-4
DECLARE
  CURSOR c
  IS
    SELECT * FROM produce;
  cr c%rowtype;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO cr;
    EXIT
  WHEN c%notfound;
    dbms_output.put_line(cr.code || ' - ' || cr.name);
  END LOOP;
  CLOSE c;
END;

-- Example 8-5
DECLARE
  CURSOR c
  IS SELECT name FROM produce;
  name_c VARCHAR2(5);
  s      VARCHAR2(15);
BEGIN
  OPEN c;
  LOOP FETCH c INTO name_c;
    EXIT WHEN c%notfound;
  END LOOP;
  EXCEPTION
WHEN OTHERS THEN
  IF c%ISOPEN THEN s := 'c is still Open';
    dbms_output.put_line(s);
    dbms_output.put_line(SQLERRM);
    CLOSE c;
  END IF;
END;

-- Example 8-6
DECLARE
  CURSOR c
  IS
    SELECT code, name
    FROM produce
    WHERE type = 'Veggie';
BEGIN
  FOR c_index IN c
  LOOP
    dbms_output.put_line(c_index.code ||' '|| c_index.name);
  END LOOP;
END;

-- Example 8-7
DECLARE
  output VARCHAR2(40);
BEGIN
  FOR p_row IN
  (SELECT * FROM produce)
  LOOP
    Output := 'The name of this ' || p_row.type || ' is: ' || p_row.name;
    dbms_output.put_line(output);
  END LOOP;
END;

-- Example 8-8
DECLARE
  cr produce_v%rowtype;
BEGIN
  FOR c IN
  (
    SELECT * FROM produce_v order by code
  )
  LOOP
    cr := c;
    dbms_output.put_line
    (cr.code ||' '|| cr.name||' '||cr.price);
  END LOOP;
END;

-- Example 9-1
DECLARE
  name_uc produce.name%type;
  FUNCTION uc_name(
      code_p produce.code%type)
    RETURN VARCHAR2
  IS
    name_v produce.name%type; -- variable declaration within the function's Declaration part
  BEGIN
    SELECT upper(name)
    INTO name_v
    FROM produce
    WHERE code = code_p; -- Executable part
    RETURN name_v;
  EXCEPTION -- Exception part
  WHEN OTHERS THEN
    RETURN 'Error'; -- the function returns an Error when the produce code is not in the produce table
  END uc_name;
  BEGIN -- the start of the program's Executable part
    FOR p IN
    (SELECT * FROM produce /*--WHERE code < 9*/
    )
    LOOP
      name_uc := uc_name(p.code); -- use the function
      dbms_output.put_line(p.code||' '||name_uc);
    END LOOP;
    dbms_output.put_line(uc_name(9)); -- use the function again; here the function returns an Error
  END;

-- Example 9-2
DECLARE
PROCEDURE update_price(
    name_p produce.name%type)
IS
  p_row produce%rowtype;
BEGIN
  SELECT * INTO p_row FROM produce
  WHERE name = name_p FOR UPDATE OF price;
  UPDATE produce SET price = price+(price*0.1)
  WHERE name = name_p;
END update_price; -- enf of procedure
BEGIN             -- now use the procedure
  update_price('Apple');
  update_price('Carrot');

END;
Posted by at No comments:
Subscribe to: Posts (Atom)