PL/SQL 
3rd generation (high-level) language: 
  variables (SQL datatypes, plus boolean, binary_integer, natural), 
  flow control [if, case, for, while], 
  exception handling, 
  SQL DML statements,
  blocks, procedures, functions, packages, triggers.

stored procedure: complex business logic
stored in the DB: 
  1. reduces network traffic
  2. reduces code duplication, centralized.  compiled

--define a procedure
CREATE OR REPLACE PROCEDURE hello_world IS 
    greetings VARCHAR2(20);
BEGIN
    greetings := 'Hello world';
    dbms_output.put_line(greetings);
END;
/

--run a procedure in SQL*Plus (or called by another procedure or trigger)
EXEC hello_world

--allow output of messages from server
SET SERVEROUTPUT ON

--delete a procedure
DROP PROCEDURE hello_world;


What procedures do I have?
SELECT * FROM user_procedures;
SELECT * FROM obj WHERE object_type='PROCEDURE';
SELECT * FROM obj WHERE object_name='HELLO_WORLD';
text of procedure???


if compile errors and want to see them:
SHOW ERRORS

paste into SQL*Plus to debug.
Put the Create Procedure in a .sql file and @ it.


--anonymous block: execute on the fly in SQL*Plus
DECLARE
  vars...
BEGIN
  statememts
END;
/

Begin
  hello_world;
End;
/

--EXEC some_proc     ==Begin some_proc End;


IF condition THEN
...
ELSIF condition THEN      --yes, "elsif"
...
ELSE
...
END IF;


CASE variable
 WHEN value1 THEN ...
 WHEN valu2 THEN ...
 ...
 ELSE ...   --optional
END CASE;

CASE
 WHEN condition1 THEN ...
 WHEN condition2 THEN ...
 ...
 ELSE ...
END CASE;


WHILE condition LOOP
...
END LOOP:


LOOP
...
  EXIT WHEN condition;
...
END LOOP;


FOR var IN low..high LOOP
...
END LOOP;

 
--implicit cursor: SELECT INTO 
Select onevalue INTO variable FROM ...
Select value1,value2,value3 INTO variable1,variable2,variable3 FROM ...


CREATE OR REPLACE PROCEDURE price_count IS 
  low NUMBER := 0;  -- comment
  range NUMBER := 20;
  high NUMBER := range-.01;
  num_products NUMBER;
  total NUMBER := 0; 
BEGIN
  WHILE high < 300 LOOP    --comment
    SELECT Count(p_code) INTO num_products FROM product
      WHERE p_price BETWEEN low AND high;
    DBMS_OUTPUT.PUT_LINE('There are '||num_products||' products with price  between '||low||' and '||high);
    total := total + num_products;
    low := high + .01;  
    high := high + range;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total products: '||total);
END;
/



--input parameters.  datatypes: no sizes

CREATE OR REPLACE PROCEDURE paramtest (p1 Number, p2 Varchar2, p3 Date) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('p1='||p1||'  p2='||p2||'  p3='||p3);
END;
/


--pass parameter values
EXEC paramtest(23,'hello','12-dec-2001')

--in an anonymous block to test:
DECLARE
  v1 number := 123;
  v2 varchar2(12) := 'arfarf';
  v3 date := '1-jan=2006';
BEGIN
  paramtest(v1,v2,v3);
END;
/



--output parameters too

CREATE OR REPLACE PROCEDURE paramtest2 (p1 Number, p2 OUT Number, p3 IN OUT Number) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before: p1='||p1||'  p2='||p2||'  p3='||p3);
  p2 := 10 * p1;
  p3 := 10 * p3;
  DBMS_OUTPUT.PUT_LINE('After:  p1='||p1||'  p2='||p2||'  p3='||p3);
END;
/

--test it
DECLARE
  v1 number := 123;
  v2 number := 5;
  v3 number := 10;
BEGIN
  paramtest2(v1,v2,v3);
  DBMS_OUTPUT.PUT_LINE('After:  v1='||v1||'  v2='||v2||'  v3='||v3);
END;
/

Lab exercise:
Modify the price_range procedure to have 3 parameters: starting price,
ending price, range.



PL/SQL cursor: current set of records.
is resultset of its associated Select statement, each row can be
processed in a loop.

Create Or Replace Procedure cursortest Is
  CURSOR product_cur IS       --define a cursor.
    SELECT * FROM product;    --this is the cursor's associated Select statement

Begin
  --special for loop that first executes the cursor's Select, then
  --loops over each row/record in the resultset.  
  --loop variable is not declared.
  FOR product_rec IN product_cur LOOP
    dbms_output.put_line(product_rec.p_code||' '||product_rec.p_descript);
  END LOOP;
End;
/


simpler: sort of implicit declaration of cursor in For loop:
Begin
  FOR product_rec IN (SELECT * FROM product) LOOP
    dbms_output.put_line(product_rec.p_code||' '||product_rec.p_descript);
  END LOOP;
End;
/




stored function: like a procedure but returns a value

Create Or Replace FUNCTION avg_vendor_price (v_code_param Number) RETURN Number IS  
  avg_price Number;
Begin
  Select Avg(p_price) Into avg_price From product,vendor 
    Where product.v_code=vendor.v_code And vendor.v_code=v_code_param;
  RETURN avg_price;
End;
/


--callable like a built-in function:
SELECT avg_vendor_price(21225) FROM dual;
Select avg_vendor_price((select v_code from vendor where v_name like 'Gomez%')) from dual;
--callable from block=
Begin
  dbms_output.put_line('21255 average product price: '||avg_vendor_price(21255));
End;
/     ?????????????????


SELECT * FROM user_procedures;
SELECT * FROM obj WHERE object_type='FUNCTION';

DROP FUNCTION avg_vendor_price;


Create or Replace Function cuticality(heft Number, girth Number) Return Varchar2 Is
Begin
  If