PL/SQL基本语法

PL/SQL的基本组成与命名规则

PL/SQL是对于SQL语言的拓展，它不仅允许各种SQL语言的使用，还可以定义变量和常量，以及使用条件，循环来帮助逻辑上的运行，是一种过程化的编程语言

PL/SQL结构分为三大部分

 DECLARE
     --声明 在此声明编程用到的变量名称与其类型，游标，以及存储过程和函数
 BEGIN
     --执行 运行逻辑部分，代码主体
 EXCEPTION
     --执行异常 出现错误之后，如何进行显示与处理
 END;

其中，只有begin和end是必须的，只有begin，end一样可以运行

在自定义标识符的时候，与SQL要求一致，首字符必须为字母，可使用下划线，不能是系统自带的保留字

同时，为了让代码具有较高的可读性，PL/SQL中变量有对应的命名规则，虽然说不是必须按格式命名才可以运行，但是建议如下命名

• 程序变量 v_name

• 程序常量 c_name

• 游标 cursor_name

• 异常 e_name

• 表类型 name_table_type

• 表 name_table

• 记录类型 name_record

• 申明中的替代变量 p_name

• 绑定变量 g_name

常用例：

 --自定义变量及类型
 declare
     v_ename varchar2(20);
 begin
     select ename into v_ename from emp where empno=7499;
      dbms_output.put_line('名字是'||v_ename);--打印内容
 end;
 ​
 --使用原表中的类型，不自定义类型
 declare
     v_ename emp.ename %type;
 begin
     select ename into v_ename from emp where empno=7369;
      dbms_output.put_line('名字是'||v_ename);
 end;
 ​
 --自定义记录类型
 declare 
     type emp_record is record(
         v_ename emp.ename%type,
         v_deptno number(6)
         );
     v_emp_record emp_record;--声明记录类型的变量
 begin 
     select ename,deptno into v_emp_record from emp where empno=7499;
     dbms_output.put_line(v_emp_record.v_ename||','||v_emp_record.v_deptno);
 end;
 ​
 --使用原记录类型
 declare 
     e emp%rowtype;
 begin
     select * into e from emp where empno =7369;
     dbms_output.put_line(e.ename||','||e.deptno);
 end;

if语句

其实与SQL中非常相似，只是需要申明

示例：

declare
     v_empno emp.empno%type:=&empno;--这里的&后内容为显示内容，可以随意替换，让对方知道输入什么就好
     v_ename emp.ename%type;
     v_sal emp.sal%type;
 begin
     select ename,sal into v_ename,v_sal from emp where empno=v_empno;
     if v_sal<1500 then
     dbms_output.put_line('不行啊'||v_ename||'李在干神魔');
     elsif v_sal<3000 and v_sal>=1500 then--这里是elsif不是elseif
     dbms_output.put_line('挺行啊'||v_ename||'大兄弟');
     else
     dbms_output.put_line('真牛逼啊');
     end if;--if到最后一定要以end if收尾，并且不能少这个分号
 end;

循环 for while

最简单的循环就是直接loop：

 declare 
     v_num number(5):=1;--定义变量的时候直接给值，注意这里不是=而是:=
 begin
     loop
         dbms_output.put_line('当前是'||v_num||'次');--需要键入中文时给拼接符||
         v_num := v_num+1;
         exit when v_num=10;--退出loop条件
     end loop;--关闭loop
 end;

while循环：

 declare
     v_num number(30):=1;
 begin
     while v_num<10 loop--退出条件相当于挪到了上面
     dbms_output.put_line('当前是'||v_num||'次');
     v_num:=v_num+1;
     end loop;--只要用到loop，一定记得关闭
 end;

for循环：

 declare
     v_num number(3):=0;
 begin
     for fasdfadf in 2 .. 4 loop--不用定义
     dbms_output.put_line('当前是'||v_num||'次');
     v_num:=v_num+1;
     end loop;
 end;

这里注意，假设for后的语句，如果仅仅为了表示计数，循环多少次，那么for后的变量无需定义且可以是任意变量，in后的数字也无意义，仅代表次数，2..4为3次，那么换成4..6也为3次。

但是如果将for后变量替换为有意义的变量v_num，那么in后数字不仅代表循环次数，还代表进入循环的v_num的值，那么循环中就不必对v_num再进行操作，因为其值已经固定

 declare
     v_num number(3):=0;
 begin
     for v_num in 2..4 loop
     dbms_output.put_line('当前是'||v_num||'次');
     end loop;
 end;

可以和插入表格结合起来：

create table temp_table(num_col number);
 declare
     v_num number(4):=1;
 begin
     insert into temp_table values(v_num);
     for v_num in 22..24 loop
         dbms_output.put_line('当前是'||v_num||'次');
         insert into temp_table values(v_num);
     end loop;
     for v_num in reverse 22 ..24   loop --加上reverse就是反向变化
         insert into temp_table values(v_num);
         dbms_output.put_line('而当前是'||v_num||'次');
     end loop;
 end;

游标

游标为指向查询结果的指针，通过读取游标我们可以直接读取出查询结果

游标的使用就分三步，先定义，再打开，最后读取出结果，直接上例子最好理解

 --查询10号部门的员工姓名工资
 declare
     v_ename emp.ename%type;
     v_sal emp.sal%type;
     v_num number(4) :=&部门号;
     cursor emp_cursor is select ename,sal from emp where deptno=v_num;--在申明中定义游标类型，这个游标要读什么数据，从哪读
 begin
     open emp_cursor;--先打开游标
     fetch emp_cursor into v_ename,v_sal;--前面已经定义过，我这个游标要读ename，sal，所以这里直接从游标中抓对应数据存到变量里
     while emp_cursor%found loop--游标属性，如果最近一次fetch抓取成功过为true，运行loop
         dbms_output.put_line(v_ename||', '||v_sal);
         fetch emp_cursor into v_ename,v_sal;--我再向后抓取一次，为了下一次while语句中的判断能正常执行
     end loop;--while那一行判断为false时end loop跳出循环
     close emp_cursor;--记得关闭一下游标
 end;

再与for循环相结合，游标for循环

 declare
     v_num number(4) :=&部门号;--甚至不用申明姓名，工资，因为用的i.ename,i,sal
     cursor emp_cursor is select ename,sal from emp where deptno=v_num;
 begin
     --for循环加游标，不用打开游标，for循环聪明的很，他会自己打开
     for i in emp_cursor loop
         dbms_output.put_line(i.ename||','||i.sal);
     end loop;
     --正是因为没有打开游标，所以也不用关闭
 end;

如果想要添加，修改，删除数据，可以使用隐式游标

 declare
 begin
     update emp set sal=sal-100 where deptno=10;
     if sql%found then --此时sql为隐式游标
          dbms_output.put_line('更新了'||sql%rowcount||'行数据');--sql%count返回行号
     else
         dbms_output.put_line('更新了，但是没有完全更新');
     end if;
 end;    

异常处理

在程序运行出现异常的时候，每一种异常都有相应的错误号显示，但是只有其中24种最常见的，oracle给予他们定义了名称，其他种类的错误只有错误号，没有对应名称

 declare 
     v_ename emp.ename%type;
 begin 
     select ename into v_ename from  emp where deptno = 10;
     dbms_output.put_line(v_ename);
 exception
     when too_many_rows then --这里写错误的名称，too_many_rows为oracle已定义名称，可以直接使用
     dbms_output.put_line('太多行了啊');--最终就不会显示大量的错误报告，而只打印这一行
 end;

如果是没有对应名称的错误，则为非预定义异常，就需要自己进行名称定义，然后使用exception处理

 declare
     e_exception exception; --先定义异常名称
     pragma exception_init(e_exception,-02291);--将错误号与异常名称绑定，注意错误号只能输入数字部分，否则会无法读取
 begin
     insert into emp values(119,'admim',null,null,null,null,null,50);
 exception
     when e_exception then
     dbms_output.put_line('你在干神魔，这都能搞出bug来');
 end;

函数与过程

oracle可以把一段程序储存在数据库中，在需要的时候可以直接运行，这就是函数和过程，两者最大的区别在于，函数有返回值，而过程没有返回值

例子是最好的老师

--通过员工的编号，返回员工的姓名，带参数的函数
 create or replace function getname11(p_empno number)--关键字function为函数，p——empno为形参，占好一个number位置
 return varchar2
 is--相当于declare
     v_ename emp.ename%type;--这里的v_ename和之前一样，为实参
 begin
     select ename into v_ename from emp where empno=p_empno;
     return v_ename;
 end;--函数创建完成
     
 --运行函数，这里只用了函数的返回值，不需要其他值的写入，可以不申明任何变量，直接调用函数得到返回值即我们所需
 begin
     dbms_output.put_line(getname11(7499));
 end;
 ​
 ​
 --获取某个部门的人数和工资总和
 create or replace function count1(p_deptno number,p_sum out number)
 return number--这里需要得到部门人数和工资总和两个内容，但是返回值只有一个，所以就返回一个，另一个放在函数里面，注明为out量
 is
     v_count number(10);
 begin
     select count(*),sum(sal)into v_count,p_sum from emp where deptno=p_deptno group by deptno;
     return v_count;
 end;
 ​
 --运行上面函数count1
 declare
     v_count number(5);
     v_sum number(10);
     v_deptno number(5):=&deptno;
 begin
     v_count:=count1(v_deptno,v_sum);--先运行一遍函数，用一个v_count接住count返回值，同时把v_sum丢进函数中一同运行，因为注明为out值，所以会将sum值写入v_sum
     dbms_output.put_line('人数为'||v_count||',工资总和为'||v_sum);
 exception when no_data_found then
     dbms_output.put_line('没有找到数据！');
 end;

存储过程除了自身不能返回任何值之外，和函数基本完全一致，利用注明为out量的变量返回数值，关键字为procedure

 create or replace procedure gettime22(p_date out varchar2)
 is  
 begin
     p_date:=to_char('sysdate','yyyy-MM-dd day hh24-mi-ss');
 end;
 ​
 --运行
 declare
     v_date varchar(50);
 begin
     dbms_output.put_line(v_date);
 end;