plsql程序设计

一plsql程序简介

plsql就是对sql程序的扩展，使我们更好的在oracle数据库上操作数据

1.plsql的程序结构

plsql的程序结构分为四部分：

declare

声明部分(变量的声明，光标的申明，异常说明)；

begin

plsql执行语句

exception

异常处理语句

end；

二.变量和常量的定义

语法：

变量(常量名)   (constant) 类型  默认值

注意:

在定义变量还可以使用not null选项。使用not null 选项以为这其值可以改变，但不允许为其赋值为null。如果赋null程序将抛出异常

例；

declaremy_name VARCHAR2(12) not null default 'sb';begindbms_output.put_line(my_name);end;

(1)基本的变量类型；char,varchar2,date,number,boolean,long

(2)特殊类型

a.引用型：%type      例：my_name   emp.name%type           表示my_name的类型和emp表中的name字段的类型保持一致

declare my_name emp.ename%type;my_salary emp.sal%type;beginselect ename ,sal into my_name,my_salary from emp where empno=7369;dbms_output.put_line(my_name||'的工资是'||my_salary);end;

b.行类型：%rowtype  例 my_row    emp%rowtype                    表示my_row的类型和emp表的结构类型一致,所得到的是一行数据

declare my_row emp%rowtype;beginselect *into my_row from emp where empno=7369;dbms_output.put_line(my_row.ename||'的工资是'||my_row.sal);end;

c.记录类型

把很多单个的变量组合在一起，构成一个新的类型，使用记录数据类型变量时，需要先在声明部分先定义记录的结构,然后定义变量，在在执行部分引用该记录变量本身或其中的成员

语法格式:

type 记录名 is record(

field1_name data_type [not null][:=default_value],
field2_name data_type [not null] [:=default_value],

......

fieldn_name data_type [not null][:=default_value],

);

例

declaretype record_strc is record (my_name emp.ename% TYPE,my_sal emp.sal%type);my_record record_strc;beginselect ename,sal into my_record from emp where empno=7369;dbms_output.put_line(my_record.my_name||'的工资是'|| my_record.my_sal);end;

e.记录表类型

语法格式:
type talbe_name is table of data_type [not null] index by binary_integer;
data_type即记录表，它可以是任何的pl/sql类型，关键字index by binary_integer指示系统创建一个主键索引，以引用记录表变量中的特定行

declaretype table_strc is table of emp%rowtype index by binary_integer;my_table table_strc;beginselect * into my_table(-1) from emp where empno=7369;dbms_output.put_line(my_table(-1).ename||'的工资是'|| my_table(-1).sal);end;

记录表类型相当于数组，所以要给一个下表，不过记录表的下表可以任意指定，没有任何限制，也可以为负数

三 控制语句

1.条件语句---if

(1)if语句

语法结构:if 条件表达式   then

                      pl/sql语句

                         end if;

/declarex number :=2;y number :=3;begin if x<y then   dbms_output.put_line('x大于y');  end if;end;

(2)if.....else 语句

语法格式: if 条件表达式 then

                           pl/sql语句；

                              else

                              pl/sql语句；

                             end if;

declarex number :=2;y number :=3;begin if x>y then   dbms_output.put_line('x大于y');  else    dbms_output.put_line('x小于y');  end if;end;

(3)if....eseif语句

语法格式:if 条件表达式 then

                pl/sql语句

               elsif   条件表达式 then

               pl/sql语句

              。。。。。。

            else

           pl/sql语句

            end if;

注意不能省略else语句和elsif的写法

declarex number;beginx:=82; if x>90 then   dbms_output.put_line('成绩是优秀');  elsif x>80 then    dbms_output.put_line('成绩良好');    elsif x>60 then     dbms_output.put_line('成绩及格');     else     dbms_output.put_line('成绩不及格');  end if;end;

2.条件语句----case

(1)简单的case语句

CASE    selector   
WHEN    selector_value_1    THEN      statements_1

WHEN    selector_value_2    THEN    statements_2...

WHEN     selector_value_n    THEN    statements_n

[ ELSE     else_statements ]

END CASE;]

declarex varchar2(1);beginx:='B';case xwhen 'A' thendbms_output.put_line('成绩优秀');when 'B' then dbms_output.put_line('成绩良好');when 'C' then dbms_output.put_line('成绩及格');elsedbms_output.put_line('成绩不及格');end case;end;

(2)搜索时case语句

语法格式：

     CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]                    

declarea1 number;begina1:=82;case when a1>90 thendbms_output.put_line('成绩优秀');when a1>80 then dbms_output.put_line('成绩良好');when a1>60 then dbms_output.put_line('成绩及格');elsedbms_output.put_line('成绩不及格');end case;end;

2.循环语句

(1)loop循环

语法格式:

[ label ]    LOOP
  statements
END    LOOP [ label ];

a.loop ..............exit语句

declare x number;beginx:=0;loopx:=x+1;if x>5 then exit;end if;dbms_output.put_line('x的值为'||x);end loop;end;

b.

loop

pl/sql语句

exit when condition;

end loop;

declare x number;beginx:=0;loopx:=x+1;exit when x>5;dbms_output.put_line('x的值为'||x);end loop;end;

c.有包含continue的loop语句

declare x number;beginx:=0;loopx:=x+1;if x<3 then dbms_output.put_line('执行continue后x的值为'||x);continue;end if;exit when x>5;dbms_output.put_line('x的值为'||x);end loop;end;

(2)for ..............loop循环

[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ label ];

注意：

1.加reverse，则表示循环从大到小，逐渐减小，

2.lower_bound写在前面，upper_bound写在后面，不然程序不会执行

declare x number;beginfor x in 1..5 loopdbms_output.put_line('x的值为'||x);end loop;end;

reverse ，从大到小               

declare x number;beginfor x in reverse 1..5 loopdbms_output.put_line('x的值为'||x);end loop;end;

(3)while循环

while语法:

while condition

loop

pl/sql语句；

end loop；

declare x number;beginx:=1;while x<5loopx:=x+1;dbms_output.put_line('x的值为'||x);end loop;end;

3.游标

在使用select语句进行查询时，很多情况下都是返回多行，为此pl/sql提供了游标类处理这种情况

(1)游标的语法格式

cursor  游标名 [(参数名 数据类型 [,参数名 数据类型])]

   is select 语句

使用游标获取数据分为四步：

第一：定义游标

第二：打开游标

第三：获取一行游标的值    FETCH cursor_name INTO into_clause  去一行数据付给变量，因为游标的初始状态下，cursor的指针指向的是第一条记录，所以就是从第一条开始查找数据。

第四：关闭游标

游标的属性；

 %isopen（游标是否打开）     %rowcount (影响的行数)
  %found（还有行）      %notfound（没有行）

set serveroutput on;declare cursor ecursor is select ename, sal from emp where deptno=10;type erecord is record (my_name emp.ename% type,my_sal emp.sal%type);my_record erecord;beginopen ecursor;loopfetch ecursor into my_record;exit when ecursor%notfound;dbms_output.put_line('姓名是'||my_record.my_name);end loop;end;

带参数的游标

set serveroutput on;declarecursor ecursor(num number) is select ename, sal from emp where deptno=num;type erecord is record (my_name emp.ename% type,my_sal emp.sal%type);my_record erecord;beginopen ecursor(20);loopfetch ecursor into my_record;exit when ecursor%notfound;dbms_output.put_line('姓名是'||my_record.my_name);end loop;end; 

4.游标变量

游标变量都是显示游标，定义一个游标变量可以不需要指定

使用游标变量的步骤；

第一步:声明一个游标变量

声明语法格式:

type type_name is ref cursor(return return_type);

如果写return_type,则表示该游标是一个强类型的游标变量，即该游标的返回值是固定的，与返回类型相同，负责是一个弱类型的

定义游标变量: my_cursor type_name

第二步:打开游标

使用游标变量的时候，打开游标就要对游标定义

例:open type_name for  select 语句

第三步:抓取数据

declare type v_cursor is ref cursor;my_cursor v_cursor;type t_record is record (my_name emp.ename%type,my_sal emp.sal%type);my_record t_record;beginopen my_cursor for select ename,sal from emp where deptno=30;dbms_output.put_line('游标已打开');loopfetch my_cursor into my_record;exit when my_cursor%notfound;dbms_output.put_line(my_record.my_name||'的工资是'||my_record.my_sal);end loop;close my_cursor;end;

四 异常语句

1.系统异常

异常语句在exception中编写，相当于java中的捕获异常，当有异常发生时，exception捕获该异常并处理它

declarex number;BEGINx:=1/0;exceptionwhen Zero_Divide then dbms_output.put_line('除数不能为0');when OTHERS then dbms_output.put_line('其他例外');end;

注意：写others的原因是，不管发生其他任何的例外，都会被处理

2.自定义异常

pl/sql中的自定义异常直接用变量来定义

declarecursor my_cursor is select ename from emp where deptno=50;e_name emp.ename%type;--自定义一个异常no_data_emp exception;BEGINopen my_cursor;--使用raise来抛出异常fetch my_cursor into e_name ;if my_cursor%notfound then raise no_data_emp;end if;close my_cursor;exceptionwhen  no_data_emp then dbms_output.put_line('没有此数据');when OTHERS then dbms_output.put_line('其他例外');end;

注意；使用raise来抛出异常