Oracle基础

一，Oracle建表(create table)

语法

Create table 表名(
	列名 类型 约束 ,
    列名 类型 约束 ,
    列名 类型 约束 
)

常用的数据类型

VARCHAR2(length)

字符串类型：存储可变的长度的字符串，length:是字符串的最大长度，默认不填的时候是1，最大长度不超过4000。

CHAR(length)

字符串类型：存储固定长度的字符串，length:字符串的固定长度大小，默认是1，最大长度不超过2000。

NUMBER(a,b)

数值类型：存储数值类型，可以存整数，也可以存浮点型。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。例子：

number(6,2)，输入123.12345，实际存入：123.12 。

number(4,2)，输入12312.345，实际春如：提示不能存入，超过存储的指定的精度。

DATA

时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。例子：

内置函数sysdate获取的就是DATA类型

TIMESTAMP

时间类型：存储的不仅是日期和时间，还包含了时区。例子：

内置函数systimestamp获取的就是timestamp类型

CLOB

大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。

BLOB

二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象

例子

create table student(
       stu_name varchar2(5),
       stu_money  number(4,2),
       stu_date date,
       birthday timestamp
)
select * from student ;
insert into student values('admin',12.23,to_date('1990-12-10','yyyy-mm-dd'),sysdate)
-- 当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate from dual

表空间

Oracle表空间（tablespaces）是一个逻辑的概念，真正存放数据的是数据文件（data files）。一个Oracle数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是Oracle数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。

约束

非空（NOT NULL）约束、 
唯一（UNIQUE）约束、
主键（PRIMARY KEY）约束、
外键（FOREIGN KEY）约束、
条件（CHECK）约束

例子

create table student(
       stu_name varchar2(5) primary key,
       stu_money  number(4,2) not null,
       stu_date date,
       uuid number(18) unique key
       birthday timestamp
)

外键（FOREIGN KEY）约束
 
alter table student add constraint fk_stu_grade 
foreign key (gid) references grade(gid)

条件（CHECK）约束
alter table student
  add constraint ch_student_age
  check (age>0 and age<=50);
  
 删除约束
 ALTER TABLE student DROP CONSTRAINT SYS_C00115525;
 添加约束
 ALTER TABLE student MODIFY(MGR CONSTRAINT CK_EMP_MGR NOT NULL);
 约束重命名
 ALTER TABLE student RENAME CONSTRAINT SYS_C0059699 TO PK_T1
 禁用约束
 ALTER TABLE student DISABLE CONSTRAINT UK_EMP_COMM;
 
 添加主键约束
  alter table emp add constraint emp_id_pk primary key(id);
 添加唯一约束
  alter table emp add constraint emp_code_uq unique(code);
 添加唯一约束
  alter table emp modify ename not null;
  
 添加外键约束
 alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;
 
 on delete cascade  级联删除 ，删除主表，子表数据一并删除
 on delete set null  删除主表，子表对应的数据设置为空

二，插入数据

语法

insert into 表名（列名1,列名2,列名3.....）values(值1,值2,值3.....);

注意点

1,列名可以省略，当列名不填时，默认的是表中的所有列，列的顺序是按照建表的顺序进行排列的。
2,列名的数量和值的数量要一致，并且值的类型要和列的类型一一对应。
3,当表当中某些字段设置了某些约束的情况下，必须按照字段的约束来进行该值的插入

insert插入一个select的结果集

INSERT INTO 表名 SELECT 子句

三，删除数据

语法

delete from 表名 where 条件

注意点

where条件可以省略，如果省略则删除所有数据

truncate命令：重置表格

truncate table 表名

四，修改数据

语法

update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件

五，查询数据

select *|列 from 表名 
where 条件
group by 列 
having 条件 
order by 列 desc/asc

运算符

关系运算符：>、<、>=、<=、<>、（！=）
逻辑运算符：AND、OR、NOT；
范围运算符：BETWEEN、AND；
谓词范围：IN、NOT IN
空判断：IS NULL、IS NOT NULL；
模糊查询：LIKE
算术运算符：+ - * /
连接符：|| 

DISTINCT 关键字

Oracle DISTINCT关键字的作用可以对Oracle查询结果进行重复数据的消除

select distinct stu_name,stu_money,stu_date from student;

查询条件

 = 	表示精确查询
 in 表示范围查询 
select * from emp where deptno not in (10,20) 
 like 表示模糊查询
 select * from emp where ename like 'S%' 
 BETWEEN...AND 表示数字的范围
 select * from emp where sal between 1000 and 4000
 
 

连接查询

内联
	inner join 
	select * from emp inner join dept on emp.deptno=dept.deptno
	select * from emp,dept where emp.deptno=dept.deptno
	特点:将两张表有关联的数据全部查出，
左外联
	select * from emp left join dept on emp.deptno=dept.deptno
	特点：以左表emp为基准，查出关联右表的数据。
			emp的数据会全部查出来
右外联
	select * from emp right join dept on emp.deptno=dept.deptno
全联
	select * from emp full join dept on emp.deptno=dept.deptno
	两张表的数据会全部查询出来，如果没有关联的数据以null代替

select * from emp , dept 

Oracle伪列

ROWID

Oracle表中的每一行在数据文件中都有一个物理地址， ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。 ROWID 值可以唯一的标识表中的一行。通过Oracle select 查询出来的ROWID，返回的就是该行数据的物理地址。

select t.*,t.rowid from emp t ;

ROWNUM

ORACLE ROWNUM表示的Oracle查询结果集的顺序，ROWNUM为每个查询结果集的行标识一个行号，第一行返回1，第二行返回2，依次顺序递增。

ROWNUM 与 ROWID 不同， ROWID 是插入记录时生成， ROWNUM 是查询数据时生成。ROWID 标识的是行的物理地址。 ROWNUM 标识的是查询结果中的行的次序。

select t.*,t.rowid,rownum from emp t ;

例如查询前3条数据

select t.*,t.rowid from emp t where rownum<=3

函数

有系统函数和自定义函数

字符函数

函数	说明	案例	结果
ASCII(X)	求字符X的ASCII码	select ASCII(‘A’) FROM DUAL;	65
CHR(X)	求ASCII码对应的字符	select CHR(65) FROM DUAL;	‘A’
LENGTH(X)	求字符串X的长度	select LENGTH(‘ORACLE技术圈’)from DUAL;	9
CONCATA(X,Y)	返回连接两个字符串X和Y的结果	select CONCAT(‘ORACLE’,‘技术圈’) from DUAL;	ORACLE技术圈
INSTR(X,Y[,START])	查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始	SELECT INSTR(‘ORACLE技术圈’,‘技术’) FROM DUAL;	7
LOWER(X)	把字符串X中大写字母转换为小写	SELECT LOWER(‘ORACLE技术圈’) FROM DUAL;	oracle技术圈
UPPER(X)	把字符串X中小写字母转换为大写	SELECT UPPER(‘Oracle技术圈’) FROM DUAL;	ORACLE技术圈
INITCAP(X)	把字符串X中所有单词首字母转换为大写，其余小写。	SELECT INITCAP('ORACLE is good ') FROM DUAL;	Oracle Is Good
LTRIM(X[,Y])	去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格	SELECT LTRIM(’–ORACLE技术圈’,’-’) FROM DUAL;	ORACLE技术圈
RTRIM(X[,Y])	去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格	SELECT RTRIM(‘ORACLE技术圈–’,’-’) FROM DUAL;	ORACLE技术圈
TRIM(X[,Y])	去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格	SELECT TRIM(’–ORACLE技术圈–’,’-’) FROM DUAL;	ORACLE技术圈
REPLACE(X,old,new）	查找字符串X中old字符，并利用new字符替换	SELECT REPLACE(‘ORACLE技术圈’,‘技术圈’,‘技术交流’) FROM DUAL;	ORACLE技术交流
SUBSTR(X,start[,length])	截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾	SELECT SUBSTR(‘ORACLE技术圈’,1,6) FROM DUAL;	ORACLE
RPAD(X,length[,Y])	对字符串X进行右补字符Y使字符串长度达到length长度	SELECT RPAD(‘ORACLE’,9,’-’) from DUAL;	ORACLE—
LPAD(X,length[,Y])	对字符串X进行左补字符Y使字符串长度达到length长度	SELECT LPAD(‘ORACLE’,9,’-’) from DUAL;	—ORACLE

日期函数

1,
SYSDATE函数：该函数没有参数，可以得到系统的当前时间。
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
2,
select systimestamp from dual;
3,数据库时区函数：
select dbtimezone from dual;
4,给日期加上指定的月份函数
ADD_MONTHS（r,n）
select ADD_MONTHS(to_date('2020-10-10','yyyy-mm-dd'),1) from dual
select add_months(sysdate,1) from dual 
5,月份最后一天函数:
LAST_DAY(r)
select last_day(sysdate) from dual
select last_day(to_date('2021-2-10','yyyy-mm-dd')) from dual
6,指定日期后一周的日期函数:
NEXT_DAY(r,c)
 select next_day(to_date('2018-11-12','yyyy-mm-dd'),'星期四') from dual;  
 select next_day(sysdate,'星期四') from dual;
7,回指定日期中特定部分的函数：
EXTRACT（time）
select  extract( year from timestamp '2018-11-12 15:36:01') as year,
        extract( month from timestamp '2018-11-12 15:36:01') as month,        
        extract( day from timestamp '2018-11-12 15:36:01') as day,  
        extract( minute from timestamp '2018-11-12 15:36:01') as minute,
        extract( second from timestamp '2018-11-12 15:36:01') as second
 from dual;
8,返回两个日期间的月份数：
MONTHS_BETWEEN(r1,r2)
select   MONTHS_BETWEEN(
to_date('2020-10-10','yyyy-mm-dd'),
to_date('2021-11-10','yyyy-mm-dd')) 
from dual 
9,日期截取函数
ROUND（r[,f]）

数值函数

函数	解释	案例	结果
ABS(X)	求数值X的绝对值	select abs(-9) from dual;	9
COS(X)	求数值X的余弦	select cos(1) from dual;	0.54030230586814
ACOS(X)	求数值X的反余弦	select acos(1) from dual;	0
CEIL(X)	求大于或等于数值X的最小值	select ceil(7.8) from dual;	8
FLOOR(X)	求小于或等于数值X的最大值	select floor(7.8) from dual;	7
log(x,y)	求x为底y的对数	select log(2,8) from dual;	3
mod(x,y)	求x除以y的余数	select mod(13,4) from dual;	1
power(x,y)	求x的y次幂	select power(2,4) from dual;	16
sqrt(x)	求x的平方根	select sqrt(16) from dual;	4
round(x[,y])	求数值x在y位进行四舍五入。y不填时，默认为y=0;当y>0时，是四舍五入到小数点右边y位。当y<0时，是四舍五入到小数点左边|y|位。	select round(7.816, 2), round(7.816), round(76.816, -1) from dual;	7.82 / 8 / 80
trunc(x[,y])	求数值x在y位进行直接截取y不填时，默认为y=0;当y>0时，是截取到小数点右边y位。当y<0时，是截取到小数点左边|y|位。	select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual;	7.81 / 7

转换函数

函数	解释	案例	结果
asciistr(x)	把字符串x转换为数据库字符集对应的ASCII值	select asciistr(‘Oracle技术圈’) from dual;	Oracle\6280\672F\5708
bin_to_num(x1[x2…])	把二进制数值转换为对应的十进制数值	select bin_to_num(1,0,0) from dual;	4
cast(x as type)	数据类型转换函数，该函数可以把x转换为对应的type的数据类型，基本上用于数字，字符，时间类型安装数据库规则进行互转，	select cast(‘123’ as number) num,cast(123 as varchar2(3)) as ch,cast(to_date(‘20181112’,‘yyyymmdd’) as varchar2(12)) as time from dual;	123/‘123’/12-11月-18(三列值，用"/"隔开)
convert(x,d_chset[,r_chset])	字符串在字符集间的转换函数，对字符串x按照原字符集r_chset转换为目标字符集d_chset，当r_chset不填时，默认选择数据库服务器字符集。	select CONVERT(‘oracle技术圈’,‘US7ASCII’,‘ZHS16GBK’) from dual;	oracle???
to_char(x[,f])	把字符串或时间类型x按格式f进行格式化转换为字符串。	select to_char(123.46,‘999.9’) from dual; select to_char(sysdate,‘yyyy-mm-dd’) from dual;	123.52018-11-13
to_date(x[,f])	可以把字符串x按照格式f进行格式化转换为时间类型结果。	select to_date(‘2018-11-13’,‘yyyy-mm-dd’) from dual;	2018/11/13
to_number(x[,f])	可以把字符串x按照格式f进行格式化转换为数值类型结果。	select to_number(‘123.74’,‘999.99’) from dual	123.74

聚合函数

max()
min()
sum()
count()
avg()

Oracle序列

Oracle序列Sequence是用来生成连续的整数数据的对象，它经常用来作为业务中无规则的主键。Oracle序列可以是升序列也可以是降序列。

语法

CREATE SEQUENCE sequence_name
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[START WITH num]
[INCREMENT BY increment]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析

1、MAXVALUE/MINVALUE：指定的是序列的最大值和最小值。

2、NOMAXVALUE/NOMINVALUE：不指定序列的最大值和最小值，使用系统的默认选项，升序的最大值：10^27次方，降序是-1。升序最小值：1，降序的最小值：-10^26。

3、START WITH：指定从某一个整数开始，升序默认是1，降序默认是-1。

4、CYCLE | NOCYCLE:表示序列达到最大值或者最小值的时候，是否重新开始。CYCLE：重新开始，NOCYCLE：不重新开始。

5、CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率 。

6、NOCACHE：不预先在内存中生成序列号。

例子

--删除序列
drop sequence seq_user
--创建序列
create sequence seq_user  
MAXVALUE 1000
START WITH 1 
INCREMENT BY 1
noCYCLE 

使用序列

Select seqEmp.nextval  from dual; 

Select seqEmp.currval  from dual;

insert into student(u_id,u_name) values(seq_user.nextval,'张三')

触发器

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。

因此触发器不需要人为的去调用，也不能调用。

然后，触发器的触发条件其实在你定义的时候就已经设定好了。

语法

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

例子：使用触发器实现序号自增

--创建表格
create table student (
       u_id number(10) primary key ,
       u_name varchar2(10)
)
--创建序列 
create sequence seq_user1  
nomaxvalue
START WITH 1 
INCREMENT BY 1
noCYCLE 
cache 10
--创建触发器
create trigger usr_tgr
before insert on student 
for each row--对表的每一行触发器执行一次
declare
 next_id number;  
begin
  select seq_user.NEXTVAL INTO next_id FROM DUAL;
  :new.u_id := next_id;
end;

insert into student(u_name) values('李四')

分页查询

select * from (
	select emp.*,rownum rn from emp where rownum<=(4-1)*3+3
) where rn>(4-1)*3

三、row_number()与rownum的区别

使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而row_number()在包含排序从句后是先排序再计算行号码。

同义词

同义词是现有对象的一个别名
	简化SQL语句
	隐藏对象的名称和所有者
	提供对对象的公共访问

--创建同义词
create synonym stu1 for syh.stu;
select * from syh.stu
select * from stu1

索引

最终目的：提高数据的查询效率

PL-SQL

语法

语法
DECLARE --定义部分 ，全局变量  declare
   <declarations section> 
BEGIN 
   --可执行命令部分
   <executable command(s)>
EXCEPTION 
   --异常部分
   <exception handling> 
END;

变量初始值赋值

declare 
   v_name varchar2(10) := 'Hellow';
   v_age number(2) default 18 ;
   v_id student.u_id%type ;
   v_name1 student.u_name%type;
begin
   select u_id,u_name into v_id,v_name1 from student where u_name='小花';
   dbms_output.put_line(v_name||v_age||v_id||v_name1);
end;

条件语句

declare 
   v_age number(2) := 10;
begin
   if v_age>10 
     then dbms_output.put_line('v_age>10');
   else
     dbms_output.put_line('v_age<=10');
   end if;  -- 注意
end;

declare 
   v_age number(2) := 10;
begin
   if v_age>10 then 
     dbms_output.put_line('v_age>10');
   elsif v_age = 10 then 		
     dbms_output.put_line('v_age=10');
   else
     dbms_output.put_line('v_age>10');
   end if;
end;

Case语句

declare 
   v_score number(2) := 80;
begin
  case 
    when v_score >=60 and v_score<70 then
       dbms_output.put_line('一般');
    when v_score >=70 and v_score<80 then
       dbms_output.put_line('良好');
    when v_score >=80 and v_score<100 then
       dbms_output.put_line('优秀');
  end case;
end;

--- 只能等值判断 switch
declare 
   v_score number(2) := 80;
begin
  case v_score
    when 70 then 
          dbms_output.put_line('一般');
    when 80 then 
          dbms_output.put_line('良好');
    when 90 then 
          dbms_output.put_line('优秀');
  end case;
end;

循环

Loop循环

declare 
    v_num int:=1;
    v_sum int:=0;
begin
    loop    
      v_sum:=v_sum+v_num;
      v_num:=v_num+1;
      if v_num>100 then
        exit;	
      end if;
    end loop; 
    dbms_output.put_line(v_sum);
end;

while循环

declare 
   v_num int :=1;
   v_sum int :=0;
begin 
   while(v_num<=100) loop
      v_sum:=v_sum+v_num;
      v_num:=v_num+1;               
   end loop;
   dbms_output.put_line('1-100之和是 '||to_char(v_sum));
end;

for循环

declare 
   v_sum int :=0;
begin
  for i in 1..100 loop
    if i=50 then
      --continue; 跳出本次循环
      exit; -- 退出循环 ，相当于break;
    end if;
    v_sum := v_sum+i;
  end loop;
  dbms_output.put_line(v_sum);
end;

%type 和 ％rowtype

%type:
为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致.
％rowtype
如果一个表有较多的列，使用%ROWTYPE来定义一个表示表中一行记录的变量

举例说明

--组织机构结构表
CREATE TABLE SF_ORG
(
ORG_ID INT NOT NULL, --组织机构主键ID
ORG_NAME VARCHAR2(50),--组织机构名称
PARENT_ID INT--组织机构的父级
)
 
--一级组织机构
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);
 
--二级部门
 
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);

DECLARE 
  V_ORG_NAME SF_ORG.ORG_NAME%TYPE; --与ORG_NAME类型相同
  V_PARENT_ID SF_ORG.PARENT_ID%TYPE;--与PARENT_ID类型相同
BEGIN
  SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID
  FROM SF_ORG SO
  WHERE SO.ORG_ID=&ORG_ID;
  DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_NAME);
  DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_PARENT_ID));
end;

declare 
 v_org_rec sf_org%rowtype;--定义一个变量，变量的类型是一条记录，
begin
 select * into v_org_rec from sf_org 
 where org_id = &org_id;
 dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);
end;

Oracle 接受用户输入的值

-- Oracle 接受 用户输入的值
declare
   v_num number(2) := #  --  &num  用户输入的值
begin
  dbms_output.put_line(v_num);
end;  

异常处理

PL/SQL的预定义异常

ACCESS_INTO_NULL	视图给一个没有初始化的对象赋值
DUP_VAL_ON_INDEX	重复的值存储在使用唯一索引的数据库列中
INVALID_NUMBER	视图将一个非有效的字符串转换成数字
LOGIN_DENIED	使用无效的用户名和口令登录Oracle
NO_DATA_FOUND	语句无返回数据
TOO_MANY_ROWS	在执行SELECT INTO语句后返回多行时出现

例子

--- 异常处理
declare 
    v_num number ;
begin
    v_num := 1/0;
    select empno into v_num from emp where empno=100;
exception
    when ZERO_DIVIDE then 
      dbms_output.put_line('异常');
    when NO_DATA_FOUND then 
      dbms_output.put_line('没有数据');
    when others then
      dbms_output.put_line('未知异常');
end;

捕获抛出的逻辑异常

declare
    v_age number;

begin
    v_age:=&age;
    if(v_age<0 or v_age>130) then
      raise_application_error(-88888,'年龄非法！');
    end if;
    Dbms_Output.put_line('您的年龄：！'||v_age); 
exception
    when others then
         Dbms_Output.put_line('出现异常！'); 
end;

捕获自定义异常

declare
    v_age number;
    ex_myage exception; --自定义异常
    pragma exception_init(ex_myage,-20000); --注册自定义异常，编号范围20000-20300 默认前缀都是 ORA-
begin
    v_age:='&age';
    if(v_age<0 or v_age>130) then
      raise_application_error(-20000,'年龄非法！');
    end if;
    Dbms_Output.put_line('您的年龄：！'||v_age); 
exception
    when ex_myage then
         Dbms_Output.put_line(SQLERRM); --sqlerrm 获取异常信息
end;

游标 CURSOR

用来处理使用select语句从数据库中检索到的多行记录的工具

用来存储多条查询数据的一种数据结构（'结果集'），
  	它有一个 '指针'，从上往下移动（'fetch'），从而能够 '遍历每条记录'

使用游标

declare
    --1，定义游标
    cursor cursor_org is 
           select * from sf_org ;
    -- 定义变量
    v_org_rec cursor_org%rowtype;
begin
    --2，打开游标
    open cursor_org;
    --3，获取游标的数据 一次获取一行
    fetch cursor_org into v_org_rec;        
    dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);
    --4，关闭游标
    close cursor_org;
end;

循环获取

declare
    --1，定义游标
    cursor cursor_org is 
           select * from sf_org ;
    -- 定义变量
    v_org_rec cursor_org%rowtype;
begin
    --2，打开游标
    open cursor_org;
    --3，获取游标的数据 一次获取一行
    loop
       fetch cursor_org into v_org_rec;
       --当游标没有记录 退出循环
       exit when cursor_org%notfound;
       dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);     
    
    end loop;
    dbms_output.put_line('总记录：'||cursor_org%rowcount);
    --4，关闭游标
    close cursor_org;
end;

游标的属性

%found 	用于检验游标是否成功，通常在FETCH语句前使用，当游标按照条件查询出一条记录时，返回true 
%isopen 	判断游标是否处于打开状态，视图打开一个已经打开或者已经关闭的游标，将会出现错误 
%notfound 	与%found的作用相反，当按照条件无法查询到记录时，返回true 
%rowcount 	循环执行游标读取数据时，返回检索出的记录数据的行数 

游标的分类

隐式游标 dml

1. 自动创建
   (1) DML
   (2) select into
   
2. 自动管理
   (1) 无需人为干预（自动声明、打开、关闭）
   (2) 默认游标名：'SQL'

例子

declare
  v_count number;
begin
  insert into stu_info (id, name, sex) values (3, '瑶瑶', '女');
  if sql%found then
    dbms_output.put_line('插入成功!');
  end if;

  update stu_info t set t.name = '悠悠' where t.id = 3;
  if sql%found then
    dbms_output.put_line('更新成功!');
  end if;

  delete from stu_info t where t.id = 3;
  if sql%found then
    dbms_output.put_line('删除成功!');
  end if;

  select count(1) into v_count from stu_info t;
  if sql%found then
    dbms_output.put_line('总记录为： ' || v_count);
  end if;

  if sql%isopen then
    dbms_output.put_line('不可能的，永远不可能走这一步');
  else
    dbms_output.put_line('系统已自动关闭游标');
  end if;
end;

显式游标 cursor 带参数

declare
    --定义游标
    cursor cursor_org(id_ sf_org.org_id%type) 
           is select * from sf_org where org_id=id_;
    v_org_rec sf_org%rowtype;
begin
    --打开游标 带参数 将1的值 赋值id_
    open cursor_org(2);
    
    fetch cursor_org into v_org_rec ;
    dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);     
    
    close cursor_org;
end;

动态游标

自定义类型 ref cursor

declare 
      -- 定义类型，该类型是一个动态游标
      type cousor_org_type is ref cursor;
      cousor_org cousor_org_type;
      v_org_rec sf_org%rowtype;
      sql_str varchar2(100) := 'select * from sf_org';
begin
      -- 打开游标
      open cousor_org for sql_str;
      
      loop 
           fetch cousor_org into v_org_rec;        
           exit when cousor_org%notfound;
           dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);     
      end loop;
      close cousor_org;
end;

--- 动态游标 有return

declare
    type cursor_org_type is ref cursor return sf_org%rowtype;
    cousor_org cursor_org_type;
    v_org_rec sf_org%rowtype;
begin
  
    open cousor_org for select * from sf_org;
    
    fetch cousor_org into v_org_rec ;
    dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);     
    
    
    close cousor_org;
end;

系统类型 sys_refcursor

declare
    
    --type cursor_org_type is ref cursor return sf_org%rowtype;
    cousor_org sys_refcursor;
    v_org_rec sf_org%rowtype;
begin
  
    open cousor_org for select * from sf_org;
    loop
        fetch cousor_org into v_org_rec ;
    	exit when cousor_org%notfound;
    	dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);
    end loop;
    close cousor_org;
end;

使用绑定变量

-- 使用绑定变量
declare
    cousor_org sys_refcursor;
    v_org_sql varchar2(100);
    v_org_rec sf_org%rowtype;
begin
    v_org_sql := 'select * from sf_org where org_id=:id_';

    open cousor_org for v_org_sql 
         using 3;
    fetch cousor_org into v_org_rec ;
    dbms_output.put_line(v_org_rec.org_id||v_org_rec.org_name||v_org_rec.parent_id);     
    
    close cousor_org;
end;

记录 record

declare 
	-- 独立定义记录，可以定义参数  %rowtype也是记录，但是这个必须是和表的字段一致
	type record_sf_org is record(
          v_org_id sf_org.org_id%type,
          v_org_name sf_org.org_name%type
     );
     sf_org_row record_sf_org;
begin
    select org_id,org_name into sf_org_row from sf_org 
    where org_id = 1;
    
    dbms_output.put_line(sf_org_row.v_org_id||sf_org_row.v_org_name);
end;

子程序，存储过程

一.什么是存储过程

存储过程，百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

简单的说就是专门干一件事一段sql语句。

可以由数据库自己去调用，也可以由java程序去调用。

在oracle数据库中存储过程是procedure。

二.为什么要写存储过程

1,效率高
  存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本  上），都要先分析编译才会执行。所以相对而言存储过程效率更高。
  
2.降低网络流量
存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高
存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高
当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高
完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

使用存储过程

语法

无参存储过程

--create or replace 如果有就替换没有就创建
--as / is 任选其中一个
create or replace procedure pro_name as / is
--声明部分
begin
  --可执行部分
  exception
    --异常部分
end; 

带参数的存储过程

create or replace procedure pro_name(u_id number(10),u_name emp.uName%type) 
as
	--声明
begin
	--执行
	exception
		--异常
end;

例子

create or replace procedure pro_01 as 

begin
  dbms_output.put_line('Hello Procedure');
end;
-- 调用存储过程
declare
begin
  pro_01();
end;
-- 调用存储过程
call pro_01();
exec pro_01();  --是sqlplus命令

带参数的例子

-- 编写存储过程，获得某个员工的姓名，工资，职位
create or replace procedure pro_emp(
       empid in emp.empno%type,
       empname out emp.ename%type,
       empsal out emp.sal%type,
       empjob out emp.job%type
) 
as
begin
  select ename,sal,job into empname,empsal,empjob 
  from emp where empno = empid;
end;

参数形式

存储过程传递参数有哪三种模式？
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值 
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值