oracle使用五(PL/SQL)

PL/SQL 数据类型：

   declare

      width integer;

      height integer ;

      area integer;

      currentdate date;

   begin

      currentdate := sysdate;

      height :=2;

      area :=6;

      width :=area/height;

      DBMS_OUTPUT.put_line('width='||width);

      DBMS_OUTPUT.put_line('current='||currentdate);

   EXCEPTION

       WHEN ZERO_DIVIDE THEN

       DBMS_OUTPUT.put_line('Division by zero');

   end;

    /

PL/SQL 允许使用SELECT ... INTO 语句从数据库中提取记录并将值赋给变量。

     declare

        firstName varchar(20);

        lastName varchar(20);

        salary_temp number(20);

     begin

        salary_temp :=2;

           select first_name,last_name,salary/2 

                           into firstName,lastName,salary_temp 

           from employees2 where employee_id =2;

       dbms_output.put_line('firstName ='||firstName);

       dbms_output.put_line('lastName ='||lastName);

       dbms_output.put_line('salary ='||salary_temp);

    end;

    /

  创建一个表：

       Create table my_book(

           File_descr varchar2(40),

           Book_file BFILE

       );

创建文件目录

create directory txtPath as 'e:/oracleFilePath';

把文件添加到数据库表中

insert into my_book values('第一章',BFILENAME('txtpath','test.txt'));

插入 BLOB  数据类型： 

    Create table my_diagrams(

         Chapter_descr varchar2(40),

         Diagram_no integer,

         Diagram blob

);

通过PL/SQL插入 Blob 数据：

    declare

        l_bfile BFILE;

        l_blob BLOB;

    begin

        insert into my_diagrams (diagram)

        values(empty_blob())

        return diagram into l_blob;

        l_bfile :=bfilename('TXTPATH','Sample1.jpg');

        dbms_lob.open(l_bfile,dbms_lob.file_readonly);

DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));

        Dbms_lob.close(l_bfile);

        commit;

     end;

      /

Dbms_lob.open() 函数打开文件.

DBMS_LOB.loadfromfile() 函数将 l_bfile 中的文件加载到l_blob 

变量中.

CLOB数据类型。

create table my_book_text(

 chapter_id number(3),

 chapter_descr varchar2(40),

 chapter_text clob);

添加clob 数据：

insert into my_book_text values(5,'第五章 PL/SQL 简介','PL/SQL 可用于创建存储过程，触发器和程序包等，用来处理业务规则，数据库时间或给SQL命令的执行添加程序逻辑。');

读取 CLOB 数据类型：

 set serveroutput on

 declare

   clob_var clob;

   amount integer;

   offset integer;

   output_var varchar2(100);

 begin

   select chapter_text into clob_var from my_book_text where chapter_id=5;

   amount :=9;  -- 要读取的字符数

   offset :=1;  -- 起始位置

   DBMS_LOB.READ (clob_var,amount,offset,output_var);

   DBMS_OUTPUT.PUT_LINE(output_var);

   end;

 /

条件控制（IF-THEN-ELSE语句）：

以下代码演示了从 employees2 表中检索employee_id 为 3 的记录 ,如果 salary 大于 15000 则减去 1000 ,否则salary 加 100

 declare

     firstName varchar(20);

     lastName varchar2(20);

     salarytemp number(10);

  begin

      select first_name,last_name,salary into      firstName,lastName,salarytemp from employees2 where   employee_id=3;

if salarytemp > 15000 then

   update employees2 set salary = salary-1000 where employee_id = 3;

 else

     update employees2 set salary = salary+100 where employee_id=3;

 end if;

   dbms_output.put_line('firstName ='||firstName);

   dbms_output.put_line('lastName='||lastName);

   dbms_output.put_line('salarytemp = '||salarytemp);

 end;

 /

Case 语句：

    以下代码演示了选择器。系统先计算选择器值。然后再依次选择 WHEN 子句。

set serveroutput on

begin

case '&grade'

  when 'A' then dbms_output.put_line('优异');

  when 'B' then dbms_output.put_line('优秀');

  when 'C' then dbms_output.put_line('良好');

  when 'D' then dbms_output.put_line('一般');

  when 'E' then dbms_output.put_line('较差');

      else dbms_output.put_line('没有此成绩');

end case;

end;

/

Loop 循环：以下代码演示了loop的使用

 declare

     x number;

  begin

     x :=0;

  loop

     x:=x+1;

   if x>=3 then

   exit;

   end if;

    dbms_output.put_line('循环体 x ='||x);

  end loop;

    dbms_output.put_line('循环体外 x ='||x);

  end;

  /

另外一种表现形式:

 declare

     x number;

  begin

     x :=0;

  loop

      x:=x+1;

      exit when x>=3;

        dbms_output.put_line('循环体内 x ='||x);

  end loop;

    dbms_output.put_line('循环体外 x ='||x);

    end;

/

以下代码演示了要求用户输入学生所得分数。如果分大于60代码将显示“该生已通过”

set serveroutput on

 begin

  loop

  if &marks>60 then

    dbms_output.put_line('该生已通过');

    exit;

  else

    dbms_output.put_line('该生不合格');

    exit;

  end if;

 end loop;

 end;

 /

While循环：

 declare

      x number ;

 begin

    x:=0;

      while x<=3 loop

      x:=x+1;

      dbms_output.put_line('循环内'||x);

     end loop;

   dbms_output.put_line('循环外'||x);

 end;

 /

以下代码演示了while 循环得使用。声明了销量的 monthly_value 和 daily_value,并将其初始化为0。While执行循环，直至每月销量的值大于等于4000

set serveroutput on

 declare

   monthly_value number :=0;

   daily_value number :=0;

  begin

    while monthly_value <= 4000

    loop

      monthly_value := daily_value * 31;

      daily_value := daily_value +10;

      dbms_output.put_line('每日销量:' || daily_value);

    end loop;

  dbms_output.put_line('每月销量' || monthly_value);

 end;

 /

For 循环语句：

 begin

    for i in 1..5 loop

     dbms_output.put_line('循环 I 的值 = '||i);

  end loop;

   dbms_output.put_line('end loop');

  end;

  /

Reverse(递减) 的使用

begin

    for i in reverse 1..5 loop

     dbms_output.put_line('循环 I 的值 = '||i);

  end loop;

   dbms_output.put_line('end loop');

  end;

  /

以下代码显示了25个偶数

set serveroutput on

 begin

  for eve_number in 1..25

   loop

     dbms_output.put_line(eve_number*2);

   end loop;

 end;

 /

Goto 语句 

 set serveroutput on

  declare

     x number;

  begin

    x:=0;

     <<goto_flag>>

       x := x+1;

       dbms_output.put_line('x='||x);

    if x <=3 then

      goto goto_flag;

  end if;

  end;

 /

以下代码先检查 itemcode=’i201’的库存。如果现有库存小于再定购级别，则对现有库存进行更新。

   declare

      qtyhand itemfile.qty_hand%type;

      relevel itemfile.re_level%type;

   begin

     select qty_hand,re_level into qtyhand,relevel from itemfile where itemcode='i201';

   if qtyhand < relevel then

     goto updation;

   else 

     goto quit;

   end if;

    <<updation>>

     update itemfile set qty_hand = qty_hand+re_level where itemcode='i201';

   <<quit>>

       null;

   end;

 /

Oracle 属性类型的使用:

  %TYPE

set serveroutput on

 declare

 empid employees2.employee_id%type;

 firstname varchar2(20);

 lastname varchar2(20);

 begin

    select employee_id,first_name,last_name 

  into empid,firstname,lastname from employees2 

  where employee_id= 3;

 dbms_output.put_line('empid='||empid || 

' firstname ='||firstname || '  lastname='||lastname);

 end;

 /

 %ROWTYPE 用法:

set serveroutput on

declare

  myrecord employees2%rowtype;

begin

  select * into myrecord from employees2 where employee_id=3;

  dbms_output.put_line('empid='||myrecord.employee_id ||

 ' firstname ='|| myrecord.first_name ||'  lastname ='|| myrecord.last_name);

end;

/

复合变量:由几个相关的值构成的变量

set serveroutput on

declare

 TYPE emprecord is RECORD (

    empid number(20),

    firstname varchar2(40),

    lastname varchar2(40)

  );

  temprecord emprecord;

begin

   select employee_id,first_name,last_name into 

temprecord from employees2 where employee_id =3; dbms_output.put_line('empid='||temprecord.empid || '  firstname ='||temprecord.firstname || '  lastname='||temprecord.lastname);

end;

/

执行动态的SQL 语句：以下代码演示了首先执行一条创建表的动态SQL，接着执行了带参数的SELECT 语句。

DECLARE

  sql_stmt VARCHAR2(200);

  emp_id NUMBER(4) := 7566;

  emp_rec emp%ROWTYPE;

BEGIN

  EXECUTE IMMEDIATE 

    'CREATE TABLE bonus2 (id NUMBER, amt NUMBER)';

  sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

  EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

END;

/

预定义异常:

 返回多行异常：

declare

   firstname varchar2(20);

 begin

    select first_name into firstname from employees2 where division_id ='SAL';

    dbms_output.put_line('first_name=' || firstname);

    exception

    when too_many_rows then

      dbms_output.put_line('不能返回多行数据');

 end;

 /

Zero_divide 异常用于一个数字除以零的时候发生，下面例子试图用 1/0,因此引发 zero_divide 异常：

Set serveroutput on

Begin

  Dbms_output.put_line(1/0);

Exception when

  Zero_divide then

  Dbms_output.put_line(‘除数不能为零’);

End;

/

用户自定义异常：

declare

  firstname varchar(20);

  ex exception;

begin

  select first_name into firstname from employees2 where employee_id=3;

 if firstname<>'jack'  then

   raise ex;

 end if;

 dbms_output.put_line('firstname = '||firstname);

exception

when ex then

  dbms_output.put_line('不是所需要的数据');

end;

/

以下代码演示了用户接受输入的类别。IF 语句将用户输入的类别与指定的类别相匹配。如果指定的类别中不存在将引发invalidcategory 异常

set serveroutput on

 declare

 invalidcategory exception;

 category varchar2(10);

begin

 category := '&category';

if category not in('附件','顶盖','备件') then 

  raise invalidcategory;

else

  dbms_output.put_line('您输入的类别是'||category);

end if;

exception

  when invalidcategory then

  dbms_output.put_line('无法识别该类别');

end;

/

引发应用程序错误：RAISE_APPLICATION_ERROR用于创建用户定义的错误消息，用户定义的错误消息可以比指定的异常描述得更详细。引发应用程序错误的语法如下：

RAISE_APPLICATION_ERROR（error_number,error_message）;

declare

  firstname varchar(20);

  ex exception;

begin

  select first_name into firstname from employees2 where employee_id=3;

 if firstname<>'jack'  then

   raise ex;

 end if;

 dbms_output.put_line('firstname = '||firstname);

exception

when ex then

  raise_application_error(-20005,'不是所需要的数据');

end;

/

DECLARE

  rate itemfile.itemrate%TYPE;

  rate_exception EXCEPTION;

BEGIN

  SELECT NVL(itemrate,0) INTO rate FROM itemfile 

  WHERE  itemcode = 'i206';

  IF rate = 0 THEN

    RAISE rate_exception;

  ELSE

    DBMS_OUTPUT.PUT_LINE('项费率为：' || rate);

  END IF;

EXCEPTION

  WHEN rate_exception THEN

    RAISE_APPLICATION_ERROR(-20001, '未指定项费率');

END;

/