pl/sql基础语法操作

oracle pl/sql语言（procedural language/sql）是结合了结构化查询与oracle自身过程控制为一体的强大语言。

语法执行块

语法结构：

[ declare 可选 声明变量部分--declaration statements (1);]begin --执行部分--executable statements (2);
[ exception --可选 异常处理--exception statements (3);]end;

语法解析：

• 声明部分：包含变量和常量的定义，这部分有关键字declare开始，如果不声明变量和产量可以省略这部分。
• 执行部分：执行部分是pl/sql块指令部分，由关键字begin开始，关键字end结束。所有可执行pl/sql语句放在这一块，该部分执行命令并操作变量。其他pl/sql块可以作为子块嵌套在该部分。pl/sql块的执行部分是必选的。注意end关键字后面要用分号结尾。
• 异常处理部分：该部分是可选的，该部分用exception关键字把可执行部分分为两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。

声明与赋值

变量

  pl/sql支持sql中的数据类型，pl/sql中正常支持number,varchar2,date等oracle sql数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。语法格式：变量名 数据类型长度 [:=初始值]declarev_name varchar2(20) := 'FULAOSHI';begindbms_output.put_line(v_name);  --打印输出  fulaoshiv_name := 'HELLO WORLD!';dbms_output.put_line(v_name);  -- hello world!end;声明部分 变量   
1. 变量名 数据类型[长度] [:=初始值]   
2. 常量名 constant 数据类型 :=常量值  
declare 
v1 number(38) :=1;
v2 number :=2;
v3 constant number :=3;
v4 number;
beginv4:=v1+v2;dbms_output.put_line(v4);  ---3  v4:=v1+v3;dbms_output.put_line(v4);    ---4 v4:=v4+v1;dbms_output.put_line(v4);     ---5/* v3:=33;   -- 常量不可以再赋值dbms_output.put_line(v3);*/v4:=33;    dbms_output.put_line(v4);   --33 
end;--用plsql 变量计算 123+456的结果 式子
declare
v1 number:=123;
v2 number:=456;
v3 number;
beginv3:=v1+v2;dbms_output.put_line(v3);dbms_output.put_line(v1||'+'||v2||'='||v3);
end;

引用型变量

%type    引用表中字段数据类型赋给变量数据类型
%rowtype 引用数据库表中的一行(所有字段)作为数据类型declare
vename emp.ename%type := 'SMITH';
vsal   emp.sal%type:=800;
begindbms_output.put_line(vename);dbms_output.put_line(vsal);
end;declare
v emp%rowtype;  --- 变量名.字段名
beginv.ename:='SMITH';v.sal:=800;dbms_output.put_line(v.ename);dbms_output.put_line(v.sal);
end;

赋值

注意:
select  into  赋值 是把查询结果赋给变量 只能是单行结果集   可以是多列--打印dept表10部门的部门名称和部门所在地
select  dname,loc
from dept
where deptno=10;declare
v dept%rowtype;
beginselect  dname,loc into v.dname,v.locfrom deptwhere deptno=10;dbms_output.put_line(v.dname||'   '||v.loc);
end;
--注意:
一个变量 一次只能被赋一个值调用时赋值
declare
v1 number(10) := &v1;
v2 varchar2(20) :=&v2;
v4 varchar2(20) :='&v4';
v3 date := &v3;   ---传值的格式需要特别注意
begindbms_output.put_line(v1);dbms_output.put_line(v2);dbms_output.put_line(v4);dbms_output.put_line(v3);
end;
--传入一个部门编号  打印该部门的部门信息

时间变量赋值

--外部输入赋值变量：declarev_date date;v_date_str varchar2(50);beginv_date := to_date('&INPUT','YYYY-MM-DD'); --执行部分 外部传参v_date_str := to_char(v_date, 'YYYY-MM-DD');dbms_output.put_line(v_date_str);exceptionwhen others thendbms_output.put_line('HAVE ERR');end;

打印输出

dbms_output.put_line 打印输出 并换行
dbms_output.put      打印输出 不换行 会将结果输出到缓存  必须最后搭配一个换行才能显示结果begindbms_output.put('abc');dbms_output.put('ABC');dbms_output.put_line('');dbms_output.put_line('q');
end;

异常处理

too_many_rows   返回多行
no_data_found   没找到数据
zero_divide     试图用0做除数
dup_val_on_index 违反唯一性限制    
others      其他异常  写到最后sqlcode  错误代码
sqlerrm  错误描述--异常处理部分实例：declarev_name number(20) := 100;begindbms_output.put_line(v_name); ---100v_name := 'HELLO WORLD!';dbms_output.put_line(v_name);  --数据类型exceptionwhen others thendbms_output.put_line('HAVE ERR'); end;--传入一个员工编号 打印该员工的 员工姓名以及职位--键盘输入一个员工姓名  打印员工编号,输入一个部门编号 打印员工编号 带上异常处理
declare
vempno number;
beginselect  empno into vempnofrom empwhere ename='&ename';---字符串加''，数值型可以不加dbms_output.put_line(vempno);select empno  into vempnofrom empwhere deptno=&deptno;dbms_output.put_line(vempno);
exception when no_data_found thendbms_output.put_line('&ename'||'该员工不存在'); when too_many_rows thendbms_output.put_line(&deptno||'部门不止一人'); when others then dbms_output.put_line('have error'); 
end;

流程控制

循环
判断

if then elsif then…

 if 条件 then执行逻辑1;end if;(1)用if关键字开始，end if关键字结束，注意end if后加分号；(2)条件部分可以不使用括号，但是必须以关键字then来表示条件结束，如果条件成立，则执行then后到end if之间的执行逻辑，如果条件不成立，则不执行条件语句块。(3)条件可以使用关系运算符和逻辑运算符。if then elsifif 条件1 then执行逻辑1;elsif 条件2 then执行逻辑2;...[else]执行逻辑n;end if;如果条件1成立，则执行逻辑1；如果条件1不成立，则判断条件2，此时若条件2成立，则执行逻辑2；若条件1与条件2均不成立，则执行else后面的执行逻辑3。declarev_num number;v_no number;beginv_num := 4;v_no := 10;if v_num = 3 and v_no = 10 thendbms_output.put_line('IS GOOD!');elsedbms_output.put_line('IS BAD!');end if;exceptionwhen others thendbms_output.put_line('HAVE ERR');end;--练习      
1、传入一个员工编号
操作emp10表 数据同emp
select * from emp10;
drop table emp10;
create table emp10 as select * from emp;
如果这个员工的岗位是ANALYST或者CLERK或者SALESMAN，就给该员工加500元奖金
如果这个员工的岗位是MANAGER，就给该员工加800块奖金
如果这个员工的岗位是PRESIDENT，不做处理declare
vjob varchar2(20);
beginselect job into vjobfrom  emp10where empno=&empno;
--如果这个员工的岗位是ANALYST或者CLERK或者SALESMAN，就给该员工加500元奖金  if vjob in ('ANALYST','CLERK','SALESMAN') thenupdate emp10 set comm=nvl(comm,0)+500  where empno=&empno;
--如果这个员工的岗位是MANAGER，就给该员工加800块奖金elsif vjob='MANAGER' thenupdate emp10 set comm=nvl(comm,0)+800  where empno=&empno;
--如果这个员工的岗位是PRESIDENT，不做处理elsif vjob='PRESIDENT' thendbms_output.put_line('不处理');end if;
--commit;
exception  when others then dbms_output.put_line('报错');
end;2、输入一个员工编号
如果该员工的职位是 ANALYST ,CLERK  或者SALESMAN 就给他加500奖金 
如果他的工资不足3000 则再给他加500奖金
如果该员工职位是MANAGER  给他加800奖金declare
vjob varchar2(20);
vsal number;
beginselect job,sal into vjob,vsalfrom emp10where empno=&empno;
--如果该员工的职位是 ANALYST ,CLERK  或者SALESMAN 就给他加500奖金 if vjob in ('ANALYST','CLERK','SALESMAN') thenupdate emp10 set comm=nvl(comm,0)+500  where empno=&empno;
--如果他的工资不足3000 则再给他加500奖金  if vsal<3000 then update emp10 set comm=nvl(comm,0)+500  where empno=&empno;end if; 
--如果该员工职位是MANAGER  给他加800奖金 elsif vjob='MANAGER' thenupdate emp10 set comm=nvl(comm,0)+800  where empno=&empno;end if; 
--commit ;
exception when others thendbms_output.put_line('报错'); 
end;select * from emp10;

case when

语法: 
case when 条件1 then 要执行的语句;when  条件2 then 要执行的语句;....
else  要执行的语句;
end case;--用case when 做传入一个员工编号如果这个员工工资小于1000，就给他加300奖金如果这个员工工资1000到2000，就给他加200奖金如果这个员工工资大于2000，就给他加100奖金输出员工编号，员工名字，员工工资，员工奖金    declare 
vemp emp%rowtype;
beginselect sal,empno,ename,nvl(comm ,0)into vemp.sal,vemp.empno,vemp.ename,vemp.comm from emp where empno=&empno; 
case when   vemp.sal<1000 then vemp.comm:=vemp.comm+300;when  vemp.sal between 1000 and 2000  then vemp.comm:=vemp.comm+200; else  vemp.comm:=vemp.comm+100;
end case;
dbms_output.put_line(vemp.sal||'   '||vemp.empno||'   '||vemp.ename||'   '||vemp.comm);
end;--输入一个字符  如果是大写字母 打印大写字母  
--如果是小写字母 打印小写字母 否则打印我不认识该字符 使用 case when 判断declare
v char(1):='&v';
begincase when regexp_like(v,'[A-Z]') thendbms_output.put_line('大写字母');when regexp_like(v,'[a-z]') thendbms_output.put_line('小写字母');else  dbms_output.put_line('我不认识该字符');end case;   
exception when others thendbms_output.put_line('错了');
end;