PL/SQL语言语法

一、前言

SQL全称是“结构化查询语言(Structured Query Language)”,而PL/SQL是过程语言(Procedure Language),是对SQL的扩展。PL/SQL语言比任何其他程序设计语言(C、C++、java等)操作数据库的效率要高。

二、基本语法结构

declare --定义部分:包括定义变量、游标
    /* 定义游标。有游标就肯定有打开游标、循环、关闭游标
     * 小技巧:定义完游标后,游标需要的变量马上在后面定义,如定义ename,sal。
     */
    cursor cemp is select ename,sal from emp; 
    pename emp.ename%type; --定义引用型变量(即引用emp表的ename列的类型,ename是什么类型pename也跟着是什么类型)
    psal number; --定义一般变量
    /* 定义记录型变量(以 _rec 结尾):代表一行数据。如:select * into emp_rec from emp where empno='2'; 
     * 使用:emp_rec.ename,emp_rec.sal
     */
    emp_rec emp%rowtype; 

begin
  --打开游标
  open cemp;
    loop
      fetch cemp into pename,psal; --获取一行数据
      exit when cemp%notfound; --退出条件
      
      dbms_output.put_line(pename||'的薪水是:'||psal);
     
    end loop;
  --关闭游标
  close cemp;

  exception
    when... then...
    when others... then...

end;
/

1. 定义游标

cursor 游标名 [(参数名 数据类型[,参数名 数据类型]...)] is select 语句;
如:
定义:corsor cemp(dno number) is select ename from emp where deptno=dno;
打开:open cemp(2);

2. 循环(注意退出条件)

  • loop循环

      [<<label_name>>]
      LOOP
       [EXIT...WHEN...]
       [IF...EXIT]
       statement...
      END LOOP [label_name]
      
      【语法说明】
      <<label_name>>:LOOP结构的标签,是可选项。
      LOOP:LOOP循环开始标志。
      statement:LOOP语句中循环执行的语句序列。
      END LOOP:LOOP循环结束标志,可以添加LOOP结构的标签。
    
  • for...loop循环

      [<<label_name>>]
      FOR index_name IN
        [ REVERSE ]
        lower_bound .. upper_bound
        LOOP
        statement...
      END LOOP [label_name];
    
      【语法说明】
      index_name:循环计数器,是一个变量,它可以得到当前的循环指数。需要注意的是,不能为其手工赋值。
      REVERSE:可选项,指定循环方式。默认的循环方式由下标(lower_bound)到上标(upper_bound)。使用该选项则从上标界到下标界。
      lower_bound:循环范围的下标界。
      upper_bound:循环范围的上标界。
      下标和上标之间的".."不能省略。
    
      for example:
      1. for i in 1..10 loop
      2. select eno into empNo from emp;
         for n in empNo loop
    
  • while...loop循环

      [<<label_name>>]
      WHILE boolean_expression
      LOOP
        statement...
      END LOOP [label_name];
    
      【语法说明】
      boolean_expression:布尔表达式。
      statement:语句序列,当boolean_expression为TRUE时,该语句序列可获得执行权。
    

3. 条件判断

  • if...elsif

      IF 条件1 THEN 
        statement...
      ELSIF 条件2 THEN 
        statement...
      ELSE 
        statement...
      END IF;
    
  • case

      CASE [selector]
        WHEN 表达式1 THEN 语句序列1;
        WHEN 表达式2 THEN 语句序列2;
        WHEN 表达式3 THEN 语句序列3;
        ……
        [ELSE 语句序列N];
      END CASE;
    

三、写PL/SQL思路

先把以下两个思路根据需求弄清楚再写sql,这样思路会比较清晰,程序也比较不容易出错

1. SQL语句

for example:  
select ename,sal from emp;
游标 --> 循环 --> 退出条件(notfount or other)

2. 变量(初始值、如何得到需要的值)

根据需求确定需要什么变量。计算变量的值的时候,能用算术计算的就尽量用,少用sql,提高效率。如计算总工资,如果循环过程有各个职工的工资,累加即可,少用sum()。

四、属性

  • 游标属性:%found、%notfound、%isopen、%rowcount:影响的行数,即当前游标执行了多少行,并不是查询数据的总行数
  • 游标的限制:默认情况下,oracle只允许在同一个会话中,打开300个游标。
    修改游标数据限制:
    alter system set open_cursor=400 scope=both;
    scope的取值:both,memory,spfile。(spfile数据库需重启,修改配置文件;memory只在当前会话生效,both包括其他2个)
posted @ 2017-10-15 10:37  Sky.宾桀锋  阅读(205)  评论(0编辑  收藏  举报