一、视图
1、创建只读视图
create or replace view view_name
as
select column_name,..... from table_name1,table_name2,........
where 条件
with read only /with check option
注:with read only子句不能与order by 子句同时存在,with check option为视图创建了一个约束,约束是对表或视图中的数据进行限制,在增加或修改数据时,要符合where后面的条件。
2、查看视图
select view_name,text from user_views where view_name='视图名'; --text表示视图的定义
使用视图进行插入时,插入的数据要同时满足基表和视图的相关约束
3、视图的修改
a,先删除视图,再重新创建
b,在创建视图的create 语句中使用or replace选项
4、视图的删除
drop view 视图名 [cascade constraints] --cascade constraints 删除视图时,同时删除约束
二、PL/SQL
PL/SQL是一种结构化编程语言,程序基本单元是块,主要的块形式有函数、过程和匿名块。一个PL/SQL块由三个部分(声明部分,执行部分,异常处理部分)组成。其中,只有执行部分是必须的,其他两个部分是可选的,该结构的最后,必须有分号。
变量声明部分用来定义变量、类型、游标、子程序、触发器、异常等,被声明的元素在本块范围内有效。
1、PL/SQL数据类型
数字类型:number,pls_integer,binary_integer,
字符类型:varchar2,char,nchar,nvacher2
日期类型:date,timestamp
布尔类型:boolean(true,false,null)
type定义的数据类型:type 数据类型名 is 数据类型
2、PL/SQL运算符
算术运算符:+(加,正),-(减,负),* ,/
比较运算符:>,<,=,>=,<=,<>(不等于)
逻辑运算符:and , not , or , between, like等
赋值运算符: :=
字符串连接: ||
一元运算符
3、PL/SQL记录类型
type 类型名 is record (
字段1 定义,
字段2 定义,
字段3 定义,
.............
leavedate hiredate%type default sysdate;
cus customer%rowtype;
4、PL/SQL集合类型
type 类型名 is table of 类型
记录类型变量中包含若干类型不同的数据,集合类型变量中包含多个相同类型的元素,集合类型类似于面向对象技术中的类,除了可以用构造函数,还有其他方法。
eg: type intbegins is table of integer
begins1 intbegins = intbegins(12,28,20);
begins2 intbegins =intbegins();
三、oracle条件控制语句
1、if else 判断
if {条件表达式} then
{语句 1 }
[elsif{条件表达式2} then
{语句 2:}]
[else
{语句3:}]
end if
2、if .... then结构
eg: declare cus_sta number;
begin
select count(*) into cus_sta from customernew where state='gold';
if cus_sta >0 then
dbms_output.put_line('本公司有拥有贵宾会员卡的顾客');
end if;
end;
3、if else 结构
eg : declare cus_sta number;
begin
select count(*) into cus_sta from customernew where state='gold';
if cus_sta >0 then
dbms_output.put_line('本公司有拥有贵宾会员卡的顾客');
else
dbms_output.put_line('本公司没有拥有贵宾会员卡的顾客');
end if;
end;
4、case when分支a、简单case语句
case 变量/表达式
when 值1
流程语句1
when 值2
流程语句2
....................else
默认流程语句
end case;
b、搜索式case语句
case
when 布尔表达式1
流程语句1
when 布尔表达式2
流程语句2else
默认流程语句
end case;
5、无条件loop循环,while循环,for循环
declare cur_id number :=100;
cur_name varchar2(20);
begin
--loop
/* 方式1 loop循环
if cur_id >= 112 then
exit;
end if; */
/* 方式 2
exit when cur_id >=112; */
/*方式3 whle循环 --前面的loop就不要了 */
while cur_id <112 loop
cur_id := cur_id+1;
select cust_first_name into cur_name from customernew where customer_id=cur_id;
dbms_output.put_line(cur_id||'号员工姓名是:'||cur_name);
end loop;
end;
declare cur_name varchar2(20);
begin
for cur_id in 101..112 loop --for循环自行声明变量cur_id,在变量声明列表中,无须出现对cur_id的声明
select cust_first_name into cur_name from customernew where customer_id=cur_id;
dbms_output.put_line(cur_id||'号员工姓名是:'||cur_name);
end loop;
end;
四、游标
游标在关系数据库中用来操作查询出来的数据集,游标基于数据库的表返回结果集,它指示结果集中的当前位置。
1、游标的分类
静态游标:
显示游标:是指在使用前必须有明确的游标声明和定义,这样的游标定义会关联数据查询语句,通常会返回一行或是多行
隐示游标:和显式游标不同,它被PL/SQL自动管理,也被称为PL/SQL游标,由oracle自动管理。该游标用户无法控制。
动态游标:动态关联结果集的临时对象、
强类型动态游标:在使用游标之前有,虽未指定游标的查询定义,但是游标的返回类型(必须是记录类型的)已经确定。 type 游标名 is ref cursor return 记录类型
弱类型动态游标:非受限游标,可以为任何查询打开。 type 游标名 is ref cursor
2、显示游标
显示游标的使用顺序可以明确在分成声明游标、打开游标(open 游标名)、读取数据(fetch语句)、关闭游标(close 游标名)4个步骤
eg:
declare
cursor cus_cur2 is --声明一个游标
select customer_id,cust_first_name from customernew where customer_id=113;
cus cus_cur2%rowtype; --声明变量
begin
open cus_cur2; --打开游标
fetch cus_cur2 into cus; --fetch语句
dbms_output.put_line('编号:'||cus.customer_id||'姓名:'||cus.cus_first_name);
close cus_curs; --关闭游标
end;
使用bulk collect和for语句的游标,bulk collect into语句可以批量提取数据
带参数游标:
declare
cursor cus_cur(min_credit customernew.credit_limit%type) --声明带参数游标
is
select * from customernew where credit_limit >= min_credit order by customer_id;
cuscre customernew %rowtype;
begin
open cus_cur(37000); --为游标参数传值
fetch cus_cur into cuscre;
while cus_cur%found loop
dbms_output.put_line('编号:'||cuscre.customer_id||'赊销限额:'||cuscre.credit_limit);
fetch cus_cur into cuscre;
end loop;
close cus_cur;
end;
显示游标的属性
%isopen:判断游标是否打开,打开则返回true,否则返回false
%found:判断当前游标最近一次fetch是否取到数据,若是,则返回true,否则返回false
%notfound:与%found相反
%rowcount:判断自游标打开以来,到目前为止,用fetch命令获取的行数,并非所有的总记录数。
3、隐式游标
如果在PL/SQL程序中使用select语句或者DML语句进行操作,数据库服务器将自动打开一个隐式游标,用来存放该语句的执行结果,所以,这种游标无须定义,也无须打开和关闭,隐式游标始终存放最近 条语句的执行结果。
SQL隐式游标和cursor for游标是oracle内置的游标,SQL游标与当前会话有关,当前会话中的update,删除操作都会影响SQL隐式游标的属性,cursor for游标用于循环。
eg: declare
id customernew.customer_id%type;
name customernew.cust_first_name%type;
begin
select customer_id,cust_first_name into id,name from customernew where customer_id=230;
if sql%found then
dbms_output.put_line('编号:'||id||‘姓名:’||name);
end if;
end;
4、动态(ref)游标
动态游标可以在运行时与不同的语句关联,是动态的。动态游标用于处理多行的查询结果集。是在打开游标时才确定所对应的查询,因此,动态游标可以依次对应多个查询。
强类型动态游标
declare
type categories_name is ref cursor --声明动态游标
return categories%rowtype;
c_count number;
categorie categories%rowtype;
cu_categorie categories_name;
begin
select count(*) into c_count from categories where category_name='software1';
if c_count=0 then
open cu_categorie for select * from categories; --------------打开游标变量
else
open cu_categorie for select * from categories where category_name='software1';
end if;
fetch cu_categorie into categorie;
while cu_categorie%found loop
dbms_output.put_line('编号:'||categorie.category_id||'名称:'||categorie.category_name||'描述:‘||categorie.category_description);
fetch cu_categorie into categorie;
end loop;
end;
弱类型动态游标
declare
type cate_id is ref cursor;
c_count number;
product product_information%rowtype;
categorie categories%rowtype;
cu_cate cate_id;
begin
select count(*) into c_count from product_information where category_id=22;
if c_count=0 then
open cu_cate for select * from categories where category_id=22;
fetch cu_cate into categorie
while cu_cate%found loop
dbms_output.put_line(categorie.category_id||':'||categorie.category_name);
fetch cu_cate into categorie;
end loop;
else
open cu_cate for select * from product_information where category_id=22;
fetch cu_cate into product
while cu_cate%found loop
dbms_output.put_line(product.product_id||':'||product.category_name);
fetch cu_cate into product;
end loop;
end if;
end;