Oracle PL/SQL Block 小结

最新推荐文章于 2023-08-09 13:42:23 发布

ctsfu7793225

最新推荐文章于 2023-08-09 13:42:23 发布

阅读量358

点赞数

PLSQL Block 的结构：

[@more@]

一些说明小结：
- place a semicolon(;) at the end of 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 DDL statement: CREATE,ALTER,DROP
- does not support DCL statement: 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时关于表示符大小写的一些规范：