PLsql基础语法学习记录

PLsql中的基础语法

持续更新中。。。

1. 程序结构

PLSQL语言的是不区分大小写的，PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。

declare
--声明变量，游标（结果集类型变量），程序中无声明变量则可以省略
    i integer;
begin
    --执行语句
    --异常处理
end;

2. 打印输出

begin
    dbms_output.put_line('hello world!');
end;

其中DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out,而PUT_LINE（）是调用的方法,相当于println（）方法

3. 声明变量

PLSQL编程中常见的变量分两大类:

• 普通数据类型 (char,varchar2, date, number, boolean, long)
• 特殊变量类型（引用型变量、记录型变量）

声明变量的方式为 变量名 变量类型（变量长度）
例如： v_name varchar2(20);


基本数据类型：

3.1 普通变量

变量赋值的方式有两种：
1、直接赋值语句 := 比如:v_name := ‘zhangsan’
2、语句赋值,使用 select…into… 赋值：（语法 select 值 into 变量）

【例】 通过select来声明普通变量，并将结果输出在屏幕上：

--PLsql声明普通变量
declare 
	v_sal number(10,2); --数字类型的变量，长10位，2位小数
	v_email varchar2(20);
	v_hire_date date;
begin
	select salary,email,hire_date
	into v_sal,v_email,v_hire_date  --plsql的语法是select...into...
	from emp
	where emp_id = 1;  --查找到id=1 的员工的信息，把员工信息赋予到变量中 
	--plsql不能直接select from，只能用这种DML式语法
	dbms_output.put_line(v_sal||','||v_email||','||v_hire_date); --打印输出
end

3.2 引用型变量

变量的类型和长度，取决于表中字段的类型和长度。
通过 表名.列名%TYPE 指定变量的类型和长度
【例】：声明员工姓名、员工薪水两个字段，可以用以下的语法

declare 
    v_name emp.aname%TYPE;
    v_salary emp.esalary%TYPE;
begin
    select ename,esalary
    into v_name,v_salary
    from emp
    where rownum = 1;
end;

3.3 记录型变量

接受表中的一整行记录,相当于Java中的一个对象
语法：变量名称 表名%ROWTYPE

declare
    v_emp emp%ROWTYPE;
begin
    select *
    into v_emp
    from emp
    where rownum = 1;
    dbms_output.putline(v_emp.ename||','||v_emp.esalary);
    -- 用我们定义地记录变量名直接点出来
end
-- || 是字符串连接的意思

如果有一个表,有100个字段,那么你程序如果要使用这100个字段话,
如果你使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题。
记录型变量只能存储一个完整的行数据，只能一行，多行就报错.

3.4 记录类型的变量

基本语法格式：
TYPE 类型名 is record(
变量名1，
变量名2，
…
);

declare --记录类型的变量
    type emp_record is(
        v_ename emp.ename%type,
        v_sal emp.esal%type
    );
    e emp_record;

    v_x number(3) := 5 ;
begin
    select ename,sal into e from emp
    where empno = 394;
    dbms_output.put_line(e.v_ename||','||e.v_sal);
end;

3.5 异常处理

异常处理被写进 begin…end; 里面，常见的异常类型有：

语法格式如下：

declare 
    v_ename varchar2(30);
begin
    select ename into v_ename from emp
    where empo = 123;
exception 
    when no_data_found then
    dbms_output.put_line('查无此人');
end;

4.流程控制

4.1 选择条件分支

begin
    if 条件1 then 执行1
    elsif 条件2 then 执行2
    else 执行3
    end if;
end;
--注意关键字 elsif

4.2 loop循环

loop循环

begin 
    loop
    exit when 退出循环条件
    end loop;
end;

例如
【示例】打印数字1-10

declare 
    num := 1;
begin
    loop
        exit when num>10;
        dbms_output.put_line(num);
        num = num + 1;
    end loop;
end;

4.3 while 循环

while循环语句的一般形式：

while <布尔表达式> loop
	要执行的语句；
end loop;

其中，循环语句的执行顺序是先判断<布尔表达式>的真假，如果为真则循环执行，否则退出循环。
在while语句中仍然可以使用exit和exit when子句。

5. 游标

用于临时存储一个查询返回的多行数据(结果集，类似于Java的Jdbc连接返回的ResultSet集合，Java中的迭代器),通过遍历游标，可以逐行访问处理该结果集的数据。
游标是一个私有的SQL工作区,分为隐式游标和显示游标,我们通常声明的是显示游标。

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据整行
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假（能拿到数据并且能返回一条数据，则真）
%NOTFOUND	布尔型	与%FOUND属性返回值相反（一般用于退出循环的判断）
%ISOPEN	布尔值	游标已经打开时值为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE，通常用来判断退出循环。

游标的创建和使用：

1. 声明游标: cursor 游标名 is 查询结果集
2. 打开游标: open 游标名
3. 从游标中取出数据: fetch 游标名 into 变量名
   游标名%found: 找到数据
   游标名%notfound: 没有找到数据
4. 关闭游标

5.1 带参数的游标

【例】带参数的游标：

declare
  emp_rows emp%rowtype;
  -- 1.
  cursor v_row is select * from emp;
begin
  -- 2.
  open v_row;
  	-- 2.1
    loop
      -- 3.
      fetch v_row into emp_rows;
      -- 3.1
      exit when v_row%notfound;
      dbms_output.put_line(emp_rows.ename);
    -- 3.2
    end loop;
  -- 4.
  close v_row;
end;

5.2 不带参数的游标

【示例】使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
【例】不带参数的游标

declare -- 声明游标  --没有参数的游标
    cursor c_emp is select ename,esalary from emp; --查询语句
    --定义游标的名字
    v_ename emp.ename%TYPE;
    v_salary emp.esalary%TYPE;  -- 声明变量接受游标中的数据
begin
    open c_emp; --打开游标
    loop   -- 遍历游标
        fetch c_emp;
            into v_ename,v_salary; --将获得到的数据复制到我们定义的变量
            exit when c_emp%notfound; --游标没数据了返回为true
            dbms_output.put_line('姓名：' || v_ename || '薪水：' || v_salary);
    end loop; --循环结束
    close c_emp; -- 关闭游标
end;

6. 存储过程

JAVA可以通过封装对象和方法，来解决复用问题。
PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的PLSQL程序称之为存储过程。

create or replace procedure 过程名[(参数列表)] is
-- 没有使用declare声明变量，但是可以在begin上边直接声明变量
    begin 
    --执行部分
    end [过程名];
根据参数的类型,我们将其分为3类:
    |不带参数
    |带输入参数的
    |带输入输出参数(返回值)的

6.1 不带参数的存储过程

create or replace procedure P_PRINT_HELLO is
begin 
    dbms_output.putline('HELLO');
end P_PRINT_HELLO; --加上存储过程的名字，结束

调用过程，新建一个程序窗口：
BEGIN
P_PRINT_HELLO;
end;

6.2 带输入参数的存储过程

功能输入员工名字，打印HELLO员工名。

--P_PRINT_HELLO 过程名字,in代表是输入的参数，
--后面EMP.ENAME%TYPE是变量V_ENAME的类型,is可换为as
create or replace procedure P_PRINT_HELLO(v_ename in emp.ename%TYPE) is
--声明变量
BEGIN 
    dbms_output.putline('HELLO ' || v_ename);
    --输出hello和你调用这个过程传入的参数
end P_PRINT_HELLO;

6.3 带输出参数的存储过程

输入员工名字，将“HELLO员工名”当作返回值输出,in输入参数,out输出参数

 create or replace procedure P_PRINT_HELLO (
 	V_ENAME IN EMP.ENAME%TYPE,V_RETURN out VARCHAR2) is
      --声明变量
    begin
       V_RETURN:='HELLO '||V_ENAME;   -- :=普通变量赋值
    end P_PRINT_HELLO;

语法:
  create or replace procedure 存储过程名(参数名称 in 参数类型,返回值名称 in 返回值参数类型) is
  -- 定义变量
  begin
  
  end;


-- 实例一: 无参数无返回值
create or replace procedure run1 is
-- 声明变量
begin
  dbms_output.put_line('run1');
end;
-- 调用
begin
  run1;
end;

-- 实例二: 有参数无返回值
create or replace procedure run2(i_sid in student.tid%type) is
-- 声明变量
  v_sname student.sname%type;
  v_sid student.tid%type;
begin
  select tid,sname into v_sid,v_sname from student where tid = i_sid;
  dbms_output.put_line(v_sid);
  dbms_output.put_line(v_sname);
end;
-- 调用
begin
  run2(1);
end;

-- 实例三: 有参数有返回值
create or replace procedure run3(i_sid in student.tid%type,i_sname out student.sname%type) is
begin
  select sname into i_sname from student where tid = i_sid;
end;
-- 调用
declare
  v_sname student.sname%type;
begin
  run3(1,v_sname);
  dbms_output.put_line(v_sname);
end;

7 函数

函数可以接受零个或者多个输入参数，并且函数必须有返回值。
语法格式：

create [or replace] function fun_name[(parameter1[,parameter2]...) 
return data_type 
is
  [inner_variable]
begin
  function body;
[exception]
  [dowith_sentences;]
end [fun_name];

由于函数有返回值，所以在函数主体部分（即begin部分）必须使用return语句返回函数值，并且要求返回值的类型要与函数声明时的返回值类型（即data_type）相同。

【删除函数】

drop function fun_name;

7.1 不带参数的函数

--构建函数
create or replace function getDate
return varchar2
is
    v_date varchar2(12);
begin
    v_date := to_char(sysdate,'yyyy-mm-dd');
    return v_date;
end;

--调用函数：
declare 
    v_date varchar2(12);
begin
    v_date := getDate;
    dbms_output.put_line(v_date);
end;

7.2 带参数的函数

获取某部门的工资总和：

create or replace function get_salary(