oracle

Oracle

1、用户

oracle第一次登陆，可以登录sys、system用户。

请输入用户名:  system
输入口令:(这里输入之前使用的命令)

sys和system用户：

Oracle安装会自动的生成sys用户和system用户
(1)、sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install
(2)、system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限，默认的密码是manager
(3)、一般讲，对数据库维护，使用system用户登录就可以拉
注意：也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。

scott用户：

1、scott是默认用户，需要导入才能使用

2、导入scott的用户 (默认scott用户是锁定状态)

@D:\oracle\app\oracle\product\11.2.0\server\rdbms\admin\scott

3、解锁scott用户

alter user scott account unlock;

4、设置scott密码生效

alter User scott identified by tiger;

连接、展示

解锁后即可使用：

SQL> conn scott
输入口令:
已连接。
SQL>

1)、连接命令
1.conn[ect]
用法：conn 用户名/密码@网络服务名 [as sysdba/sysoper]
说明：当用特权用户身份连接时，必须带上as sysdba或是as sysoper
eg、
2)、展示当前用户命令
SQL> show user
USER 为 "SCOTT"
SQL> conn system/oracle@orcl
已连接。
SQL> show user
USER 为 "SYSTEM"
SQL>
以上命令实现类似切换用户的效果

用户管理：

SQL> conn sys as sysdba;
输入口令:
已连接。
SQL> create user cgl identified by eking;

用户已创建。

SQL> grant CONNECT,RESOUCE to cgl;
grant CONNECT,RESOUCE to cgl
              *
第 1 行出现错误:
ORA-01919: 角色 'RESOUCE' 不存在


SQL> grant CONNECT to cgl;

授权成功。


//相关命令
1、用户的管理

创建用户：create user 用户名 identified by 密码;

2、用户的授(包含两个权限 CONNECT,RESOUCE)

grant CONNECT,RESOUCE to 用户;

3、修改密码

ALTER USER 用户名 IDENTIFIED BY 新密码

4、让密码失效：登陆之后需要立即修改密码

ALETR USER 用户名 PASSWORD EXPIRE ;

5、锁定用户

ALTER USER 用户名 ACCOUNT LOCK;

6、解锁用户

ALTER USER 用户名 ACCOUNT UNLOCK;

7、将A用户名的操作权限授予其他用户

ps:在A用户下访问B 用户名的表

GRANT SELECT,INSET,UPDATE ,DELETE ON 用户名.表名 TO 其他用户

8、回收权限

REVOKE CONNECT ,RESOURCE FROM 用户名;

9、删除用户

DROP USER 用户名  (CASCADE); 需要先删除用户名创建的内容

角色

1)、connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限呢？
create cluster
create database link
create session
alter session
create table
create view
create sequence



2)、resource角色
resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含unlimited tablespace系统权限。
resource角色包含以下系统权限：
create cluster
create indextype
create table
create sequence
create type
create procedure
create trigger



3)、dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

2、数据类型

CHAR(length)

存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。

VARCHAR2(length)

存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。

NUMBER(p，s)

既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。

FlOAT/DOUBLE

小数型

INT/INTEGER

整数型

DATE

存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。

TIMESTAMP

不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。

CLOB

存储大的文本，比如存储非结构化的XML文档

BLOB

存储二进制对象，如图形、视频、声音等。

日期类型

--oracle插入日期
insert into timedemo values('1906/05/06')
insert into timedemo values(to_date('2000-01-02','yyyy-mm-dd'))

--临时修改默认格式，重启后失效
alter session set nls_date_format ='yyyy-mm-dd';
insert into timedemo values('1905-05-06')


select  e.employee_id ,e.first_name ,e.salary,e.hire_date from hr.employees e where e.hire_date >to_date('2001-06-10 16:00:00','yyyy-mm-dd hh24:mi:ss')
--这里查出的数据为2001之后的数据，如2003
select  e.employee_id ,e.first_name ,e.salary,e.hire_date from hr.employees e where e.hire_date >'2001/06/10'

3、表

-创建表
--学生表
create table student (
   xh number(4), --学号
   xm varchar2(20), --姓名
   sex char(2), --性别
   birthday date, --出生日期
   sal number(7,2) --奖学金
);

--班级表
create table class(
  classid number(2),
  cname varchar2(40)
);



--修改表
--添加一个字段
sql>alter table student add (classid number(2));

--修改一个字段的长度
sql>alter table student modify (xm varchar2(30));
--修改字段的类型或是名字（不能有数据） 不建议做
sql>alter table student modify (xm char(30));
--删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应该是加在后面)
sql>alter table student drop column sal;
--修改表的名字 很少有这种需求
sql>rename student to stu;


--删除表
sql>drop table student;



--添加数据
--所有字段都插入数据
insert into student values ('a001', '张三', '男', '01-5 月-05', 10);
--oracle中默认的日期格式‘dd-mon-yy’ dd 天 mon 月份 yy 2位的年 ‘09-6 月-99’ 1999年6月9日
--修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
alter session set nls_date_format ='yyyy-mm-dd';
--修改后，可以用我们熟悉的格式添加日期类型：
insert into student values ('a002', 'mike', '男', '1905-05-06', 10);
--插入部分字段
insert into student(xh, xm, sex) values ('a003', 'john', '女');
--插入空值
insert into student(xh, xm, sex, birthday) values ('a004', 'martin', '男', null);
--问题来了，如果你要查询student表里birthday为null的记录，怎么写sql呢？
--错误写法：select * from student where birthday = null;
--正确写法：select * from student where birthday is null;
--如果要查询birthday不为null,则应该这样写：
select * from student where birthday is not null;



--修改数据
--修改一个字段
update student set sex = '女' where xh = 'a001';
--修改多个字段
update student set sex = '男', birthday = '1984-04-01' where xh = 'a001';
--修改含有null值的数据
不要用 = null 而是用 is null；
select * from student where birthday is null;



--删除数据
delete from student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
--delete的数据可以恢复。
savepoint a; --创建保存点
delete from student;
rollback to a; --恢复到保存点
一个有经验的dba，在确保完成无误的情况下要定期创建还原点。

drop table student; --删除表的结构和数据；
delete from student where xh = 'a001'; --删除一条记录；
truncate table student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

事务

1、提交事务

2、回滚事务

1.设置保存点 savepoint a
2.取消部分事务 rollback to a
3.取消全部事务 rollback

这个回滚事务，必须是没有commit前使用的；如果事务提交了，那么无论你刚才做了多少个保存点，都统统没用。如果没有手动执行commit,而是exit了，那么会自动提交。

savepoint a;--保存点

update county set name='东城' where pro_id=110000 and name like '东%' --修改数据

rollback to a; --回滚

SQL

1、关键字

in
on 
like
order 
as

2、sql函数

--1、统计函数

--count计数函数
select count(*) from hr.employees e where salary<5000;

--max,min最值函数，avg平均函数
select max(salary),min(salary),avg(salary) from hr.employees where salary <10000;

--求和函数
select sum(salary) from hr.employees where salary<6000;


--2、字符函数

--截取字符函数
SELECT substr(last_name,1,4),substr(salary,1,3) AS d FROM hr.employees;

--提取长度函数
select last_name,length(last_name),salary,length(salary) from hr.employees;

--lower/upper大小写转换
SELECT lower(last_name)||upper(first_name) AS d FROM hr.employees;

--替代函数，如下，遇到'a'就替换为'Hello'
select replace(last_name,'a','Hello') new_name from hr.employees;

--查询位置返回索引
select instr(last_name,'a') new_name from hr.employees;

3、约束

一、维护数据的完整性
数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

​

二、约束
约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null、 unique， primary key， foreign key和check 五种。
1)、not null(非空)
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
2)、unique(唯一)
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
3)、primary key(主键)
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。
需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。
4)、foreign key(外键)
用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique 约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
5)、check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。

SQL> create table goods(
   goodsId char(8) primary key, --主键
   goodsName varchar2(30),
   unitprice number(10,2) check(unitprice>0),
   category varchar2(8),
   provider varchar2(30)
);
SQL> create table customer( 
   customerId char(8) primary key, --主键
   name varchar2(50) not null, --不为空
   address varchar2(50),
   email varchar2(50) unique, --唯一
   sex char(2) default '男' check(sex in ('男','女')), -- 一个char能存半个汉字，两位char能存一个汉字
   cardId char(18)
);
SQL> create table purchase( 
   customerId char(8) references customer(customerId),
   goodsId char(8) references goods(goodsId),
   nums number(10) check (nums between 1 and 30)
);

4、序列

序列是oracle的一种数据库对象，用它可以生成递增的整数，独立存在，可以充当主键.

create sequence my_seq increment by 2 start with 1 nomaxvalue nocycle cache 20;

所示序列从1开始，增量为2,无最大值，不循环，缓存20个值。

Create 创建

Sequence 序列 seqEmop 序列名称

Increment by 步长

Stat with 1 开始值

Maxvalue  最大值

Minvalue  最小值



Cycle 循环 nocycle 不循环

Cache 缓存   Cache<maxvalue-minvalue/increment by//一般不采用缓存

Nextval 下一个

Currval 当前值


--创建序列
create sequence stu_id
  increment by 1 
  start with 1 
  maxvalue 999;


select stu_id.currval,stu_id.nextval from dual;
--将序列插入表
insert into student(s_id,s_name,s_sex) values(stu_id.nextval,'Jessasy','女'); 

--s_id为null,设置序列为主键是否可行？
insert into student(s_name,s_sex) values('orange','女'); 
select * from student;

5、记录

记录是Oracle中的一种数据类型，可以把它理解成结构体或者类。

declare 
   type Book is Record( --定义一个记录
   title varchar2(50),
   author varchar2(50),
   book_id number(5)
   );

book1 Book;

begin

--像结构体一样赋值
      book1.title:='bye';
      book1.author:='jackson';
      book1.book_id:='10029';
      Dbms_Output.put_line(book1.title);
      Dbms_Output.put_line(book1.author);
      Dbms_Output.put_line(book1.book_id);

end;

记录类型可以结合表、游标进行操作，在下部分讲解。

查询

排序查询

order by 字段 asc|desc

---排序查询
--根据入职时间进行升序排行，查询工资
select first_name||last_name Name,salary, e.hire_date from hr.employees e order by e.hire_date asc;

多表查询

多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

--多表查询
--显示一类员工的最高与最低工资,以及其当前薪水
select e.first_name||e.last_Name as Name ,e.salary, j.min_salary,j.max_salary
from hr.employees e ,hr.jobs j
where e.job_id=j.job_id;



--查询工资高于10000的员工的工作内容以及入职时间

select j.job_title,e.hire_date
from hr.employees e,hr.jobs j
where e.salary>10000;

连接查询

select h.first_name,j.job_title from hr.employees h right join hr.jobs j on h.job_id=j.job_id;

分组查询

--分组查询除了分组条件外，select后面不允许有原始字段

--分组查询：通过入职时间分组查询平均工资
select avg(salary), e.hire_date from hr.employees e group by e.hire_date;

--分组查询：通过入职时间分组查询平均工资,并过滤掉平均工资小于5000的
select avg(salary), e.hire_date from hr.employees e group by e.hire_date having avg(salary)>5000;

having关键字用于限制分组显示结果。

where在对表查询之前就进行过滤。

--分组查询：通过入职时间分组查询平均工资,工资低于8000的不参与,并过滤掉平均工资小于5000的
select avg(salary), e.hire_date from hr.employees e where salary>8000    group by e.hire_date having avg(salary)>5000;

分页查询

--从表中查询工资大于5000的30行数据的全名和薪水
select h.first_name||h.last_name Name,h.salary from
(select e.*,rownum rn
from hr.employees e where e.salary>5000 and rownum<50) h
where rn>=20;

--查出10名学生
SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM student) A ) WHERE RN BETWEEN 1 AND 10;

自连接

自连接是指在同一张表的连接查询
问题：显示某个员工的上级领导的姓名？
比如显示员工‘FORD’的上级

SELECT worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';

模糊查询

--模糊查询 %：表示0到多个字符 _：表示任意单个字符
--查询所有姓以D开头的员工的姓名、薪水、入职时间
select first_name||last_name Name,salary ,hire_date from hr.employees where First_name like 'D%' ;

--查询以任意字母开头，第二位是e的员工的姓名、薪水、入职时间
select first_name||last_name Name,salary ,hire_date from hr.employees where First_name like '_e%' ;

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus。
多用于数据量比较大的数据局库，运行速度快。

select  e.employee_id ,e.first_name ,e.salary from hr.employees e where e.hire_date >to_date('2001-06-10 16:00:00','yyyy-mm-dd hh24:mi:ss')
union 
select   h.employee_id,h.first_name ,h.salary    from hr.employees h where salary>8000 



--合并查询
--求并集  该操作符与union相似，但是它不会取消重复行，而且不会排序
select  e.employee_id ,e.first_name ,e.salary from hr.employees e where e.hire_date >to_date('2001-06-10 ','yyyy-mm-dd ')
union all
select   h.employee_id,h.first_name ,h.salary    from hr.employees h where salary>8000 

--求交集
select  e.employee_id ,e.first_name ,e.salary from hr.employees e where e.hire_date >to_date('2001-06-10 ','yyyy-mm-dd ')
intersect
select   h.employee_id,h.first_name ,h.salary    from hr.employees h where salary>8000 

--求差集
select  e.employee_id ,e.first_name ,e.salary from hr.employees e where e.hire_date >to_date('2001-06-10 ','yyyy-mm-dd ')
minus
select   h.employee_id,h.first_name ,h.salary    from hr.employees h where salary>8000 

pl/sql语句

PL/SQL不是一个独立的编程语言;它是Oracle编程环境中的工具。

DECLARE 
   message  varchar2(20):= 'Hello, World! my god'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

在plsql developer工具中使用可以输出语句，在output中查看

1、基本语法

PL/SQL是块结构语言; PL/SQL程序划分成几个部分，并在每个部分中写入逻辑代码块。每个块由三个子部分组成 -

• 声明部分 - 此部分是以关键字DECLARE开头。这是一个可选部分，并定义了程序中要使用的所有变量，游标，子程序和其他元素。
• 可执行命令部分 - 此部分包含在关键字BEGIN和END之间，这是一个强制性部分。它由程序的可执行PL/SQL语句组成。它应该有至少一个可执行代码行，它可以只是一个NULL命令，表示不执行任何操作。
• 异常处理部分 - 此部分以关键字EXCEPTION开头。这是一个可选部分，它包含处理程序中错误的异常。

每个PL/SQL语句以分号(;)结尾。 使用BEGIN和END可以将PL/SQL块嵌套在其他PL/SQL块中。 至于/符号，在命令行中表示运作代码。

2、运算符

+,-, *, /
加法，减法/负，乘法，除法
%
属性绑定
'
字符串分隔符
.
组件选择符
(,)
表达式或列表分隔符
:
主机变量指示符
,
项目分隔符
"
引用标识符分隔符
=
关系运算符
@
远程访问指示符
;
声明或语句终止符
:=
赋值运算符
=>
关联运算符
ΙΙ
连接运算符
**
指数运算符
<<, >>
标签分隔符(开始和结束)
/*, */
多行注释分隔符(开始和结束)
--
单行注释指示符
..
范围运算符

<, >, <=, >=
关系运算符

<>, '=, ~=, ^=
不同版本的”不等于”运算符

DECLARE 
   message  varchar2(20):= 'Hello, World! my god'; 
   msg varchar2(10):='nice';
   b varchar2(10);
BEGIN 
   if msg<>message then
      b:='1';

   dbms_output.put_line(b);
   end if; 

END; 

3、数据类型

标量(SCALAR)类型 - 它是没有内部组件的单个值，例如：NUMBER，DATE或BOOLEAN等。

大对象(LOB)类型 - 指向与其他数据项(例如：文本，图形图像，视频剪辑和声音波形)分开存储的大对象的指针。

复合类型 - 具有可单独访问的内部组件的数据项。例如，集合和记录。

引用类型 - 指向其他数据项。

4、变量、常量

变量的声明很简单，不用多说。

也有局部变量和全局变量。

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
   c_number constant varchar2(20) :='This''s a const';--需要初始值、赋值后不可修改,''在字符串中表示为'
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   dbms_output.put_line(c_number); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 

Outer Variable num1: 95
Outer Variable num2: 85
This's a const
Inner Variable num1: 195
Inner Variable num2: 185

5、条件、循环语句

这里的条件语句语法和pb一样。

DECLARE 
   message  varchar2(20):= 'Hello, World! my god'; 
   msg varchar2(10):='nice';
   b varchar2(10);
BEGIN 
   if msg<>message then
      b:='1';

   dbms_output.put_line(b);
   end if; 

END; 

循环语句：

declare
  num int:=10;
  a number;
BEGIN 

---1、loop直接循环
  loop  
      dbms_output.put_line(num);
      num:=num+10;

      exit when num>100; --通过exit直接结束
  end loop;  --表示循环结束


---2、for循环
  FOR a in 10 .. 20 LOOP 
      dbms_output.put_line('value of a: ' || a); 
  END LOOP;

---a在循环中赋值后，局部有值，全局没有
  dbms_output.put_line('-------------' || a); 

---3、while循环
  a:=100;
  while a<1000 loop
         a:=a+100;
         dbms_output.put_line('value of a: ' || a); 
  end loop;

END; 

6、dbms包

1
DBMS_OUTPUT.DISABLE;
禁用消息输出。
2
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);
启用消息输出。buffer_size设置为NULL值表示无限制的缓冲区大小。
3
DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER);
检索一行缓冲的信息。
4
DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER);
从缓冲区中检索一行数组。
5
DBMS_OUTPUT.NEW_LINE;
放置一个行尾标记
6
DBMS_OUTPUT.PUT(item IN VARCHAR2);
在缓冲区中放置一个部分行。
7
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);
在缓冲区中放置一行。

7、强制类型转换

有些子程序对数据类型有要求，所以我们需要类型转换。

TO_NUMBER(char[,'format_model'])  字符转换到数字类型
TO_DATE(char[,'format_model'])  字符转换到日期类型
  格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。
TO_CHAR(date[,'format_model'[,nlsparams]])
  第二个参数可以省略，不指定格式，按系统默认格式输出。

 n int :=10;
dbms_output.put_line(to_char(n));

8、字符串

||和concat都是用来连接字符串的，||是级联运算符，concat是字符串函数。

declare 
  str1 varchar2(100):='hello';
  str2 varchar2(100):='world';
begin
  dbms_output.put_line(str1||str2);
   dbms_output.put_line(concat(str1,str2));
end;

要在字符串文字中包含单引号，需要在彼此之间键入两个单引号。 例如，
'this isn''t what it looks like'

9、数组

declare
TYPE strs IS VARRAY(5) of varchar2(20);---定义数组类型
o string(10) :='hello'; --突然发现还有string。。。
str strs;  ---变量
begin

  dbms_output.put_line(o);
  str:=strs('jim','tom','sqlly','siri');
  dbms_output.put_line(str.count);  --count是元素数量
 for i in 1..str.count loop
     dbms_output.put_line(str(i));
     end loop;
end;
/

10、函数/过程

这是过程：

--过程子程序
declare
procedure sayhi(c number) as  --在pl/sql中不需要使用create关键字声明
    begin
           dbms_output.put_line('ok hi'||c);

    end;
begin

     sayhi(10);
end; 

这是函数：

declare 
function hello return string is msg string(10);
    begin 
         dbms_output.put_line('hello');
    return 'world';
    end;

begin

dbms_output.put_line(hello());

end;

原本程序是需要使用create等关键字声明的，在plsql中declare内不使用。

过程和函数的区别在于过程没有返回值。

11、参数模式

函数和过程的参数都是有模式的，默认是IN.

IN
IN参数允许将值传递给子程序。它是一个只读参数。在子程序中，IN参数的作用如常数，它不能被赋值。可以将常量，文字，初始化的变量或表达式作为IN参数传递。也可以将其初始化为默认值; 然而，在这种情况下，从子程序调用中省略它。 它是参数传递的默认模式。参数通过引用传递。


2
OUT
OUT参数返回一个值给调用程序。在子程序中，OUT参数像变量一样。 可以更改其值并在分配该值后引用该值。实际参数必须是可变的，并且通过值传递。


3
IN OUT
IN OUT参数将初始值传递给子程序，并将更新的值返回给调用者。 它可以分配一个值，该值可以被读取。对应于IN OUT形式参数的实际参数必须是变量，而不是常量或表达式。正式参数必须分配一个值。实际参数(实参)通过值传递。

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 12; 
   b:= 35; 
   findMin(a, b, c); 
   dbms_output.put_line('两个数：12, 35中的最小值是 : ' || c); 
END; 

12、游标

declare 
 sid student.s_id%type; --%取出表的类型，匹配给变量
 sname student.s_name%type;
 ssex student.s_sex%type;

cursor c_stat is select * from student;

begin 
       open c_stat;
       fetch c_stat into sid,sname,ssex;
       close c_stat;

       dbms_output.put_line(sid||sname||ssex);


end;

SQL%NOTFOUND 是一个布尔值。与最近的sql语句（update,insert,delete,select）发生交互，当最近的一条sql语句没有涉及任何行的时候，则返回true。否则返回false。这样的语句在实际应用中，是非常有用的。例如要update一行数据时，如果没有找到，就可以作相应操作。如：

begin

  update table_name set salary = 10000 where emp_id = 10;

  if sql%notfound then

   insert into table_name (id,name,salary)values("","","") ;

  end if;

end;

当update emp_id为10的这行记录，如果update影响条数为0，则插入一条数据。

任何SQL游标属性将被访问为sql％attribute_name，如下例所示。

DECLARE  
   total_rows number(5); 
BEGIN 
   UPDATE hr.employees 
   SET salary = salary + 100; 
   IF sql%notfound THEN 
      dbms_output.put_line('没有找到客户信息~'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line('一共有：' || total_rows || ' 个客户的工资被更新！ '); 
   END IF;  
END; 

13、触发器

1、 触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。然后，触发器的触发条件其实在你定义的时候就已经设定好了。

2、触发器可以分为语句级触发器和行级触发器.

语句级的触发器可以在某些语句执行前或执行后被触发。

行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。

3、触发器是为了响应以下任何事件而被执行的 -

• 数据库操作(DML)语句(DELETE，INSERT或UPDATE)
• 数据库定义(DDL)语句(CREATE，ALTER或DROP)。
• 数据库操作(SERVERERROR，LOGON，LOGOFF，STARTUP或SHUTDOWN)。

语句级触发器：在student表上执行insert操作之后执行。

CREATE OR REPLACE  TRIGGER trigger_1 
after insert on student
declare 

begin
dbms_output.put_line('ok');
end;

--执行insert操作后会触发
insert into student values(7231,'jim','f');

行级触发器：：new 和：old代表新行数据和老行数据。

CREATE OR REPLACE  TRIGGER trigger_2 
 before update on student
for each row --行级触发器

begin
    if :new.s_id>:old.s_id then
       dbms_output.put_line('id增加');
    end if;
end;

update student set s_id=7235 where s_name='jim';

触发器能实现如下功能：

功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑

序列、触发器实现主键自增

使用触发器实现主键序列自增

--建立一个测试表
create table tab_user(
   id number(11) primary key,
   username varchar(50),
   password varchar(50)
);


--创建序列
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;

--创建触发器
CREATE OR REPLACE TRIGGER MY_TGR
  BEFORE INSERT ON TAB_USER
  FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
  NEXT_ID NUMBER;
BEGIN
  SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
  :NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;


--执行插入操作
insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;

select * from tab_user;

--create sequence
create sequence sequence_0 increment by 1 start with 1 maxvalue 999 nocycle;

--create trigger
create or replace trigger trigger_stu2 before insert on student
for each row

declare
    NewID student.s_id%type;--获取表中行类型

begin 
      select sequence_0.nextval into NewID from dual;
      :new.s_id:=NewID;

      if sql%found then  --sql%found
         dbms_output.put_line('触发器执行成功，主键自增');
      end if;
end;


--触发
insert into student(s_name,s_sex) values('rio','f');
select * from student;

14、记录

记录类型类似结构体、对象，主要有以下用法：

用户定义的记录

基于表的记录

基于游标的记录

用户定义的记录

declare 
   type Book is Record(
   title varchar2(50),
   author varchar2(50),
   book_id number(5)
   );

   book1 Book;

--记录即可作为过程/函数参数
PROCEDURE printbook (book Book) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 

begin

      book1.title:='bye';
      book1.author:='jackson';
      book1.book_id:='10029';
       Dbms_Output.put_line(book1.title);
      Dbms_Output.put_line(book1.author);
      Dbms_Output.put_line(book1.book_id);


      printbook(book1);
end;

基于表的记录

这里的stu_rec就是一个记录类型，由多种数据类型组合而成。

DECLARE 
   stu_rec student%rowtype; --取出一行的类型赋给记录
BEGIN 
   SELECT * into stu_rec 
   FROM student
   WHERE s_id = 7161;  
   dbms_output.put_line('ID: ' || stu_rec.s_id); 
   dbms_output.put_line('姓名: ' || stu_rec.s_name); 
   dbms_output.put_line('性别: ' || stu_rec.s_sex); 

END;

基于游标的记录

将游标的数据输入记录类型中：

declare 
         ---定义游标
         cursor stu_sor(sid number) is select * from student where s_id=sid;
         --定义记录
         stu_rec student%Rowtype;

begin
     open stu_sor(7163);
          loop
                 fetch stu_sor into stu_rec;
          exit when stu_sor%NotFound;
                 dbms_output.put_line(stu_rec.s_name);
                 dbms_output.put_line(stu_rec.s_sex);
          end loop;


end;         

面向对象

oracle9i之后开始支持面向对象，号称面向对象数据库。

Oracle的对象体系遵从面向对象思想的基本特征，许多概念同C++、JAVA中类似，具有继承，重载，多态等特征，但又有自己的特点。

--创建类，含有属性和方法签名
CREATE OR REPLACE TYPE rectangle AS OBJECT 
(
  length number, 
  width number, 
  member function enlarge( inc number) return rectangle, 
  member procedure display, 
  map member function measure return number 
); 
/


--方法体
CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN  
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   MAP MEMBER FUNCTION measure return number IS 
   BEGIN 
      return (sqrt(length*length + width*width)); 
   END measure; 
END; 
/


DECLARE 
   r1 rectangle; 
   r2 rectangle; 
   r3 rectangle; 
   inc_factor number := 5; 
BEGIN 
   r1 := rectangle(3, 4); 
   r2 := rectangle(5, 7); 
   r3 := r1.enlarge(inc_factor); 
   r3.display;  
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END;

分页操作

--分页查询
SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM student) A WHERE ROWNUM <= 10 ) WHERE RN >= 1;

--分页查询
SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM student) A ) WHERE RN BETWEEN 1 AND 10;

xml中的语法

 select * from (select t.*,rownum rownu from STUDENT t
        where rownum<=#{param1}*#{param2})tt
        where tt.rownu>(#{param1}-1)*#{param2}