select pl sql code example
Example 1: PL/SQL
DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;
Example 2: function pl sql with select
CREATE OR REPLACE FUNCTION get_total_sales(
in_year PLS_INTEGER
)
RETURN NUMBER
IS
l_total_sales NUMBER := 0;
BEGIN
SELECT SUM(unit_price * quantity)
INTO l_total_sales
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY EXTRACT(YEAR FROM order_date)
HAVING EXTRACT(YEAR FROM order_date) = in_year;
RETURN l_total_sales;
END;