Oracl中PL/SQL编程(10级学员 张帅鹏课堂总结)

转载 2012年03月22日 21:02:04

OraclPL/SQL编程

概述: PL/SQLoracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中,并结合处理语句。举例,一般在银行系统中,都是事先写好的sql语句来处理业务,为了保证安全性,这些都是不可见的。就可以使用PL/SQL来完整我们需要的功能处理。

一、PL/SQL程序结构及组成

使用了程序块的结构组织的代码.最简单的程序块是一种被称为匿名块的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。

PL/SQL程序通常包括3部分:Declare部分、exception部分、Beginend部分.

BeginendPL/SQL的必须部分,总是begin开始end结束.

 

Declare部分包含定义变量、常量和游标等类型的代码.

 

Beginend之间可以嵌套beginendexception.

 

//打开服务器的输出

 set serveroutput on;

程序结构:

declare

      说明部分    (变量说明,光标申明,例外说明〕

begin

      语句序列   DML语句〕…

exception

      例外处理语句  

End;

变量与常量的声明:

说明变量类型:char, varchar2, date, number, boolean, long

引用变量emp.ename%type:引用emp表中ename字段的类型

emp%rowtype 记录型变量,可直接加点加字段名访问字段值

 

     变量名  类型(字符,日期,数字,布尔)[:=缺省值];

     常量名 constant 类型(字符,日期,数字,布尔) [:=缺省值];

 

变量与常量的区别:

     变量可以在任何部分进行赋值.

     而常量在declare中声明并且赋值.

注意:都是在declare中声明

作用域:

   是能够引用变量名称这样的标识符的程序块。

二、逻辑语言

a)         条件判断语句

i.          If语句

 IF   条件  THEN 语句1;

       语句2;

        end if;

ii.        If .. Else语句

IF  条件  THEN  语句序列1  

    ESLE   语句序列 2

    END   IF

iii.      IF   elsif  else 语句;

IF   条件  THEN 语句;

   ELSIF  语句  THEN  语句;

  ELSE    语句;

 END  IF;

b)        循环语句

i.          While循环

WHILE  total  <= 25000  LOOP

.. .

total : = total + salary;

END  LOOP;

ii.        根据条件跳出循环

Loop

EXIT [when   条件];

……

End loop

iii.      For循环

FOR   I   IN   1 . . 3    LOOP

语句序列 ;

END    LOOP ;

三、游标(光标) cursor

概述:游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。在oracle数据库中可以使用显示或隐式两种游标。

a)         隐式游标

在执行一个sql语句时,oracle服务器将自动创建一个隐式游标,这个游标是内存中处理该语句的工作区域,其中存储了执行SQL语句的结果.通过游标的属性可获得sql语句执行的结果以及游标状态信息。

   游标的主要属性如下:

      %found 布尔属性  如果sql语句至少影响一行则为true 否则为false

      %notfound 布尔属性%found相反。

      %isopen 布尔属性游标是否打开  打开为true 否则为false

      %rowcount 数字属性返回受sql语句影响的行数

b)        显示游标

是在PL/SQL程序中使用包含select语句来声明的游标。如果需要处理从数据库中检索的一组记录,则可以使用显示游标.使用显示游标处理数据需要四个步骤:声明游标,打开游标,检索数据,关闭游标。

i.          声明游标

 声明游标就是通过定义游标的名称,游标的特性来声明游标,以及打开游标后就可调用查询语句,声明的语法如下:

     Cursor cursor_name[parameter[,parameter]….]

     [return return_type] is select_statement;

Parameter作为游标的输入参数,它可以让用户在打开游标式,向游标传递值;语法如下:

  Parameter_name [in] datatype[{:=|default} expression]\

举例:

  declare

   cursor emp_cursor (pno in number(4) default 7369)

   is select * from emp

       where empno=pno;

ii.        打开游标

 就是指执行声明游标时指定的查询语句。打开的方式只需使用open打开语法:

 Open cursor_name(参数);

 如果没有指定参数就采用默认值执行select语句

iii.      检索数据

检索数据就是从检索到的结果集中获取数据保存到变量中,以便变量进行处理。

使用fetch语句找出结果集中的单行,并从中提取单个值传递给主变量。

 语法如下:

   Fetch cursor_name into [variable_list[record_variable]]

变量用于存储检索的数据

iv.       关闭游标

close 游标名称

v.         游标for循环

依次读取结果集中的行,for循环开始时,游标会自动打开(不需要使用open方法开启),每循环读取一次,系统自动读取当前数据(不需要使用fetch),当退出for循环时,游标也会自动关闭(不需要使用close方法)

vi.       游标变量

游标变量也可以处理多行查询结果集。

 游标变量的定义包括两个步骤:

1、  定义cursor类型的指针

语法:

  Type ref_cursor_name is ref cursor[return return_type]

举例:

   Type var_cursor_name is ref cursor;

2、  定义ref cursor类型的变量

  v_rc  var_cursor_name;

综合写法如下:

   Type var_cursor_name is ref cursor;

   v_rc  var_cursor_name;

上面的综合声明的游标变量称为弱的ref cursor类型,因为它没有指明游标返回的结果,因此它可以指向任何一个具有多列的select查询结果.

 

相对于上面还有一种称为:ref cursor类型.

声明方式如下:

   Type varcursorName is ref cursor return emp%rowtype; //指明了返回的结果

   Vcn varcursorName; //声明一个强的ref cursor类型的变量

使用游标变量与游标使用方式一样,也需要声明,打开,检索,关闭游标变量。

vii.     使用游标更新数据库

定位游标之后进行删除|修改指定的数据行更新的时候需要使用for update选项语法如下:

Cursor cursor_name is select_statement;

For update[of column[,column],[nowait]]

Of用来指定要锁定的列,如果忽略of那么表中选择的数据行都将锁定。如果被锁定行已经被锁定了,那么必须等待释放才能锁定对于这种情况我们可以使用nowait语句。

 

当使用for update语句声明游标后,可以再delete|update语句中使用where current of子句,修改|删除游标结果集中当前行对应的表中的数据。

 语法如下:

   Where { current of cursor_name|search_condition}

 

四、实例代码:

实例1:统计每年入职的员工个数

 

 

 


declare

cursor cl isselectcount(*),to_char(hiredate,'yyyy')from emp groupby to_char(hiredate,'yyyy');

emp_num number;

emp_year char(4);

emp_count number:=0;

emp_row1 varchar2(100):='total';

emp_row2 varchar2(100):='';

begin

  selectcount(*)into emp_count from emp;

  emp_row2:=emp_row2||' '||emp_count;

open cl;

loop

  fetch cl into emp_num,emp_year;

  exitwhen cl%notfound;

  emp_count:=emp_count+emp_num;

  emp_row1:=emp_row1||' '||emp_year;

  emp_row2:=emp_row2||'    '||emp_num;

  endloop;

  close cl;

  dbms_output.put_line(emp_row1);

  dbms_output.put_line(emp_row2);

  end;

实例二:为员工长工资。从最低工资调起每人长10%,但工资总额不能超过50万元,请计算长工资的人数和长工资后的工资总额,并输出输出长工资人数及工资总额。

declare

cursor cl isselect empno,sal from emp orderby sal;

salCount number;

empCount number:=0;

emp_no emp.empno%type;

emp_sal emp.sal%type;

begin

  selectsum(sal)into salCount from emp;

  open cl;

  while salCount<50000

  loop

    fetch cl into emp_no,emp_sal;

    exitwhen cl%notfound;

    update emp set sal=sal*1.1where empno=emp_no;

    salCount:=salCount+emp_sal*0.1;

    empCount:=empCount+1;

    endloop;

    close cl;

    dbms_output.put_line('长工资人数'||empCount||'长工资后总额'||salCount);

    end;

实例三:用PL/SQL语言编写一程序,实现按部门分段(6000以上、(60003000)3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金),参考如下格式:

部门      小于3000           3000-6000                 大于6000  工资总额

 10              2                  1                       0           8750

  20             3                  2                       0         10875   

  30             5                  0                       0           8450

declare

cursor cl isselect deptno,sum(sal)from emp groupby deptno orderby deptno asc;

emp_deptno emp.deptno%type;

emp_salsum number;

sal1 number;

sal2 number;

sal3 number;

begin

  open cl;

  loop

    fetch cl into emp_deptno,emp_salsum;

    exitwhen cl%notfound;

    selectcount(*)into sal1 from emp where deptno=emp_deptno and sal<3000;

    selectcount(*)into sal2 from emp where deptno=emp_deptno and(sal between3000and600);

    selectcount(*)into sal3 from emp where deptno=emp_deptno and sal>6000;

    dbms_output.put_line(emp_deptno||' '||sal1||' '||sal2||' '||sal3||' '||emp_salsum);

    endloop;

    end;

 

怎样使用PL/SQL在不安装oracle 客户端的情况下使用oracle数据库

在网上查了好多这方面的例子,但是似乎说的都不准确,在咨询朋友后终于实现了本机不安装oracle 的情况下,在windows系统上实现连接服务器上的数据库,现在贴出来与大家共享。 首先,我们需要一个PL...
  • zona_wzq
  • zona_wzq
  • 2016年08月15日 09:30
  • 523

PL\SQL如何将从一张表中选出的数据插入到另一张表中

 今天在做数据库操作的时候,有一个需求是将A表中的前500000条数据查出来,然后插入到表B中.表A与表B具有同样的字段类型,数据库为Oracle 10g,数据库管理工具用的PL\SQL,由于数...
  • u011878191
  • u011878191
  • 2015年11月10日 00:14
  • 2359

PL/SQL高级编程

实验目的:1、掌握PL/SQL的数据结构和编程结构,掌握应用PL/SQL编写简单程序的方法 2、理解存储过程的概念,掌握编写存储过程的方法 3、理解函数的概念,掌握编写存储过程的方法 实验内容:一、P...
  • codefight
  • codefight
  • 2017年05月27日 16:31
  • 592

PL/SQL练习题一(基础)

Display the employee records who joins the department before their manager? SELECT emp1.* FROM EMPLO...
  • OnlyQi
  • OnlyQi
  • 2016年04月09日 11:47
  • 1690

PL/SQL基础语法入门

先前安装了PL/SQL软件 PL/SQL全称为Procedural Language/SQL。 PL/SQL也是一种程序语言,叫做过程化SQL语言,是Oracle数据库对SQL语句...
  • chance2015
  • chance2015
  • 2015年12月26日 18:20
  • 2264

Oracl pl/sql块的语法

Oracle培训课程:pl/sql块的语法,PL/SQL是一种块结构的语言,一个PL/SQL程序包含了一个或者多个逻辑块,逻辑块中可以声明变量,变量在使用之前必须先声明。除了正常的执行程序外,PL/S...
  • oraclestudyroad
  • oraclestudyroad
  • 2016年08月18日 13:15
  • 206

Oracle之课程实验五(pl/sql)

课程名称:Oracle11g数据库应用简明教程   实验题目:实验三使用报表统计各部门人数 姓名:李日中 学号:2010324264班级:10计本3班 实验日期:2013-6-1  成绩:   一、 ...
  • QQ1130141391
  • QQ1130141391
  • 2013年10月22日 22:16
  • 1240

PL/SQL简介与简单实用

一、PL/SQL简介: (1)PL/SQL俗称过程化SQL语言(Procedural Language/SQL),是Oracle对SQL的 过程化扩充 而形成的程序开发语言。在普通SQL语句的使用...
  • super_YC
  • super_YC
  • 2017年04月03日 04:59
  • 324

PL/SQL编程基础

PL/SQL编程基础 实践目的   (1) 掌握PL/SQL块结构、PL/SQL的基本语法、PL/SQL的控制结构。   (2) 掌握PL/SQL块中使用复合数据类型和游标的方法。 实践内容 任务中涉...
  • u012701023
  • u012701023
  • 2015年06月07日 23:42
  • 911

PL/SQL 游标的使用

 游标的使用 ①游标概念 为了处理SQL 语句,ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息, 其中包括要处理的行的数目,一个指向语句被分析以...
  • wzg775192833
  • wzg775192833
  • 2014年11月17日 15:06
  • 1491
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracl中PL/SQL编程(10级学员 张帅鹏课堂总结)
举报原因:
原因补充:

(最多只允许输入30个字)