存储过程相关学习"/>
oracle存储过程相关学习
/***************************内连接************************************//*内连接 inner join 的作用和 where 的写法，查询结果无区别
使用内连接进行多表关联查询时，返回的查询结果中只包含符合查询条件和连接条件的行，
内连接消除了与另一个表中的任何不匹配的行。
*/
select *
from tms_employee_info a
join tms_teacher_information b on a.empno = b.teacher_no
and a.series_id = b.series_id
and a.series_id = '02';
select *
from tms_employee_info a, tms_teacher_information b
where a.empno = b.teacher_no
and a.series_id = b.series_id
and a.series_id = '02';
/*************************外连接******************************/
/*外连接扩展了内连接的结果集，处理返回所有匹配的行外，
还会返回一部分或者全部不匹配的行，其主要取决于外连接的类型。
左外连接 left outer join/ left join
右外连接 right outer join/right join
全外连接 full outer join/full join
*/
select *
from tms_employee_info a
left join tms_teacher_information b on a.series_id = b.series_id
and a.empno = b.teacher_no
and a.series_id = '02';
/*左（右）外连接的查询结果中不仅包含了满足连接条件的数据行，而且还包含左（右）表中不满足接条件的数据行
全外连接返回满足连接条件的数据行，同时还会返回两个表中不满足的数据行
*/
/*************自然连接******natural join*********************************************/
/*自然连接，会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。
在自然连接中，用户不要需要明确指定进行连接的列，这个任务由Oracle系统自动完成*/
select concat('hello','world') information from dual;
select ini full outerse
select lower(t.um_id) low,upper(t.um_id) up from tmsdata.tms_employee_info t
/*replace(str，str1，str2)函数：str为原始字符串，str1为出现在str中的字符串，str2是把str中出现的所有str1替换为str2*/
select replace('Bad Luck Bad Girl','Bad','Good') from dual;
/*substr('str',i,j)函数截取字符串，i为截取开始位置，j为截取的长度*/
select substr('MessageBox',8,3) from dual;
/* ABS(NUM) 取数据的绝对值*/
select abs(-333) from dual;
select trunc(888.123,2) from dual;
select trunc(123.123,-2) from dual;
select round(3.1415926,1) from dual;
select power(2,3) from dual;
/************************loop循环***********************************/
declare
sum_i int := 0;
i int := 0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i=100;
end loop;
dbms_output.put_line('loop循环----前100个自然数的和是：'||sum_i);
end;
/**********************while循环*********************************/
declare
sum_i int :=0;
i int := 0;
begin
while i<=99
loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line('while循环----前100个自然数的和是：'||sum_i);
end;
/**********************for循环*********************************/
declare
sum_i int := 0;
begin
for i in reverse 1 .. 100 loop /*默认为递增，reverse标志为递减*/
if mod(i, 2) = 0 then
sum_i := sum_i + i;
end if;
dbms_output.put(' '||i);
end loop;
dbms_output.put_line('');
dbms_output.put_line('for循环---前100个偶数的和是；'||sum_i);
end;
/**********************cursor：游标*********************************/
/*************1、显式游标**************/
declare
cursor cur_emp(var_branchId in varchar2:='4') --声明一个游标叫cur_emp
is select t.empno, --此处的查询出的结果值，要与record记录类型中的成员变量保持数量一致
t.emp_name,
t.branch_id
from tms_employee_info t
where t.branch_id=var_branchId;
--以下是显示声明游标的方式
type record_emp is record --声明一个记录类型（record类型）
(
--定义当前记录的成员变量
var_empno tms_employee_info.empno%type,
var_empname tms_employee_info.emp_name%type,
var_branchId tms_employee_info.branch_id%type
);
emp_row record_emp; --声明一个record_emp类型的变量
begin
open cur_emp; --打开游标
fetch cur_emp into emp_row; --先让指针指向结果集中的第一行，并将值保存到emp_row中
while cur_emp%found loop
dbms_output.put_line('--人员编号--'||emp_row.var_empno||'--人员姓名--'||emp_row.var_empname||'--机构id--'||emp_row.var_branchId);
fetch cur_emp into emp_row; --让指针指向结果集中的下一行，并将值保存到emp_row中
end loop;
close cur_emp; --关闭游标
end;
/*************1、隐式游标**************/
--隐式游标主要处理数据操纵语句（如update、delete语句）的执行结果，也可以处理select语句的查询结果。
--由于隐式游标也有属性，当使用隐式游标时，需要在属性前面加----隐式游标的默认名称：sql
begin
update emp
set sal=sal*(1+0.2)
where job='SALESMEN';
if sql%notfound then --判断update语句有没有影响到任何一条数据
dbms_output.put_line('没有雇员需要上调工资');
else
dbms_output.put_line('有'||sql%rowcount||'雇员上调了20%');
end if;
end;
--标识符"sql"就是updtae语句在更新数据过程中所使用的 “隐式游标”
--当需要使用隐式游标的属性时，标识符 “sql”就必须显示地添加到属性名称之前。
--通过for语句循环 “隐式游标”
begin
for emp_record in (select *
from tms_employee_info t
where t.series_id = '02') loop
dbms_output.put_line('人员编码：' || emp_record.empno || ';人员姓名：' ||
emp_record.emp_name || ';机构：' ||
emp_record.branch_id);
end loop;
end;
--for语句循环 “显示游标”
declare
cursor cur_emp is
select * from tms_employee_info t where t.series_id='02';
begin
for emp_record in cur_emp
loop
dbms_output.put_line('人员编码：' || emp_record.empno || ';人员姓名：' ||
emp_record.emp_name || ';机构：' ||
emp_record.branch_id);
end loop;
end;
/*******************异常处理机制********************/
declare
var_empno number;
var_empname varchar2(50);
begin
select t.empno, t.emp_name
into var_empno, var_empname
from tms_employee_info t
where t.branch_id = '3';
if sql%found then
dbms_output.put_line('人员编码：' || var_empno || ';人员姓名：' ||var_empname);
end if;
exception
when too_many_rows then
dbms_output.put_line('返回记录超过一行');
when no_data_found then
dbms_output.put_line('没有数据');
end;
--自定义异常：错误编号异常、业务逻辑异常
--1.错误编号异常
declare
primary_iterant exception;
pragma exception_init(primary_iterant,-00001);/*-00001错误编号表示:违反唯一性约束*/
begin
--向表中插入一条违反唯一性约束的数据，来触发异常
insert into dept values (10,'ruanjian','shenzhen');
exception primary_iterant then
dbms_output.put_line('主键不允许重复！');
end;
--2.业务逻辑异常
--业务逻辑异常，需要有一个引发异常的机制，引发业务逻辑异常通常使用 raise 语句来实现
--当引发一个异常时，控制就会转到EXCEPTION异常处理部分执行异常处理语句
--业务逻辑异常首先在DECLARE部分使用EXCEPTION类型声明一个异常变量
declare
null_exception exception;
var_empno tms_employee_info.empno%type;
var_region_code tms_employee_info.region_code%type;
dept_row tms_employee_info%rowtype;
begin
select t.empno,t.region_code into var_empno, var_region_code from tms_employee_info t where t.empno='E00010119911';
if dept_row.empno is null then
raise null_exception;
end if;
exception
when null_exception then
dbms_output.put_line('值为null');
end;
/*******************存储过程********************/
--不检查存储过程是否存在，直接创建
create procedure pro_insertDept is
begin
insert into dept values(77,'dddd','33333');
commit;
dbms_output.put_line('insert complated!');
end pro_pro_insertDept
--如果存储过程名已经存在，则覆盖替换
create or replace procedure pro_insertDept is
declare
var_empno tms_employee_info.empno%type;
var_empname tms_employee_info.emp_name%type;
begin
select t.empno,t.emp_name into var_empno,var_empname from tms_employee_info t where t.series_id='02' and t.empno='E00010119911';
dbms_output.put_line('insert complated!');
end pro_insertDept
--删除存储过程
drop procedure pro_insertDept;
/*******************存储过程的参数********************/
--1.IN模式参数--in在参数名称之后
create or replace procedure insert_dept(
var_deptno in number,
var_ename in varchar2,
var_loc in varchar2
) is
begin
insert into dept values(var_deptno,var_ename,var_loc);
commit;
end insert_dept;
--1.1 向存储过程中传值的3中方式 ——————传值时，值传递时与顺序无关，但与值的个数有关
/***************************************************************************************/
--a、指定名称传递
--pro_name(parameter1 => value1, parameter2 => value2,.....)
--调用存储过程，并通过 “指定名称传值” 与顺序无关，与个数有关
begin
insert_dept(var_ename => 'xiaoxiao',var_loc => 'here',var_deptno => 40 ,);
end;
/***************************************************************************************/
--b、按位置传递
--使用时，参数值位置必须与存储过程中定义的参数顺序相同
begin
insert_dept(40,'xiaoxiao','here'); --保持参数顺序与存储过程中的顺序相同
end;
/***************************************************************************************/
--c、混合方式传递
--混合方式就是将两种方式结合在一起使用
/*注：在某个位置使用到 “指定名称传递” 方式传入参数值后，
其后面的参数值也要使用 “指定参数传递” ，因为 “指定参数传递” 方式有可能已经破坏了参数原始的定义顺序。*/
begin
insert_dept(40,var_ename => 'xiaoxiao',var_loc => 'here');
end;
/***************************************************************************************/
--2、OUT模式参数
/*是一种输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中使用，
关键字OUT位于参数名称之后。*/
create or replace procedure select_emp(
var_empno in varchar2, --定义IN模式变量，接收存储过程外的参数
var_empname out tms_employee_info.emp_name%type, --定义OUT模式参数，向存储过程外输出参数
var_umid out tms_employee_info.um_id%type --定义OUT模式参数，向存储过程外输出参数
) is
begin
select t.emp_name, t.um_id
into var_empname, var_umid
from tms_employee_info t
where t.series_id = '02'
and t.empno = var_empno;
exception
when no_data_found then
dbms_output.put_line('该部门编号不存在');
end select_emp;
--删除存储过程
drop procedure select_emp;
--调用存储过程，并且定义接收存储过程（OUT）传递出来的值的变量
/*OUT参数的值会在调用处返回，out参数被赋予的值，可以在存储过程外任意使用*/
declare
var_empname tms_employee_info.emp_name%type; --定义变量，接收存储过程，OUT模式输出的参数
var_umid tms_employee_info.um_id%type; --定义变量，接收存储过程，OUT模式输出的参数
begin
tmscde.select_emp('E00010119911',var_empname,var_umid);
dbms_output.put_line('人员姓名：'||var_empname||'UMID：'||var_umid);
end;
/***************************************************************************************/
--3、IN OUT模式
--IN OUT 参数可以兼顾其他两种参数的特点，在调用存储过程时，可以从外界向该类型的参数传入值，在执行完存储过程之后，
--可以将该参数的值返回给外界。
create or replace procedure pro_square(
num in out number, --计算它的平方或平方根，in out参数
flag in boolean, --in 参数
i int:=2
) is
begin
if flag then
num:=power(num,i);
else
num:=sqrt(num);
end if;
end;
declare
var_number number; --存储要进行运算的值（in）和运算后的结果（out） 传入和接收 in out参数num
var_temp number; --存储要进行运算的值
boo_flag boolean;
begin
var_temp:=3;
var_number:=var_temp;
boo_flag:=false;
pro_square(var_number,boo_flag);
if boo_flag then
dbms_output.put_line(var_temp||'平方'||var_number);
else
dbms_output.put_line(var_temp||'平方根'||var_number);
end if;
end;
drop procedure pro_square;
--IN参数的默认值
--声明IN参数的同时，可以给参数设置默认值，这样在调用存储过程时，如果没有向IN传入参数，
--则存储过程使用default值进行操作。
create or replace procedure select_emp(
var_empno in varchar2 default 'E00010119911',
var_ename in varchar2 default '郑新良',
var_serieid in varchar2 default '02'
) is
begin
select t.empno,t.emp_name into var_empno,var_ename
from tms_employee_info t
where t.empno = var_empno
and t.emp_name = var_ename
and t.series_id = var_serieid
while true loop
dbms_output.put_line(var_empno||var_ename);
end loop;
end;
drop procedure select_emp;
/**************************************函数*************************************************/
--函数可以接受零个或多个输入值，并且函数 “ 必须有返回值 ”
create or replace function fun_name(parameter1,parameter2,.....) return data_type is
inner_variable;
begin
plsql_sentence;
exception
dowith_sentence;
end fun_name;
--由于函数有返回值，所以在函数主体部分（即begin部分）必须使用return语句返回函数值，
--并且要求返回值的类型要与函数声明时的返回值类型（即data_type）相同。
create or replace function fun_select return number is
num_branchId number;
begin
select t.branch_id into num_branchId from tms_employee_info t where t.series_id='02' and t.empno='E00010119911';
return(num_branchId);
exception
when no_data_found then
dbms_output.put_line('没有数据');
return (0);
end;
drop function fun_select;
--调用函数
declare
var_sum number;
begin
var_sum:=fun_select;
dbms_output.put_line('eeeeee :'||var_sum);
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
/**************************************触发器*************************************************/
--能够引起触发器运行的操作被成为————触发事件
--如：DML(insert,update,delete)、DDL(create,alter,drop)
--引发数据库系统事件（系统启动，退出，产生异常错误）
--引发用户事件（登陆，退出数据库操作等）
create or replace trigger tri_name
[before | after | instead of] tri_event
on table_name | view_name | user_name | db_name
[ for each row][ when tri_condition]
begin
plsql_sencentes;
end tri_name;
--trigger:创建触发器关键字
--before/after/instead of ： 表示触发时机
--before：执行DML，DDL等操作前触发
--after： 执行DML,DDL等操作之后触发
--instead of：表示触发器为替代触发器
--on：表示操作 table_name | view_name | user_name | db_name
--for each row：指定触发器为行级触发器，指DML操作对每一行数据进行操作时都会引起触发器的执行。
--如果未指定，则默认为语句级触发器，无论数据操作影响多少行，触发器只会执行一次
--tri_condition:触发条件表达式，
--plsql_sencentes:PL/SQL语句，触发器功能实现的主体
--创建dept_log数据表，存储操作种类信息和操作日期
create dept_log(
operate_tag varchar2(10),
operate_date date
);
--创建一个触发器，该触发器在insert,update,delete发生时都可以触发，
create or replace trigger tri_dept
before insert or update or delete --创建触发器，当发生插入，修改，删除操作引发触发器执行
on dept
declare
var_tag varchar2(10); --创建一个变量，存储对dept表执行的操作类型
begin
if inserting then --判断操作类型，并赋值给变量
var_tag:='插入';
else if updating then
var_tag:='更新';
else if deleting then
var_tag:='删除';
end if;
insert into dept values(var_tag,sysdate); --向日表中插入对dept表的操作信息
end tri_dept;
--判断特定列是否被更新，
if updating(dname) then
do something about update dname
end if;
/**************************************程序包*************************************************/
--程序包的规范
--该“规范”用于规定在程序包中可以使用哪些变量、类型、游标和子程序（指各种命名的PL/SQL块），
--注：程序包一定要在“包主体”之前被创建
create [or replace] package pack_name is --程序包的 名称
[declare_variable]; --规范内声明的变量
[declare_type]; --规范内声明的类型
[declare_cursor]; --规范内定义的游标
[declare_function]; --规范内声明的函数，但仅定义参数和返回值类型，不包括函数体
[declare_procedure]; --规范内声明的存储过程，但仅定义参数，不包括存储过程主体。
end pack_name;
--创建一个程序包的“规范”
create or replace package pack_teacher is
function fun_avg_score(num_deptno number) return number;
procedure pro_regulate_sum(var_job varchar2,num_proportion number);
end pack_emp;
/**************************************程序包主体*************************************************/
--《程序包主体名称》必须与《规范的名称》“相同”，这样通过这个相同的名称Oracle就可以将 “规范” 和 “主体” 结合在一起组成程序包，
--并实现一起进行代码编译。
--创建程序包主体
create [or replace] package body pack_name is --程序包的名称要求与 “规范” 的名称相同
[inner_variable] --程序包主体内部变量
[cursor_body] --游标主体
[function_title] --从 “规范” 中引入函数头部声明
{
begin
fun_plsql; --PL/SQL 语句，这是函数主要实现功能的实现部分，从begin————>end是函数的body
[exception]
[dowith_sencentes;] --异常处理语句
end [fun_name] --函数名称
}
[procedure_title] --从 “规范” 中引入存储过程头部声明
{
begin
pro_plsql; --存储过程主要实现功能部分，从begin————>end是存储过程的body
[exception]
[dowith_sencentes;] --异常处理语句
end [pro_name;] --存储过程名
}
......
end [pack_name];
/*****************************************************************************************************/
--1---创建规范
create or replace package pack_teacher is
--"规范"中定义函数
function fun_avg_score(var_score_type varchar2,var_seriesid varchar2) return number;
--“规范”中定义存储过程
procedure pro_regulate_sum(var_seriesid varchar2,var_score_type varchar2,var_score_sum out number);
end pack_teacher;
drop package pack_teacher;
-2---创建程序主体
create or replace package body pack_teacher is
-- --引入“规范”中的函数
function fun_avg_score(var_score_type varchar2, var_seriesid varchar2)
return number is
num_avg_score number; --定义内部变量
begin
select avg(t.score) into num_avg_score
from tms_teacher_score_detail t
where t.score_type = var_score_type
and t.series_id = var_seriesid;
return(num_avg_score);
exception
when no_data_found then
dbms_output.put_line('没有数据');
return 0;
end fun_avg_score;
--存储过程
procedure pro_regulate_sum(var_seriesid in varchar2,
var_score_type in varchar2,
var_score_sum out number) is
begin
select sum(t.score) into var_score_sum
from tms_teacher_score_detail t
where t.series_id = var_seriesid
and t.score_type = var_score_type;
end pro_regulate_sum;
end pack_teacher;
drop package pack_teacher;
-3--创建匿名的PL/SQL块
declare
var_score_type tms_teacher_score_detail.score_type%type;
var_seriesid tms_teacher_score_detail.series_id%type;
var_score_avg number;
var_score_sum number;
begin
var_score_type := '02001';
var_seriesid := '02';
--调用函数
var_score_avg := pack_teacher.fun_avg_score(var_score_type, var_seriesid);
dbms_output.put_line('讲师的平均分数为： ' || var_score_avg);
--调用存储过程
pack_teacher.pro_regulate_sum(var_seriesid,
var_score_type,
var_score_sum);
dbms_output.put_line('讲师的分数总和为： ' || var_score_sum);
end;
/*****************************************************************************************************/
--数据表
/*create table 创建表
create index 创建数据表索引
create procedure 创建存储过程
create function 创建函数
create view 创建视图
create trigger 创建触发器
*/
--1.create
create table company_emp(
empno number(4) primary key not null, --非空约束，主键约束
ename varchar2(10 byte),
job varchar2(9 byte),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
--使用 constraint 关键字为约束，指定一个约束名
create table invoice(
invoice_in number constraint invoice_pk not null unique,--不为空，且唯一(约束)
vender_id number not null, --not null指定列值不接收null值
invoice_number varchar2(50) not null,
invoice_date date default sysdate, --default设置默认值
invoice_total number(9,2) not null,
payment_total number(9,2) default 0
)
--2.alter
--可以修改表、视图、索引、触发器的定义等
alter table company_emp add description varchar2(200) null;--向表中添加新列
alter table company_emp drop column description;--删除指定列
--3.drop
drop table company_emp;--移除company_emp表
/*****************************************************************************************************/
--创建表副本
/*create table 提供的 AS SELECT 语句，允许从一个现有的表中创建一个新的表，
创建的表可以包含原表的所有架构、字段属性、约束和数据记录，也可以仅架构完全相同，而不包含数据*/
create table <New_Table> as select {* | column(s)} from <Old_Table> [where <condition>];
--克隆一个数据表，包含原表所有信息
create table emp_copy as select * from tms_employee_info;
--创建一个架构而不包含任何数据
create table emp_copy as select * from tms_employee_info where 1=2;
/*****************************************************************************************************/
--创建外键约束
/* vendor表，可以将invoice表的vendor_id与vendor表的vendor_id字段进行"外键约束"，
也就是说，invoice表中的字段取值必须是vendor表中已经存在的供应商字段*/
create table vendor(
vendor_id number,--供应商ID
vendor_name varchar2(50) not null,--供应商名称
constraint vendors_pk primary key (vendor_id), --主键
constraint vendor_name_uq unique (vendor_name) --唯一性约束
)
CREATE TABLE invoice(
invoice_id number CONSTRAINT invoice_pk NOT NULL unique,--不为空，且唯一(约束)
vender_id number REFERENCES vendors (vendor_id), --not null指定列值不接收null值
invoice_number varchar2(50) not null,
invoice_date date DEFAULT SYSDATE, --default设置默认值
invoice_total number(9,2) NOT NULL,
payment_total number(9,2) DEFAULT 0,
CONSTRAINT invoiceid_vendor_pk PRIMARY KEY (invoice_id,vendor_id),
CONSTRAINT vendor_id_un UNIQUE (vendor_id)
)
/***************************************************************************************/
--修改表列 ALTER TABLE
ALTER TABLE [schema_name.] table_name{
ADD column_name data_type [column_attributes] |
DROP column column_name |
MODIFY column_name data_type [column_attributes]
}
--新增表列 add
ALTER TABLE invoice_check ADD invoice_name varchar2(20);
--新增列时，可以同时指定列的约束信息(如：NOT NULL、UNIQUE、CHECK)
ALTER TABLE invoice_check ADD invoice_name varchar2(20) CHECK (LENGTH (invoice_name)<=8);
--添加的同时检查该字段的长度不能大于8个字符
--修改表列 modify
ALTER TABLE invoice_check MODIFY invoice_name varchar2(100) check(length(invoice_name <=50));
--删除表列 drop column
/*ALTER TABLE 的 DROP COLUMN 语法可以将现有的列删除*/
ALTER TABLE invoice_check DROP COLUMN invoice_name;
--重命名表列 rename column
ALTER TABLE invoice_check RENAME COLUMN invoice_name TO invoice_name_short;
--修改约束
ALTER TABLE table_name {
ADD constraint constraint_name constraint_definition [DISABLLE] | --添加约束
DROP consistent constraint_name | --删除约束
enable [novalidate] constraint_name | --启用约束
Disable constraint_name --禁用约束
}
/***************************************************************************************/
--创建索引
create [unique] | [bitmap] index index_name
on table_name ([column1 [ASC|DESC],column2 [ASC | DESC],...] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLINE]
[NOSORT];
--索引
1.自动创建
2.单列索引
3.复合索引
4.B树索引
5.位图索引
6.函数索引
--修改索引
/*1.重命名索引*/
ALTER INDEX ... RENAME TO ...
ALTER INDEX index_name RENAME TO new_index_name;
/*2.合并和重建索引
1.合并索引：合并索引不改变索引的物理组织结构，只是简单的将B树叶子节点中的存储碎片和并在一起*/
ALTER INDEX COALESCE
ALTER INDEX idx_ename_empno COALESCE
/*2.重建索引：重新创建一个新的索引，删除原来的索引*/
ALTER INDEX index_name REBUILD;
3.分配和释放索引空间
--将索引扩容为200K
ALTER INDEX idx_ename_empno ALLOCATE EXTENT(SIZE 200k);
--释放索引多余的空间
ALTER INDEX idx_ename_empno DEALLOCATE UNUSED;
--也可以使用存储语句更改索引所在的表空间
ALTER INDEX idx_ename_empno REBUILD TABLESPACE users;
4.删除索引
DROP INDEX idx_ename_empno ;
--创建视图
create view or replace view_name as select * from table_name;
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]
--or replace 如果视图存在则重新创建
--force 创建视图，而不管基表是否存在
--noforce 只在基表存在的情况下创建视图（默认值）
--view_name 视图名称
--alias 为由视图查询选择的表达式指定名字（别名的个数必须与由视图选择的表达式的个数匹配）
--subquery 是一个完整的select语句，对于在select列表中的字段，可以使用别名
--with check option 指定在视图中只有可访问的航才能被插入和修改
--constraint 为check option 约束指定的名字
--with read only 确保在视图中没有DML操作被执行
--创建简单试图，不包含任何函数，表达式和分组数据的视图
CREATE OR REPLACE VIEW v_deptnoemp AS SELECT empno,empname,job,mgr,hirdate,sal,comm from emp where deptno=20;
--创建复杂视图，其中包含多个表的数据，以及函数或分组等
create or replace view v_view_name
as select d.deptno,sum(em) sumsal,e.empno,e.empname from dept d,emp e
where e.empno = d.deptno(+)
group by d.deptno;
select t.emp_name ||'的员工编号是：'||t.empno 员工信息 from tms_employee_info t where t.series_id='02'
select * from tms_employee_info t where t.date_hire = to_date('2002/12/12','YYYY/MM/DD');
select to_char(t.date_hire,'YYYY-MM-DD') 入职时间 from tms_employee_info t where t.series_id='02';
select t.empno, t.emp_name, t.src_type
from tms_employee_info t
where t.series_id = '02'
group by t.empno, t.emp_name, t.src_type;
/*********************************order by******************************************************/
--在select子句中可以使用 order by 子句排序。 ORDER BY 子句可以为一个表达式或一个列名作为排序的条件
--order by 子句必须是 SELECT 子句的最后一个子句。
SELECT expr from table_name [WHERE condition(s)] [ORDER BY {column,expr} [ASC | DESC]];
--ROWNUM伪劣
select rownum, t.empno, t.emp_name, t.src_type
from tms_employee_info t
where t.series_id = '02' and rownum<=10 order by t.date_hire;
select empno,ename,job,sal,comm,hiredate from emp where deptno=20 order by empno;
--使用函数
函数分类：
1.单行函数：仅对单个行进行计算，并且每行返回一个结果。单行函数包含字符、数字、日期及转换
2.多行函数：用来组成操作数据，每个行租给出一个结果
INITCAP 函数转换像是方式为首字母大写
CONCAT 函数连接 两个列的 内容
ROUND 函数四舍五入
--统计函数
SUM 求和
AVG 指定字段平均值
MIN 查找字段中的最小值
MAX 查找字段中的最大值
COUNT 计算字段中的值的数目
COUNT(*) 计算查询结果的记录数
select min(t.date_hire) 最早日期，max(t.date_hire) 最晚日期 from tms_employee_info t;
--NULL值处理
--NVL(expr1，expr2)函数，如果expr1的值或计算结果为null，则返回expr2.若expr2值也为null,则 NVL()函数返回NULL
SELECT MIN(MVL(comm,0)) 最低提成,MAX(NVL(comm，0)) 最高提成 FROM emp;
--分组函数 GROUP BY
SELECT column,group_function(column)
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
--使用 GROUP BY 子句时，
----除了作为分组函数参数的列不用包含在GROUP BY 子句中之外，任何在SELECT列表中的其他列都必须包括在组函数中。
---group by 子句来指定要分组的列
SELECT t.branch_id,sum(t.series_id) FROM tms_employee_info t GROUP BY t.branch_id
/* 在select列表中的任何列或表达式（非统计函数计算列）必须在 GROUP BY 子句中。
在 GROUP BY 子句中的列或表达式“不比一定”出现在SELECT 列表中*/
--外连接
--右外连接
--当（+）在等号 “左边”，返回table2中所有的数据
SELECT table1.cloumn,table2.cloumn FROM table1,table2 WHERE table1.cloumn(+) = table2.cloumn;
--左外连接
--当（+）在等号 “右边”，返回table1中所有的数据
SELECT table1.cloumn,table2.cloumn FROM table1,table2 WHERE table1.cloumn = table2.cloumn(+);
SELECT table1.cloumn,
table2.cloumn
FROM table1
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.cloumn_name = table2.cloumn_name)];
--交叉连接
--交叉连接是指用A表中的记录行与B表中的记录行数相乘得到的 “ 笛卡尔积 ”，--避免产生--笛卡尔积
--如果在进行连接查询是不指定任何连接条件，将产生交叉查询。
--自然连接
--如果两个表中有相同名字和数据类型的列，
--那么可以使用自然连接来自动匹配数据类型和列名，自然连接使用NATURAL JOIN 关键字
--子查询
--分类：
1.相关子查询：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
2.非相子查询：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
效率：
非相关子查询 > 相关子查询
select * from emp where sal > (select sal from emp where ename = 'simth');
--1.非相关子查询
1.单行单列子查询：标量子查询，通常与比较运算符 =、>、<、>=、<=、!=联合使用
2.多行单列子查询：返回单列多行数据时，不允许与比较运算符进行组合运算，必须使用特定的关键字 ANY 和 ALL
来将外层查询的单个值与子查询的多行进行比较运算。
3.多列子查询：返回多列数据的子查询，这种类型的子查询通常用在 UPDATE 语句中。
select empno,ename,sal,mgr,hiredate,sal from emp
where job = (select job from emp where ename = 'simth');
如果子查询返回多行，则不能使用大于、等于之类的单行比较符，需要使用多行比较符，
--IN 等于列表中的任何成员
--ANY 比较子查询返回的每个值
--ALL 比较子查询返回的全部值
--ANY
<ANY 小于最大值
>ANY 大于最小值
=ANY 等同于 IN
<ALL 小于最小值
>ALL 大于最大值
--2.相关子查询
1.相关子查询不像非相关子查询只执行一次，没当主查询执行一次时，会执行一次相关子查询。
2.相关子查询中的内层查询需要引用到一个或多个包含它的外层查询的列值，它在外部查询执行之后每次都需要执行。
select e.empno,e.ename,e.sal,e.deptno from emp e
where e.sal > (select avg(sal) from emp e1 where e.deptno = e1.deptno)
order by e.deptno;
--表集合操作
--联合运算 （ 没有重复值 ）
从两个查询返回的结果集去掉重复值后合并后的结果，使用 UNION 操作符
--全联合运算
返回查询结果的并集，包含重复值，使用 UNION ALL
--相交运算
返回多个查询结果中的相同的行，使用 INTERSECT
--相减运算
返回在第1个查询中存在，而在第2个查询中不存在的行，使用 MINUS
--联合与全联合运算
--联合：无重复数据
--全联合：包含重复数据
select_statment_1
UNION [ALL]
select_statment_2
UNION [ALL]
select_statment_3
UNION [ALL]
...
[ORDER BY order_by_list]
--使用原则：
1.被选择的列数和列的数据类型必须与所有用在查询中的 SELECT 语句一致。列的类型可以不相同
2.联合运算在做检查的时候，不忽略空值（NULL 值）
3.默认情况下，输出以 SELECT 子句的第一列的升序排序
4. IN 运算有比 UNION 运算高的优先级
5.联合运算在所有被选择的列上进行。
--使用 MERGE 合并表行
提供了在多个表之间合并数据的能力， 使用该语句可以有条件的 更新 和 插入数据 到数据库中，
对数据进行 插入 时，如果行存在，则执行 UPDATE 更新操作。如果是一个新的行，则执行 INSERT 语句进行插入。
MERGE INTO table_name table_alias
USING (table | view | sub_query) alias
ON (join condition)
WHERE MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
/***********************************记录类型****************************************************/
--定义记录类型
TYPE type_name IS RECORD(
field_declaration
[,field_declaration]
....
)
DECLARE
--定义记录类型
TYPE t_emp IS RECORD (
v_empno number,
v_ename varchar(20),
v_job varchar2(9),
v_mgr number(4),
v_hiredate date,
v_sal number(7,2),
v_comm number(7,2),
v_deptno number(2)
);
--使用 RECORD 类型,声明一个t_emp记录类型的变量 emp_info
emp_info t_emp;
begin
select * into emp_info from emp where empno := empno;
--向emp_copy表中插入记录类型的值
INSERT INTO emp_copy VALUES emp_info;
exception
when others
then
null;
end;
更多推荐
oracle存储过程相关学习
发布评论