PLSQL基础_JAVA_编程开发_程序员俱乐部

pl/sql程序块的基本结构
declare        //声明变量
begin  //程序块的主体部分
...
end；
/              //表示执行该程序

在声明变量的时候，如果变量是一个常量的话使用constant关键字并且赋值。
pl/sql中赋值的符号位为 :=（冒号跟等号）.
例如 text constant number(4)  := 1 ;
在PL/SQL中定义变量和常量的时候 需要指定其类型，比如number、varchar2等
但是到后面为该字段赋值的时候与表的字段类型不符会报错。所以通常情况下我们改成这样写。
text_number constant text.number%type := 1 ;
text_name text.name%type;
这样是意思是定义了text_name和text_number变量 为text表中name，number两个字段。这样在后面就不必担心其类型的不匹配了。
如果我们需要定义表中的所有字段，而这字段有几十个或者上百个，那这工作不是太繁琐了吗，别担心。我们还可以使用%rowtype方式。
例如：declare
emp_number constant emp.empno%type := 7900 ;
one_emp emp%rowtype;
这样我们就把emp表中的所有的字段都定义进去了，方便吧。
接下来我们来看看条件选着语句吧。语法形式如下：
   if condition then
statements
elsif condition then
statements
else
statements
end if;
其中condition表示布尔表达式，statements表示condition为true时的执行语句。if条件语句用于选择条件较小的情况。如果选着的条件很多，为了以后方便维护，建议使用case语句，语法如下。
   case search_expression
when expression1 then result1;
when expression2 then result2;
...
   else default_result;
end case;
其中search_expression表示待求值的表达式，when expression1 then result1表示如果expression1的值和待求值表达式的值相同返回result1.
如果匹配全部选项之后任然没有相等的则返回default_result.
上面这种叫普通case表达式，还有一种叫搜索case表达式。其语法如下：
case
when condition1 then result1;
when condition2 then result2;
...
else default_result;
end case;
其中condition表示判断的条件范围。
与简单case表达式比较可以发现，case关键字后面不再跟随待求值表达式，而when子句也换成条件语句（condition），其实搜索case表达式就是将待求值表达式放在条件语句中进行比较，这样就不是只能进行单个值比较了。学完这些之后我想我们应该能写个简单的sql语句了。

例题：在检阅图书信息的同时我们需要判断该书是否是新书（最近一个月出版）如果是的话显示‘new’，根据借阅的情况我们判断它是否受欢迎（一个月借阅次数5次以上）如果是显示‘hot’，如果两者都是显示‘new，hot’。根据需求写出sql语句。
select b.bookname,b.bookpress,b.booktime,(
case
when (booktime between add_months(sysdate,-1) and sysdate) and (
(select count(1) from lend l where     l.bookid = b.bookid and (l.lendtime between    add_months(sysdate,-1) and sysdate)
)>=5
    )
then 'new,hot'
when booktime between add_months(sysdate,-1) and sysdate
        then  'new'
when (booktime between add_months(sysdate,-1) and   sysdate) and (
(select count(1) from lend l where     l.bookid = b.bookid and (l.lendtime between    add_months(sysdate,-1) and sysdate)
)>=5
then 'hot'
else '空'
end
) as 信息结果
from book b ;
相信大家都能看明白吧。下面我们来学习循环语句。
循环语句一般由循环体和循环结束条件组成。
最简单的循环语句语法。
loop
    statements;
    exit condition;
end loop;
其中statements表示循环体，condition表示循环结束条件
对上面的语句进行优化 把循环结束条件放到外面去，
while condition
  loop
statements;
end loop;
现在给一个小例子，循环输出数字1到10；
declare
i binary_integer :=1;
begin
  while i<=10;
loop
  dbms_output.put_line(i);
  i := i+;
end loop;
end;
/
在sqlplus中能循环打印出1到10。
跟java语言一样还用一种用得最多的是for循环，语法如下：
for loop_variable in [reverse] lower_bound .. upper_bound
loop
  statements;
end loop;
其中loop_variable表示循环变量跟java一样不需要提前定义，in 表示取值的范围，reverse表示逆向输出，默认是顺序输出。lower_bound表示循环开始值，upper_bound表示最大值。
学习了循环的基础知识之后我们就可以学习游标的知识了，循环主要用在游标中，而游标常用于对结果集中单独的行进行操作。使用游标有4个步骤
声明游标、打开游标、检索游标、关闭游标。
使用cursor关键字声明游标。
使用open关键字打开游标
使用fetch关键字检索游标
使用close关键字关闭游标
来让我们来看个简单的游标循环
declare
   cursor emp_cursor (dept_number nuber := 20) //声明一个游标并给初始值(此处需要特别注意 后面是没有分号的 加分号报错)
   is
   select empno,ename,job,sal from emp where deptno = dept_number ;
type emp_type is record(
  empno number(4),
  ename varchar2(10),
  job varchar2(10),
  sal number(7,3)
);                                   //创建纪录类型
one_emp emp_type;
begin
   open emp_cursor(20);                      //打开游标
loop
   fetch emp_cursor into one_emp ;            //检索游标
   exit when emp_cursor%notfound ;            //当游标无返回纪录时退出循环
   dbms_output.put_line('当前检索行'||emp_cursor%rowtype||'+:'||one_emp.ename);
   end loop;                                  //结束循环
   close emp_cursor;                          //关闭游标
   end;
/
让我们看看另一种写法用for循环实现的游标
set serveroutput on
declare
  cursor emp_cursor(dept_number number := 20)
  is
  select empno,ename,job,sal from emp where deptno=dept_number;
begin
  for current_cursor in emp_cursor            //此处的for循环简化了很多的代码
    loop
      dbms_output.put_line(emp_cursor%rowcount||current_cursor.ename);
      end loop;
      end;
但是使用for循环的时候有点需要特别注意 不能对游标open、fetch、close的操作。如果游标有输入参数，则只能使用该默认值。
  使用游标循环出的数据之后我们可以做什么呢？我们可以update和delete对应的数据行。正是因为有这样的需求我们才会用到游标。
使用for update of 数据行 nowait ; //表示锁定数据行 接下来对改行进行操作
例如：
--案例：加工资，普通员工加1000，经理加3000。
--说明：经理，job='MANAGER'；其它都是普通员工。
declare
cursor emp_cursor is select * from emp for update;
BEGIN
  FOR emp_cur IN  emp_cursor
  LOOP 
     IF(emp_cur.job='MANAGER')THEN
         UPDATE emp e SET e.sal=e.sal+3000 WHERE e.empno = emp_cur.empno;
     ELSE
         UPDATE emp e SET e.sal=e.sal+1000 WHERE e.empno = emp_cur.empno;
     END IF;
  END LOOP;
END;

任何程序都有可能产生异常，pl/sql也一样，下面我们来看看它是怎么处理异常的。
语法如下：
exception
when exception1 then statements1;
when exception2 then statements2;
when others then statementN;
这语法很好理解 定义exception关键字 然后分别判断执行。
实例：
set serveroutput on
declare
age varchar2(4) := '11岁';
begin
   dbms_output.put_line('测试异常发生前内容有没有执行');
   dbms_output.put_line(cast(age as number));  //cast函数表示进行类型转换
   exception
     when value_error then dbms_output.put_line('异常提示：该字符串不能转换成数字');
     dbms_output.put_line('异常发生后内容有没有执行');
     end;
     /