leo_logic

Oracle高阶课笔记
温馨提示:--默认打印功能是关闭的 SQL> set serverout on --开启 SQL> S...
扫描右侧二维码阅读全文
15
2019/03

Oracle高阶课笔记

温馨提示:

--默认打印功能是关闭的
SQL> set serverout on --开启
SQL> SET SERVEROUTPUT  OFF --关闭
--显示错误
SQL> show errors 

一、PL/SQL(轻量版)_基本语法

1.语法初识
1.1语法模板

declare
  --声明变量、类型、游标
begin
  --程序执行部分(类似于Java里的main方法)
  dbms_output.put_line('helloWorld');
--exception
   -- 针对begin块中出现的异常,提供处理机制
   --when ··· then ···
end;
 --多行注释可用/**/

1.2建议命名规则

标识符命名规则例子
程序变量v_nameV_name
程序常量C_nameC_company_name
游标变量Name_cursorEmp__cursor
异常标识E_nameE_too_many
表类型Name_table_typeEmp_record_type
Name_tableEmp
记录类型Name_recordEmp_record
SQL*PLUSP_nameP_sal
绑定变量G_nameG_year_sal

1.3例子:

--原版代码01
declare
  --声明变量,变量类型和表中数据类型最好一致
  v_sal number(20,2);
  v_email varchar2(20);
  v_hire_date date;
begin
  --sql语句
  SELECT salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id=100;
  --注意上边代码salary,email,hire_date into v_sal,v_email,v_hire_date这部分前后要一一对应
  --打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

上面这段代码有个麻烦得地方就是在变量声明得时候还得查原数据类型,那么如何解决这个问题呢?
这里我们尝试另外一种声明方式: v_sal number(20,2);改为employees.salary%type;
例如下面这段代码就不需要去关注原类型了,交给Oracle去判断:

--迭代代码02
declare
  --声明变量
  v_sal employees.salary%type; --迭代部分
  v_email employees.email%type;  --迭代部分
  v_hire_date employees.hire_date%type;  --迭代部分
begin
  --sql语句
  SELECT salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id=100;
  --打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

2.记录类型:
逻辑相关得数据作为一个单元储存起来并作用是存放不相同但逻辑相关得信息。类似于Java得类。
语法:

    **TYPE** record_type IS **RECORD**(   --注意**是着重部分
        Field1 type1 [NOT NULL] [:= exp1],
                    ···
        Fieldn typen [NOT NULL] [:= expn]
    );
--迭代代码03
declare
  --声明变量
type emp_record is record(  --迭代部分
  v_sal employees.salary%type,  -- 注意这里是分号
  v_email employees.email%type,
  v_hire_date employees.hire_date%type -- 注意这里没加逗号
  ); --注意这儿是分号
  -- 定义一个记录类型得成员变量
  V_emp_record emp_record;  --迭代部分
begin
  --sql语句
  SELECT salary,email,hire_date into V_emp_record from employees where employee_id=100;  --迭代部分
  --打印
  dbms_output.put_line(V_emp_record.v_sal||','||V_emp_record.v_email||','||V_emp_record.v_hire_date);  --迭代部分
end;

3.第一阶段阶段复习:
3.1PL/SQL基本语法格式
3.2变量很多得情况将变量放到记录类型里type ··· is record(,,,);
3.3流程控制:

  ①条件判断:(两种)
       方法一:if···then elsif then ··· else ··· end if;
       方法二:case ··· when ··· then··· end;         
       
  ②循环结构:(三种)
       方式一:loop ··· exit when ··· end loop;
       方式二:while ··· loop ···end loop;
       方式三:for i in ··· loop ··· end loop;
  goto、exit;

3.4游标得使用(类似于Java中得Iterator,遍历集合功能)
3.5异常得处理(三种方式)
目的是掌握以下6点和7点:
3.6会写一个存储函数(有返回值),存储过程(无返回值)(存储函数会保存在function文件夹中)
3.7会写一个触发器
3.8复习代码:

declare
  --变量类型等
  v_sal number(8,2):=0;  --Oracle中赋值用的:=
begin
  select salary into v_sal 
  from employees 
  where employee_id = 123;
  
  dbms_output.put_line('salary:'||v_sal);

end;
  • 直接申明表中所有信息进行查询:
declare
  V_emp_record employees%rowtype;  
begin
  --sql语句
  select * into V_emp_record
  from employees
  where employee_id = 100;  --迭代部分
  --打印dbms_output.put_line(V_emp_record.employee_id||','||V_emp_record.salary||','||V_emp_record.email);  --迭代部分
end;
SQL修改操作
declare
  v_emp_id number(10);
begin
  v_emp_id :=123;
  update employees
  set salary = salary+100
  where employee_id = v_emp_id;
  dbms_output.put_line('执行成功');
end;

3.9本小结中出现的%ROWTYPE操作符,返回一个记录类型,其数据类型和数据表的数据结构相一致。
使用%ROWTYPE特性的优点在于:

  • 所有引用的数据库中列的个数和数据类型可以不必知道;
  • 所有引用的数据库中列的个数和数据类型可以实时改变。
    4.数据库赋值

    数据库赋值是通过SELECT语句来完成的,每次执行SELECT语句赋值一次,一般要求赋值的变量与SELECT中的列名要一一对应。如:
    declare 
        emp_id emp.empno%TYPE := 7788;
        emp_name emp.ename%TYPE;
        wages emp.sal%TYPE;
    begin
        SELECT ename,NVL(sal,0) + NVL(comm,0) INTO emp_name,wages
        FROM emp WHERE empno = emp_id;
    DBMS_OUTPUT.PUT_LINE(emp_name||'---'||to_char(wages));
    END;

提示:不能将SELECT语句中的列赋值给布尔变量。

二、Oracle流程控制

①条件判断(两种):

方式一:if...then elif then ... else ... end if;
方式二:case ... when ... then ... end;

②循环结构(三种):

方式一:loop...exit when ... end loop;
方式二:while ... loop ... end loop;
方式三:for i in ... loop ... end loop;

1.Oracle判断结构

/*
1.1.1例题:
if解题
查询150号员工的工资 **说明工资是变量**
若工资大于或等于10000则打印‘salary >=10000’ 
若工资在500-10000之间,则打印‘ 5000 <= salary < 10000’
否则打印‘salary<5000’
*/
declare
  v_sal employees.salary%type;
begin
  SELECT SALARY into v_sal FROM employees WHERE employee_id = 150;

  IF v_sal >= 1000 then dbms_output.put_line('salary >=10000');
  ELSIF v_sal >= 5000 then dbms_output.put_line('5000 <= salary < 10000');
  ELSE dbms_output.put_line('salary<5000');
  END IF;
end;
1.1.2例题:
查询150号员工的工资 **说明工资是变量**
若工资大于或等于10000则打印‘salary >=10000’ 
若工资在500-10000之间,则打印‘ 5000 <= salary < 10000’
否则打印‘salary<5000’
case解题版
*/
declare
  v_sal employees.salary%type;
  v_temp varchar2(30);
begin
  SELECT SALARY into v_sal FROM employees WHERE employee_id = 150;
  
  v_temp:=
  case trunc(v_sal/5000) when 0 then 'salary<5000'
                         when 1 then '5000<=salary<10000'
                         ELSE 'salary>=10000'
             
  END;
       dbms_output.put_line(v_temp);     
end;
/*
1.2.1列2:
找出id为123的员工职位对应的A,B,C,D
*/
-- if结构
declare 
  --声明变量
  v_job_id varchar2(30);
  v_tmp varchar2(30);
begin
  SELECT JOB_ID into v_job_id FROM employees WHERE employee_id = 123;
  IF v_job_id = 'IT_PROG' then v_tmp:='A';
  ELSIF v_job_id = 'AC_NCT' then v_tmp:='B';
  ELSIF v_job_id = 'AC_ACCOUNT' then v_tmp:='C';
  ELSE v_tmp:='D';
  END IF;
  dbms_output.put_line(v_tmp);
END;

2.循环

--打印1-100(版本1loop):
--Java循环:①初始化条件②循环体③循环条件④迭代条件
declare 
  --①初始化条件
  v_i number(5):=1;
begin
  loop
    --②循环体
    dbms_output.put_line(v_i);
   --③循环条件
   exit when v_i > 100;
        --④迭代条件
        v_i := v_i+1;
   end loop;
END;
--打印1-100(版本2while)
--Java循环:①初始化条件②循环体③循环条件④迭代条件
declare 
  --①初始化条件
  v_i number(5):=1;
begin
  while v_i <=100 loop --③循环条件
    --②循环体
    dbms_output.put_line(v_i);
        --④迭代条件
        v_i := v_i+1;
   end loop;
END;
--打印1-100(版本3for)
--Java循环:①初始化条件②循环体③循环条件④迭代条件
declare 
  --①初始化条件
  v_i number(5):=1;
begin
  for [reverse] c in 1..100 loop  --有了reverse关键词就可以反向输出
      dbms_output.put_line(c);
  end loop;
END;
--输出1-100的质数
declare 
  v_i number(3):=2;
  v_j number(3):=2;
  v_flag number(1):=1;
begin
  while v_i <=100 loop --外层循环循环到100
        while(v_j <= sqrt(v_i)) loop --内层循环循环到sqrt(v_i))结束,sqrt的作用是取得n的平方根
                  if mod(v_i,v_j)=0 then v_flag :=0;
                  end if;
                  v_j := v_j + 1;
        end loop;
        
        if v_flag = 1 then dbms_output.put_line(v_i);
        end if;
        v_j :=2;
        v_i :=v_i + 1;
        v_flag :=1;
  end loop;
end;
--输出1-100的质数,for循环版
declare 
  v_flag number(1):=1;
begin
  for v_i in 2..100 loop
      for v_j in 2..sqrt(v_i) loop
          if mod(v_i,v_j)=0 then v_flag :=0;
          end if;
      end loop;
      
      if v_flag = 1 then dbms_output.put_line(v_i);
      end if;
      
      v_flag:=1;
  end loop;
end;

上面这段代码还可以用到跳转的概念,引出

--输出1-100的质数,for循环版.goto label;标签转跳版
declare 
  v_flag number(1):=1;
begin
  for v_i in 2..100 loop
      for v_j in 2..sqrt(v_i) loop
          if mod(v_i,v_j)=0 then v_flag :=0;
          goto label;                        --goto label;标签转跳版
          end if; 
      end loop;
      <<label>>
      if v_flag = 1 then dbms_output.put_line(v_i);
      end if;
      v_flag:=1;
  end loop;
end;
--输出1-100的自然数,for和goto标签循环版。在50时跳出循环
declare
  v_number number(5);
begin
  for v_number in 1..100 loop
      dbms_output.put_line(v_number);
      if v_number=50 then goto label;
      end if;
  end loop;  
  <<label>>
  dbms_output.put_line('打印结束');
end;
--输出1-100的自然数,for循环和exit退出版。在50时跳出循环
declare
  v_number number(5);
begin
  for v_number in 1..100 loop
      dbms_output.put_line(v_number);
      if v_number=50 then dbms_output.put_line('打印结束'); 
      exit;
      end if;
  end loop;  
end;

存储过程:

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
    --执行体
END 存储过程名字;
--创建函数语法
create [or replace] function [schema.]function_name
(函数参数列表) --参数有IN、OUT、IN OUT三种类型;IN代表需要输入的参数,OUT代表要返回的参数,IN OUT代表即是输入参数也是返回参数。
return datetype--返回类型
[is | as ]---任选一个,后面跟pLSQL代码块
[declare]--有参数时使用关键字
begin
  --执行的SQL
end;

三、Oracle游标的使用

Last modification:March 20th, 2019 at 10:02 am
If you think my article is useful to you, please feel free to appreciate

Leave a Comment