我有客户谁可以有一个或多个帐户，这些帐户中的每一个可以包含没有或1个或多个交易（借方或贷方）。
我目前有一个函数（get_customer_balance），它似乎正在工作。你可以从下面的测试用例中看到。
我想更新此函数以利用SQL_MACRO，但当我用SQL_MACRO函数交换工作函数时，我得到以下错误：
ORA-64629：表SQL宏只能出现在SQL语句的FROM子句中
我一直在努力尝试让这个工作，但一直没有成功。有人可以帮助我的代码，可以利用宏和返回的客户余额。
下面是我的测试用例和如何成功运行代码并重现错误的说明。感谢所有回答的人。

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS  SELECT 'L382059', 'Leo', 'Langford','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Tom', 'Micelli','Y' FROM DUAL UNION ALL 
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL  UNION ALL 
SELECT 'X060162', 'Lisa','Saladino','Y'  FROM DUAL UNION ALL 
SELECT 'Y331964', 'Sandy', 'Herring','Y' FROM DUAL  UNION ALL 
SELECT 'Z888555', 'Barbara', 'Broadwater','Y' FROM DUAL;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

CREATE TABLE vendors AS
    SELECT level AS vendor_id,
       'Vendor ' || level AS vendor_name
    FROM   dual
    CONNECT BY level <= 3;

ALTER TABLE vendors 
    ADD ( CONSTRAINT vendors_pk
   PRIMARY KEY (vendor_id));

CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS 
SELECT 'Z17ARWYYZRCU2Q2', 'P382319', 1, 'Y' FROM DUAL 
 UNION ALL
 SELECT '0T81Z07CS6LXQ7Z', 'P382319', 3, 'Y' FROM DUAL 
UNION ALL 
SELECT 'YWYXC3Q5N9XZ7S', 'L382059', 1, 'Y' FROM DUAL UNION ALL
SELECT '612ZKAQ66VA3W3', 'Y331964', 3, 'Y' FROM DUAL    UNION ALL 
SELECT 'BCHD9TW78W67S1D', 'Z888555', 3, 'Y' FROM DUAL UNION ALL 
SELECT '0HLS87LDR1TE8WB', 
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'Z69AG7DKS37UYU',
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'B17ARWYYZRCU2Q2', 
'X060162', 3, 'Y' FROM DUAL  UNION ALL 
SELECT 'THVQD6M9LR7AVK', 'E379466', 1, 'Y' FROM DUAL  UNION ALL 
SELECT '0Z76WT5NTLRZPTW',
'E379466', 1, 'Y' FROM DUAL;

ALTER TABLE customer_accounts 
ADD CONSTRAINT customer_accounts_pk PRIMARY KEY (ACCOUNT_NUMBER);

ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_customer_fk FOREIGN KEY (CUSTOMER_ID) REFERENCES customers(customer_id);

ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_vendor_fk FOREIGN KEY (VENDOR_ID) REFERENCES vendors(vendor_id);

create table transactions (
     transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    account_number VARCHAR2(15),
    transaction_type varchar2(1) DEFAULT 'C',
    transaction_amount NUMBER(10,2),
     transaction_date DATE DEFAULT SYSDATE 
);

ALTER TABLE TRANSACTIONS ADD CONSTRAINT transactions_account_number_fk  FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES customer_accounts
(account_number);

insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT 'Z17ARWYYZRCU2Q2', 'D', (LEVEL * 1250.50), date '2023-05-14' + level * interval '5 15:13' day to minute from dual
          connect by level <= 7
union all
SELECT 'Z17ARWYYZRCU2Q2', 'C', (LEVEL * 1175.75), date '2023-07-04' + level * interval '1 21:23' day to minute from dual
          connect by level <= 5
union all
SELECT '0T81Z07CS6LXQ7Z', 'D', (LEVEL * 1250.50), date '2023-02-14' + level * interval '3 15:13' day to minute from dual
          connect by level <= 17
union all
SELECT '0T81Z07CS6LXQ7Z', 'C', (LEVEL * 75.75), date '2023-02-04' + level * interval '2 21:23' day to minute from dual
          connect by level <= 11
union all
select '612ZKAQ66VA3W3', 'D', 555.25 * LEVEL, (DATE '2023-07-13' + 13/24) + (level * 2)  from dual 
    connect by level <= 25 
UNION ALL 
select '612ZKAQ66VA3W3', 'C', 555.25 * LEVEL, (DATE '2023-07-23' + 13/24) + (level * 2)  from dual 
    connect by level <= 20 
UNION ALL 
select 'BCHD9TW78W67S1D', 'D', 1125.25 * LEVEL, date '2023-01-23' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 'BCHD9TW78W67S1D', 'C', 925.00 * LEVEL, date '2023-01-25' + level * interval '1 1' day to hour from dual
          connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'D', (LEVEL * 1250.50), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
          connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'C', (LEVEL * 1175.75), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
          connect by level <= 5
union all
SELECT 'Z69AG7DKS37UYU', 'D', ((LEVEL * 5) * 1750), date '2023-06-01' + level * interval '1 18:43:35' day to second from dual
          connect by level <= 15
union all
SELECT 'Z69AG7DKS37UYU', 'C', ((LEVEL * 5) * 1750), date '2023-06-11' + level * interval '1 15:23:49' day to second from dual
          connect by level <= 13
union all
SELECT '0Z76WT5NTLRZPTW', 'D', (LEVEL * 100.57), date '2023-04-02' + level * interval '1 4' day to hour from dual
          connect by level <= 5
union all
SELECT '0Z76WT5NTLRZPTW', 'C', (LEVEL * 25.26), date '2023-04-04' + level * interval '1 4' day to hour from dual
          connect by level <= 5
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 250.10), date '2023-05-10' + level * interval '1 7' day to hour from dual
          connect by level <= 13
union all
SELECT 'THVQD6M9LR7AVK', 'C', (LEVEL * 133.11), (SYSDATE - LEVEL)  from dual
          connect by level <= 9;

CREATE OR REPLACE FUNCTION   get_customer_balance
(  i_customer_id   IN   customers.customer_id%TYPE
)
RETURN  transactions.transaction_amount%TYPE
IS
  v_balance   transactions.transaction_amount%TYPE;
BEGIN
  SELECT SUM (
                 CASE  t.transaction_type
             WHEN  'C'
             THEN  -t.transaction_amount
             ELSE  t.transaction_amount
         END 
             )
  INTO   v_balance
  FROM   customer_accounts  ca
  JOIN   transactions       t  ON  t.account_number  = ca.account_number
  WHERE  ca.customer_id  = i_customer_id  -- one customer
  OR     ca.customer_id  IS NULL;         -- all customers

RETURN nvl(v_balance,0);
END  get_customer_balance;
/

/* WORKS */

SELECT C.CUSTOMER_ID,
       C.FIRST_NAME,
       C.LAST_NAME, 
       get_customer_balance(C.CUSTOMER_ID) AS balance
FROM   customers c
ORDER BY balance DESC;

========================
SWAP OUT FUNCTION HERE
========================

create or replace function get_customer_balance (
  i_customer_id in varchar2
) return clob sql_macro 
is
begin
  return q'[
   SELECT 
     SUM 
     (
       CASE  t.transaction_type
       WHEN  'C'
       THEN -t.transaction_amount
       ELSE t.transaction_amount
       END 
      )
   FROM  customer_accounts  ca
   JOIN  transactions       t  ON  t.account_number  = ca.account_number
   WHERE  ca.customer_id  = i_customer_id  -- one customer
   OR   ca.customer_id  IS NULL    ]';
end;
/

/* EXACT CODE DOESN'T WORK */

SELECT C.CUSTOMER_ID,
       C.FIRST_NAME,
       C.LAST_NAME, 
       get_customer_balance(C.CUSTOMER_ID) AS balance
FROM   customers c
ORDER BY balance DESC;