如何编写 PL/SQL 程序

Myth丶恋晨 2022-01-15 17:09 280阅读 0赞

本文的操作选用的数据库是oracle 数据库，登陆的用户是自带的scott用户，默认密码：tiger，有不懂得可以相互讨论一下，谢谢.

首先需要了解PL/SQL块的结构，PL/SQL块由定义部门、执行部门、例外处理部分三个部分组成：

declare 
     /**
      *定义部分---定义常量、变量、复杂数据类型、游标、例解
      *
      */
    begin 
     /**
      * 执行部分--- PL/SQL语句和SQL语句
      *
      */ 
    exception
      /**
       *例外处理部分---处理运行错误
       *
       */ 
    end;  /*结束快标记*/

其中定义部分以declare（大小写都可以）开始，该部分是可选的；执行部分以begin开始，该部分是必须的；例外处理部分以exception开始，该部分也是可选的；而end则是PL/SQL块的结束标记。

下面是一个简单的例子，演示一下：

declare 
        v_ename varchar(20);
    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;

接下来我们来看看PL/SQL块的分类：

1.匿名块 ：也就是没有名称的PL/SQL块，匿名块可以内嵌到应用程序（Pro\*C++）中，也可以在交互环境（例如 SQL\*Plus）中直接使用。 上面的例子就是所谓的匿名块.

2.命名块：具有特定名称标识的PL/SQL块，在PL/SQL块begin使用<<>>加以标记。当使用嵌套块时，为了区分多级嵌套层次关系，，可以使用命名块加以区分。示例如下：

declare 
        v_deptno number(2);
        v_dname  varchar2(10); 
    begin 
    	 <<outer>>
    	  begin 
    	  	 <<inner>>
    	  select deptno into v_deptno
    	       from emp    
    	       where lower(ename) =lower('&name');
    	  end; --<<inner>>        
    	select dname into v_dname 
    	         from dept
    	         where deptno=v_deptno;
    	  dbms_output.put_line('部门名:'||v_dname);   
    end;  --<<outer>>

关于命名块，本人还有很多疑问，因为有的书上不是这样写的，运行会出错。个人理解而已，呵呵。。

3.子程序 ：包括过程、函数和包.

3.1 过程

过程用于执行特定操作。当建立过程时，既可以指定输入参数（IN）、也可以指定输出参数（OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。

在SQL\*Plus中可以使用create procedure 命令建立过程，在开发过程中，建议使用create or replace procedure 。示例如下：

create or replace procedure update_sal(name varchar2,newsal number)
     is
     begin
     	  update emp set sal=newsal
     	  where lower(ename)=lower(name);
     end;

在SQL\*Plus中调用时，可以使用exec、call命令 ,不过在PL/SQL工具的SQL窗口中时，只能使用call  .

SQL> exec update_sal('scott',1000);
     
    PL/SQL procedure successfully completed
     
    SQL>

3.2 函数

函数用于返回特定的数据。当建立函数时，在函数头部必须包含return 字句，而在函数体内必须包含return 语句返回数据。在SQL\*Plus中，可以使用create function 命令建立函数，示例如下：

create function annual_income(name varchar2)
     return number is
          annual_salary number(7,2);
    begin 
    	  select sal*12+nvl(comm,0) into annual_salary
    	        from emp 
    	        where lower(ename)=lower(name);
    	        return annual_salary;
    end;

如上例所示，函数annual\_income用于返回雇员的全年收入（包括工资和奖金）。下面使用SQL\*Plus绑定变量存放输出结果,示例如下：

SQL> call annual_income('scott') into :income;
     
    Method called
    income
    ---------
    24000
     
    SQL> print income
    income
    ---------
    24000
     
    SQL>

关于包、触发器等等以后再补充。。

以上存储过程和函数只是简单的介绍，要深入的了解，请参考其它的博文.