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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.)