oracle qualify,Oracle PL/SQL Block 小结

PLSQL Block 的结构：[@more@]

一些说明小结：

- placea semicolon(;) at the endof a SQL

statement or PL/SQL control statement

- section keywords DECLARE,BEGIN,and

EXCEPTION are not followed by semicolons

- END and all other PL/SQL

statement require a semicolon to terminate the statement

- statement can

continue over several lines

- blocks can be entirely separate or nested one

within another

- a exception section can also contain nested blocks

the scope and the visibilityh of the identifier:

-

the scope of an identifier is that region of a program unit(block,subprogram,or

package) from which you can reference the identifier

- an identifier is

visible only in the regions from which you can reference the identifier using an

unqualified name

- a block can look up to the enclosing block

- a block

cannot look down to enclosed blocks or sideways to sibling blocks

- a plsql

program is terminated and executed by a slash(/) on a line by itself

Qualify an Identifier:

- by using the block label

prefix

- the systax of the label is :<>

plsql_block; - can reference another blocks’ variables by using

label,syntax:another_label_name.variables_name

PL/SQL support & not support SQL languages:

- SELECT

-

DML:UPDATE,INSERT,DELETE，MERGE --&gt Determine DML outcome with implicit cursor

attributes

- Transaction Control Language:COMMIT,ROLLBACK,SAVEPOINT

-

the keywork END signals the end of a PL/SQL block,not the end of a

transaction

- Just as a block can span multiple transactions,a transaction

can span multiple blocks

- does not directly support DDLstatement: CREATE,ALTER,DROP

- does not support DCLstatement: GRANT,REVOKE

SELECT Statements in PL/SQL:

- Retrieve data from the database with a

SELECT statement

- Queries must return one and only one row

-

Syntax:

SELECT select_list

INTO variable_name

[,variable_name]...[,|record_name]

FROM table_name

WHERE

condition;

- PL/SQL variable assignments always use :=,and SQL column assignments

always use =,like in the UPDATE statement

- lexical units can be classified as:

* delimiters

*

identifiers

* literals

* comments

- identifiers

* can contain up to 30 characters *

must begin with an alphabetic character

* should not have the same name as a

database table column name

* should not be reserved words

* can contain

numerals,dollar signs,underscores,and number signs

* cannot contain

characters such as hyphens,slashes,and spaces

- literals

* character and date literals must be enclosed in single

quotation marks

* numbers can be simple values or scientific notation

- comments

* prefix single-line comments with two dashes(--)

* place

multiple-line comments between the symbols /* and

*/

书写Oracle PL/SQL时关于表示符大小写的一些规范：