五.PL/SQL

PL/SQL

PL/SQL是一种块结构的语言,一个PL/SQL程序包含了一个或多个逻辑块,逻辑块中可以声明变量,变量在使用之前必须先声明.除了正常的执行程序外,PL/SQL还提供了专门的异常处理部分进行异常处理.每个逻辑块分成三个部分,语法是:

[DECLARE --declaration statements] ①

BEGIN

--executable statement②

[EXCEPTION --exception statement]③

END;

①声明部分:声明变量和常量.由关键字Declare开始,如果不声明,此部分可省略.

②执行部分:PL/SQL块的指令部分,由关键字Begin开始,End结尾.所有可执行的PL/SQL语句都在这部分.其他的PL/SQL块可以作为子块嵌套在该部分.此部分是必选的.END关键字后边用分号结尾

③异常处理部分:可选部分,用Exception把可执行部分分成两个小部分,前部分的程序是正常运行的程序,一旦出现异常就跳转到后部分异常部分执行.

PL/SQL块中的每一条语句都必须以分号结束,SQL语句可以是多行,但分号表示该语句结束.

变量声明

示例:

DECLARE sname VARCHAR2(20):='jerry';①

BEGIN

sname:=sname||' and tom';②

dbms_output.put_line(sname);③

END

①声明一个变量sname,初始化值是"jerry".字符串用单引号,如果字符串出现单引号可以使用两个单引号('')来表示,即单引号同时也具有转义的作用.

②对变量sname重新赋值,赋值运算符是":=".

③dbms_output.put_line是输出语句,可以把一个变量的值输出,在SQL*plus中输出数据时,可能没有结果显示,可以使用命令:set serveroutput on设置输出到SQL*plus控制台上.

对变量赋值还可以使用SELECT...INTO语句从数据库中查询数据对变量进行赋值.但是查询的结果只能是一行记录,不能是零行或者多行记录.

示例:

DECLARE sname VARCHAR2(20) DEFAULT 'jerry';

BEGIN

SELECT ename INTO sname FROM emp WHERE empno=7934; --查询的结果必须是一行,不能多行或没有记录

dbms_output.put_line(sname);

END

声明常量

常量在声明时赋值,并且运行时不允许重新赋值,使用CONSTANT关键字声明常量.

DECLARE　pi CONSTANT NUMBER:=3.14 ;--圆周率,常量赋值可以用":="赋值,也可以使用DEFAULT关键字赋值.

r NUMBER DEFAULT 3;--半径

area NUMBER;--面积

BEGIN

area:=pi*r*r;

dbms_output.put_line(area);

END

宿主常量

在SQL*Plus中还可以声明Session(会话,也就是一个客户端从连接到退出的过程称为当前用户的会话)全局级变量,该变量在整个会话过程中均起作用,类似的这种变量称为宿主变量.宿主变量在PL/Sql引用时要用":变量名"引用.

示例在Sql*plus下:

var emp_name varchar(30);

begin

select ename into :emp_name from emp where empno=7499;

end

在Sql*plus下

print emp_name; --在Sql*plus中,使用print可以输出变量中的结果

PL/SQL数据类型

标题数据类型

属性数据类型

当声明一个变量的值是数据库中的一行或者是数据库中某列时,可以直接使用属性类型来声明.Oracle中存在两种属性类型:%TYPE和%ROWTYPE.

%ROWTYPE

引用数据库表中的一行作为数据类型,即RECORD类型(记录类型),是PL/SQL附加的数据类型.表示一条记录,就相当于C#中的一个对象.可以使用"."来访问记录中的属性.

示例:

DECLARE myemp EMP%ROWTYPE;

BEGIN

SELECT * INTO myemp FROM EMP WHERE empno=4934;

dbms_output.put_line(myemp.ename);

END

%TYPE

引用某个变量或者数据库的列的类型作为某变量的数据类型.

DECLARE sal emp.sal%TYPE;

mysal NUMBER(4):=3000;

totalsal mysal%TYPE; --%TYPE可以引用表中的某列的类型为变量的数据类型,也可以引用某变量的类型作为新变量的数据类型

BEGIN

SELECT SAL INTO sal FROM emp WHERE empno=1231;

totalsal:=sal+mysal;

dbms_output.put_line(totalsal);

END

PL/SQL条件控制和循环控制

条件控制

IF -THEN -END IF;

IF-THEN-ELSE-END IF;

IF-THEN-ELSE IF-END IF;

多分枝条件CASE-WHEN-THEN-ELSE END CASE;

示例:IF-THEN-END IF;

Declare new sal emp.sal%TYPE;

begin

select sal into newsal from emp where ename='james';

if newsal>900 then

update emp set comm=800 where ename='james';

end if;

end;

示例:CASE-WHEN-THEN-ELSE END CASE;

输入一个字母A/B/C分别输出对应的级别信息

Declare v_grade CHAR(1):=UPPER('&grade'); 　　--&grade 此赋值方式表示在运行时由键盘输入字符到v_grade变量中.

BEGIN

　　case v_grade

　　when 'A' then dbms_output.put_line('Excellent');

　　when 'B' then dbms_output.put_line('Very Good');

　　when 'C' then dbms_output.put_line('Good');

　　else dbms_output.put_line('No such grade');

　　end case;

END;

循环结构

1.无条件循环LOOP-END LOOP语句

2.WHILE循环语句

3.FOR循环语句

在循环中EXIT用来强制结束循环,相当于C#循环中的break.

LOOP循环

语法:

　　LOOP

　　　　--循环体

　　END LOOP

示例:计算1+2+3+....+100的和

declare 
counter number:=0;
sumcounter number:=0;
begin
  loop
        counter:=counter+1;
        sumcounter:=sumcounter+counter;
        if counter=100 then --中断循环的方式一 
           exit;
        end if;
        --exit when counter=100;--中断循环的方式二
  end loop;
  dbms_output.put_line(sumcounter);
end;

WHILE循环

语法:

WHILE 条件LOOP

　　--循环体

END LOOP;

示例:

declare counter number:=0;
sumcounter number:=0;
begin
  while counter<100 loop
    counter:=counter+1;
    sumcounter:=counter+sumcounter;
  end loop;
  dbms_output.put_line(sumcounter);
end;

FOR循环

语法:

FOR 循环变量 IN [REVERSE] 循环下限..循环上限LOOP

　　--循环体

END LOOP;

示例:

declare counter number:=0;
sumcounter number:=0;
begin
  for counter in  1..10 loop
    sumcounter:=sumcounter+counter;
  end loop;
  dbms_output.put_line(sumcounter);
end;

顺序结构

在程序顺序结构中有两个特殊的语句:GOTO和NULL

GOTO语句: 无条件的跳转到标签指定的语句去执行.标签是用双尖括号括起来的标示符,在PL/SQL块中必须具有唯一的名称,标签后必须紧跟可执行语句或者PL/SQL块.GOTO不能跳转到　IF/CASE／LOOP语句或者子块中．

NULL语句：什么都不做，只是将控制权转到下一行语句．NULL语句是可执行语句．NULL语句可以用在IF或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方．比如GOTO的目标地方不需要执行任何语句时．

示例:

declare sumsal emp.sal%type;
BEGIN
  select sum(sal) into sumsal from emp;
  if sumsal>2000 then
    goto first_lable;
  else
    goto second_lable;--如果小于的话就跳转到第二个标签,不执行任何动作 
  end if;
  <<first_lable>>
  dbms_output.put_line('above 20000:'||sumsal);
  <<second_lable>>
  null;
end;

5.PL/SQL中动态执行SQL语句

语法:

EXECUTE　IMMEDIATE　动态语句字符串

INTO　变量列表］

[USING 参数列表]

如果动态语句是SELECT语句,可以把查询的结果保存到INTO后面的变量中.如果动态语句中存在参数,USING为语句中的参数传值.

动态SQL中的参数格式是:[:参数名],参数在运行时需要使用USING传值.

示例:

declare
sql_stmt varchar2(200);  --动态sql语句
emp_id number(4):=7566;
salary number(7,2);
dept_id number(2):=90;
dept_name varchar2(14):='personnel';
location varchar2(13):='dallas';
emp_rec emp%ROWTYPE;
begin
  --无子句的execute immediate
  execute immediate 'create table bonus1(id number,amt number)';
  --using 子句的execute immediate
  sql_stmt:='insert into dept values(:1,:2,:3)';
  execute immediate sql_stmt using dept_id,dept_name,location;
  --into 子句r execute immediate
  sql_stmt:='select * from emp where empno=:id';
  execute immediate sql_stmt into emp_rec using emp_id;
  --returning into 子句的execute immediate
  sql_stmt:='update emp set sal=2000 where empno=:1
  returning sal into :2';
  execute immediate sql_stmt using emp_id returning into salary;
  
  execute immediate 'delete from dept where deptno=:num' using dept_id;
  
end;

1.[:1,:2,:3]为参数标识符,因此需要USING关键字对三个参数分别赋值.

2.对动态语句可以使用INTO子句把查询的结果保存到一个变量中,要求该结果只能是单行.

3.在Oracle的Insert/update/delete语句都可以使用Returning子句把操作影响的行中的数据返回,对sql语句中存在Returning子句时,在动态执行时可以使用returning into来接收

4.动态执行参数中可以是:[:数字]也可以是[:字符串].

6.PL/SQL的异常处理

常见的预定义异常

异常的语法:

BEGIN

　　EXCEPTION --异常处理开始

　　WHEN 异常名1 THEN

　　--对应异常处理

　　WHEN 异常名2 THEN

　　--对应异常处理

　　WHEN OTHERS THEN

　　--其他异常处理

END;

示例:

declare
    newsal emp.sal%TYPE
BEGIN
    select sal ilnto newsal from emp; --此种方法返回多行,是错误的.
exception
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line('返回的记录太多');
    WHEN OTHERS　THEN
        dbms_output.put_line('未知异常');
END;

自定义异常
a.异常定义:在PL/SQL块的声明部分采用Exception关键字声明异常,定义方法与定义变量相同.比如声明一个myexception异常方法是:myexception EXCEPTION;
b.异常引发:在程序可执行区域,使用RAISE关键字进行引发．比如引发myexception方法是:RAISE myexception;
示例:

declare
    sal emp.sal%TYPE;
    myexp EXCEPTION;    --定义异常变量
BEGIN
    select sal into sal from emp where ename='james';
    if sal<5000 then
        raise myexp;    --用RAISE抛出异常
    end if;
exception
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('未找到记录');
    WHEN MYEXP THEN 
    dbms_output.put_line('工资过低');   
END;

引发应用程序异常

Oracle系统为用户预留了自定义异常码,其范围介于-20000到-20999之间的负整数.
引发应用程序异常的语法:
RAISE_APPLICATION_ERROR(异常码,异常信息)
示例:

declare
    sal emp.sal%TYPE;
    myexp EXCEPTION;    --定义异常变量
BEGIN
    select sal into sal from emp where ename='james';
    if sal<5000 then
        raise myexp;    --用RAISE抛出异常
    end if;
exception
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('未找到记录');
    WHEN MYEXP THEN 
    RAISE_APPLICATION_ERROR(-20001,'工资过低');
END;

posted @ 2016-04-28 16:57 xfyn 阅读(275) 评论(0) 编辑收藏举报

刷新页面返回顶部

xfyn

五.PL/SQL

公告