Oracle PL/SQL随堂笔记总结

时间:2022-08-16 22:56:40

1.pl/sql编程

2.存储过程

3.函数

4.触发器

5.包

6.pl/sql基础 -定义并使用变量

7.pl/sql的进阶

8.oracle的视图

1.pl/sql编程

    1.理解oracle的pl/sql的概念
    2.掌握pl/sql编程技术(过程、函数、触发器)
    pl/sql是标准sql语句的扩展
    简介
        1.过程、函数、触发器都是由pl/sql编写
        2.过程、函数、触发器是在oracle中
        3.pl/sql是非常强大的过程语言
        4.过程、函数等可以在java程序被调用
    学习必要性:
        1.提高应用程序的性能
        2.模块化的设计思想
        3.减少网络传输量
        4.提高安全性
    不好的方面:
        移植性差
    pl/sql可以使用变量和逻辑控制语句
    可编写:分页存储过程模块,订单处理存储过程模块,转账存储过程模块……

    块:block 由三部分构成
        定义部分,从declare开始,可选;
        执行部分,从begin开始,必须;
        例外处理部分,从exception开始,可选;

       
实例1-只包括执行部分的pl/sql块

            set serveroutput on  --打开输出选项
begin
dbms_output.put_line(
'hello');
end;
--说明:dbms_output是oracle所提供的包(类似于java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程。

        实例2-包含定义部分和执行部分的pl/sql块

            declare
v_name
varchar2(5);
v_sal
number(7,2);
begin
select ename,sal into v_name,v_sal from scott.emp where empno=&jingyu;
dbms_output.put_line(
'员工名:'||v_name||' 员工薪水:'||v_sal);
end;
--如果返回值不惟一,需要用到参照变量;

        实例3-包含定义部分,执行部分和例外处理部分
            --例外处理部分,为了提高程序的健壮性,应该对可能的错误进行处理
            oracle事先定义了一些例外,no_data_found就是找不到数据的例外。
            上例就可以在end前加入如下异常处理部分代码:

            exception
when no_data_found then
dbms_output.put_line(
'数据不存在,请重新输入!');

     pl/sql编写规范
        1.注释
            单行注释 --
            多行注释 /*   */
        2.标识符号的命名规范
            1).定义变量时,用v_ 做前缀
            2).定义常量时,用c_ 做前缀
            3).定义游标时,用_cursor 做后缀
            4).定义例外时,用e_ 做前缀

2.存储过程

    a.最简单的存储过程:

        create or replace procedure sp_pro1 is
begin
insert into mytest1 values('shunping','m123');
end;

    b.调用存储过程:

        exec sp_pro1;

    c.存储过程示例:
        1.编写一个存储过程,实现功能:可以输入雇员名,新工资,可以修改雇员的工资。

            create or replace procedure sp_pro2(v_ename varchar2,v_newsal number) is
begin
update emp1 set sal=v_newsal where ename=v_ename;
end;

        2.演示java程序调用oracle的存储过程
            pl/sql第二讲最后几分钟。
        3.如何使用过程返回值
            以后解决

3.函数

函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。
  案例1:输入雇员的姓名,返回该雇员的年薪。

    create function sp_fun1(spName varchar2)
return number is
yearSal
number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp1 where ename=spName;
return yearSal;
end;
    sql>调用函数
var income number;
call sp_fun1(
'SCOTT') into:income;

4.触发器

触发器是指隐含执行的存储过程。当定义一个触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql块。

5.包

包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
  实例1:创建包

    create package sp_package is
procedure update_sal(ename varchar2,newsal number);
function annual_sal(name varchar2) return number;
end;

    --包的规范只包含了过程和函数的说明,没有过程和函数的实现代码。
    包体用于实现包规范中的过程和函数
  实例2:建立包体

    create or replace package body sp_package is
procedure update_sal(name varchar2,newSal number) is
begin
update emp1 set sal=newSal where ename=name;
end;
function annual_sal(name varchar2)return number is
yearSal
number;
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp1 where ename=name;
return yearSal;
end;
end;

  实例3:如何调用包的过程和函数
    调用过程:

    call sp_package.update_sal('SMITH',133);

    调用函数:

    var abc number
call sp_package.annual(
'SMITH') into:abc;

6.pl/sql基础 -定义并使用变量

在编写pl/sql程序时,可以定义变量和常量;在pl/sql程序中包括有:
①标量类型(scalar)
    标量定义的案例

        v_name  varchar2(20);
v_sal
number(6,2);
v_sal
number(6,2):=5.4;
v_hiredate date;
v_valid boolean
not null default false;

    实例1:输入员工号,显示雇员姓名、工资、个人所得税(税率为0.03)。

            declare
c_taxRate
number(3,2):=0.03;
v_ename
varchar2(5);
v_sal
number(7,2);
v_taxSal
number(7,2);
begin
select ename,sal into v_ename,v_sal from emp1 where empno=&no;
v_taxSal:
=v_sal*c_taxRate;
dbms_output.put_line(
'雇员姓名:'||v_ename||' 工资:'||v_sal||' 个人所得税:'||v_taxSal);
end;

            ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
            当ename为MARTIN时,会报上述错误,解决方法,定义v_ename时,v_ename emp1.ename%type;
②复合类型(composite)
用于存放多个值的变量。主要包括这几种
    1)pl/sql记录    
        类似于高级语言中的结构体(不知道结构体可以先简单理解成类)
        实例1:

            declare
--定义一个pl/sql类型,emp_record_type,类型包含3个数据,分别是name,salary,title
type emp_record_type is record (name emp1.ename%type,salary emp1.sal%type,title emp1.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp1 where empno=&no;
dbms_output.put_line(
'雇员姓名:'||sp_record.name||' 雇员工资'||sp_record.salary);
end;

    2)pl/sql表    
        相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制。
        实例1:
            declare

              --定义一个pl/sql表类型,该类型用于存放emp1表中ename字段的这种数据
type emp_table_type is table of emp1.ename%type index by binary_integer;
sp_table emp_table_type;
begin
select ename into sp_table(-1) from emp1 where empno=&no;
dbms_output.put_line(
'名字是:'||sp_table(-1));
end;

    3)嵌套表;   

    4)varray;
③参照类型(reference)
参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。
在编写pl/sql时,可以使用游标变量(ref_cursor)和对象类型变量(ref_obj_type)两种参照变量类型。
    1).游标变量(ref_cursor)
    实例1:请使用pl/sql编写一个块,可以输入部门号,显示该部门所有员工的姓名及其工资。

    --pl/sql参照变量 之游标变量
declare
--定义一个游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp1.ename%type;
v_sal emp1.sal
%type;
begin
--将游标和select结合
open test_cursor for select ename,sal from emp1 where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断何时退出循环 应该是test_cursor为空时退出。
exit when test_cursor%notfound;
dbms_output.put_line(
'名字:'||v_ename||' 工资'||v_sal);
end loop;
end;

    实例2:在实例1基础上,如果哪个员工的工资少于200元,就增加100元。
④lob(large object);

7.pl/sql的进阶

控制结构
    (1)使用if语句,
        条件分支语句:if  then;if  then else;if then elsif then else;
        案例1:编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员工资增加10%。

            --Method1:
create procedure update_sal(name varchar2) is
begin
update emp1 set sal=1.1*sal where sal<2000 and ename=name;
end;
            --Method2:
create or replace procedure update_sal2(name varchar2) is
v_sal emp1.sal
%type;
begin
select sal into v_sal from emp1 where ename=name;
if v_sal<2000 then
update emp1 set sal=1.1*sal where ename=name;
--exec调用没有问题;但call调用有问题,在下次查询才会打印出来
dbms_output.put_line('此员工工资太低,已经给默认涨10%!');
end if;
end;

        案例2:编写一个过程,输入一个雇员名,如果他的补助不是0,就增加100.如果是0,就改成200;

            create procedure update_comm(name varchar2) is
v_comm emp1.comm
%type;
begin
select comm into v_comm from emp1 where ename=name;
if v_comm<>0 then
update emp1 set comm=comm+100 where ename=name;
else
update emp1 set comm=200 where ename=name;
end if;
end;

        案例3:编写一个过程,输入一个雇员编号,如果他是president,工资加1000,如果是manager,工资加500;如果是其他职位,工资加200;

            create or replace procedure update_sal(spNo number) is
--当上面参数写empNo时,实验是不可以的,过程可以成功创建,但是调用有问题。所以最好取不同名字。
v_job emp1.job%type;
begin
select job into v_job from emp1 where empno=spNo;
if v_job='PRESIDENT' then
update emp1 set sal=sal+1000 where empno=spNo;
elsif v_job
='MANAGER' then
update emp1 set sal=sal+500 where empno=spNo;
else
update emp1 set sal=sal+200 where empno=spNo;
end if;
end;

    (2)循环语句,
        1.第一种循环结构:
        loop
        ..
        exit when v_num>=10;
        end loop;
        案例:user表中插入10条记录,编号1-10,姓名"小明"。

            create or replace procedure loop_insert_data(name varchar2) is
v_num
number:=1;
begin
loop
insert into user1 values(v_num,name);
--判断退出条件
exit when v_num>=10;
v_num:
=v_num+1;
end loop;
end;

        2.第二种循环结构:
        while v_num<=20 loop
        ..
        end loop;
        案例:user表中插入10条记录,编号11-20,姓名"小亮"。

            create or replace procedure loop_insert_data2(name varchar2) is
v_num
number:=11;
begin
while v_num<=20 loop
insert into user1 values(v_num,name);
v_num:
=v_num+1;
end loop;
end;

        3.第三种循环结构:for循环(不推荐使用,不够灵活)

            begin
for i in reverse 1..10 loop
insert into user1 values (i,'顺平');
end loop;
end;

    (3)控制语句goto和null;
    goto不推荐使用,降低了程序的可读性。
    null 不执行任何操作,只是为了提高程序的可读性。
pl/sql进阶:综合案例之编写分页的过程。
1).无返回值的存储过程,向book表中添加书籍;
create table book(bookid number,bookname varchar2(50),publishhouse varchar2(50));
2).有返回值的存储过程,可以输入一个员工的编号,可以返回员工的姓名;
3).有返回值的存储过程(列表[结果集]),输入一个部门号,返回该部门所有员工的信息;
    ①创建一个包,定义一个类型test_cursor
    ②创建一个过程
    ③如何在java中调用
4).作业:有了上面的基础,相信大家可以完成分页的存储过程了,要求:可以输入表名,每页显示记录数,当前页。返回总记录数,总页数,和返回的结果集。
    --1.建立一个包,定义一个游标的类型

    create or replace package sp_package_fy as
type sp_fy_cursor
is ref cursor;
end sp_package_fy;

    --2.建立一个实现分页的存储过程_修正版

    create or replace procedure sp_fenye(spTable in varchar2,spNum in number,spCurrentPage in number,
spTotalRecord out
number,spTotalPage out number,sp_cursor out sp_package_fy.sp_fy_cursor) is
v_sql
varchar2(1000);
v_begin
number:=spNum*(spCurrentPage-1)+1;
v_end
number:=spCurrentPage*spNum;
begin
--v_sql赋值
v_sql:='select * from (select rownum rn,t1.* from (select * from '||spTable||') t1 where rownum<='||v_end||')
where rn>=
'||v_begin;
--显示指定分页后详细的信息
open sp_cursor for v_sql;

--显示总记录数
v_sql:='select count(*) from '||spTable;
execute immediate v_sql into spTotalRecord;

--显示总页数
if mod(spTotalRecord,spNum) <> 0 then
spTotalPage:
=spTotalRecord/spNum+1;
else
spTotalPage:
=spTotalRecord/spNum;
end if;
--关闭游标
--close sp_cursor;
end;
--在java程序中调用分页存储过程。以后学java时再分析。

例外处理
例外的分类:Oracle将例外分为预定义例外,非预定义例外和自定义例外3类。
(1)预定义例外:定义了oracle常见的错误;

data_not_found
case_not_found
cursor_already_open
invalid number
too_many_rows
zero_divide
logon_denied
timeout_on_resourse

(2)非预定义例外:用于处理预定义例外不能处理的例外;

(3)自定义例外:用于处理与oracle错误无关的其他情况。
实例:编写一个存储过程,输入一个名字,工资加1000,如果没有该人,抛出自定义例外。 

8.oracle的视图

视图是一个虚拟表,其内容由查询定义,同真实的表一样,视图包含一系列带有名称的行和列数据。但是视图并不在数据库中以存储的数据集形式存在,行和列数据来*定义视图的查询所引用的表。

视图与表的区别

①表需要占用磁盘空间,视图不需要;

②视图不能添加索引;

③使用视图可以简化复杂查询;(比如学生选课系统)

④视图有利于提高安全性;(比如不同用户查看不同视图)

创建只读视图:create or replace view 视图名 as select 语句 with read only;

删除视图:drop view 视图名;