Oracle数据库 中的基础的一些语法结构-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle数据库 中的基础的一些语法结构

简介: 方括号里的内容为可选项 大括号是必填 1PL/SQL结构块 DECLARE /* * 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常 */ BEGIN /* * 执行部分——PL/SQL语句和SQL语句 */ EXCEPTION /* *...

 

方括号里的内容为可选项

大括号是必填

1PL/SQL结构块

DECLARE
/*
 * 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常
 */
BEGIN
/*
 * 执行部分——PL/SQL语句和SQL语句
 */
EXCEPTION
/*
 * 异常处理部分——处理运行异常
 */
END; 
/*块结束标记 */

 

2 创建序列

1 CREATE SEQUENCE [ schema. ] sequence_name
2   [ { INCREMENT BY | START WITH } integer
3   | { MAXVALUE integer | NOMAXVALUE }
4   | { MINVALUE integer | NOMINVALUE }
5   | { CYCLE | NOCYCLE }
6   | { CACHE integer | NOCACHE }
7   | ...
8   ]...
9 ;

 

  1. schema:模式,即用户名称
  2. sequence_name:序列名称
  3. INCREMENT BY:定义序列的步长,如果省略,则默认为1,如果出现负值,则代表Oracle序列的值是按照此步长递减的。
  4. START WITH:定义序列的初始值,默认为1。
  5. MAXVALUE:序列生成器能产生的最大值。NOMAXVALUE是默认选项,代表没有最大值定义。
  6. MINVALUE:序列生成器能产生的最小值。NOMINVALUE是默认选项,代表没有最小值定义。
  7. CYCLE和NOCYCLE:当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值,最小值为1。对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
  8. CACHE:定义存放序列的内存块的大小,默认为20,相当于预加载。缓冲部分序列计数以便更快获取序列值,可以改善序列的性能,但缓存选项可能会造成数据丢失。NOCACHE表示不缓冲

 3 删除序列

 1 DROP SEQUENCE [ schema. ] sequence_name ; 

4 修改序列

1 ALTER SEQUENCE [ schema. ] sequence_name
2   { INCREMENT BY integer
3   | { MAXVALUE integer | NOMAXVALUE }
4   | { MINVALUE integer | NOMINVALUE }
5   | { CYCLE | NOCYCLE }
6   | { CACHE integer | NOCACHE }
7   | ...
8   } ...
9 ;

 

 注意,不能修改序列的初始值,否则会报ORA-02283。如果需要修改初始值,先删除序列再重新创建序列设定初始值。

复合数据类型

5.1. 记录(Record)

1 TYPE record_name IS RECORD(
2     varable1 data_type1  [NOT NULL]  [:= default_value ],
3     varable2 data_type2  [NOT NULL]  [:= default_value ],
4     ......,
5     varablen data_typen  [NOT NULL]  [:= default_value ] 
6 );

 

 

5.2. 表(TABLE)

5.2.1. 索引表

1 TYPE table_name IS TABLE OF element_type [NOT NULL]
2 INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2];

 

5.2.2 嵌套表

 1 TYPE type_name IS TABLE OF element_type; 

5.3. 数组(VARRAY) 

 1 TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL]; 

 

显式游标

6.1声明/定义游标

1 CURSOR cursor_name
2   [(parameter_dec [, parameter_dec ]…)]
3     [RETURN datatype]
4       IS
5         select_statement;

6.2 打开游标

 1 OPEN cursor_name [ ( cursor_parameter [ [,] actual_cursor_parameter ]... ) ] 

6.3读取数据

1 FETCH { cursor | cursor_variable | :host_cursor_variable }
2   { into_clause | bulk_collect_into_clause [ LIMIT numeric_expression ] } ;

 

6.4 关闭游标

 1 CLOSE cursor_name; 

游标FOR循环

1 FOR index_variable IN cursor_name[(value[, value]…)] LOOP
2     -- 游标处理语句
3 END LOOP;

 

 8条件结构

 1. 简单IF结构

1 -- 简单IF结构
2 IF <布尔表达式> THEN
3     满足条件时执行的语句
4 END IF;

2. IF-ELSE结构

1 -- IF-ELSE结构
2 IF <布尔表达式> THEN
3     满足条件时执行的语句
4 ELSE
5     不满足条件时执行的语句
6 END IF;

 

3 多重IF

 1 -- 多重IF
 2 IF <布尔表达式1> THEN
 3     满足条件1时执行的语句
 4 ELSIF <布尔表达式2> THEN
 5     满足条件2时执行的语句
 6 ELSIF <布尔表达式3> THEN
 7     满足条件3时执行的语句
 8 ELSE
 9     满足条件1、2、3均不满足时执行的语句
10 END IF;

注意:ELSIF不能写成ELSEIF

CASE

语法一

 1 CASE 条件表达式
 2     WHEN 条件表达式结果1 THEN 
 3         语句1
 4     WHEN 条件表达式结果2 THEN
 5         语句2
 6     ......
 7     WHEN 条件表达式结果n THEN
 8         语句n
 9   [ELSE 条件表达式结果]
10 END CASE;

 

语法二

 1 CASE 
 2   WHEN 条件表达式1 THEN
 3      语句1
 4   WHEN 条件表达式2 THEN
 5      语句2
 6   ......
 7   WHEN 条件表达式n THEN 
 8      语句n
 9   [ELSE 语句]
10 END CASE;

 

10 循环结构 

 简单循环

1 LOOP
2     循环体语句;
3     [EXIT WHEN <条件语句>]
4 END LOOP;

 

 

WHILE 循环

1 WHILE <布尔表达式> LOOP
2     循环体语句;
3 END LOOP;

 

FOR循环

1 [<<标签>>]
2 FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
3     循环体语句;
4 END LOOP [<<标签>>];

 11 异常处理通常放在PL/SQL程序的后部,语法结构为

1 EXCEPTION
2     WHEN { exception [ OR exception ]... | OTHERS }
3         THEN statement [ statement ]...

 

11.1非预定义的异常处理

非预定义异常有错误号没有名字,处理的办法是:自己定义一个名字,绑定到错误号,捕获错误名。处理这类异常,首先必须对非预定义的Oracle异常进行定义。

如:

1 myexcp EXCEPTION;

然后使用EXCEPTION_INIT语句与标准的ORACLE错误联系起来,如:
2 PRAGMA EXCEPTION_INIT(myexcp,-02292);

说明:ORA-02292是违反完整性约束的错误代码。
11.2 定义异常
1 异常名称 EXCEPTION;
2 PRAGMA EXCEPTION_INIT(异常的名字,错误号);

 


11.3抛出异常
1 RAISE 异常的名称

 

RAISE_APPLICATION_ERROR过程可用于创建用户定义的错误信息,可以在可执行部分和异常处理部分使用
1 RAISE APPLICATION_ERROR(错误号,错误描述);

 


11.4 处理异常
1 1 EXCEPTION
2 2     WHEN { exception [ OR exception ]... | OTHERS }
3 3         THEN statement [ statement ]...

 

12. FORALL

使用FORALL,可以将多个DML批量发送给SQL引擎来执行,最大限度地减少上下文交互所带来的开销。

1 FORALL index_name IN 
2     { lower_bound .. upper_bound
3     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
4     | VALUES OF index_collection
5     }
6  [ SAVE EXCEPTIONS ] dml_statement;

 

 

index_name:一个无需声明的标识符,作为集合下标使用。

lower_bound .. upper_bound:数字表达式,来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。

INDICES OF collection_name:用于指向稀疏数组的实际下标。跳过没有赋值的元素,例如被 DELETE 的元素,NULL 也算值。

VALUES OF index_collection_name:把该集合中的值当作下标,且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS:可选关键字,表示即使一些DML语句失败,直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement:静态语句,例如:UPDATE或者DELETE;或者动态(EXECUTE IMMEDIATE)DML语句

 13存储过程

1 CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
2    [ ( parameter_declaration [, parameter_declaration ]... ) ]
3    [ invoker_rights_clause ]
4    { IS | AS }
5    { [ declare_section ] body | call_spec | EXTERNAL} ;

 

 

procedure_name:过程名称。

parameter_declaration:参数声明

格式如下

1 parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
2           | { OUT | IN OUT } [ NOCOPY ] datatype 

 

IN:输入参数。

OUT:输出参数。

IN OUT:输入输出参数。

invoker_rights_clause:这个过程使用谁的权限运行

as或is用于开始一个PL/SQL块

declare_section:声明部分。

body:过程块主体,执行部分。

一般只有在确认procedure_name过程是新过程或是要更新的过程时,才使用OR REPALCE关键字

 13 创建函数

 1 CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
 2   [ ( parameter_declaration [, parameter_declaration]... ) 
 3   ]
 4   RETURN datatype
 5   [ { invoker_rights_clause
 6     | DETERMINISTIC
 7     | parallel_enable_clause
 8     | RESULT_CACHE  [ relies_on_clause ]
 9     }...
10   ]
11   { { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
12   | [ PIPELINED ] { IS | AS } { [ declare_section ] body 
13                               | call_spec
14                               | EXTERNAL
15                               }
16   } ;

 

14 建立包规范

1 CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
2  PACKAGE [ schema. ] package_name
3   [ invoker_rights_clause ]
4   { IS | AS } item_list_1 END [ package_name ] ;

package_name:包名。

invoker_rights_clause:使用谁的权限运行

item_list_1:声明包的公用组件列表

 1 { type_definition -- 数据类型
 2 | cursor_declaration -- 游标
 3 | item_declaration -- 变量、常量等
 4 | function_declaration -- 函数
 5 | procedure_declaration -- 过程
 6 }
 7   [ { type_definition
 8     | cursor_declaration
 9     | item_declaration
10     | function_declaration
11     | procedure_declaration
12     | pragma
13     }
14   ]...

 

15 建立包体

1 CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ] package_name
2 { IS | AS } 
3     BEGIN statement [ statement | pragma ]...
4       [ EXCEPTION exception_handler [ exception_handler ]... ]
5  [ initialize_section ]
6 END [ package_name ] ;

 动态SQL 使用EXECUTE IMMEDIATE语句

1 EXECUTE IMMEDIATE dynamic_sql_stmt
2   [ { into_clause | bulk_collect_into_clause } [ using_clause ]
3   | using_clause [ dynamic_returning_clause ]
4   | dynamic_returning_clause
5   ] ;

dynamic_sql_stmt:是代表一条SQL语句或一个PL/SQL块的字符串表达式。

into_clause:用于存放被选出的字段值的变量或被选出的行记录。格式如:

INTO { variable [, variable ]... | record )

using_clause:SQL或PL/SQL字符串中包括用于参数绑定的占位符时,该子句为占位符绑定值,也可用于返回值。输入bind_argument参数是一个表达式,它的值将被输入(IN模式)或输出(OUT模式)或输入输出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。格式如:

USING [ IN | OUT | IN OUT ] bind_argument
  [ [,] [ [ IN | OUT | IN OUT ] bind_argument ]...

dynamic_returning_clause:指明用于存放返回值的变量或记录。格式如:

{ RETURNING | RETURN } { into_clause | bulk_collect_into_clause }

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章