你不需要一个函数（这将涉及SQL和PL/SQL之间的上下文切换，并且可能会更慢）。
相反，您可以在SQL中完成所有操作并使用相关子查询：

SELECT CA.ACCOUNT_NUMBER,
       C.FIRST_NAME,
       C.LAST_NAME, 
       CA.IS_ACTIVE,
       (
         SELECT SUM(
                  CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
                  * transaction_amount
                )
         FROM   transactions t
         WHERE  t.account_number = ca.account_number
       ) AS balance
FROM   CUSTOMER_ACCOUNTS CA
       INNER JOIN customers c
       ON ca.customer_id = c.customer_id;

或者，从Oracle 12，LATERAL连接：

SELECT CA.ACCOUNT_NUMBER,
       C.FIRST_NAME,
       C.LAST_NAME, 
       CA.IS_ACTIVE,
       t.balance
FROM   CUSTOMER_ACCOUNTS CA
       INNER JOIN customers c
       ON ca.customer_id = c.customer_id
       CROSS JOIN LATERAL (
         SELECT SUM(
                  CASE t.transaction_type WHEN 'C' THEN -1 ELSE 1 END 
                  * t.transaction_amount
                ) AS balance
         FROM   transactions t
         WHERE  t.account_number = ca.account_number
       ) t;

对于您的示例数据，两者都输出：
| 联系我们|公司简介|激活|平衡| BALANCE |
| - -----|- -----|- -----|- -----| ------------ |
| 约翰|卡鲁奇|Y轴|联系我们| null |
| 约翰|卡鲁奇|Y轴|联系我们| null |
| 约翰|卡鲁奇|Y轴|联系我们| null |
| 约翰|卡鲁奇|Y轴|联系我们| null |
| 邦妮|温特伯顿|Y轴|联系我们| null |
| 丽莎|萨拉迪诺|Y轴|联系我们| null |
| 丽莎|萨拉迪诺|Y轴|四千五| 4500 |
| 丽莎|萨拉迪诺|Y轴|联系我们| null |
| 布莱恩|泰西奥|Y轴|联系我们| null |
| 布莱恩|泰西奥|Y轴|联系我们| null |
| 布莱恩|泰西奥|Y轴|联系我们| null |
| 谢丽尔|布拉西|Y轴|联系我们| null |
| 谢丽尔|布拉西|Y轴|联系我们| null |
| 彼得|克莱门扎|Y轴|联系我们| null |
如果你确实需要一个函数，那么你可以将子查询的代码 Package 在一个函数中（也许还可以添加SQL_MACRO(SCALAR)）。

CREATE FUNCTION get_account_balance(
  i_account_number IN VARCHAR2
) RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
  RETURN q'{(
    SELECT SUM(
             CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
             * transaction_amount
           )
    FROM   transactions
    WHERE  account_number = i_account_number -- one account
    OR     i_account_number IS NULL          -- all accounts
  )}';
END;
/

或：

CREATE OR REPLACE FUNCTION get_account_balance(
  i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
  v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
  SELECT SUM(
           CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
           * transaction_amount
         )
  INTO   v_balance
  FROM   transactions
  WHERE  account_number = i_account_number -- one account
  OR     i_account_number IS NULL;         -- all accounts

  RETURN v_balance;
END;
/

fiddle

你不需要一个函数。构建一个进行聚合的视图并重用它。您还可以使用JOIN查询的MERGE提示将过滤器“推送”到这个非常简单的视图中（或者，如果示例中启用了复杂视图合并，数据库可能会自动执行此操作）。WHERE过滤器会自动推送。

/*To demonstrate pushdown benefit*/
create index ix_trx_account
on transactions(account_number)

create view v_account_balance
  as
select
  account_number,
  sum(decode(transaction_type, 'D', 1, 'C', -1) * transaction_amount) AS balance_to_date
from transactions
group by account_number

transactions表中的数据比问题中的数据多：

insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT '0Z8NQN6JZRZWFPX', 'D', 1000 * LEVEL, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 3
union all
SELECT '0Z8NQN6JZRZWFPX', 'C', 500 * LEVEL, date '2023-03-30' + level * interval '2' day from dual
          connect by level <= 2

union all
SELECT 'TFWVBRC5QHQLC4', 'D', 1100 * LEVEL, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 3
union all
SELECT 'TFWVBRC5QHQLC4', 'C', 200 * LEVEL, date '2023-03-30' + level * interval '2' day from dual
          connect by level <= 2

select /*+ gather_plan_statistics*/
  a.*
  , coalesce(bal.balance_to_date, 0) as balance_to_date
from customer_accounts a
  left join v_account_balance bal
  on a.account_number = bal.account_number

| 客户ID|供应商ID|激活|至今余额| BALANCE_TO_DATE |
| - -----|- -----|- -----|- -----| ------------ |
| D379171|八|Y轴|0| 0 |
| D379171|七个|Y轴|0| 0 |
| D379171|七个|Y轴|0| 0 |
| D379171| 4| Y轴|0| 0 |
| E379466| 5个|Y轴|0| 0 |
| K380989|一个|Y轴|0| 0 |
| K380989| 2| Y轴|四千五| 4500 |
| K380989|三个|Y轴|0| 0 |
| X098533|一个|Y轴|0| 0 |
| X098533| 2| Y轴|0| 0 |
| X098533|三个|Y轴|0| 0 |
| Y098273|七个|Y轴|0| 0 |
| Y098273|八|Y轴|六千| 6000 |
| Z098555|十个|Y轴|0| 0 |

select *
from dbms_xplan.display_cursor(format => '+PREDICATE')

SQL_ID  6fqbu5hrg75mz, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/   a.*   ,
coalesce(bal.balance_to_date, 0) as balance_to_date from
customer_accounts a   left join v_account_balance bal   on
a.account_number = bal.account_number
 
Plan hash value: 2666622943
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |       |       |     6 (100)|          |
|   1 |  HASH GROUP BY                        |                   |    22 |  1166 |     6  (17)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER                  |                   |    22 |  1166 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                  | CUSTOMER_ACCOUNTS |    14 |   406 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| TRANSACTIONS      |     1 |    24 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN                  | IX_TRX_ACCOUNT    |     5 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("A"."ACCOUNT_NUMBER"="ACCOUNT_NUMBER")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

fiddle
如果您还需要特定日期的余额，那么SQL_MACRO可能是在查询中推送日期过滤器并避免上下文切换的更好选择。