pl/sql语言入门

pl/sql是一种编程语言

• pl/sql编程语言是对sql语言的拓展，使得sql语言具有过程化编程的特性；
• pl/sql编程语言比一般的过程化编程语言更加灵活高效；
• pl/sql编程语言主要用来编写存储过程和存储函数等。

pl/sql中的变量类型

pl/sql语句必须写在声明的方法中，有固定的格式，其中declare部分可省略，下面介绍简单的声明方法结构及其变量类型。

注意：

1. 本文涉及到的用例以scott用户下的表格为例；
2. 如果你在sql developer上执行dbms输出可以去’查看’–>'dbms输出’中查看输出结果，如果在sql plus中，则要在declare之前使用set serveroutput on命令设置环境变量serveroutput为打开状态，从而使得pl/sql程序能够在脚本中输出结果，而不用特意去dbms输出中查看。

1. 普通变量

declare
  i number(2) := 10;        -- 普通变量赋值，赋值用 :=
  s varchar2(10) := '小明';      
begin
  dbms_output.put_line('输出i:' || i); -- dbms_output.put_line()用于输出并换行，|| 用于字符串拼接
  dbms_output.put_line('输出s:' || s); -- dbms_output.put()将内容写到内存，等到put_line时一起输出为一行
end;

2. 引用型变量

declare
  -- 引用型变量，引用emp.ename字段（列）的类型，可以在这里赋值，也可以用select into语句赋值
  ena emp.ename%type;
begin
  select ename into ena from emp where empno=7782;
  dbms_output.put_line('输出ena:' || ena);
end;

3. 记录型变量

declare
  -- 记录型变量，引用emp记录（行）的类型，可以在这里赋值，也可以用select into语句赋值
  emprow emp%rowtype; 
begin
  select * into emprow from emp where empno=7782;
  dbms_output.put_line(emprow.ename || '的工作为' || emprow.job);
end;

pl/sql中的if判断

-- 输入 <18      --> 未成年人
--     18<?<40  --> 中年人
--     >40      --> 老年人

-- num为地址值，含义是在该地址上保存了键盘输入的值，这里只是一个提示信息，可省略
-- 注意！accept后的'地址名称'要与&后面的'地址名称'一致，否则会将新建一个未知输入
accept i prompt '请输入数字i';
accept j prompt '请输入数字j';
declare
  i number(3) := &i; -- &为地址符号，将num地址上的值赋给i
  j number(3) := &j;
begin
  -- 语法类似java，第一个不成立才进入第二个条件
  -- elsif和else都可以省略
  if i<18 then
    dbms_output.put_line('未成年人');
  elsif i<40 then  
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

pl/sql中的循环

介绍三种基本的循环，以一个例子说明：三种方式输出1-10。

1. while循环

declare
  i number(2) := 1;
begin
  dbms_output.put('while循环: ');
  while i<11 loop
    dbms_output.put(i || ' ');
    i := i+1;
  end loop;
  dbms_output.new_line();
end;

2. exit循环(比较常用)

declare
  i number(2) := 1;
begin
  dbms_output.put('exit循环: ');
  loop
    exit when i>10; 
    dbms_output.put(i || ' ');
    i := i+1;
  end loop;
  dbms_output.new_line();
end;

3. for循环

declare
  
begin
  dbms_output.put('for循环: ');
  for i in 1..10 loop
    dbms_output.put(i || ' ');
  end loop;
  dbms_output.new_line();
end;

pl/sql中的游标

有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。
游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

1. 没有传入参数的游标

-- 输出emp表中所有员工的姓名
declare
  cursor c1 is select * from emp; -- 定义游标
  emprow emp%rowtype;             -- 定义记录变量接收游标中的记录
begin
  dbms_output.put('游标输出: ');
  open c1; -- 打开游标，记得这里要加分号
    loop
      fetch c1 into emprow;  -- 从游标中取值
      exit when c1%notfound; -- 当游标到底的时候退出
      dbms_output.put(emprow.ename || ' ');
    end loop;
    dbms_output.new_line();
  close c1;
end;

2. 有传入参数的游标

-- 给指定部门员工涨工资
declare 
  cursor c2(dno emp.deptno%type) is
  select * from emp where deptno=dno; -- 定义带传入参数的游标，用于后面的查询条件
  emprow emp%rowtype; -- 定义记录变量接收游标中的记录
  upsal emp.sal%type; -- 定义临时变量sal,方便输出
begin
  open c2(10); -- 切记，一定要在打开的时候赋值
    loop
      fetch c2 into emprow;     -- 从游标中取值
      exit when c2%notfound; -- 当游标到底的时候退出
      dbms_output.put(emprow.ename || '的原来工资为：' || emprow.sal || ', ');
      update emp set sal=sal+100 where empno=emprow.empno; -- 更新工资
      commit;
      select sal into upsal from emp where empno=emprow.empno;
      dbms_output.put('现在的工资为：' || upsal);
      dbms_output.new_line();
    end loop;
  close c2;
end;
-- 测试：查询10号部门员工信息
select * from emp where deptno=10;