开发者社区> 余二五> 正文

PLSQL

简介:
+关注继续查看
PL/SQL块
 PL/SQL块是在SQL语言之上发展起来的一种应用,可以集中的处理各种复杂的SQL操作。
组成:
 DECLARE:
  声明部分
 BEGIN
  编写趁许的主题
 EXCEPTION
  捕获异常
 END ;
 /
看一个简单的PL/SQL块
DECLARE
 i NUMBER ;
BEGIN
 i := 30 ;
 DBMS_OUTPUT.put_line('I的内容为:'||i) ;
END ;
/
此时,直接执行程序即可。
执行之后发现没有任何的输出。因为Oracle在系统设置中默认设置了输出不显示,如果要显示的话,输入以下命令:
 set serveroutput on
PL/SQL块还可以接收用户的输入信息,例如:现在要求用户输入一个雇员编号,之后根据输入的内容进行查询,查询雇员的姓名。
 • 用户的输入信息使用“&”完成。
DECLARE
 eno NUMBER ;
 en VARCHAR(30) ;
BEGIN
 -- 输入的信息保存在eno里
 eno := &no ;
 -- 之后根据eno的值,对数据库进行查询操作
 SELECT ename INTO en FROM emp WHERE empno=eno ;
 DBMS_OUTPUT.put_line('编号为:'||eno||'雇员的姓名为:'||en) ;
EXCEPTION
 WHEN no_data_found THEN
  DBMS_OUTPUT.put_line('没有此雇员') ;
END ;
/
在以上的查询中再进一步:可以根据雇员的编号查出姓名及其领导的姓名和所在的部门,进行显示。
DECLARE
 eno emp.empno%TYPE ;
 en emp.ename%TYPE ;
 mn emp.ename%TYPE ;
 dn dept.dname%TYPE ;
BEGIN
 -- 输入的信息保存在eno里
 eno := &no ;
 -- 之后根据eno的值,对数据库进行查询操作
 SELECT e.ename,m.ename,d.dname INTO en,mn,dn FROM emp e,dept d,emp m WHERE e.empno=7369 AND e.mgr=m.empno AND e.deptno=d.deptno ;
 DBMS_OUTPUT.put_line('编号为:'||eno||'雇员的姓名为:'||en) ;
 DBMS_OUTPUT.put_line('编号为:'||eno||'雇员的上级姓名为:'||mn) ;
 DBMS_OUTPUT.put_line('编号为:'||eno||'雇员所在的部门:'||dn) ;
EXCEPTION
 WHEN no_data_found THEN
  DBMS_OUTPUT.put_line('没有此雇员') ;
END ;
/
说明:
 • emp.empno%TYPE ;:表示以emp表中的empno字段的类型定义变量
 • e.ename,m.ename,d.dname INTO en,mn,dn:一次可以同时放进去多个值
PL/SQL之中也包含了:循环、分支等条件控制语句
1、 Loop循环
格式:
 LOOP
  循环的语句 ;
  EXIT WHEN 终止条件 ;
  循环条件必须更改 ;
 END LOOP ;
循环输出1~10。
DECLARE
 cou NUMBER ;
BEGIN
 -- 必须给一个初始值
 cou := 1 ;
 LOOP 
  DBMS_OUTPUT.put_line('cou = '||cou) ;
  EXIT WHEN cou>10 ;
  cou := cou + 1 ;
 END LOOP ;
END ;
/
 此循环是先执行一次之后再进行判断
2、 while循环
格式:
 while(判断循环的条件) loop
  循环的语句 ;
  循环条件的改变 ;
 End loop ;
使用此语句修改上面的程序:
DECLARE
 cou NUMBER ;
BEGIN
 -- 必须给一个初始值
 cou := 1 ;
 WHILE(cou<10) LOOP
  DBMS_OUTPUT.put_line('cou = '||cou) ;
  cou := cou + 1 ;
 END LOOP ;
END ;
/
 此语句,是先判断,之后如果条件满足则执行,与while循环类似。
3、 for循环
格式:
 FOR 变量名称 in 变量的初始值..结束值 LOOP
  循环语句 ;
 END LOOP ;
DECLARE
 cou NUMBER ;
BEGIN
 FOR cou IN 1..10 LOOP
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 END LOOP ;
END ;
/
4、 IF语句
 条件判断
格式:
 IF 条件 THEN
  满足条件时,执行此语句
 END IF ;
DECLARE
 cou NUMBER ;
BEGIN
 cou := 11 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 END IF ;
END ;
/
5、 IF…ELSE语句
 如果IF满足了,则执行,否则执行ELSE
DECLARE
 cou NUMBER ;
BEGIN
 cou := 1 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 ELSE
  DBMS_OUTPUT.put_line('条件不满足') ;
 END IF ;
END ;
/
6、 IF…ELSIF…ELSE语句
DECLARE
 cou NUMBER ;
BEGIN
 cou := 1 ;
 IF cou>10 THEN
  DBMS_OUTPUT.put_line('cou = '||cou) ;
 ELSIF cou<5 THEN
  DBMS_OUTPUT.put_line('值小于5') ;
 ELSE
  DBMS_OUTPUT.put_line('条件不满足') ;
 END IF ;
END ;
/
问题1:
 输入一个雇员的编号,如果其工资高于3500,则显示高工资,工资大于2000,则显示中等工资,工资小于2000的则认为是低等工资。
DECLARE
 eno emp.empno%TYPE ;
 sal emp.sal%TYPE ;
BEGIN
 eno := &en ;
 SELECT sal INTO sal FROM emp WHERE empno=eno ;
 IF sal>3500 THEN
  DBMS_OUTPUT.put_line('高工资。。。') ;
 ELSIF sal>2000 THEN
  DBMS_OUTPUT.put_line('中等工资。。') ;
 ELSE
  DBMS_OUTPUT.put_line('底工资。。。') ;
 END IF ;
END ;
/
问题2:
 输入一个雇员编号,根据它所在的部门涨工资,规则:
 • 10部门上涨10%
 • 20部门上涨20%
 • 30部门上涨30%
 所有部门的上涨工资,最不能超过5000,如果超过5000,则工资就为5000。
DECLARE
 eno emp.empno%TYPE ;
 dno emp.deptno%TYPE ;
 sal emp.sal%TYPE ;
BEGIN
 eno := &en ;
 SELECT deptno,sal INTO dno,sal FROM emp WHERE empno=eno ;
 IF dno=10 THEN
  IF sal*1.1>5000 THEN
   UPDATE emp SET sal=5000 WHERE empno=eno ;
  ELSE
   UPDATE emp SET sal=sal*1.1 WHERE empno=eno ;
  END IF ;
 ELSIF dno=20 THEN
  IF sal*1.2>5000 THEN
   UPDATE emp SET sal=5000 WHERE empno=eno ;
  ELSE
   UPDATE emp SET sal=sal*1.2 WHERE empno=eno ;
  END IF ;
 ELSIF dno=30 THEN
  IF sal*1.3>5000 THEN
   UPDATE emp SET sal=5000 WHERE empno=eno ;
  ELSE
   UPDATE emp SET sal=sal*1.3 WHERE empno=eno ;
  END IF ;
 ELSE
  null ;
 END IF ;
END ;
/
7、 GOTO语句
 无条件跳转语句
DECLARE
 eno emp.empno%TYPE ;
 sal emp.sal%TYPE ;
BEGIN
 eno := &en ;
 SELECT sal INTO sal FROM emp WHERE empno=eno ;
 IF sal>3500 THEN
  goto po1 ;
 ELSIF sal>2000 THEN
  goto po2 ;
 ELSE
  goto po3 ;
 END IF ;
 <<po1>>
  DBMS_OUTPUT.put_line('高工资。。。') ;
 <<po2>>
  DBMS_OUTPUT.put_line('中等工资。。') ;
 <<po3>>
  DBMS_OUTPUT.put_line('底工资。。。') ;
END ;
/
游标
 操作步骤:
 • 声明游标
 • 打开游标
 • 取出结果,此时的结果取出的是一行数据
 • 关闭游标
到底那种类型可以把一行的数据都装进来
 • 此时使用ROWTYPE类型,此类型表示可以把一行的数据都装进来。
例如:查询雇员编号为7369的信息(肯定是一行信息)。
DECLARE
 eno emp.empno%TYPE ;
 empInfo emp%ROWTYPE ;
BEGIN
 eno := &en ;
 SELECT * INTO empInfo FROM emp WHERE empno=eno ;
 DBMS_OUTPUT.put_line('雇员编号:'||empInfo.empno) ;
 DBMS_OUTPUT.put_line('雇员姓名:'||empInfo.ename) ;
END ;
/
编写第一个游标,输出全部的信息。
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
BEGIN
 -- 游标操作使用循环,但是在操作之前必须先将游标打开
 OPEN mycur ;
 -- 使游标向下一行
 FETCH mycur INTO empInfo ;
 -- 判断此行是否有数据被发现
 WHILE (mycur%FOUND) LOOP
  DBMS_OUTPUT.put_line('雇员编号:'||empInfo.empno) ;
  DBMS_OUTPUT.put_line('雇员姓名:'||empInfo.ename) ;
  -- 修改游标,继续向下
  FETCH mycur INTO empInfo ;
 END LOOP ;
END ;
/
也可以使用另外一种方式循环游标:LOOP…END LOOP;
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
BEGIN
 -- 游标操作使用循环,但是在操作之前必须先将游标打开
 OPEN mycur ;
 LOOP
  -- 使游标向下一行
  FETCH mycur INTO empInfo ;
  EXIT WHEN mycur%NOTFOUND ;
  DBMS_OUTPUT.put_line('雇员编号:'||empInfo.empno) ;
  DBMS_OUTPUT.put_line('雇员姓名:'||empInfo.ename) ;
 END LOOP ;
END ;
/
注意1:
 在打开游标之前最好先判断游标是否已经是打开的。
 通过ISOPEN判断,格式:游标%ISOPEN
 IF mycur%ISOPEN THEN
  null ;
 ELSE
  OPEN mycur ;
 END IF ;
注意2:
 可以使用ROWCOUNT对游标所操作的行数进行记录。
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环,但是在操作之前必须先将游标打开
 IF mycur%ISOPEN THEN
  null ;
 ELSE
  OPEN mycur ;
 END IF ;
 LOOP
  -- 使游标向下一行
  FETCH mycur INTO empInfo ;
  EXIT WHEN mycur%NOTFOUND ;
  cou := mycur%ROWCOUNT ;
  DBMS_OUTPUT.put_line(cou||'雇员编号:'||empInfo.empno) ;
  DBMS_OUTPUT.put_line(cou||'雇员姓名:'||empInfo.ename) ;
 END LOOP ;
END ;
/
使用for循环操作游标(比较常用)
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环,但是在操作之前必须先将游标打开
 FOR empInfo IN mycur LOOP
  cou := mycur%ROWCOUNT ;
  DBMS_OUTPUT.put_line(cou||'雇员编号:'||empInfo.empno) ;
  DBMS_OUTPUT.put_line(cou||'雇员姓名:'||empInfo.ename) ;
 END LOOP ;
END ;
/
练习:
 一次性上涨全部雇员的工资。根据它所在的部门涨工资,规则:
 • 10部门上涨10%
 • 20部门上涨20%
 • 30部门上涨30%
 所有部门的上涨工资,最不能超过5000,如果超过5000,则工资就为5000。
DECLARE
 -- 声明游标
 CURSOR mycur IS SELECT * FROM emp ;
 empInfo emp%ROWTYPE ;
 cou NUMBER ;
BEGIN
 -- 游标操作使用循环,但是在操作之前必须先将游标打开
 FOR empInfo IN mycur LOOP
  IF empInfo.deptno=10 THEN
   IF empInfo.sal*1.1>5000 THEN
    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;
   ELSE
    UPDATE emp SET sal=sal*1.1 WHERE empno=empInfo.empno ;
   END IF ;
  ELSIF empInfo.deptno=20 THEN
   IF empInfo.sal*1.2>5000 THEN
    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;
   ELSE
    UPDATE emp SET sal=sal*1.2 WHERE empno=empInfo.empno ;
   END IF ;
  ELSIF empInfo.deptno=30 THEN
   IF empInfo.sal*1.3>5000 THEN
    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;
   ELSE
    UPDATE emp SET sal=sal*1.3 WHERE empno=empInfo.empno ;
   END IF ;
  ELSE
   null ;
  END IF ;
 END LOOP ;
END ;
/
过程(存储过程):
 过程 = 过程的声明 + PL/SQL块
现在定义一个简单的过程,就是打印一个数字
CREATE OR REPLACE PROCEDURE myproc
AS
 i NUMBER ;
BEGIN
 i := 100 ;
 DBMS_OUTPUT.put_line('i = '||i) ;
END ;
/
执行过程:
 exec 过程名字
下面编写一个过程,要求,可以传入部门的编号,部门的名称,部门的位置,之后调用此过程就可以完成部门的增加操作。
CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
 cou NUMBER ;
BEGIN
 -- 判断插入的部门编号是否存在,如果存在则不能插入
 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
 IF cou=0 THEN
  -- 可以增加新的部门
  INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
  DBMS_OUTPUT.put_line('部门插入成功!') ;
 ELSE
  DBMS_OUTPUT.put_line('部门已存在,无法插入!') ;
 END IF ; 
END ;
/
过程的参数类型:
 • IN:值传递,默认的
 • IN OUT:带值进,带值出
 • OUT:不带值进,带值出
IN OUT类型:
CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
 cou NUMBER ;
BEGIN
 -- 判断插入的部门编号是否存在,如果存在则不能插入
 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
 IF cou=0 THEN
  -- 可以增加新的部门
  INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
  DBMS_OUTPUT.put_line('部门插入成功!') ;
  -- 修改dno的值
  dno := 1 ;
 ELSE
  DBMS_OUTPUT.put_line('部门已存在,无法插入!') ;
  dno := -1 ;
 END IF ;
END ;
/
编写PL/SQL块验证过程:
DECLARE
 deptno dept.deptno%TYPE ;
BEGIN 
 deptno := 12 ;
 myproc(deptno,'开发','南京') ;
 DBMS_OUTPUT.put_line(deptno) ;
END ;
/
OUT类型
 不带任何值进,只把值带出来。
CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)
AS
BEGIN
 dno := 10 ;
END ;
/
DECLARE
 deptno dept.deptno%TYPE ;
BEGIN 
 myproc(deptno) ;
 DBMS_OUTPUT.put_line(deptno) ;
END ;
/
函数:
 函数就是一个有返回值的过程。
定义一个函数:此函数可以根据雇员的编号查询出雇员的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER
AS
 rsal NUMBER ;
BEGIN
 SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
 RETURN rsal ;
END ;
/
直接写SQL语句,调用此函数:
 SELECT myfun(7369) FROM dual ;









本文转自 李兴华 51CTO博客,原文链接:http://blog.51cto.com/lixinghua/91221,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
14868 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
15489 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18794 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
36365 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
20102 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27969 0
+关注
20380
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载