本文包含以下内容： 

1、Oracle数据库 PL/SQL使用 

2、Oracle数据库中的5种约束条件(非空约束、唯一约束、主键约束、外键约束、check约束) 

3、Oracle数据库单引号、双引号区别（OTL编程时不注意会经常犯错） 

5、事务 

6、系统日期&系统时间 

7、序列 

8、存储过程 

9、函数

1. Oracle数据库 PL/SQL使用

【简介】 

PL/SQL是在oracle里面的编程语言，用来写存储过程、触发器、函数等等。 

PL/SQL语言是SQL的补充。SQL没有分支，没有循环，是第四代编程语言，非过程的，只要求得结果。

【注意】

必须先执行set serveroutput on 才能看到输出。 

执行set serveroutput on以后能导致 SQL*Plus检索和显示buffer。

“/”表示执行，相当于输入了run;

【举例】

常用变量类型：

1）binary_integer：整数，主要用来计数而不是用来表示字段类型 

2）number：数字类型 

3）char：定长字符串 

4）varchar2：变长字符串 

5）date：日期 

6）long：长字符串，最长2GB 

7）boolean：布尔类型，可以取值为true、false和null值（建议boolean类型在声明时给初值，否则就是空值）

变量声明的规则： 

1）变量名不能够使用保留字，如from、select等 

2）第一个字符必须是字母 

3）变量名最多包含30个字符 

4）不要与数据库的表或者列同名 

5）每一行只能声明一个变量

说明： 

1）constant相当于Java里面的final，常量； C++中的const, 常量。 

2）“||”是字符串连接符。 

3）dbms_output.put_line不能打印布尔类型的值。

//循环 
SQL> declare 
2 i binary_integer := 1; 
3 begin 
4 loop 
5 dbms_output.put_line(i); 
6 i := i + 1; 
7 exit when (i >= 9); 
8 end loop; 
9 end; 
10 / 
1 
2 
3 
4 
5 
6 
7 
8
PL/SQL procedure successfully completed.

【总结】 

PL/SQL写程序非常的固定： 

[DECLARE] 

–声明部分，可选。声明各种变量游标 

BEGIN 

–执行部分，必须。从这儿开始，程序开始执行 

[EXCEPTION] 

–异常处理部分，可选。相当于catch到exception时执行的东西 

END; 

–结束，end后要有分号

–eg：最简单的语句块

begin 
dbms_output.put_line(‘Hello World!’); 
end; 
/

2.Oracle中的5种约束条件

oracle中对表有五种约束条件 

非空（not null）约束 

唯一（unique）约束 

主键（primary key）约束 

外键（foreign key）约束 

check约束

2.1 非空约束

create table TT ( 
id number(3) not null, 
name varchar2(100) constraint TT_NAME_NN not null 
);

2.2 唯一性约束

1）、唯一约束要求被约束的列或列的组合值是唯一的，不能有两个相同值存在。 

2）、唯一约束可以定义在列级也能定义在表级。表级可以定义字段的组合。 

3）、唯一约束允许空值，因为空值不等于任何值。 

4）、组合字段的唯一约束，只要字段的组合不完全一样就可以插入表。 

create table TT ( 
id number(3), 
name varchar2(100), 
constraint TT_ID_NAME_UK unique (id, name) 
);

违反唯一性插入举例： 

SQL> drop table TT; 

Table dropped.
//创建 
SQL> SQL> create table TT ( 
2 id number(3), 
3 name varchar2(100), 
4 constraint TT_ID_NAME_UK unique (id, name) 
5 ); 
Table created.
//插入 
SQL> insert into TT values(1, ‘aaaa’); 
1 row created.
//插入 
SQL> insert into TT values(1, ‘bbbb’); 
1 row created.
//再次重复插入，报错！ 
SQL> insert into TT values(1, ‘aaaa’); 
insert into TT values(1, ‘aaaa’) 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SYSTEM.TT_ID_NAME_UK) violated

2.3主键

**通过主键可以找到唯一一行与之对应的记录。 

1）、一个表只能有一个主键约束。 

2）、单一主键定义在列级，组合主键定义在表级。 

3）、主键不允许空值，不允许出现重复值。 

SQL> CREATE TABLE TB_PK_EXAMPLE( ID number, NAME varchar2(50), DESCRIPTION varchar2(300), CONSTRAINT TB_PK_EXAMPLE_PK PRIMARY KEY(ID)); 
Table created.
//插入一行数据 
SQL> insert into TB_PK_EXAMPLE values(1, ‘laoyang’, ‘testing’); 
1 row created.
//插入一行数据，ID相同，违反主键约束，会报错。 
SQL> insert into TB_PK_EXAMPLE values(1, ‘zhang’, ‘testing’); 
insert into TB_PK_EXAMPLE values(1, ‘zhang’, ‘testing’) 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SYSTEM.TB_PK_EXAMPLE_PK) violated

2.4外键

1）一个表的外键必须是，另一个表的主键或唯一键。 

2) 外键可以为空值，一个外键值必须匹配一个在父表中存在的值或者空值。 

3) 外键可以定义在表级(单列)，列级(单列或列的组合)。 

4) 外键必须指向父表的PK字段或UK字段。

//创建表 
SQL> create table tb_supplier 
2 ( 
3 supplier_id number not null, 
4 supplier_name varchar2(50) not null, 
5 contact_name varchar2(50), 
6 CONSTRAINT pk_supplier PRIMARY KEY (supplier_id) 
7 );
Table created.
//创建表 
SQL> create table tb_products 
2 ( 
3 product_id number not null, 
4 product_name varchar2(100), 
5 supplier_id number not null, 
//外键约束 
// 
6 constraint fk_products_supplier foreign key (supplier_id) references tb_supplier(supplier_id) 
7 );
Table created.

2.5 Check约束

check约束定义了一个每行都必须满足的条件，类似与在插入数据时，对数据做了一个where过滤。 

举例如下： 

SQL> CREATE TABLE tb_age (age NUMBER(3)); 
Table created. 
//限定年龄的范围【0,125】 
SQL> ALTER TABLE tb_age ADD CONSTRAINT ck_temp_age CHECK((AGE>0) AND (AGE <= 125)); 
Table altered. 
//在给定范围内可以插入。 
SQL> insert into tb_age values(33); 
1 row created.
//超出范围内的两个测试值-1， 126都会报错。 
SQL> insert into tb_age values(126); 
insert into tb_age values(126) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SYSTEM.CK_TEMP_AGE) violated
SQL> insert into tb_age values(-1); 
insert into tb_age values(-1) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SYSTEM.CK_TEMP_AGE) violated

3.Oracle双引号&单引号区别

1.双引号的作用： 

关键字，对象名、字段名加双引号，则示意 Oracle将严格区分大小写，否则Oracl都默认大写。

2.单引号的作用： 

1）引用一个字符串常量，也就是界定一个字符串的开始和结束。 

2）转义符，对紧随其后出现的字符(单引号)进行转义。 

3） 表示它本身，也就是它作为一个字符串的一部分而出现在一个字符串常量中，这点与2密不可分。

4.group by & count使用 

1）、分组的实质就是一行（或多行）中的某一列（或多列）具有相同值。

2）、组是非空的，如果分组成功，组至少包含一个成员（或行）。

3）、组是独特的，意味着，当查询中使用group by时，select列表中就不必使用distinct关键字。

4）、当针对非空表的查询（包含group by）中使用聚集函数count时，它绝对不会返回0。至少会返回一个非0//创建表

4.Oracle字符集

//oracle安装的宿主机查询 
[root@WEB-W031 rc.d]# echo $NLS_LANG 
AMERICAN_AMERICA.AL32UTF8
oracle环境变量中的NLS_LANG是定义客户端的字符集。 
比如环境变量NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 
但是数据库三个实例的字符集可以分别是： 
AMERICAN_AMERICA.AL32UTF8、 
AMERICAN_AMERICA.WE8ISO8859P1、 
AMERICAN_AMERICA.ZHS16GBK。

5、事务

事务会把数据库从一种一致状态转变为另一种一致状态，这就是事务的任务。

oracle的事务提供了ACID特征：

原子性（atomicity）：事务中的所有动作要么都发生，要么都不发生。

一致性（consistency）：事务将数据库从一种一致状态转变为下一种一致状态。

隔离性（isolation）：一个事务的影响在该事务提交前对其他事务都不可见。

持久性（durability）：事务一旦提交，其结果就是永久性的。

其中最重要的特性是它的原子性。

一定要显式地使用commit或rollback来终止你的事务。commit：commit会结束你的事务，并使得已做的所有修改成为永久性的（持久保存）。

6、系统日期、系统时间操作

//系统日期

//系统时间

截取时间举例（截取年份）：

7、序列

序列（sequence）是oracle数据库对象中的一个，每次使用时它会自动增加（或减少）。

//创建序列
SQL> create sequence seq1 
2 increment by 1 
3 start with 1 
4 maxvalue 999999 
5 minvalue 1 
6 cycle 
7 nocache;
Sequence created.

//查询序列的nextval值

//查询序列的当前currval值

//查询序列的 nextval值

SQL> create table tb_stu(id number,name varchar2(40));

Table created.

//用序列作为参数插入表中 

SQL> insert into tb_stu values (seq1.nextval, ‘testtest’);

1 row created.

//查询表，能看到序列值的递增。 

SQL> select * from tb_stu;
ID NAME

3 testtest

//删除表中数据 
SQL> delete from tb_stu;
1 row deleted.
//修改表第一个属性类型 
SQL> alter table tb_stu modify id char(1);
Table altered. 
//向表中插入数据，注意：插入时，数字型和字符型oracle会自动做转换，序列像一个组件一样在插入记录时直接拿来用。
SQL> insert into tb_stu values (seq1.nextval, ‘test2test2’);
1 row created.
//查询表中数据（序列值仍出现递增） 
SQL> select * from tb_stu;
I NAME

4 test2test2

总结： 

创建序列语法：

CREATE SEQUENCE sequence_name 
[INCREMENT BY n] 
[START WITH n] 
[{MAXVALUE n | NOMAXVALUE}] 10^27 
[{MINVALUE n | NOMINVALUE}] -10^27 
[{CYCLE | NOCYCLE }] 
[{CACHE n | NOCACHE}];

说明： 

INCREMENT BY：每次加几个 

START WITH：从几开始 

MAXVALUE：最大值是多少，或NOMAXVALUE不设置最大值 

MINVALUE：最小值是多少，或NOMINVALUE不设置最小值 

CYCLE：累加到最大值之后循环，或NOCYCLE一直累加不循环 

CACHE：设置内存里缓存多少个序列，如果系统down掉了内存中的序列会丢失，导致序列跳号，也可以设置为NOCACHE不缓存。

8、存储过程

存储过程：一段程序，用于改变数据库对象的状态，可以包含一个或多个行为，往往是将一个表的记录经过处理后放到另一个表。

说明：和函数的功能非常相似，但又有不同，下节9会有详细的对比不同说明。

//创建存储过程
SQL> CREATE OR REPLACE PROCEDURE proc_1 (num number, name varchar2) 
2 IS 
3 BEGIN insert into tb_emp1(empno, ename) values (num, name); 
4 END; 
5 
6 /

Procedure created.

//执行存储过程（最后的“/”代表执行，详见本文1. Oracle PL/SQL使用部分描述）。 
SQL> BEGIN proc_1(11, ‘laoyang11’); 
2 END; 
3 /
PL/SQL procedure successfully completed.

//查看执行结果

总结：

创建存储过程的语法格式：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] TYPE[,….])] 
{IS | AS } 
[LOCAL declarations] 
BEGIN 
executable statements; 
[EXCEPTION 
exception_statements; 
END procedure_name;

9、函数

函数的作用是计算一个功能，往往是用来计算并返回一个计算结果。 

//创建函数（含参数）。 
//返回number类型的数据。 
SQL> CREATE OR REPLACE FUNCTION sal_tax 
2 (v_sal number) 
3 return number 
4 is 
5 begin 
6 if (v_sal < 2000) then 
7 return 0.10; 
8 elsif (v_sal < 2750) then 
9 return 0.15; 
10 else 
11 return 0.20; 
12 end if; 
13 end; 
14 /

Function created.

//查询

//函数中没有参数的，可以不加（）。 

//函数中一般不会用dbms_output.put_line打印信息，因为函数是用来计算并返回一个计算结果的，当然加了打印也没关系(测试后也验证了：不会打印） 

SQL> CREATE OR REPLACE FUNCTION hello RETURN VARCHAR2 IS 
2 var VARCHAR2(40) := ‘hello function’; 
3 BEGIN 
4 –dbms_output.put_line(‘aaaaaaaaaaa’); 
5 return var; 
6 END; 
7 /

Function created.

总结： 

函数的基本语法格式： 

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] TYPE[,….])] 
RETURN TYPE 
{IS | AS} 
BEGIN 
FUNCTION BODY 
END function_name;

重点————Oracle数据库函数和存储过程的区别： 

1）函数和过程比较类似，不同的是函数必须返回一个值，而存储过程仅是为了执行一系列的操作。

2）在调用的时候，函数可以作为表达式的一部分进行调用，并且可以使用在SELECT中。而存储过程只能作为一个PL/SQL语句进行调用，且不能在SELECT语句中使用。

3）函数的语法结构和存储过程比较类似，除了函数使用FUNCTION进行定义之外。外一个重要的特点就是，函数具有RETURN子句，指定函数的返回类型。

4）函数和存储过程都可以使用out参数来返回内容。

总之，同学们，你想要的职坐标Oracle数据库频道频道都能找到！