Mega Code Archive
Select value into a column type variable
<![CDATA[
SQL>
SQL> CREATE TABLE accounts(
2 account_id NUMBER NOT NULL PRIMARY KEY,
3 balance NUMBER );
Table created.
SQL>
SQL> INSERT INTO accounts(account_id,balance )VALUES(1,1000 );
1 row created.
SQL> INSERT INTO accounts(account_id,balance )VALUES(2,800 );
1 row created.
SQL>
SQL> CREATE OR REPLACE FUNCTION account_balance(
2 account_id_in IN accounts.account_id%TYPE)
3 RETURN accounts.balance%TYPE
4 IS
5 l_balance accounts.balance%TYPE;
6 BEGIN
7 SELECT balance
8 INTO l_balance
9 FROM accounts
10 WHERE account_id = account_id_in;
11
12 RETURN l_balance;
13
14 END account_balance;
15 /
Function created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE apply_balance(
2 account_id_in IN accounts.balance%TYPE,
3 balance_in IN accounts.balance%TYPE)
4 IS
5 BEGIN
6 UPDATE accounts
7 SET balance = balance - balance_in
8 WHERE account_id = account_id_in;
9 END apply_balance;
10 /
Procedure created.
SQL>
SQL> DECLARE
2 account_id accounts.account_id%TYPE := 2;
3 balance_remaining accounts.balance%TYPE;
4 BEGIN
5 LOOP
6
7 balance_remaining := account_balance (account_id);
8
9
10 EXIT WHEN balance_remaining < 1000;
11
12
13 apply_balance (account_id, balance_remaining);
14 END LOOP;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table accounts;
Table dropped.
SQL>
]]>