oracle快速上手

文章目录

第一章 sqlplus 连接与登录

1. 先连接，再登陆

su - oracle
sqlplus /nolog 
conn 用户名/密码[@主机名 as 身份]
exit/quit #退出

2.系统管理员登陆

sqlplus / as sysdba #sqlplus "用户名/密码[@主机名 as 身份]"
show user;

3.激活普通用户

conn sys/xxx as sysdba #连接
alter user scott identified by tiger #修改当前用户
alter user scott account unlock;
quit #退出

4.启动实例实例

startup [nomount/mount/open]
#nomount:仅打开实例，一般数据库修复时使用
#mount再读取控制文件，但不读取数据文件
#open读取数据文件，打开数据库
#一般需要备份时用mount，默认是open

5.关闭实例

#关闭数据库的时间从长到短，相反，开启的时候是从短到长
shutdown normal #不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复
shutdown transaction #不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复
shutdown immediate #不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。 
shutdown abort #不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。

第二章 sqlplus的用法详解

1.sqlplus的缓冲区

我们输入的sql命令,会被sqlplus储存在缓冲区里,熟练利用缓冲区,可以提高我们书写sql的效率，因为缓冲区的内容 可以 修改/删除,再执行。

list/l #用list/l 查看缓冲区内容，显示缓冲区里的sql语句
list [行号] #显示缓冲区中某一行的内容

Del [行号] 	#删除缓冲的sql语句
Append xxx #在某一行后追回内容
input xxx  #往缓冲增加一行内容
change /xxx/xxx #修改某行内容
#注：del，append，input，change 需要先执行list/l，然后再执行当前命令

2.Ed 用外部编辑器编辑缓冲区

如果觉得通过编辑缓冲区 极不方便,可以调用外部编辑器来编辑缓冲区的内容,命令为 edit 可简写为 ed，修改oracle用户下的.profile文件，添加 editor=vim

3.缓冲区的内容保存到外部文件

Save 把缓冲区的内容保存到外部文件
Save 路径/文件名 [create/append/replace]

4.读取外部文件到缓冲区

#1把外部文件内容读到缓冲区(但不显示)，并执行 Start 
@ 路径/文件名 
start 路径/文件名 (同上，直接执行外部文件的内容)
#2是把外部文件的内容读到缓冲区，但是不执行
get 路径/文件名 
#读取外部文件内容，显示并执行
Run 路径/文件名

5.sqlplus 中使用变量

  1. 交互式变量
  2. 提前定义变量 (undefine 取消一个变量) define xxx=aaa;
  3. 参数变量

第三章 列类型与DDL

1.oracle的列类型

类别 数据类型 oracle mysql
字符串 char char char
varchar varchar2(1-4000) varchar(0-65535)
数值 number int/short/byte/long
number(m,n) float/double/decimal
integer int/integer
日期 date/timestamp date/time
文本 clob/long tinytext/text/mediumtext/longtext
二进制对象 blob/long raw tinyblob/blob/mediumblob/longblob
二进制信息 raw binary/varbinary
枚举 enum 不支持
集合 set 不支持
自增类型 支持 不支持
函数/表达式 不支持 支持
虚拟字段 不支持 支持
空 NULL NULL
事务回滚 rollback rollback

2.建表语句 DDL

类型 oracle mysql
添加 alter table A add(xxx) alter table A add column xxx
修改列属性 alter table A modify xxx alter table A modify xxx
修改列名 alter table A rename column xx alter table A change xx
删除列 alter table A drop column xxx alter table A drop column xxx
查看列 desc 表名 desc 表名
修改表名 alter table A rename to B alter table A rename to B
查看库 select name from v$database show databases
查看表 select table_name from all_tables show tables
删除表 drop table A
清空表 truncate table A

第四章 约束

约束是加在表上的一种强制性的规则 ,是保证数据完整性的一种重要手段 .当向表中插入或修改数据时,必须满足约束所规定的条件.如性别必须是"男/女",部门号必须是已存在的部门号等等.一般而言,保证数据完整性大致有3种方法：程序代码,触发器,约束.

1.约束类型：

类型 含义
NOT NULL 非空约束
UNIQUE 唯一性约束
PRIMARY KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查约束

2.约束的创建 constraint：

#type1
create table 表名 (
列1 数据类型 constraint 约束名1 约束类型,
列2 数据类型 constraint 约束名2 约束类型,
...
);

#type2
create table 表名 (
列1 数据类型,
...
constraint 约束名1 约束类型(列名),
constraint 约束名2 约束类型(列名),
...
);

#注意： not null 只能写在列上来约束其他4种都可以列声明之后单独写

3.外键的声明

#外键的声明稍复杂一点,因为牵涉到另一张表
create table 表名 (
列1 列类型
contraint 约束名 foreign key (列名) 
references 其他表(列名)
);
#注意: 另一张表被引用的列需是主键或Unique

4.建表后添加约束

在需要批量导入数据时,约束会影响导入速度,可以先不要约束,导入完毕后,再添加约束.

alter table 表名 add (
constraint 约束名 约束类型(列名),
constraint 约束名 约束类型(列名)
);

alter table 表名 modify (
列名 constraint 约束名 not null
) ; 
//因为not null 类型必须声明在列上,无法声明在表上,所以必须用modify方式来写

5.约束的删除与禁用

想改一个约束类型,只能先删除约束再添加新的约束
语法: alter table 表名 drop constraint 约束名;

alter table student drop constraint gen_check
#注意:如果删除主键约束时,该主键是另一表的外键,则该主键不能直接删除,除非连带把外键约束也删除.
alter table dept drop constraint pk_dept cascade;

#也可以临时禁用1个约束
alter table 表名 disable consstaint 约束名
#注意:如果禁用约束后加了一些非法数据,再开启约束是会失败的

第五章 索引

索引就像字典前的“按拼音/偏旁查询目录”，可以提高查询效率，降低了增删改的效率。数据库内部常用哈希索引，和btree索引

1.索引 index 的创建与查询

create [unique] index 索引名
on 表名(列1，列2...)

建1个列上称为单列索引,否则称复合索引
索引信息存放在 user_indexes ,user_ind_columns表

2.删除索引

Drop index 索引名

3.索引index的注意事项 ：

在where子句中经常使用的列上创建索引，大量重复的值加索引意义不大
具有唯一值的列是建索引的好的选择，但具体还要看是否经常用他查询。

如果where经常用某N个列一些查询，考虑建复合索引。

索引是有代价的–降低了增删改的速度，并不是加的越多越好。

第六章 序列

序列是一种数据库对象,用来自动生成一组唯一的序号.序列是一种共享式的对象,多个用户可以共同使用序列中的序号.一般将序列应用于表的主键列,这样,当向表中插入数据时,主键列就使用了序列的序号,从而保证主键不会重复.用序列来产生主键,可以获得可靠的主键值.
一句话: 序列就是序号生成器!

1.序列 sequence 的创建

create sequence 序列名 increment by n
start with n
maxvalue n | nomaxvalue
minvalue n  | nominvalue
cycle | nocycle
cache n | nocache

2.序列 sequence 的使用

序列的作用就是为我们提供序号,序列提供了2个伪列, nextval, currval.很明显,分别是"下个值", “当前值”

select seq1.nextval from dual;

insert into xx表(col1,col2) values (seq1.nextavl,yy);

3.序列 sequence 的修改

alter sequence 序列名
选项 新值
//如：
alter sqquence 序列名
minvalue 1  --最小值
nomaxvalue  --不设置最大值(由机器决定)，或 根据表字段的值范围设置 maxvalue
maxvalue 999  -- 最大值
start with 1   --从1开始计数，数值可变
increment by 1  --每次加1，数值可变
nocycle  --一直累加，不循环；cycle：达到最大值后，将从头开始累加
nocache；  --不建缓冲区。   如果建立cache那么系统将自动读取cache值个seq，这样会加快运行速度；如果在单机中使用cache，或者oracle死了，那么下次读取的seq值将不连贯，所以不建议使用cache。
  
#注意:1不能修改开始值，2修改只影响新产生的值,不影响已产生的值

4.序列 sequence 的删除

drop sequence 序列名

第七章 同义词

同义词就是别名,外号

create [public] synonym 同义词 for 用户名.对象名
drop synonym 同义词
public 是所有用户可用的同义词,一般由DBA创建

#注:scott用户默认没有创建synonym的权限
#需要授权: grant create synonym to scott

第八章 增删改查 MDL

1.增加

INSERT INTO  表名（列1,……  列n）  VALUES(值 1,……  值 n);
INSERT INTO  表名  VALUES(值 1,……  值 n);

2.修改

update 表名 set 列1 = 新值1,列2 = 新值2 where expr

3.删除

delete from 表名 where expr

4.查询

select  列1,列2,..列n from 表名 where expr

第九章 查询子句详解

1.select 子句介绍

Where 条件查询
group by 分组
having 筛选
order by 排序

2.having介绍

having与where异同点:
having与where类似,可筛选数据 where后的表达式怎么写,having就怎么写

where针对表中的列发挥作用,查询数据;having针对分组(group)查询的结果发挥作用,筛选组

3.select rownum应用

uRownum虚拟列，代表取出的行所在的行号

第十章 连接查询

连接查询(join/left join /right join/inner join/full outer )

第十一章 子查询

子查询就是在原有的查询语句中，嵌入新的查询,来得到我们想要的结果集。一般根据子查询的嵌入位置分为，where型子查询,from型子查询

1.where型子查询即是:把内层sql语句查询的结果作为外层sql查询的条件.

#典型语法:
select * from tableName 
where colName = (select colName from tbName where ....)
{where colName in (select colName from tbName where ..)}

2.from型子查询即:把内层sql语句查询的结果作为临时表供外层sql语句再次查询.

#典型语法:
select * from (select * from tableName where ...) where....

3.exists型子查询:外层sql查询所查到的行代入内层sql查询,要使内层查询能够成.查询可以与in型子查询互换,但效率要高.

典型语法:
select * from tablename
where exists(select * from tableName where ...)

第十二章 视图 view

视图是一种虚拟表，本身不保存数据，而是从表中取得的数据。可以理解为表的映射，或更简单的理解为一个查询结果。

#比如，我们频繁的查询如下语句：
select empno,ename,sal from emp where sal > (select avg(sal) from emp);
#如果把这个语句定义为视图，并从视图查询数据

1.view的用途

视图可以帮我们简化查询(如上页中的复杂查询，如果不用视图，则需要子查询才能达到效果);视图可以帮我更精细的控制权限(比如一张表，有工资列，密码列，等，我们可以选择列生成视图，开放给不同的用户，达到列级的权限控制)

  1. view的操作语法

    创建视图：
Create or replace view 视图名
As select  语句
With read only  --是否只读
With check option –是否执行约束检查

删除视图：
Drop view 视图名

  2. 复杂视图
    如果select 语句中只针对单表进行列的查询，且没有对列进行表达式运算或函数运算，这种称为简单视图(如果对多个表进行查询，或列经过运算，或分组等，这种称为复杂视图).复杂视图不能进行DML操作
    本质区别：数学上是否一一对应。 即任意一行视图的记录，能对应表中唯一的一行，就是简单视图

第十三章 事务

1.事务 之事务的ACID特性

原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性。 组成事务的所有查询必须：要么全部执行，要么全部取消（就像上面的银行例子）。

一致性(Consistency)：指数据的规则,在事务前/后应保持一致

隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.

持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消

2.事务之事务的用法

开启事务(第1条dml语句即进入事务)
执行sql操作(普通sql操作)
设置保存点(savepoint 保存点)
提交/回滚(commit/rollback)
部分回滚(rollback to 保存点)

3.事务的隐式提交

事务可以显式的提交，也可以隐式的提交
显式：commit
隐式: 遇到DDL或DCL语句，或退出系统时会隐式提交

第十四章 sql函数

1.字符串函数

函数名称 含义
concat(字符串1,字符串2) –连接字符串，2个参数
chr() ,ascii()
instr(字符串,子串,start,occurrence) // occu代表第几次出现
length(字符串)
lower(),upper()
lpad(字符串,长度,填充字符),rpad()
ltrim(),rtrim(),trim()
replace(字符串,子串,替换字符串)
substr(字符串,开始位置,长度)

2.数学函数

名称 含义
abs() 绝对值
ceil() 进1取整
floor() 舍余取整
mod() 取模
round(数据，舍入位置) 四舍五入
trunc(数据，舍入位置) 截取

3.日期函数

名称 含义
add_months(日期，整月数) 计算日期+N月后的新日期
last_day(日期) 该日期所在月的最后一天
months_between(日期1，日期2) 返回日期相差的月份 (浮点型)
next_day（日期，周N) 返回最近的周N的日期

3.类型转换函数

名称 含义 示例
to_char() 把日期/数字转换为字符串 to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)
to_date() 字符串转日期型 to_date(‘12-09-2013’,‘dd-mm-yyyy’)
to_number() to_number(‘1,000.50’,‘999,999,99’)

第十五章 PL/sql编程

1.PL/sql概念

Procedural language sql是一种标准的数据库访问语言，但无法编程，
PL/SQL是Oracle公司开发的“方言”，允许编程，是对SQL的一种补充。

2.PL/sql的结构

declare
 变量声明部分
begin
 执行部分
exception
 异常处理部分
End

*：declare 和 exception部分是可选的 
默认:调用一个匿名块/存储过程后,只执行不输出
学习调试时: set serveroutput on

3.PL/sql中变量的定义

变量 的定义有2种格式
变量名 变量类型 [约束] default 默认值
变量名 变量类型 [约束] [:=初始值]
-------
变量名 表名%rowtype
变量名 表名.列%type
变量名 另一变量%type

4.一个简单的PL/sql块

declare
   i number :=99; 
begin
   i:=i+10;
   dbms_output.put_line(i);
end;
/
#注意打开serverout 选项

5.PL/sql块中自定义数据类型

#自定义记录类型record
type people is record(
name varchar2(10),
age number
gender char(2)
);

#自定义集合类型 table
type charset is table of char;
ans charset := charset('a','b','c','d');

6.PL/sql块中流程控制

if 条件 then
...
[else | elsif 条件 then]
...
end if;

loop 
循环体
end loop;
exit退出

#或
loop 
循环体
exit when 条件;
end loop;

while 条件  loop
循环体
end loop;

for 循环变量 in [reverse] 起始值..终止值 loop
循环体
end loop;

7.PL/sql访问数据库

PL/SQL的主要目的是对数据库进行操作,因此,在PL/SQL块中可以包含select语句,DML语句,还可以包含DCL语句. 但不能包含DDL语句.通过SQL语句及流程控制,可以编写复杂的PL/SQL块,对数据库进行复杂的访问.
注意,PL/SQL一般是在应用程序中调用.

8.PL/sql查询数据

在PL/SQL块中通过selct 语句从数据库查询数据并处理,
所以select语句下面特殊格式:
select 列1,列2... into 变量1,变量2
from
表
......

#这样,就把列1,列2的值赋给了变量1,变量2

9.PL/sql递归调用

 declare
 m number;
 res number;
 function fact(n integer) return number
 is
 begin
 if n=1 then
    return 1;
 else
    return fact(n-1)+n;
 end if;
 end;
 begin
 m:=10;
 res:=fact(m);
 dbms_output.put_line(m||'之和'||res);
 end;

10.PL/sql异常处理

异常的判断语法:
exception
 when 异常1 or 异常2 then
     语句..;
 when 异常3 or 异常4 then
     语句 ...;
 when others then
      语句 ...;
end

11.附录:PL/sql预定义异常

NO_DATA_FOUND   在使用SELECT INTO 结构，并且语句返回NULL值的时候；访问嵌套表中已经删除的表或者是访问INDEX BY表（联合数组）中的未初始化元素就会出现该异常
TOO_MANY_ROWS   常见错误，在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行，而比较运算符为相等的时候也会引发该异常。
ZERO_DIVIDE 将某个数字除以0的时候，会发生该异常
ACCESS_INTO_NULL	试图访问未初始化对象的时候出现
CASE_NOT_FOUND		如果定义了一个没有ELSE子句的CASE语句，而且没有CASE语句满足运行时条件时出现该异常
COLLECTION_IS_NULL		当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候，会出现该异常
CURSOR_ALREADY_OPEN		游标已经被OPEN，如果再次尝试打开该游标的时候，会出现该异常
DUP_VAL_ON_INDEX		如果插入一列被唯一索引约束的重复值的时候，就会引发该异常（该值被INDEX认定为冲突的）
INVALID_CURSOR		不允许的游标操作，比如关闭一个已经被关闭的游标，就会引发
INVALID_NUMBER		给数字值赋非数字值的时候，该异常就会发生，这个异常也会发生在批读取时候LIMIT子句返回非正数的时候
LOGIN_DENIED		程序中，使用错误的用户名和密码登录的时候，就会抛出这个异常
NOT_LOGGED_ON		当程序发出数据库调用，但是没有连接的时候（通常，在实际与会话断开连接之后）
PROGRAM_ERROR		当Oracle还未正式捕获的错误发生时常会发生，这是因为数据库大量的Object功能而发生
ROWTYPE_MISMATCH		如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常
SELF_IS_NULL		调用一个对象类型非静态成员方法（其中没有初始化对象类型实例）的时候发生该异常
STORAGE_ERROR		当内存不够分配SGA的足够配额或者是被破坏的时候，引发该异常
SUBSCRIPT_BEYOND_COUNT		当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候，发生该异常（类似于java的ArrayIndexOutOfBoundsException）
SUBSCRIPT_OUTSIDE_LIMIT		使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发
SYS_INVALID_ROWID		将无效的字符串转化为ROWID的时候引发
TIMEOUT_ON_RESOURCE		当数据库不能安全锁定资源的时候引发
USERENV_COMMITSCN_ERROR		只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数
VALUE_ERROR		将一个变量赋给另一个不能容纳该变量的变量时引发

第十六章 存储过程与存储函数

前面用到的过程和函数都是写在PL/SQL块中,放在缓冲区里.没有进行保存,下次要使用了再次声明,调用.我们可以把过程和函数建立并保存在数据库中,形成一个对象.这种存储后的过程和函数,称为:存储过程 存储函数

1.存储过程与存储函数创建语法

create or replace procedure 名称[(参数)]
authid current_user|definer --以定义者还是调用者的身份运行
is[不要加declare]
变量声明部分
begin
主体部分
exception
异常部分
end;

2.存储过程与存储函数的删除

drop procedure 存储过程名
drop function 函数名

第十七章 游标

游标是一种私有的工作区,用于保存sql语句的执行结果.在执行一条sql语句时,数据库服务区工作区,这里保存了sql语句执行的相关信息

工作区有2种形式的游标,隐式的和显式的.隐式游标由数据库自动定义,显示游标由用户自己定义.

1.隐式游标-cursor的属性

隐式游标的属性 描述
SQL%isopen 判断游标是否打开
sql%rowcount 对于增删改,是受影响行数,对于select,值为1
sql%found 布尔值,是否有值受到影响,对于select,值为1
sql%notfound 与found相反

2.显式游标-cursor的属性

隐式游标的属性 描述
cursor%isopen 判断游标是否打开
cursor%rowcount 当前已fetch得到的行
cursor%found 上次fetch是否得到数据
cursor%notfound 与found相反

3.游标-cursor简单例子

begin
delete from student where sid=9;
if SQL%ROWCOUNT>0 then
dbms_output.put_line('影响了');
else
dbms_output.put_line('没影响');
end if;
end;

4.自定义游标的典型流程

cursor 游标名[(参数1,参数2..)]
is
select语句 [for update] ;

open 游标名
fetch 游标名 to 变量1,变量2;

close 游标名;

第十八章 触发器

进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求，利用触发器就能满足这样的需求。它能在表中的某些特定数据变化时自动完成某些查询。运用触发器不仅可以简化程序，而且可以增加程序的灵活性。

触发器是一类特殊的事务 ,可以监视某种数据操作(insert/update/delete),并触发相关操作(insert/update/delete)

1.触发器应用场合

1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作。比如，当一个订单产生时,订单所购的商品的库存量相应减少。
2.当表上某列数据的值与其他表中的数据有联系时。比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度。
3.当需要对某张表进行跟踪时。比如，当有新订单产生时，需要及时通知相关人员进行处理，此时可以在订单表上设计添加触发器加以实现

2.触发器创建语法 之4要素

  1. 监视地点(table[列])
  2. 监视事件insert/update/delete
  3. 触发时间after/before/instead of
  4. 触发事件insert/update/delete

3.触发器创建语法

create trigger 触发器名称
after/before/instead of(触发时间)
insert/update/delete [of列名] (监视事件)
on  表名 (监视地址) 
[for each row [when条件]]
begin
sql1;
..
sqlN;
end

4.触发器的删除

drop trigger triggerName

5.触发器案例实战

1.设计一张商品表 一张订单表
2.创建3个触发器,作用分别是:
	当下订单购买商品时,相应商品减少库存
	修改订单中的商品数量时,相应商品修改库存
	当取消某订单时,相应商品增加库存

第十九章 用户管理

1.3类用户:sysdba，sysoper，普通用户

sysdba拥有最大的权限，操作所有用户的数据库。

2.创建用户与删除用户

create user 用户名 identified by “密码"
default tablespace users
temporary tablespace temp
quota 20M on users 
password expire
account unlock;

drop user 用户名
#如果用户名已经有表，视图等，则不允许删除。

3.修改用户密码

alter user 用户名 identified by "新密码"

或在sqlplus下 password命令
SQL> password[ lisi]
更改 lisi 的口令
新口令:

--不加用户名即修改自己的口令

第二十章 系统权限的授予和收回

1.授予：

grant 权限1，权限2 … to 用户1,用户2 …

2.收回：

revoke 权限1，权限2 … from 用户1,用户2 …

系统权限 说明
create table 建表
create view 建视图
create session 登陆数据库
create user 创建用户
create trigger 创建触发器
alter user 改用户
alter session 修改会员
alter database 改数据库
create tablespace 改表空间
create procedure 建过程，函数
ulimited tablespace 无限空间

第二十一章 对象权限的授予和收回

1.授予

grant 对象权限1（列名）,对象权限2(列名)...  On 对象 to 用户1,用户2 with grant option;

2.收回

revoke 对象权限1,对象权限2... on 用户名.对象名  from 用户1,用户2
权限\对象 表 视图 序列 存储程序
Alter Y Y
Select Y Y Y
Insert Y Y
Delete Y Y
Update Y Y
Refereneces Y
Execute Y Y
Grant Y Y Y
Lock Y Y Y
rename Y Y Y Y

第二十二章 权限的查询

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SYS                            UPDATE ANY CUBE BUILD PROCESS            NO
SYS                            CREATE MINING MODEL                      NO

第二十三章 导出导入

exp 用户名/密码 file=路径 rows=y
imp 用户名/密码 file=路径 tables=(表1,..)

附1：pl/sql实例

--p1
begin
dbms_output.put_line('你好 世界');
end;

--p2 引入变量
declare
    age number default 90;
    height number := 175;
begin
    dbms_output.put_line('年龄'||age||'身高'||height);
end;

--p3 变量开始运算
declare
    age number default 90;
    height number := 175;
begin
    dbms_output.put_line('年龄'||age||'身高'||height);
    age := age + 20;
    dbms_output.put_line('20年后年龄'||age||'岁');
end;

--p4 引入表达式
declare
    age number default 90;
    height number := 175;
begin
    if age>70 then
        dbms_output.put_line('古稀之年');
    else
        dbms_output.put_line('风华正茂');
    end if;
end;

--p5 流程控制
declare
    age number default 90;
    height number := 175;
    gender char(2) := '男';
begin
    if gender='男' then
        dbms_output.put_line('你可以和女性结婚');
    end if;

    if height>170 then
        dbms_output.put_line('可以打篮球');
    else 
        dbms_output.put_line('可以踢足球');
    end if;

    if age<20 then
        dbms_output.put_line('年轻小伙');
    elsif age <= 50 then
        dbms_output.put_line('年轻有为');
    elsif age <=70 then
        dbms_output.put_line('安享天伦');
    else  
        dbms_output.put_line('佩服佩服');
    end if;
end;

--p6 计算1-100的和
declare
    i number :=0;
    total number :=0;
begin
    loop
        i := i+1;
        total := total + i;
        if i=100 then
            exit;
        end if;
    end loop;
    dbms_output.put_line('总和'||total);
end;

-- p7: 跳出loop的方法
declare
    i number :=0;
    total number :=0;
begin
    loop
        i := i+1;
        total := total + i;
        exit when i>=100;
    end loop;
    dbms_output.put_line('总和'||total);
end;

--p8 whlie循环
declare
    i number :=0;
    total number :=0;
begin
    while i<100 loop
        i := i+1;
        total := total + i;
    end loop;
    dbms_output.put_line('总和'||total);
end;

--p9 for 循环
begin
    --for 循环变量 in 起始值..结束值 loop
    --xxxxx
    --end loop;
    for i in 1..9 loop
        dbms_output.put_line(i);
    end loop;

    for i in reverse 1..9 loop
        dbms_output.put_line(i);
    end loop;
end;

--p10 没有返回值的"函数"
--做一个求面积的过程
--declare
--    area number;
--    procedure 过程名(参数名 类型,...) is
--    begin
--        主体
--    end;
--begin
--end;

declare 
    area number;
    procedure mian(a number,b number) is
    begin
        area := a * b;
        dbms_output.put_line(a||'乘'||b||'的面积是'||area);
    end;
begin
    mian(5,4);
    mian(6,7);
    mian(3,7);
end;

--p11 做一个求面积的函数
--declare
--    area number;
--    function 过程名(参数名 类型,...) return 类型 is
--    begin
--        主体
--    end;
--begin
--end;

declare 
    area number;
    function mian(a number,b number) return number is
    begin
        area := a * b;
        return area;
    end;
begin
    dbms_output.put_line(mian(5,4));
    dbms_output.put_line(mian(3,7));
    dbms_output.put_line(mian(6,9));
end;

--p12 自定义变量类型 之记录类型
declare
    type student is record
    (
        sno char(5),
        name varchar2(10),
        age number
    );
    lisi student;
begin
    lisi.sno := 's1008';
    lisi.name := '李四';
    lisi.age := 19;
    dbms_output.put_line('我叫'||lisi.name||',我'||lisi.age||'岁,学号是'||lisi.sno);
end;

--p13 自定义类型之集合类型
declare 
 type answer is table of char(2);
 ans answer := answer('a','b','c','d');
begin
    dbms_output.put_line('共有'||ans.count()||'答案,分别是:');
    dbms_output.put_line(ans(1));
    dbms_output.put_line(ans(2));
    dbms_output.put_line(ans(3));
    dbms_output.put_line(ans(4));
end;

--p14 声明数据类型的第3个方法
declare
    age number;
    变量名 另一个变量%type;
    age 表名.列名%type; --声明和列一样的类型
    --简化声明record类型
    变量名 表名%rowtype;
begin
end;

--p15 测试一下rowtype
declare
    xg student%rowtype;
begin
    xg.sno := 123;
    xg.name := '小刚';
    dbms_output.put_line(xg.sno||xg.name);
end;

--p16 pl/sql操作数据库中的数据
--查询部门的名称及地区，及部门的总薪水与奖金
declare 
    depart dept%rowtype;
    total_sal number;
    total_comm number;
    procedure deptinfo(dno number)
    is
    begin
        select dname,loc into depart.dname,depart.loc from dept where deptno=dno;
        select sum(sal),sum(comm) into total_sal,total_comm from emp where deptno=dno;
        dbms_output.put_line('部门名称：'||depart.dname||'在'||depart.loc);
        dbms_output.put_line('这个部门每月工资及奖金各是'||total_sal||'和'||total_comm);
    end;
begin
    deptinfo(80);
    deptinfo(30); 
end;

--p17 引入异常处理
declare 
    depart dept%rowtype;
    total_sal number;
    total_comm number;
    procedure deptinfo(dno number)
    is
    begin
        select dname,loc into depart.dname,depart.loc from dept where deptno=dno;
        select sum(sal),sum(comm) into total_sal,total_comm from emp where deptno=dno;
        dbms_output.put_line('部门名称：'||depart.dname||'在'||depart.loc);
        dbms_output.put_line('这个部门每月工资及奖金各是'||total_sal||'和'||total_comm);
    end;
begin
    deptinfo(80);
    deptinfo(30);
exception
    when NO_DATA_FOUND then
       dbms_output.put_line('没有数据');
    when others then
       dbms_output.put_line('其他错误');
end;

--p18:递归过程或函数
--求1->N的和,N允许输入
declare
    m number;
    total number;
    function qiuhe(n number) return number
    is
    begin
        if n>1 then
            return n + qiuhe(n-1);
        else 
            return 1;
        end if;
    end;
begin
    dbms_output.put_line(qiuhe(10));
end;

--p19 存储过程/存储函数
create function qiuhe(n number) return number
is
begin
    if n>1 then
        return n + qiuhe(n-1);
    else 
        return 1;
    end if;
end;

附2：触发器实例

-- g 商品表
create table g(
		gid number,
		gname varchar2(20),
		cnt number
);
insert into g values (seq1.nextval,'牛',10);
insert into g values (seq1.nextval,'马',8);
insert into g values (seq1.nextval,'狼',7);
insert into g values (seq1.nextval,'猫',6);

-- o 订单表
create table o (
		oid number,
		gid number,
		much number
);

监视：o表
动作: insert
触发: update g
时间：after 

create trigger 触发器名字
		触发时间
		监视的动作 on 表名[监视地点]
begin
    触发后的动作
end;

create trigger t1
		after insert on o
begin
    update g set cnt=cnt-new.much where gid=new.gid;
end;

create trigger t2
		after insert on o
		for each row
begin
    update g set cnt=cnt-:new.much where gid=:new.gid;
end;

--表级触发器
create trigger t3
		after delete on goods
begin
    dbms_output.put_line('有人触发我');
end;

--行级触发器
create trigger t4
		after delete on goods
		for each row
begin
    dbms_output.put_line('有人触发我');
end;

--before发生的触发器，有机会改sql语句的值
create trigger t5
		before insert on o
		for each row
declare
		tmp number;
begin
    select cnt into tmp from g where gid=:new.gid;
    if :new.much > tmp then
        :new.much := tmp;
    end if;
    update g set cnt=cnt-:new.much where gid=:new.gid;
end;
posted @ 2019-05-05 01:25  南山道士  阅读(89)  评论(0)  编辑  收藏  举报