oracle进阶实战笔记

本文包含以下内容:
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;

【举例】

SQL> set serveroutput on
SQL> declare
2 v_sal number(6,0);
3 begin
4 v_sal := 6600;
5 if (v_sal < 2500) then
6 v_sal := v_sal * 2;
7 dbms_output.put_line('sal < 2500 ' || v_sal);
8 elsif (v_sal > 2500) then
9 v_sal := v_sal / 2;
10 dbms_output.put_line('sal > 2500 ' || v_sal);
11 else
12 dbms_output.put_line('sal = 2500 ' || v_sal);
13 end if;
14 end;
15 /
sal > 2500 3300

PL/SQL procedure successfully completed.

常用变量类型:
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> select owner, constraint_name, constraint_type, table_name from user_constraints where table_name='TT';

OWNER
--------------------------------------------------------------------------------
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYSTEM
SYS_C007178 C TT

SYSTEM
TT_NAME_NN C TT

违反唯一性插入举例:
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密不可分。

eg:

SQL> select sysdate from dual;

SYSDATE
------------------
18-APR-16

//以下则表示字符串
SQL> select 'sysdate' from dual;

'SYSDAT
-------
sysdate

4.group by & count使用
1)、分组的实质就是一行(或多行)中的某一列(或多列)具有相同值。

2)、组是非空的,如果分组成功,组至少包含一个成员(或行)。

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

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

SQL> create table fruits (name varchar2(10));
Table created.

//插入数据
SQL> insert into fruits values ('Oranges');
1 row created.

SQL> insert into fruits values ('Oranges');
1 row created.

SQL> insert into fruits values ('Oranges');
1 row created.

SQL> insert into fruits values ('Apple');
1 row created.

SQL> insert into fruits values ('Peach');
1 row created.

SQL> insert into fruits values (null);
1 row created.

SQL> insert into fruits values (null);
1 row created.

SQL> insert into fruits values (null);
1 row created.

SQL> insert into fruits values (null);
1 row created.

SQL> insert into fruits values (null);
1 row created.

//分组查询1
SQL> select name, count(name) from fruits group by name;
NAME COUNT(NAME)
---------- -----------
0
Oranges 3
Apple 1
Peach 1

//分组查询2
SQL> select name, count(*) from fruits group by name;
NAME COUNT(*)
---------- ----------
5
Oranges 3
Apple 1
Peach 11&分组查询2对比表明:
count(name)会忽略列的空值行,count(*)不会忽略空值行。

4.Oracle字符集

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

//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、系统日期、系统时间操作

//系统日期

SQL> create table tb_date(x date);



Table created.


SQL> insert into tb_date values(sysdate);


1 row created.


SQL> select * from tb_date;


X
------------------
18-APR-16

//系统时间

SQL> create table tb_time(stamp timestamp);



Table created.


SQL> insert into tb_time values(sysdate);


1 row created.


SQL> insert into tb_time values(systimestamp);


1 row created.


SQL> select * from tb_time;


STAMP
---------------------------------------------------------------------------
18-APR-16 02.11.57.000000 PM
18-APR-16 02.12.03.242952 PM

截取时间举例(截取年份):

SQL> select trunc(sysdate,'YYYY') from dual;
TRUNC(SYSDATE,'YYY
------------------
01-JAN-16

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值

SQL> select seq1.nextval from dual;


NEXTVAL
----------
1

//查询序列的当前currval值

SQL> select seq1.currval from dual;


CURRVAL
----------
1

//查询序列的 nextval值

SQL> select seq1.nextval from dual;


NEXTVAL
----------
2

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.

//查看执行结果

SQL> select * from tb_emp1;


EMPNO
----------
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
11
zhang
engineer


11
laoyang11



EMPNO
----------
ENAME
--------------------------------------------------------------------------------
JOB

总结:

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

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.

//查询

SQL> select sal_tax(2000) from dual;
SAL_TAX(2000)
-------------
          .15

SQL> select sal_tax(5555) from dual;
SAL_TAX(5555)
-------------
           .2

//函数中没有参数的,可以不加()。
//函数中一般不会用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.

SQL> select hello from dual;
HELLO
--------------------------------------------------------------------------------
hello function

总结:
函数的基本语法格式:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] TYPE[,….])]
RETURN TYPE
{IS | AS}
BEGIN
FUNCTION BODY
END function_name;

重点————函数和存储过程的区别:
1)函数和过程比较类似,不同的是函数必须返回一个值,而存储过程仅是为了执行一系列的操作。

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

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

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

2016-4-17 am0:30
作者:铭毅天下
转载请标明出处,原文地址:http://blog.csdn.net/laoyang360/article/details/51189292
如果感觉本文对您有帮助,请点击‘顶’支持一下,您的支持是我坚持写作最大的动力,谢谢!

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页