oracle PL、SQL（概念）

一.PL/SQL简介。

　　Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询和Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量申明，赋值语句，而且还有条件，循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。

二.优点：

1,支持SQL

　　　　SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库的数据。

　　　　PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。

2,支持面向对象编程

　　　　PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。

3,更好的性能

　　　　SQL是非过程语言，只能一条一条的执行，而PL/SQL把一个PL/SQL统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，以备重用，减少了应用程序和服务器之间的通信时间，所以PL/SQL是高效而快速的。

4,可移植性

　　　　使用PL/SQL编写的应用程序语言，可以移植到任何操作平台的ORACLE服务器，同时还可以编写可移植程序库，在不同环境中使用。

5,安全性

 　　　　可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分割，这样可以限制对ORACLE数据库的访问，数据库还可以授权和撤销其他用户的访问权利。

 

--1，PL/SQL--包含 过程、函数、触发器、...
     PL--> procedural  language  过程语言
 
--查看错误信息能够更详细的指出错所在
show error  

----1.1、第一个存储过程，简单定义(相当于一个静态方法)
--(1)创建一个存储过程
create  procedure  proc_holleword
is
begin
dbms_output.put_line('Hello everyone !');  
end;
--(2)显示输出内容
set serveroutput on;
--(3)执行存储过程（二选一）
execute procedurename;
exec    procedurename;

---JAVA程序调用存储过程需要用call 

----1.2、第二个存储过程(简单案例)
create or replace procedure proc_insertEmp
is
begin
 insert into emp values( 9901,'WANGQIANG' ,'CLERK');
 insert into emp values( 9902,'JIALIUL' ,'CLERK');
 insert into emp values( 9903,'ZHAOQI' ,'CLERK'); 
end;

--2，编写规范
编写规范n
2.1、注释；
单行注释 --Sql 代码
多行注释 /*...* /来划分

2.2、标志符号的命名规范
1).当定义变量时，建议用v_作为前缀v_sal
2).当定义常量时，建议用c_作为前缀c_rate
3).当定义游标时，建议用_cursor 作为后缀emp_cursor
4).当定义例外时，建议用e_作为前缀e_error


--3，代码块
declare   --定义声明
begin     --程序开始
exception --异常  
end;      ---程序结束


---- 案例 ---从对话框中输入一个员工编号，然后打印出该员工的姓名和工资
declare
v_ename varchar2(30);
v_sal number(8,2);
begin
-- '&'开头的变量 是指从对话框输入值---
  select ename,sal into v_ename,v_sal from emp where empno=&eno;
  dbms_output.put_line('员工姓名：'||v_ename||'   工资：'||v_sal);
end;
/

--4，exception 的使用案例,
declare
v_ename varchar2(30);
v_sal  number(8,2);
begin
  select ename,sal into v_ename,v_sal from emp where empno=&eno;
  dbms_output.put_line('员工姓名：'||v_ename||'   工资：'||v_sal);
exception
  when no_data_found then
  dbms_output.put_line('该员工号不存在，请重新输入员工号');
end;
/


--5，存储过程----带参数---   

----根据输入的员工编号，新的姓名，新的工资，去修改姓名和工资--------
create procedure  proc_updateEmp(v_empno number,v_ename varchar2 ,v_sal number)
is
begin
update emp set ename=v_ename  ,sal=v_sal where empno=v_empno;
end;
/
----执行--（传入参数）------------------
exec  proc_updateEmp(9900,'LAOWANG',10000);



--6，JAVA程序调用 存储过程 -------------------------------
/* 
建立一个Java程序
加入oracle的驱动包ojdbc6.jar【app-administrator-product-11.2.0-dbhome_1-jdbc-lib-ojdbc6.jar】

try {
            Class.forName("oracle.jdbc.OracleDriver");
            String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            Connection conn=DriverManager.getConnection(url, "scott", "tiger");
//调用格式
            CallableStatement  cs=conn.prepareCall("{call proc_updateEmp(?,?,?)}");
              cs.setInt(1, 9903);
              cs.setString(2, "阿甘");
              cs.setDouble(3, 9988.50);
              cs.execute();
              cs.close();
              conn.close();
        } catch (Exception e) {
        }
*/



--7，条件分支语句-------------------------------------
if  ..  then   ..  end if;
if  ..  then   ..   else  ..  end if;
if  ..  then   ..   elsif .. then    ..  else .. end if ;


----案例问题1：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
create or replace procedure  proc_updateSalByEname(v_ename varchar2)
is
v_sal emp.sal%type;---取得emp表中的sal栏目的数据类型
begin
  select sal into v_sal from emp where ename=v_ename;--根据名字查询
  if v_sal<2000  then  
    update emp set sal=sal*1.1 where ename=v_ename;
   end if; 
end;
/
----根据传入参数（名字）执行
exec proc_updateSalByEname('SMITH');



----案例问题2：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0 就在原来的基础上增加100；如果补助为0 就把补助设为200；
create or replace procedure  proc_updateCommByEname(v_ename varchar2)
is
v_comm emp.comm%type; --声明一个变量，并设置类型为emp中comm的类型
begin
--查询出comm后赋值给一个临时变量
    select comm into v_comm   from emp where ename=v_ename;
  if  nvl(v_comm,0) >0 then 
    update emp set comm=comm+100 where ename =v_ename;  
  else
    update emp set comm=200  where  ename=v_ename;
  end if;
end;
/


----案例问题3：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他的工资增加500，其它职位的雇员工资增加200 
create or replace procedure  proc_updateSalByJob(v_ename varchar2)
is
v_job emp.job%type;
begin
   select job into v_job from emp where ename=v_ename;
  if  v_job='PRESIDENT' then 
     update emp set  sal=sal+1000 where ename=v_ename;
  elsif v_job='MANAGER'  then
      update emp set  sal=sal+500 where ename=v_ename;
  else 
      update emp set  sal=sal+200 where ename=v_ename;
  end if;
end;
/


--8，loop循环---------------------------------------
loop   ..  exit when ..  num+   end loop;


----案例：建立一张stemp表 id sname  ,请插入100条数据
create table  stemp(id number(8),sname varchar2(20));

declare
v_num number(8):=1; --声明变量并赋值【 :=  赋值符号】
begin 
  loop 
       insert into stemp values(v_num, 'sname'||v_num   );
  exit when  v_num=100; --条件
       v_num:=v_num+1;
  end loop;--结束循环
end;
/

--8.2、while循环--------------------------------
while  ..  loop ..........  num+  end loop;

----案例
declare
v_num number(8):=1;
begin 
 while v_num<=100  loop 
       insert into stemp values(v_num, 'sname'||v_num   );
       v_num:=v_num+1;
  end loop;
end;
/

--8.3、for 循环--- reverse:反序-----------------
for v in 区间 loop   ...  end loop; 
for v in reverse 区间 loop  ...  end loop;

----案例
begin
for  v_num  in 1..1000  loop
    insert into stemp values(v_num, 'sname'||v_num   );
end loop;
end;
/

----案例
begin
for  v_num  in  reverse 1..1000  loop
    insert into stemp values(v_num, 'sname'||v_num   );
end loop;
end;
/