PL/SQL User’s Guide and Reference phần 9 doc

INSERT Statement

11-96 PL/SQL User’s Guide and Reference

Usage Notes

Character and date literals in the VALUES list must be enclosed by single quotes (’).

Numeric literals are not enclosed by quotes.

The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND,

%ROWCOUNT, and %ISOPEN let you access useful information about the execution of

an INSERT statement.

Examples

The following examples show various forms of INSERT statement:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp

WHERE comm > sal * 0.25;

...

INSERT INTO emp (empno, ename, job, sal, comm, deptno)

VALUES (4160, ’STURDEVIN’, ’SECURITY GUARD’, 2045, NULL, 30);

...

INSERT INTO dept

VALUES (my_deptno, UPPER(my_dname), ’CHICAGO’);

Related Topics

SELECT Statement

Literals

Language Elements 11-97

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented

by an identifier. The numeric literal 135 and the string literal ’hello world’ are

examples. For more information, see "Literals" on page 2-7.

Syntax

+

_ integer

real_number

numeric_literal

digit

integer

integer

. integer

.

. integer

E

e

+

_

integer

real_number

’ character ’

’ ’

character_literal

’ character ’

’ ’

string_literal

Literals

11-98 PL/SQL User’s Guide and Reference

Keyword and Parameter Description

character

This is a member of the PL/SQL character set. For more information, see "Character

Set" on page 2-2.

digit

This is one of the numerals 0 .. 9.

TRUE, FALSE, NULL

This is a predefined Boolean value.

Usage Notes

Two kinds of numeric literals can be used in arithmetic expressions: integers and

reals. Numeric literals must be separated by punctuation. Spaces can be used in

addition to the punctuation.

A character literal is an individual character enclosed by single quotes

(apostrophes). Character literals include all the printable characters in the PL/SQL

character set: letters, numerals, spaces, and special symbols.

PL/SQL is case sensitive within character literals. So, for example, PL/SQL

considers the literals ’Q’ and ’q’ to be different.

A string literal is a sequence of zero or more characters enclosed by single quotes.

The null string (’’) contains zero characters. To represent an apostrophe within a

string, write two single quotes. PL/SQL is case sensitive within string literals. So,

for example, PL/SQL considers the literals ’white’ and ’White’ to be different.

Also, trailing blanks are significant within string literals, so ’abc’ and ’abc ’ are

different. Trailing blanks in a literal are never trimmed.

The Boolean values TRUE and FALSE cannot be inserted into a database column.

TRUE

FALSE

NULL

boolean_literal

Literals

Language Elements 11-99

Examples

Several examples of numeric literals follow:

25 6.34 7E2 25e-03 .1 1. +17 -4.4

Several examples of character literals follow:

’H’ ’&’ ’ ’ ’9’ ’]’ ’g’

A few examples of string literals follow:

’$5,000’

’02-AUG-87’

’Don’’t leave without saving your work.’

Related Topics

Constants and Variables, Expressions

LOCK TABLE Statement

11-100 PL/SQL User’s Guide and Reference

LOCK TABLE Statement

The LOCK TABLE statement lets you lock entire database tables in a specified lock

mode. That enables you to share or deny access to tables while maintaining their

integrity. For more information, see "Using LOCK TABLE" on page 5-49.

Syntax

Keyword and Parameter Description

lock_mode

This parameter specifies the lock mode. It must be one of the following: ROW SHARE,

ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE.

NOWAIT

This optional keyword tells Oracle not to wait if the table has been locked by

another user. Control is immediately returned to your program, so it can do other

work before trying again to acquire the lock.

table_reference

This identifies a table or view that must be accessible when you execute the LOCK

TABLE statement. For the syntax of table_reference, see "DELETE Statement"

on page 11-52.

Usage Notes

If you omit the keyword NOWAIT, Oracle waits until the table is available; the wait

has no set limit. Table locks are released when your transaction issues a commit or

rollback.

A table lock never keeps other users from querying a table, and a query never

acquires a table lock.

LOCK TABLE table_reference

,

IN lock_mode MODE

NOWAIT

;

lock_table_statement

LOCK TABLE Statement

Language Elements 11-101

If your program includes SQL locking statements, make sure the Oracle users

requesting locks have the privileges needed to obtain the locks. Your DBA can lock

any table. Other users can lock tables they own or tables for which they have a

privilege, such as SELECT, INSERT, UPDATE, or DELETE.

Example

The following statement locks the accts table in shared mode:

LOCK TABLE accts IN SHARE MODE;

Related Topics

COMMIT Statement, ROLLBACK Statement

LOOP Statements

11-102 PL/SQL User’s Guide and Reference

LOOP Statements

LOOP statements execute a sequence of statements multiple times. The loop encloses

the sequence of statements that is to be repeated. PL/SQL provides four kinds of

loop statements: basic loop, WHILE loop, FOR loop, and cursor FOR loop. For more

information, see "Iterative Control: LOOP and EXIT Statements" on page 3-6.

Syntax

<< label_name >>

LOOP statement END LOOP

label_name

;

basic_loop_statement

<< label_name >>

WHILE boolean_expression

while_loop_statement

<< label_name >>

FOR index_name IN

for_loop_statement

REVERSE

lower_bound .. upper_bound

LOOP statement END LOOP

label_name

;

LOOP statement END LOOP

label_name

;