pl/sql编程基础

plsql编程基础

PL/SQL:procedure language/SQL

在原有的SQL语句上扩展，功能比原来sql更为强大的语句块

即Oracle的可编程性

PL/SQL包含以下内容

1、存储过程

2、函数

3、触发器

4、包

PL/SQL的重要组成部分

定义部分 

       声明变量，常量，游标……（该部分是可选的）

执行部分

       主要的执行内容（该部分是必须的）

异常部分

       对块中有可能出现的异常的处理（该部分是可选的）

----首先在命令窗口中需要先开启输出选项

set serveroutput on;

1、存储过程

                  create or replace procedure sp_01

                  is

                  begin

                             dbms_output.put_line('pl/sql——过程');

                  end;

存储过程的三种调用方式

      a) call sp_01()

      b) begin

              sp_01();

           end;

       c) execute sp_1();   ////仅限命令窗口使用

例子

--------请编写一个过程，可以输入雇员名，新工资，可修改雇员的工资

                      create or replace procedure sp_updatesal(v_ename in varchar2,v_sal in number) -----in表示                         输入类似交互式命令

                      as/is    ----这两者都可以

                      begin

                                update emp set sal=v_sal where ename=v_ename;

                                commit;

                                dbms_output.put_line('更新完成！')；

                       end;

 

调用(自己可以试验上面的三种调用方式)

call sp_upadatesal('SMITH',1300);

 

---查询指定部门的人数

                    create or replace procedure sp_getcountgroupbydeptno(v_dno in//可省略  number,v_count out number)

                     is

                     begin

                               select count(*) from emp where deptno=v_dno;

 

                     end;

 ----带有输出参数的过程调用

                     declare

                               v_count number

                     begin

                               sp_getcountgroupbydeptno();

                               dbms_output.put_line('v_count');

                     end;

 2、函数

                     create or replace function fun_01

                     return varchar2

                     is

                     begin

                             return 'Hello World';

                      end;

函数的调用

select fun_01() from dual;

注意：函数与过程的区别：

           a)  函数必须有返回值，过程一般为输出参数

           b)   函数无输出参数的概念，过程可以有输入和输出参数

例子：写一个函数比较两个数的大小

                     create or replace function fun_max(v_num1 number,v_num2 number)

                     return number

                     is

                     begin

                               if v_num1>v_num2 then

                                     return v_num1;

                               else

                                     return v_num2;

                     end;

调用

select fun_max(3,4) from dual;

 3、触发器（维护数据库完整性1、约束  2、触发器）

维护数据完整性，安全性

 包括三类触发器：

a) DML触发器：insert、update、delete操作时触发（对表数据进行操作）

b) DDL触发器：create、alter、drop操作时触发（对表结构操作）

c) 系统触发器：数据库启动和关闭，用户的登陆和退出

例子1，------如果发现emp表中任意一条数据执行update操作，则输出：数据被更改

                     create or replace trigger trig_upadteemp                ------创建一个触发器

                     after update                                                             ------在更新之后触发

                     on scott.emp                                                            ------基于Scott用户的emp表触发

                     [for each row]                                                           -------针对每一行操作都触发一次

                     begin                                                                        

                             dbms_output.put_line('数据更新！');               --------触发的操作

                     end;

例子2       

                     create or replace trigger trig_empchange 

                     after update or insert or delete 

                     on scott.emp

                     for each row

                     begin

                          if updating then

                                     dbms_output.put_line('更新数据！');  

                          elsif inserting  then

                                     dbms_output.put_line('添加数据！');

                          elsif deleting then

                                     dbms_output.put_line('删除数据！');

                         end if;

                       end;

 4、包

一种逻辑结构，用于组合过程和函数，以及类型

包含两个组成部分：1、包定义 2、包体

                       声明包

                       create or replace package pk_01

                       is  

                                   procedure sp_01();

                                   function fun_01 return varchar2;

                        end;                   

                        定义包体

                         create or replace package body pk_01

                         is

                                    procedure sp_01

                                    is

                                    begin

                                              dbms_output.put_line('PL/SQL——过程');

                                    end;

                                    function fun_01 

                                    return varchar2

                                    is

                                    begin

                                          return 'Hello World';

                                     end;

                            end pk_01;

包类函数，过程的调用

                             declare

                             begin

                                        pk_01.sp_01();

                             end;

                             select pk_01.fun_01() from dual;