PL/SQL编程

1、变量

declare
  v_name varchar2(50):='张三';--普通变量，注意变量赋值用‘:=’
  v_sal  emp.sal%Type; --引用型变量
begin
  select sal into v_sal from emp where empno = 7566;
  DBMS_OUTPUT.put_line('姓名：' || v_name ||',薪水：' || v_sal);
end;

2、记录型变量

declare
  v_em emp%rowtype;
begin
  select * into v_em from emp where empno = 7566;
  DBMS_OUTPUT.put_line('姓名：' || v_em.ename ||',薪水：' || v_em.sal);
end;

3、流程控制之条件分支结构

--判断emp表中的数据量是否超过20条，10~20条，10条以下
declare
  v_count number;
begin
  select count(1) into v_count from emp;

  if v_count > 20 then
    dbms_output.put_line('emp表中的数据条数超过20条，为' || v_count || '条');
  elsif v_count < 10 then
    dbms_output.put_line('emp表中的数据条数为10条以下，为' || v_count || '条');
  else
    dbms_output.put_line('emp表中的数据条数为10~20条，为' || v_count || '条');
  end if;
end;

4、循环

• 无条件循环

-- 循环输出1~10
-- 循环输出1~10
declare
  v_num number := 1;
begin
  loop
    exit when v_num > 10;
    dbms_output.put_line(v_num);
    v_num := v_num + 1;
  end loop;
end;

declare
  v_num number := 1;
begin
  loop
    if v_num > 10 then
      exit;
    end if;
    dbms_output.put_line(v_num);
    v_num := v_num + 1;
  end loop;
end;

• 有条件循环

declare
  v_num number := 1;
begin
  while v_num<=10
  loop
    dbms_output.put_line(v_num);
    v_num := v_num + 1;
  end loop;
end;

• for循环

begin
  for v_num in 1 .. 10
  loop
    dbms_output.put_line(v_num);
  end loop;
end;

5、无参游标

游标的属性	返回类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的 FETCH语句返回一行数据则为真,否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真,否则为假

-- 使用游标查询emp表中所有员工的姓名和工资，并依次打印出来
declare
  -- 声明游标
  cursor c_emp is
    select ename, sal from emp;
  -- 声明变量，接收游标中的数据
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  --打开游标
  open c_emp;
  --遍历游标
  loop
    --获取游标中的数据
    fetch c_emp
      into v_name, v_sal;
    --退出循环条件
    exit when c_emp%notfound;
    dbms_output.put_line('姓名：' || v_name || ',工资：' || v_sal);
  end loop;
  --关闭游标
  close c_emp;
end;

6、带参游标

• 带参游标

-- 使用游标查询emp表中某一部门员工的姓名和工资，并依次打印出来
declare
  -- 声明游标
  cursor c_emp(v_deptno emp.deptno%type) is
    select ename, sal from emp where deptno = v_deptno;
  -- 声明变量，接收游标中的数据
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  --打开游标
  open c_emp(20);
  --遍历游标
  loop
    --获取游标中的数据
    fetch c_emp
      into v_name, v_sal;
    --退出循环条件
    exit when c_emp%notfound;
    dbms_output.put_line('姓名：' || v_name || ',工资：' || v_sal);
  end loop;
  --关闭游标
  close c_emp;
end;

• for循环带参游标

declare
  cursor cur_students(v_class_id number) is
    select * from students where class_id = v_class_id;
  --v_students students%rowtype;
begin
  for v_students in cur_students(2) 
  loop
    dbms_output.put_line(v_students.stu_id || '-' || v_students.name ||
                         v_students.age || '-' || v_students.gender || '-' ||
                         v_students.class_id);
  end loop;
end;

7、无参存储过程

create or replace procedure proc_hello is
begin
  dbms_output.put_line('hello world');
end proc_hello;

调用：PLSQL程序中调用

begin
  proc_hello;
end;

命令行调用，通过exec命令调用。（注意打开输出开关）

SQL> set serveroutput on
SQL> exec proc_hello;
 
hello world
 
PL/SQL procedure successfully completed

注意：
1）is和as是可以互用的,用哪个都没关系的
2）过程中没有declare关键字，declare用在语句块中

8、带输入参数的存储过程

--查询某个员工的姓名和工资，传入员工号。
create or replace procedure proc_query_name(v_empno in emp.empno%type) is
  --声明变量
  v_name emp.ename%type;
  v_sal  emp.sal%type;
begin
  --通过员工号查询emp表中的姓名和工资，并赋给变量。
  select ename, sal into v_name, v_sal from emp where empno = v_empno;
  dbms_output.put_line(v_name || '_' || v_sal);
end proc_query_name;

9、带输入、输出参数的存储过程

--查询某个员工的姓名和工资，传入员工号;返回该员工工资。
create or replace procedure proc_querysal_out(i_empno in emp.empno%type,
                                              o_sal   out emp.sal%type) is
begin
  --通过员工号查询emp表中的姓名和工资，并赋给输出变量。
  select sal into o_sal from emp where empno = i_empno;
end;

调用方式：

• PLSQL程序调用

declare 
--声明变量接收存储过程中的输出参数
v_sal emp.sal%type;
begin
  proc_querysal_out(7369,v_sal);
  dbms_output.put_line(v_sal);
end;

输出：800

10、异常处理

declare
  v_name students.name%type;
  v_class_id students.class_id%type;
  v_class_name class.name%type;
begin
  select name,class_id into v_name,v_class_id from students where stu_id = 1005;
  select name into v_class_name from class where id=v_class_id;
  DBMS_OUTPUT.put_line('姓名：' || v_name ||',班级：' || v_class_name);
end;

declare
  v_name students.name%type;
  v_class_id students.class_id%type;
  v_class_name class.name%type;
begin
  select name,class_id into v_name,v_class_id from students where stu_id = 1010;
  select name into v_class_name from class where id=v_class_id;
  DBMS_OUTPUT.put_line('姓名：' || v_name ||',班级：' || v_class_name);
end;

declare
  v_name students.name%type;
  v_class_id students.class_id%type;
  v_class_name class.name%type;
begin
  select name,class_id into v_name,v_class_id from students where stu_id = 1010;
  select name into v_class_name from class where id=v_class_id;
  DBMS_OUTPUT.put_line('姓名：' || v_name ||',班级：' || v_class_name);
exception
  when no_data_found then
       dbms_output.put_line('没有找到学生信息');
end;