文档章节

_过程_函数_触发器_游标

CarlDing
 CarlDing
发布于 2016/03/19 20:54
字数 2094
阅读 31
收藏 1

1:存储过程 就是一个PL/SQL块的名称。通过这个名称来调用过程。

存储过程,保存在服务器上,以后通过名称来调用,然后由服务来执行的代码块。 1:不接收参数的过程 (略) Create or replace procedure p1 As Age int; Begin …. End;

2:接收参数的存过程

参数的声明: p2(参数名称 [输入in或是输出out类型默认是输入的参数] 参数类型 , 。。。。)

--开发一个过程,接收name,age两个参数,保存到数据表中去 create or replace procedure p2(nm in varchar,ag in int) as begin insert into person values(sys_guid(),nm,ag); end;

3:返回参数的值
--开发一个过程,接收name,age两个参数,保存到数据表中去 - 只要out类型的,则pid在传递时,必须是一个变量 create or replace procedure p2(nm in varchar,ag in int,pid out varchar) as begin select sys_guid() into pid from dual;--给返回类型的值赋值 insert into person values(pid,nm,ag); end;

--调用这个过程,保存数据 --在另一个pl/块中来调用 declare vpid varchar(32); begin p2('李四',99,vpid); dbms_output.put_line('新的id是'||vpid); end;

select * from person;

可以省去一些参数,声明参数为 输入+输出类型的:

--开发一个过程,接收name,age两个参数,保存到数据表中去 - 只要out类型的,则pid在传递时,必须是一个变量 create or replace procedure p2(nm in out varchar,ag in int,cnt out int) as vpid varchar(32); begin select sys_guid() into vpid from dual; insert into person values(vpid,nm,ag); nm:=vpid; select count(1) into cnt from person; end;

--调用这个过程,保存数据 --在另一个pl/块中来调用 declare vpid varchar(32):='赵七'; vcnt int; begin p2(vpid,99,vcnt); dbms_output.put_line('新的id是'||vpid||' 记录的数量是:'||vcnt); end;

select * from person;

参数的顺序:

=> 类似于 C++

2:存储函数 – 用户自定义的函数

函数与过程的区别: 函数必须要至少返回一个值。 1~N 过程可以返回0~N 函数可以用于查询或是赋值。 Selectd f1(..) from …; Nm:=f1(…); 而过程只能是调用。

实现+操作: create or replace function f1(a int,b int) return number as vsum int; begin vsum:=a+b; return vsum; end;

select f1(1,4) from dual;

select f1(age,100),name,age from person;

declare aa int; begin aa:=f1(4,55); dbms_output.put_line(aa); end;

3:触发器 trigger 函数,过程 – 都是让用户可以调用 的。

触发器- 保存在服务器上的一段pl/sql块 与 表相关。当达到某个条件时,由服务器来调用的代码块。

功能: 1:实现更加严格的约束 。 2:实现表之间数据的自动维护。

3.1、创建一个触发器的示例 当向person表中写入一行记录以后,就输出一个句,说 一行记录成功了。

3.2、分类 按影响的行数 表级触发器- 默认的。 与行数无关,只触发一次。 create or replace trigger t1 after delete on person declare begin dbms_output.put_line('数据已经删除了'); end;

行级触发器

在行级的触发器上,可以使用两个隐藏变量:

变量 insert Update Delete :NEW 可用 可用 不可用 :OLD 不能用 可用 可用

要求当修改记录时,检查,新修改的年龄是否大于当前的年龄:

分析:在 person表上添加 before update约束。

列级的的触发器 – 按是否指定列 – 列级的触发器只能用于update

指定只是修改age时才触发:

按是否有条件,可以分为条件触发器 当达到某个条件时,才会触发

3.3 使用原则

4:应用 限制一个表在上班时可以写入,下班以后,就不能再写入了: 8:00 ~ 17:00

表之间的自维护: 班级与学生的关系。

一个班有很多的学生。

create table cls( id varchar(32) primary key, name varchar(30), cnt int );

create table studs( id varchar(32) primary key, name varchar(30), clsid varchar(32) , constraint fk1 foreign key(clsid) references cls(id) );

create or replace trigger t2 after insert on studs FOR EACH ROW declare begin update cls set cnt=cnt+1 where id=:NEW.clsid; end;

insert into cls values('C001','一班',0);

insert into studs values('S002','Mary','C001');

select * from cls;

作业: 请实现学生删除时,修改学生班级维护。

5:游标 cursor(光标)

游标是指向记录集的一个指针。 一次,只能指向一行记录。

游标的分类: 1:隐式的游标 – 》Oracle在执行DML操作时使用的游标。 - 》 隐式的游标的名称叫:SQL 2:显式的游标 – 》 用户自己定义的游标。 1:静态游标。 2:动态游标。

游标属性: 变量 功能 SQL%rowCount 返回当前影响的行数。 SQL%notFound 没有指到记录的行上,即指到了行首或是行尾 SQL%isOpen 游标是打开的吗

对游标的操作: 显示游标的 定义游标 打开游标 遍历执行 关闭游标

1:查看隐式的游标 declare vrow int; begin update person set age=age+1; vrow:=SQL%rowCount; dbms_output.put_line('影响了几行呀'||vrow); if not SQL%isOpen then dbms_output.put_line('已经关了 '); end if; end;

2:自定义的游标 定义游标 打开游标 遍历执行 关闭游标

1:静态的游标

declare cursor cur is select id,name,age from person; --定义游标 vid varchar(30); vname varchar(30); vage int; begin --打开游标 open cur; --遍历游标 loop fetch cur into vid,vname,vage; --先做判断有数据 exit when cur%notFound; dbms_output.put_line(vid||','||vname||','||vage); end loop; --关闭游标 close cur; --用异常处理游标的必须关闭 exception when others then if cur%isOpen then close cur; end if; end;

select * from person;

静态游标的变体:参数游标 declare cursor cur(nm varchar)[] is select id,name,age from person where name like '%'||nm||'%[]'; --定义游标 vid varchar(30); vname varchar(30); vage int; begin --打开游标 open cur('a');[] --遍历游标 loop fetch cur into vid,vname,vage; --先做判断有数据 exit when cur%notFound; dbms_output.put_line(vid||','||vname||','||vage); end loop; --关闭游标 close cur; --用异常处理游标的必须关闭 exception when others then if cur%isOpen then close cur; end if; end;

declare cursor cur(nm varchar) is select id,name,age from person where name like '%'||nm||'%'; --定义游标 vperson person%rowType; begin --打开游标 open cur('%'); --遍历游标 loop fetch cur into vperson; --先做判断有数据 exit when cur%notFound; dbms_output.put_line(vperson.id||','||vperson.name||','||vperson.age); end loop; --关闭游标 close cur; --用异常处理游标的必须关闭 exception when others then if cur%isOpen then close cur; end if; end;

2:动态的游标 在定义时,不指定查询的语句,而是在打开游标时才指定执行哪一个查询语句。

在定义时,先定义一个自定义的数据类型: declare --定义一个数据类型,这个类型是动态的游标类型,即定义一个类 type ref_cur is ref cursor; --再根据上面的类型,定义一个变量 cur ref_cur; vperson person%rowType; begin --打开游标 时指定SQL open cur for 'select * from person'; --遍历游标 loop fetch cur into vperson; --先做判断有数据 exit when cur%notFound; dbms_output.put_line(vperson.id||','||vperson.name||','||vperson.age); end loop; --关闭游标 close cur; --用异常处理游标的必须关闭 exception when others then if cur%isOpen then close cur; end if; end;

6:请开发一个PL/SQL块,删除某个用户的所有表 就遍历 user_tables表,查询这个用户的所有表,组成SQL语句(drop语句),执行 Drop就可以了。

declare cursor cur is select table_name from user_tables; cursor cur2 is select constraint_name,table_name from user_constraints where constraint_type='R'; tname varchar(30); cname varchar(30); vsql varchar(100); begin open cur2; loop fetch cur2 into cname,tname; exit when cur2%notfound; vsql:='alter table '||tname||' drop constraint '||cname; dbms_output.put_line(vsql); EXECUTE IMMEDIATE vsql; end loop; close cur2; open cur; loop fetch cur into tname; exit when cur%notfound; vsql:='drop table '||tname; dbms_output.put_line(vsql); --执行上面的vsql,将这个vsql语句让Oracle数据来执行 EXECUTE IMMEDIATE vsql; end loop; close cur; end;

select constraint_name,table_name from user_constraints where constraint_type='R';

请实现删除里面的所有 view,triiger,procedure

create or replace procedure p1 as cursor cur is select table_name from user_tables; cursor cur2 is select constraint_name,table_name from user_constraints where constraint_type='R'; tname varchar(30); cname varchar(30); vsql varchar(100); begin open cur2; loop fetch cur2 into cname,tname; exit when cur2%notfound; vsql:='alter table '||tname||' drop constraint '||cname; dbms_output.put_line(vsql); EXECUTE IMMEDIATE vsql; end loop; close cur2; open cur; loop fetch cur into tname; exit when cur%notfound; vsql:='drop table '||tname; dbms_output.put_line(vsql); --执行上面的vsql,将这个vsql语句让Oracle数据来执行 EXECUTE IMMEDIATE vsql; end loop; close cur; end;

exec p1;

7:表空间 查看表空间及对应的文件:

查询某个用户保存数据用的是哪一个表空间:

创建一个新的表空间

创建一个新的用户,这个用户使用qlc做为表空间

alter TABLESPACE qlu add datafile 'C:\qlu3.dbf' size 128K AUTOEXTEND off;

© 著作权归作者所有

CarlDing
粉丝 5
博文 106
码字总数 78103
作品 0
济南
其他
私信 提问
SQL Server 开发指南

SQL Server 数据库设计 一、数据库设计的必要性 二、什么是数据库设计 三、数据库设计的重要 四、数据模型 实体-关系(E-R)数据模型 实体(Entity) 属性(Attribute) 关系(Relationship)...

ibm_hoojo
2011/07/21
0
0
【强烈强烈推荐】《ORACLE PL/SQL编程详解》全原创(共八篇)--系列文章导航

《ORACLE PL/SQL编程详解》 系列文章目录导航 ——通过知识共享树立个人品牌。 本是成书的,但后来做其他事了,就无偿的贡献出来,被读者夸其目前为止最“实在、经典”的写ORACLE PL/SQL编程...

yonghu86
2012/07/30
0
0
Oracle-14:PLSQL

------------吾亦无他,唯手熟尔,谦卑若愚,好学若饥------------- PL/SQL PL/SQL(Procedural Language):过程化sql语言! 在原本的sql语句之上,再增加一些逻辑或者循环等操作。 1:基本语法 ...

晨曦dawn
2018/05/22
0
0
SQL Server技术问题之游标优缺点

分类: MS SQL SERVER 支持三种类型的游标:Transact_SQL 游标,API 服务器游标和客户游标。 (1) Transact_SQL 游标 TransactSQL 游标是由DECLARE CURSOR 语法定义、主要用在TransactSQL 脚...

技术小牛人
2017/11/15
0
0
《MySQL必知必会》读书笔记(四) 22~30章 完结篇

1、视图 视图不包含表中应该有的任何列或数据,它包含的是一个SQL查询。在视图创建之后,可以用于表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视...

一万
2016/02/08
329
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring系列教程八: Spring实现事务的两种方式

一、 Spring事务概念: 事务是一系列的动作,它们综合在一起才是一个完整的工作单元,这些动作必须全部完成,如果有一个失败的话,那么事务就会回滚到最开始的状态,仿佛什么都没发生过一样。...

我叫小糖主
今天
5
0
CentOS 的基本使用

1. 使用 sudo 命令, 可以以 root 身份执行命令, 必须要在 /etc/sudoers 中定义普通用户 2. 设置 阿里云 yum 镜像, 参考 https://opsx.alibaba.com/mirror # 备份mv /etc/yum.repos.d/CentO...

北漂的我
昨天
3
0
Proxmox VE技巧 移除PVE “没有有效订阅” 的弹窗提示

登陆的时候提示没有有效的订阅You do not have a valid subscription for this server. Please visit www.proxmox.com to get a list of available options. 用的是免费版的,所以每次都提示......

以谁为师
昨天
3
0
Java设计模式之外观模式(门面模式)

什么是外观模式   外观模式(Facade),他隐藏了系统的复杂性,并向客户端提供了一个可以访问系统的接口。这种类型的设计模式属于结构性模式。为子系统中的一组接口提供了一个统一的访问接口...

须臾之余
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部