oracle数据库的PL/SQL语言详解(动态sql、游标)

1.什么是PL/SQL

• PL/SQL 是一种过程化语言，对sql的扩展

• 特点
  –支持所有与sql的语法
  –支持case语句，方便的实现循环
  –通过继承，实现子类具有父类的属性和方法
  –设置了新的日期类型

• 工作原理
  –由pl/sql引擎接受指令
  –将指令传递给oracle数据库服务器

• PL/SQL语句块

  –PL/SQL程序是按照块结构进行划分
  –块是PL/SQL程序的基本单位

2.语法结构

• –delcare声明关键字(用于定义变量和常量)

• –变量命名规则

  • –首字母必须是英文字母，其后可以是字母、数字、特殊字符、特殊字符$、#(java没有)和下划线

  • –不允许空格

  • –不允许空格

  • –不超过30个字符

3.基础语法实例

• 打印语句的编写以及变量的声明赋值

declare 
    v_name varchar(20) :='jack';
    v_age number :=19;
begin 
 dbms_output.put_line('姓名：'||v_name);
 DBMS_OUTPUT.put_line('年龄：'||v_age);
end;

declare 
    v_name varchar(20);
    pi constant number(10,4):=3.1415;
begin 
    v_name:='lucy';
 dbms_output.put_line('姓名：'||v_name);
 --pi:=3.1545;常量不能赋值
 dbms_output.put_line('pi:'||pi);
end;

• if -then语句

declare 
    v_age number(3):=17;
begin 
    if v_age>=18 then
        dbms_output.put_line('成年人');
    else 
        dbms_output.put_line('未成年');
        end if;
end;

• 成绩判定

--1.if 条件 then 执行语句 elsif 条件 then 执行语句 else 执行语句 endif（注意是elsif 不是elseif）
declare 
    v_score number(3):=90;
begin 
    if v_score>=90 then
        dbms_output.put_line('优秀');
    elsif v_score>=80 then
        dbms_output.put_line('良好');
    elsif v_score>=70 then
        dbms_output.put_line('中等');
    elsif v_score>=60 then
        dbms_output.put_line('及格');
    else
            dbms_output.put_line('不及格');
        end if;
end;

• case语句

--case 变量 when 值 then 执行语句 else 执行语句 end case
declare 
    v_score varchar(10):='D';
begin 
    case v_score
    when 'A' then 
        dbms_output.put_line('优秀');
    when 'B' then 
        dbms_output.put_line('良好');
    when 'C' then 
        dbms_output.put_line('中等');
    else 
         dbms_output.put_line('不及格');
    end case;
end;

• loop

--loop (循环，类似于while) loop statement; endloop;
DECLARE
    v_i number(3) :=0;
begin
    loop
        DBMS_OUTPUT.PUT_LINE ('i: ' ||v_i);
            v_i := v_i + 1;
            IF v_i > 10 THEN
              EXIT;
            END IF;
          END LOOP;
end;

• for 变量

begin

    for v_j in 1..10 loop
        DBMS_OUTPUT.PUT_LINE ('j: ' ||v_j);
          END LOOP;
end;

4.动态sql

• 动态sql
  • –编译期间sql语句是不确定的
  • –sql语句写成字符串变量，通过 execute immediate sqlStr执行
  • –插入用values(:1,:2)等进行占位,执行SQL时用execute immediate sqlStr using 变量1,变量2等;
• 例子

declare 
    v_sql1 varchar(100);
    v_sql2 varchar(100);
    v_sql3 varchar(100);
    v_id varchar(20):='1';
    v_name varchar(20):='xiaoming';
begin
    v_sql1:='create table userinfo1 (id varchar(20) primary key,name varchar(20))';
    v_sql2:='drop table userinfo1';
    v_sql3:='insert into userinfo1(id,name) values(:1,:2)';
    --execute immediate v_sql1;sql1或者sql2
    execute immediate v_sql3 using v_id,v_name;

end;

• 异常

-异常 exception when others then 执行语句
declare
    v_j number(3):=1;
begin
    v_j:=v_j/0;
    
    exception 
        when others then
        dbms_output.put_line('出错了');
end;

• 自定义异常
• 自定义异常需要在declare中声明,类型为exception
• 引发异常使用raise 异常名
• others异常是指其他异常

declare
    v_j number(5):=10;
    temp_exception exception;
    
begin
    if v_j=5 then
        raise temp_exception;
        else
        v_j:=10/0;
        end if;
    exception
        when temp_exception then
            dbms_output.put_line('v_j的值不能等于5');
        when others then
            dbms_output.put_line('其他异常');  
end;

• 游标

  • 隐式游标

  • 只能返回一条记录,但是可以是多列

declare
    v_sal number(9);
    v_loc varchar(30);
begin

    select 6000,'洛阳' into v_sal,v_loc from dual;
    dbms_output.put_line('工资：'||v_sal||'地址:'||v_loc);
end;

• 显式游标

--创建一个表用于测试
create table player(
    id int primary key,
    name varchar(20) not null,
    sex varchar(20)
);
--插入几条数据
insert into player(id,name,sex) values('1','lucy','女');
insert into player(id,name,sex) values('2','hah','男');
insert into player(id,name,sex) values('3','dack','男');
insert into player(id,name,sex) values('4','rose','女');
insert into player(id,name,sex) values('5','luck','女');
--loop循环提取游标
declare
    v_id int;
    v_name varchar(20);
    v_sex varchar(20);
    --声明游标 定义查询那些列值 用于后面的遍历
    cursor cur_player is select id,name,sex from player;-- where id='1'(可以添加条件)
begin
    --打开游标
    open cur_player;
    --提取游标
    loop
        fetch cur_player into v_id,v_name,v_sex;
        if(cur_player%notfound) then
            dbms_output.put_line('数据找完了');
            exit;
        end if;
        dbms_output.put_line('id:'||v_id||'名字：'||v_name||'性别：'||v_sex);
    end loop;
    --关闭游标
    close cur_player;
end;

• for循环提取游标

declare
    --声明游标 定义查询那些列值 用于后面的遍历
    cursor cur_player is select id,name,sex from player;-- where id='1'(可以添加条件)
begin
    --相当于java的增强for循环，t_player就是对象，列就是对象的属性，cur_player相当于实体类集合
   for t_player in cur_player loop
        dbms_output.put_line('id:'||t_player.id||'名字：'||t_player.name||'性别：'||t_player.sex);
    end loop;
    dbms_output.put_line('遍历结束了');
    --不需要再关闭游标 for已经帮助关闭了

end;

快去敲敲巩固一下吧！