玩转Oracle（7）

||-------  pl/sql 基础 -------||

pl/procedural language 过程语言

//创建表
SQL> create table mytest(
  2  name varchar2(30),
  3  pwd varchar2(30));

//创建过程
create procedure sp_pro1 is
create or replace procedure sp_pro1 is --如果存在即替换
begin
--执行部分
insert into mytest values('valen','123');
--结束部分
end;

SQL> create or replace procedure sp_pro2 is
  2  begin
  3  --执行部分
  4  delete from mytest where name='valen';
  5  --结束部分
  6  end;
  7  /

//查看过程的错误信息
show error;

//如何调用存储过程
1.exec 过程名（参数值1，参数值2）；
2.call 过程名（参数值1，参数值2）；

//pl/sql编程规范
1.单行注释 --
2.多行注释/*...*/
3.定义变量，v_作为前缀
4.定义常量，c_作为前缀
5.定义游标，_cursor作为后缀
6.定义例外，e_作为前缀

//块结构事宜图
declear
/* 定义部分--常量，变量，游标，例外，复杂数据类型 */
begin
/* 执行部分--pl/sql,sql语句 */
exception
/* 例外处理部分--处理运行的各种错误 */
end;

//实例1
set serveroutput on --打开输出选项
begin
 dbms_output.put_line('hello'); --put_line是dbms_output包中的一个过程
end;

//实例2
declare
 v_ename varchar2(5); --定义字符串变量
begin
 select ename into v_ename from emp where empno=&no;
 dbms_output.put_line('雇员名:'||v_ename);
end;

//实例3  no_data_found
declare
 v_ename varchar2(5); --定义字符串变量
begin
 select ename into v_ename from emp where empno=&no;
 dbms_output.put_line('雇员名:'||v_ename);
--异常处理
exception
 when no_data_found then
 dbms_output.put_line('该编号不存在，请重新输入');
end;

//实例4

1.可以输入雇员名，新工资，可修改雇员的工资
create procedure sp_pro3(spName varchar2,newSal number) is
begin
--执行部分,根据用户名修改工资
update emp set sal=newSal where ename=spName;
end;

2.调用过程
exec sp_pro3('VALEN',3232.3);

3.如何在java程序中调用存储过程
private static CallableStatement cs = null;

//1.调用存储过程
 cs = conn.prepareCall("{call sp_pro3(?,?)}");
//2.赋值
 cs.setString(1, "VALEN");
 cs.setInt(2, 4999);
   
//3.执行
 cs.execute();
   
//4.关闭资源
 cs.close();
 conn.close();

【控制结构】
1.条件分支语句
 if -- then
 if -- then -- else
 if -- then -- elsif -- else

【1重条件分支】 if--then
//编写一个过程，可以输入一个雇员名，如果该雇员名的工资低于2000，就给该雇员工资增加10%
create or replace procedure sp_pro4(spName varchar2) is
-- 定义
v_sal emp.sal%type;
begin
-- 执行
select sal into v_sal from emp where ename=spName;
-- 如果该雇员名的工资低于2000，就给该雇员工资增加10%
if v_sal<2000 then
   update emp set sal=sal*1.1 where ename=spName;
end if;
end;

【2重条件分支】 if--then--else
//编写一个过程，可以输入一个雇员名，如果该雇员名的补助不是0就在原来的基础上加100，如果补助为0就把补助设为200

create or replace procedure sp_pro5 (spName varchar2) is
--定义
       v_comm emp.comm%type;
begin
--执行
       select comm into v_comm from emp where ename=spName;
if v_comm<>0 then 【if v_comm<>0 不等于0】
       update emp set comm=comm+100 where ename=spName;
else
       update emp set comm=200 where ename=spName;
end if;
end;

【3多重条件分支】 if--then--elsif--else
//编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他工资增加1000，如果该雇员的职位是MANAGER就给他加工资500，其他职位加200

create or replace procedure sp_pro6 (eNo number) is
v_job emp.job%type;
begin
--执行
      select job into v_job from emp where empno=eNo;
if v_job='PRESIDENT' then
      update emp set sal=sal+1000 where empno=eNo;
elsif v_job='MANAGER' then
      update emp set sal=sal+500 where empno=eNo;
else
      update emp set sal=sal+200 where empno=eNo;
end if;
end;

【循环结构】 

loop -- end loop 相当于do while
while -- while .. loop 开始 -- end loop结束
for -- in reverse -- loop -- end loop
goto lable -- lable为事先定义好的表示名
null -- 不会执行任何操作，并且会直接控制传递到下一条语句

//编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加
create or replace procedure sp_pro7(spName varchar2) is
--定义
v_num number:=1; --冒号等号表示赋值
begin
      loop
                 insert into users values(v_num,spName);
                 --判断是否要退出循环
                 exit when v_num=10;
                 --自增
                 v_num:=v_num+1;
      end loop;
end;

//编写一个过程，可输入用户名，并循环添加10个用户名到users表中，用户编号从11开始增加
create or replace procedure sp_pro8(spName varchar2) is
--定义
v_num number:=11;
begin
      while v_num<=20 loop
           insert into users values(v_num,spName);
           v_num:=v_num+1;
      end loop;
end;

//编写一个过程，可输入用户名，并循环添加10个用户名到users表中，用户编号从21开始增加
create or replace procedure sp_pro9(spName varchar2) is
begin
      for i in reverse 21..30 loop
          insert into users values(i,spName);
      end loop;
end;

//goto
set serveroutput on; --将输出开关打开
declare
i number :=1;
begin
loop
  dbms_output.put_line('输出i='||i);
  if i=10 then
     goto end_loop;
  end if;
  i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;