Q

Creating tables in PL/SQL

How do I create tables in PL/SQL? And how do I save any changes made? I assume you want to create a normal relational-database

table, not an instance of the PL/SQL data structure confusingly called a "table," which resembles an array in other programming languages.

EXECUTE IMMEDIATE can be used to issue any SQL statement (except SELECT) from PL/SQL. See the previous question on Dynamic SQL for details, including an example of CREATE TABLE.

Saving changes in PL/SQL is exactly like saving changes in SQL*Plus. Only Data Manipulation Language (DML) statements (that is, INSERT, UPDATE and DELETE) have a pending (or uncommitted) state. The results of a DML statement are available only to the session that made the statement until it issues a COMMIT statement.

That gives the issuing session a chance to ROLLBACK partially complete transactions in an all-or-nothing situation. For example:

CREATE OR REPLACE FUNCTION  transfer
(
    in_amt      NUMBER,
    in_from_id  NUMBER,
    in_to_id    NUMBER
)
RETURN  BOOLEAN
IS
--    ***********************
--    **  t r a n s f e r  **
--    ***********************

--  transfer deducts in_amt from the account identified by in_from_id
--  and deposits it in the account identified by in_to_id.  If there
--  is any error (including insufficient funds in in_from_id) no changes
--  are made to either account and the function returns FALSE.  Only
--  if both UPDATEs succeed does transfer return TRUE.

    new_from_amt  account.total_amt%TYPE;
    return_val    BOOLEAN := FALSE;  -- Default value, may change later
BEGIN
    IF  in_amt > 0    -- positive amounts only
    THEN
        SELECT  total_amt - in_amt
        INTO    new_from_amt
        FROM    account
        WHERE   account_id = in_from_id;

        IF  new_from_amt >= 0
        THEN    -- sufficient funds
            UPDATE  account          -- withdraw from in_from_id
            SET     total_amt  = new_from_amt
            WHERE   account_id = in_from_id;

            UPDATE  account          -- deposit to in_to_id
            SET     total_amt  = total_amt + in_amt
            WHERE   account_id = in_to_id;

            COMMIT;
            return_val := TRUE;
        END IF;
    END IF;

    RETURN  return_val;
EXCEPTION
    WHEN OTHERS     -- If there's any error at any point ...
    THEN            --    ... then ...
        ROLLBACK;   --    ... none of the transaction succeeds
        RETURN  FALSE;
END;

All Data Definition Language (DDL) statements (including CREATE TABLE) take effect immediately. You do not have to COMMIT them for other sessions to see the results, and you can not roll them back. (In fact, issuing a DDL statement COMMITs pending DML statements in the same session, if there are any.)

This was first published in December 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close