PL\\SQL(day05)

PLSQL

1.常用的访问数据库的相关技术

plsql 过程化的sql
proc/c++ 在c/c++语言中访问oracle数据库的技术
ado/odbc vc中访问数据库的技术
oci oracle底层提供的客户端的连接接口
sql j/jdbc java访问数据库的技术
2.PLSQL

2.1 概念

plsql(procedured language/sql)是在标准sql的基础上增加了过程化的处理形成的语言

oracle客户端工具访问oracle服务器的操作语言

是对sql的扩充

2.2 特点

结构化模块化编程

良好的可移植性

良好的可维护性

提升系统性能

 不便于向异构数据库移植

3.SQL语言的特点

机器语言汇编语言高级语言结构化语言

只管做什么不管怎么做

没有过程和控制结构

没有算法描述能力

4.plsql扩充了sql

变量和数据类型
控制语句
过程和函数
对象类型和方法

5.plsql的程序框架

declare

/* 声明区声明变量、定义类型等 /

— 单行注释 ‘–‘

— 多行注释 /内容/

— 如果没有需要声明、定义的内容，声明区可以省略

begin

/ 执行区执行sql或plsql语句 /

exception

/ 异常处理区处理异常的区域 /

/ 可省略 */

end;

/ — 表示plsql代码结束必须独立占一行

6.plsql的开发工具

sqlplus 命令提示符的工具

plsqldeveloper 图形化的开发工具

begin

dbms_output.put_line(‘Hello World!’);

end;

— 打开输出功能

set serveroutput on

–调整字段占位长度

set 字段名 for(format) a+字段长度

或者

set linesize + 字段长度

7.标识符

7.1 作用

给变量、数据类型、游标、过程、函数、触发器、包等命名

7.2 使用变量

declare

变量名数据类型;

变量名数据类型 := 值;

begin

变量名 := 值;

-- 声明两个变量，赋值并输出
declare
     var_id number;
     var_name varchar2(20):='test';
begin
     var_id:=1;
     dbms_output.put_line(var_id||','||var_name);
end;
/

8.变量和数据类型

8.1 数据类型

1) 标量类型

number binary_integer 数字类型

char varchar2 字符串类型

date 日期类型

boolean 布尔类型

2) 复合类型

record

table

3) 参考类型 ref

ref cursor(参考游标)

4) 大类型

BLOB 0-4g

CLOB 0-4g

BFILE

8.2 变量的修饰符

变量名 constant 数据类型;

变量名数据类型 not null;

declare

— (1)

— var_id constant number;

var_id constant number:=1;

— (3)

— var_name varchar(20) not null;

var_name varchar(20) not null:=’1′;

begin

— (2)

— var_id:=1;

var_name:=’test’;

dbms_output.put_line(var_id);

dbms_output.put_line(var_name);

end;

(1) 用constant修饰的变量必须初始化
(2) 用constant修饰的变量不允许赋值
(3) 用not null修饰的变量必须初始化任何类型的变量，在赋值前 初值都是null

8.3 使用binary_integer和boolean定义变量

true false null

declare

var_id binary_integer:=100;

var_flag boolean;

begin

var_flag:=true;

if var_flag then

dbms_output.put_line(var_id);

end if;

end;

8.4 声明两个变量，类型分别和s_emp表中id、first_name相同，用来接收id=1的员工的id和first_name，并输出

declare

var_id number(7);

var_name varchar2(25);

begin

var_id:=1;

var_name:=’Carmen’;

dbms_output.put_line(var_id||’,’||var_name);

end;

获取表中字段的数据类型：表名.字段名%type

使用select语句给变量赋值：

select 字段列表 into 变量列表 from 表名 where 条件;

注意：字段列表和变量列表的顺序、数量和数据类型要一致

select语句有且只有一行结果

declare

var_id s_emp.id%type;

var_name s_emp.first_name%type;

begin

select id,first_name into var_id,var_name from s_emp

where id=1;

dbms_output.put_line(var_id||’,’||var_name);

end;

8.5 record类型相当于C语言中的结构体

8.5.1 定义record类型

type 记录类型名 is record(

字段名类型,

…

字段名类型

);

8.5.2 定义record类型，有三个字段，类型分别和s_emp表中id,first_name,salary相同。声明该类型变量，用于接收id=2的员工的id,first_name和salary，并输出。

declare

— 定义record类型

type emprecord is record(

id s_emp.id%type,

name s_emp.first_name%type,

sal number(11,2)

);

— 声明record类型变量

var_emp emprecord;

var_emp2 emprecord;

begin

select id,first_name,salary into var_emp from s_emp

where id=2;

dbms_output.put_line(var_emp.id||’,’||var_emp.name||’,’

||var_emp.sal);

var_emp2:=var_emp;

dbms_output.put_line(var_emp2.id||’,’

||var_emp2.name||’,’

||var_emp2.sal);

end;

— 查询的字段的数量少于record类型的字段的数量

declare

— 定义record类型

type emprecord is record(

id s_emp.id%type,

name s_emp.first_name%type,

sal number(11,2)

);

— 声明record类型变量

var_emp emprecord;

begin

select id,first_name into var_emp.id,var_emp.name

from s_emp where id=2;

dbms_output.put_line(var_emp.id||’,’||var_emp.name||’,’

||var_emp.sal);

end;

— 接收表中的全部字段，record类型?

8.5.3 用于接收表中整行数据的类型

表名%rowtype

— 查询s_emp表中 id=3的员工的全部信息，保存在变量中并输出

declare

var_emp s_emp%rowtype;

begin

select * into var_emp from s_emp where id=3;

dbms_output.put_line(var_emp.id||’,’||

var_emp.first_name||’,’||

var_emp.title);

end;

8.6 table类型类似于C语言中的数组

8.6.1 定义table类型的语法

type table类型 is table of 元素的数据类型

index by binary_integer;

8.6.2 定义一个table类型，声明变量，保存多个数字

declare

/* 定义table类型 /

type numstable is table of number

index by binary_integer;

/ 声明table类型的变量 */

var_nums numstable;

begin

var_nums(3):=100;

var_nums(7):=200;

var_nums(1):=300;

dbms_output.put_line(var_nums(3));

— dbms_output.put_line(var_nums(2));

end;

8.6.3 下标连续时，table类型变量的访问
  declare
       /* 定义table类型 */
       type numstable is table of number
       index by binary_integer;
       /* 声明table类型的变量 */
       var_nums numstable;
       /* 声明变量 表示下标 */
       var_i binary_integer;
   begin
       var_nums(3):=100;
       var_nums(5):=200;
       var_nums(4):=300;
       var_i:=3;
       dbms_output.put_line(var_nums(var_i));
       var_i:=var_i + 1;
       dbms_output.put_line(var_nums(var_i));
       var_i:=var_i + 1;
       dbms_output.put_line(var_nums(var_i));
       -- var_i:=var_i + 1;
   end;
   /

8.6.4 下标不连续时，对table类型变量的遍历

first() — 获取第一个元素的下标

next(n) — 获取下标为n的元素的下一个元素的下标

last() — 获取最后一个元素的下标

declare
       /* 定义table类型 */
       type numstable is table of number
       index by binary_integer;
       /* 声明table类型的变量 */
       var_nums numstable;
       /* 声明变量 表示下标 */
       var_i binary_integer;
   begin
       var_nums(3):=100;
       var_nums(7):=200;
       var_nums(4):=300;
       var_i:=var_nums.first();
       dbms_output.put_line(var_nums(var_i));
       var_i:=var_nums.next(var_i);
       dbms_output.put_line(var_nums(var_i));
       var_i:=var_nums.next(var_i);
       dbms_output.put_line(var_nums(var_i));
       -- var_i:=var_nums.next(var_i);
   end;

8.6.5 定义table类型变量，保存s_emp表中id为1，3，7的员工的信息，并输出

declare

/* 定义table类型 /

type empstable is table of s_emp%rowtype

index by binary_integer;

/ 声明table类型变量 /

var_emps empstable;

/ 声明变量保存下标 */

var_i binary_integer;

begin

select * into var_emps(1) from s_emp where id=1;

select * into var_emps(3) from s_emp where id=3;

select * into var_emps(7) from s_emp where id=7;

var_i:=var_emps.first();

dbms_output.put_line(var_emps(var_i).id||’,’||

var_emps(var_i).first_name||’,’||

var_emps(var_i).salary);

var_i:=var_emps.next(var_i);

dbms_output.put_line(var_emps(var_i).id||’,’||

var_emps(var_i).first_name||’,’||

var_emps(var_i).salary);

var_i:=var_emps.next(var_i);

dbms_output.put_line(var_emps(var_i).id||’,’||

var_emps(var_i).first_name||’,’||

var_emps(var_i).salary);

var_i:=var_emps.next(var_i);

end;

8.6. 变量的作用域

declare
   -- 全局
   var_a number:=1;
begin
   declare
      -- 局部
      var_b number:=2;
   begin
      dbms_output.put_line(var_a);
      dbms_output.put_line(var_b);
   end;
   dbms_output.put_line(var_a);
   -- dbms_output.put_line(var_b);
end;
局部既可以访问局部变量，也可以访问全局变量
全局 只可以访问全局变量局部变量和全局变量重名时,局部变量会覆盖同名的全局变量
-- 局部访问同名的全局变量：
使用标签
定义标签：     <<标签名>>
使用标签：     标签名.变量名<<g>>
declare
   -- 全局
   var_a number:=1;
begin
   declare
      -- 局部
      var_a number:=2;
   begin
      dbms_output.put_line(g.var_a);
   end;
   dbms_output.put_line(var_a);
end;

控制语句

9.1 if语句

9.1.1 语法
1. 简单if语句
  if 条件 then
  
  操作
  
  end if;
2. if…else语句
  if 条件 then
  
  操作1
  
  else
  
  操作2
  
  end if;
3. 多分支if
  if 条件1 then
  
  操作1
  
  elsif 条件2 then
  
  操作2
  
  ….
  
  else
  
  操作n
  
  end if;
4. 嵌入if
  if 条件1 then
  
  if 条件2 then
  
  操作1
  
  else
  
  操作2
  
  end if;
  
  else
  
  if 条件3 then
  
  操作3
  
  else
  
  操作4
  
  end if;
  
  end if;
9.1.2 练习：声明三个数字类型变量并赋值，输出最大值

— 方法一：

declare

var_a number:=1;

var_b number:=200;

var_c number:=100;

begin

if var_a > var_b then

if var_a > var_c then

dbms_output.put_line(var_a);

else

dbms_output.put_line(var_c);

end if;

else

if var_b > var_c then

dbms_output.put_line(var_b);

else

dbms_output.put_line(var_c);

end if;

end if;

end;

/

— 方法二：

declare

var_a number:=&var_a;// 后面这’&’是输入相当于c语言里的scanf

var_b number:=&var_b;

var_c number:=&var_c;

var_temp number;

begin

var_temp:=var_a;

if var_b > var_temp then

var_temp:=var_b;

end if;

if var_c >var_temp then

var_temp:=var_c;

end if;

dbms_output.put_line(var_temp);

end;

9.1.3 NULL的运算特点

declare

var_a number;

var_b number;

begin

if var_a > var_b then

dbms_output.put_line(‘a>b’);

elsif var_a < var_b then

dbms_output.put_line(‘a<b’);

elsif var_a = var_b then

dbms_output.put_line(‘a=b’);

elsif var_a is null and var_b is null then

dbms_output.put_line(

‘var_a is null and var_b is null’);

end if;

end;

/

9.2 循环语句

循环变量初始化循环条件循环操作条件的更新

 9.2.1 简单循环
   1) 语法
       loop
         -- 循环操作
       end loop;
   2) 结束循环的方式
      -- 方式一：
      if 退出循环的条件 then
             exit;
      end if;
      -- 方式二：
      exit when 退出循环的条件;
    3) 使用简单循环输出1..10
       declare
          var_i number:=1;
       begin
          loop
             dbms_output.put_line(var_i);
             exit when var_i=10;
             var_i:=var_i+1;
          end loop;
       end;
       /
       -- 使用if退出循环
       declare
          var_i number:=10;
       begin
          loop
             dbms_output.put_line(var_i);
             if var_i=1 then
                  dbms_output.put_line('loop over!');
                  exit;
             end if;
             var_i:=var_i-1;
          end loop;
       end;
       /9.2.2 while循环
  1) 语法
    while 条件 loop
        -- 循环操作
     end loop;
  2) 使用while循环输出1..10
    declare
        var_i number:=1;
    begin
        while var_i<=10 loop
            dbms_output.put_line(var_i);
            var_i:=var_i+1;
        end loop;
    end;    -- 练习：从1..100累加，输出和大于2000时对应的数字
    declare
        var_i number:=1;
        var_sum number:=0;
    begin
        while var_i<=100 loop
             var_sum:=var_sum + var_i;
             if var_sum >=2000 then
                dbms_output.put_line(var_i);
                exit;
             end  if;
             var_i := var_i + 1;
         end loop;
    end; 9.2.3 for 循环
    智能循环
    1) 语法
        for 循环变量 in 区间 loop
             -- 循环操作
        end loop;
    2) 使用for 循环输出1..10    begin
       for var_i in 1..10 loop
             dbms_output.put_line(var_i);
       end loop;
    end;     -- 练习 使用for循环输出10..1
     begin
       for var_i in 10..1 loop
             dbms_output.put_line(var_i);
       end loop;
     end;
     -- 上边代码循环一次也不执行    使用 reverse 实现倒序输出
    begin
       for var_i in reverse 1..10 loop
             dbms_output.put_line(var_i);
       end loop;
     end;     -- 使用退出循环的方式  提前结束循环
     begin
       for var_i in 1..10 loop
             dbms_output.put_line(var_i);
             if var_i=5 then
                 -- var_i:=11; 错误
             end if;
       end loop;
     end;     a.循环变量不需要声明
     b.区间必须是从小到大  使用reverse实现反转循环
     c.循环变量不允许赋值

9.3 跳转语句 goto

9.3.1 语法

<<标签名>>

— 这里必须有语句

NULL; — 空语句

 goto 标签名;

9.3.2 使用goto退出嵌套循环

begin

for var_i in 1..3 loop

for var_j in 1..5 loop

dbms_output.put_line(var_j);

if var_j=3 then

goto outer;

end if;

end loop;

NULL;

end;

9.3.3 使用if..exit方式退出循环

begin

<> //提到前面来比较好，能够一眼被一眼的看出来

for var_i in 1..3 loop

for var_j in 1..5 loop

dbms_output.put_line(var_j);

if var_j=3 then

exit outer;

end if;

end loop;

dbms_output.put_line(‘loop over’);

end;

plsql中使用sql语句

select语句
select语句要和into 配合使用

select 字段列表 into 变量列表 from 表名 where 条件;
dml语句
tcl语句

可以直接在plsql中使用
ddl语句
不能直接在plsql中使用需要用动态sql实现

11.动态sql

11.1 概念

把一条字符串对应的sql语句，当成真正的sql语句去执行

11.2 案例：创建一张表

/* 错误：ddl语句不能直接在plsql中使用 */

begin

create table testdsql_zsm_00(id number);

end;

— 在plsql中可以使用函数

declare

sqlstr varchar2(100);

begin

sqlstr:=’create table testdsql_zsm_00(id number)’;

sqlstr:=substr(sqlstr,1,length(sqlstr)-1)

||’,name varchar2(20))’;

— dbms_output.put_line(sqlstr);

execute immediate sqlstr;

end;

11.3 DML语句的动态sql

1) 直接在plsql中使用dml和tcl语句

begin

insert into testdsql_zsm_00 values(1,’test1′);

commit;

end;

2) 常规字符串的拼接

declare

sqlstr varchar2(100);

begin

sqlstr:= ‘insert into testdsql_zsm_00 values(2,”test2”)’;

execute immediate sqlstr;

commit;

end;

3)带有变量的字符串的拼接

declare

var_id number:=3;

var_name varchar2(20):=’test3′;

sqlstr varchar2(100);

begin

sqlstr:=’insert into testdsql_zsm_00 values(‘||var_id||

‘,”’||var_name||”’)’;

— dbms_output.put_line(sqlstr);

execute immediate sqlstr;

commit;

end;

使用占位符配合using解决字符串拼接的问题
占位符： :占位符名

execute immediate sqlstr using 变量列表;

declare

var_id number:=4;

var_name varchar2(20):=’test4′;

sqlstr varchar2(100);

begin

sqlstr:=’insert into testdsql_zsm_00 values(:b0,:b1)’; //:b0 :b1 就是占位符

execute immediate sqlstr using var_id,var_name;

commit;

end;

11.4 select语句动态sql

必须是普通的select语句(不带into)

select语句有且只有一行结果

 declare
    sqlstr varchar2(100);
    var_name s_emp.first_name%type;
 begin
    sqlstr:='select first_name from s_emp where id=1';
    execute immediate sqlstr into var_name;
    dbms_output.put_line(var_name);
 end;

总结：

1.变量和类型

定义和使用record和table类型

type 类型名 is record(

字段类型,

….

字段类型

);

type 类型名 is table of 元素类型 index by binary_integer;

表名.字段%type

表名%rowtype

2.控制语句

分支：if

循环：简单循环、while循环、for循环

跳转语句：goto

结束循环的两种方式：

exit when 退出循环的条件;

    if 退出循环的条件 then
        exit;
    end if;

3.动态sql

sqlstr varchar2(200):=”;

execute immediate sqlstr;

‘insert into 表名 values(:b0,:b1,….)’

execute immediate sqlstr using 变量1,变量2,….;

练习：

1.定义record类型，声明变量，保存s_dept表中id=31的部门的

信息

使用动态sql语句，删除testdsql_zsm_00表中指定id 的信息

个人收藏笔记记录

开通VIP