1> Execute a function that is defined in a package

Question: How can I execute a function that is defined in a package using Oracle/PLSQL?
Answer: To execute a function that is defined in a package, you'll have to prefix the function name with the package name.
package_name.function_name (parameter1, parameter2, ... parameter_n)
You can execute a function a few different ways.

Solution #1

First, we'll take a look at how to execute a function using a test block. Below we've declared a variable called result that is a number. We've passed in a value of 15000 into the function and the result of the function will be returned to the variable called result.
declare
   result number;
begin
   -- Call the function
   result := package_name.function_name (15000);
end;

Solution #2

We can also execute a function by running a SQL statement. For example:
select package_name.function_name (15000)
from dual;

2> SELECT INTO

PL/SQL SELECT INTO statement is the simplest and fastest way to fetch a single row from a table into variables. The following illustrates the syntax of the PL/SQL SELECT INTO statement:
In this syntax, the number and type of columns in the variable_list must match those of the select_list. Besides the WHERE clause, you can use other clauses in the SELECT statement such as INNER JOIN, GROUP BY, HAVING, and UNION.


The following example uses a SELECT INTO statement to get the name of a customer based on the customer id, which is the primary key of the customers table.
This article explains how to use Oracle functions to get top 1 record by using PL/SQL.
The code on this page shows how to use Oracle PL/SQL to retrieve the top 1 record. Here is what we're going to explain.
  • How to get the top value from a table. Different SQL syntax for Oracle 8i, 9i, 10g, 11g, and above.
  • How to get a value from a single cell of a table by Oracle functions.
  • The PL/SQL approach of using implicit cursor and explicit cursor.
This function uses the SELECT...INTO command to get the latest order date for a customer by using an implicit cursor.
SELECT...INTO command is a type of hidden cursor in Oracle PL/SQL (called implicit cursor) that allows us to skip the steps that explicit cursor uses, eg. declare the cursor, open the cursor, process its rows in a loop, then closed it.
The following function can be used for Oracle 8i and above.

function get_latest_order_date(i_curstomer_id in number) return date
is
         
v_order_date date;
   
begin             
    select Order_Date into v_order_date
    from
    (
        select Order_Date
        from orders
        where customer_id = i_curstomer_id            
        order by Order_Date desc
    )
    where rownum < 2;  -- This guarantees there is only one row is returned.
    
    return v_order_date;

exception
   when no_data_found then return to_date('01/01/1900', 'DD/MM/YYYY');
end;

3> OVER and PARTITION BY

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:
SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders
Will return something like this:
Cost  OrderNum
10.00 345
10.00 346
10.00 347
10.00 348
Quick translation:
  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo
FROM Orders
My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:
Cost  OrderNum   CustomerNo
8.00 345        1
8.00 346        1
8.00 347        1
2.00 348        2
The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.
Ex, get sum(BAD_CREDIT) by partition condition:
select TEMP.EMP_ID, temp.AREA_CODE,TEMP.ORG_ID_B1, TEMP.PI_M_LEVEL_ID, SUM(temp.BAD_CREDIT) OVER(PARTITION BY temp.AREA_CODE,TEMP.ORG_ID_B1, TEMP.PI_M_LEVEL_ID) NP_BAD_CREDIT_B1 from TEMP;

4> Oracle Merge command

https://www.vertica.com/blog/use-merge-update-1-million-rows/
https://www.databasestar.com/oracle-merge-sql/
https://www.oracletutorial.com/oracle-basics/oracle-merge/

5> Oracle FETCH BULK COLLECT INTO pl sql

 You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection (ie table of varray):
https://gerardnico.com/lang/plsql/bulk_collect
How To Use Bulk Collect Clause With SELECT INTO Statement In Oracle Database




--
Prev: Oracle SQL căn bản P1