PLSQL程序设计

PLSQL程序设计

sqlplus scott/123456@127.0.0.1:1521/orcl

declare
    --说明部分
begin
    --程序
    dbms_output.put_line('Hello World');
end;


什么是PL/SQL?
PL/SQL(Procedure Language/SQL)是oracle对sql语言的过程化扩展.
指在SQL命令语言中增加了过程处理语句(如分支,循环等),使SQL语言具有过程处理能力.

程序结构

declare
    说明部分(变量说明,光标声明,列外说明)
begin
    语句序列(DML语句)
exception
    例外处理语句
end;
/

变量和常量说明

1.说明变量(char,varchar2,date,number,boolean,long)
var1 char(15);          --说明变量名,数据类型和长度后用分号结束说明语句.
married boolean:=true;
psal number(7,2);
my_name emp.ename%type; --引用型变量,即my_name的类型与emp表中enamel列的类型一样
emp_rec emp%rowtype;    --记录型变量(代表一行)

记录变量分量的引用:
emp_rec.ename:='ADAMS';



-- 查询7839的姓名和薪水
declare
   --定义变量保存姓名和薪水
   --pename varchar2(20);
   --psal number;
   pename emp.ename%type;
   psal emp.sal%type;
begin
  --得到姓名和薪水
  select ename,sal into pename,psal from emp where empno=7839;
  
  dbms_output.put_line(pename||'的薪水是'||psal);
end;
/

declare
   --定义记录型变量:代表一行
   emp_rec emp%rowtype;
   
begin
  select * into emp_rec from emp where empno=7839; 
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/

--接收键盘输入
--num:地址值,在该地址上 保存了输入的值
accept num prompt '请输入一个数字';
declare
  --定义变量保存输入的数字
  pnum number :=#
begin
  if pnum=0 then dbms_output.put_line('您输入的是0');
     elsif pnum=1 then dbms_output.put_line('您输入的是1');
     else dbms_output.put_line('您输入的是其他数字');
  end if;
end;
/

declare
  --定义变量保存输入的数字
  pnum number :=1;
begin
 loop
   --退出条件
   exit when pnum >10;
   dbms_output.put_line(pnum);
   pnum :=pnum+1;
 end loop;
end;
/

光标(Cursor)==ResultSet

光标语法:
CURSOR 光标名[(参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句;

1.光标的属性:
%isopen %rowcount(影响的行数)
%found %notfound
2.默认,一个会话中只能打开300个光标.
3.cursor_sharing 什么作用?-->性能优化(应急).
show parameter cursor;
默认值EXACT,FORCE,SIMILAR


用于存储一个查询返回的多行数据
cursor c1 is select ename from emp;

打开光标: open c1;(打开光标执行查询)
取一行光标的值:fetch c1 into pename;(取一行到变量中)
关闭光标: close c1;(关闭游标释放资源)
注意:上面的pename必须与emp表中的ename列类型一致.
定义:pename emp.ename%type;

--查询并打印员工的姓名和薪水
declare
 --定义光标
 cursor cemp is select ename,sal from emp;
 pename emp.ename%type;
 psal emp.sal%type;
begin
  --打开光标
  open cemp;
  loop
  --取一条记录
  fetch cemp into pename,psal;
  ---退出条件
  exit when  cemp%notfound;
       dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  --关闭光标'
   close cemp;
end;

 
--涨工资,总裁1000 经理800 其他400
declare
  --alter table "scott"."emp" rename column "job" to empjob
  cursor cemp is select empno,empjob from emp;
  pempon emp.empno%type;
  pjob emp.empjob%type;
begin
  open cemp;
  loop
    --取一条记录
    fetch cemp into pempon,pjob;
    exit when cemp%notfound;
    --判断职位
    if pjob ='PRESIDENT'then update emp set sal=sal+1000 where empno=pempno;
     elsif  pjob ='MANAGER'then update emp set sal=sal+800 where empno=pempno;
     else  update emp set sal=sal+400 where empno=pempno;
    end if;
   end loop;
  --关闭光标'
   close cemp;
   --提交
   commit;
   dbms_output.put_line('success');
end;

例外

例外:程序设计语言提供的一种功能,用来增强程序的健壮性和容错性.

oracle的异常处理
系统定义列外:
No_data_found(没有找到数据)
Too_many_rows (select...into语句匹配到多个行)
Zero_Divide (被零除)
Value_error (算术或转换错误)
Timeout_on_resource(在等待资源时发生超时)

用户定义的例外:
no_data exception;--定义异常

raise no_data;--抛出异常


被0除
declare
    pnum number;
begin
    pnum :=1/0;
exception
    when zero_divide then dbms_output.put_line('exception');
                          dbms_output.put_line('exception1');
    when value_error then dbms_output.put_line('exception2');
    when others then dbms_output.put_line('exception3');
end;

使用自定义例外
declare
    cursor cemp is select ename from scott.emp where deptno=50;
    pename scott.emp.ename%type;
    --自定义例外
    no_emp_found exception;
begin
    open cemp;
        --取记录
        fetch cemp into pename;
        if cemp%notfound then raise no_emp_found;
           end if;
     --pmon:process monitor负责清理
     close cemp;       
exception
    when no_emp_found then dbms_output.put_line('exception');
    when others then dbms_output.put_line('exception3');
end;