pl sql function code example

Example 1: function in plsql

CREATE FUNCTION function_name … RETURN BOOLEAN IS
	Definitions;
BEGIN
	Statement;
	RETURN "Hello World!";
END;

Example 2: function plsql

CREATE OR REPLACE FUNCTION my_schema.my_function(param1 IN number) RETURN output1 IS
    cnumber NUMBER;
BEGIN
    cnumber := 2;
    RETURN (cnumber * param1);
EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001, 'An error was encountered - '
            || sqlcode || ' -ERROR- ' || sqlerrm);
END;

Example 3: PL/SQL

DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;

Example 4: 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
    -- get total sales
    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 the total sales
    RETURN l_total_sales;
END;