PL/SQL基础(2):单元

时间:2021-12-24 23:51:36

本篇是 Oracle基础小结 系列之一。


PL/SQL程序单元包括:PL/SQL匿名块、PL/SQL函数、PL/SQL存储过程、PL/SQL包、PL/SQL触发器等。这里就用过的几个做简单记录,另外虽然PL/SQL异常处理不是程序单元,但也是其中重要的组成部分,也放在这里一并阐述。因此这部分包括:

1、PL/SQL匿名块

2、PL/SQL函数

3、PL/SQL存储过程

4、PL/SQL包

5、PL/SQL触发器

6、PL/SQL异常处理

 

PL/SQL匿名块

PL/SQl匿名块即是PL/SQL单元的基础,其它程序单元均是匿名块增加单元头信息构成相应功能单元。注意PL/SQL里的变量需要在BEGIN前先声明。一个PL/SQL匿名块(Wiki)如下:

<<label>>  -- this isoptional

declare

-- this section is optional

  number1 number(2);

  number2 number1%type    :=17;            -- value default

  text1   varchar2(12) :='Hello world';

  text2   date         := SYSDATE;       -- current date and time

begin

-- this section is mandatory, must contain at least oneexecutable statement

  SELECT street_number

    INTO number1

    FROM address

    WHERE name = 'INU';

exception

-- this section is optional

   WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error Code is '||to_char(sqlcode )  );

    DBMS_OUTPUT.PUT_LINE('Error Message is '|| sqlerrm   );

end;

引用:

https://en.wikipedia.org/wiki/PL/SQL#PL.2FSQL_anonymous_block

 

PL/SQL函数

         PL/SQL函数同其它语言函数一样,参数有输入(IN)输出(OUT)引用(IN OUT),并且有返回值(RETURN)。函数的参数类型不带长度信息,这点与声明部分不同。

格式:

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type[, ...])]

RETURN return_datatype

{IS | AS}

BEGIN

  < function_body >

END [function_name];

示例:

1. create or replace function func  (  

2.         -- 入参、出参列表, 逗号分隔。  

3.         uid in varchar2,                          -- 不能带长度信息  

4.         startDate in date,                        -- 第二个输入参数  

5.         defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序  

6.         isok out number,                          -- 输出参数  

7.         result out varchar2                       -- 第二个输出参数  

8. )  

9. return number      -- 定义返回类型  

10.as  

11.-- 变量声明,每个声明用分号结束。可以在声明的同时初始化  

12.var1 varchar2(11);  

13.var2 number(2) := 123;  

14.  

15.begin  

16.        -- 字符串拼接用 ||  

17.        dbms_output.put_line('isok:' || 'abc');  

18.         

19.  

20.        return ret_val;  

21.end;  

引用:

http://www.tutorialspoint.com/plsql/plsql_functions.htm

http://wen866595.iteye.com/blog/1733887

 

PL/SQL存储过程

         PL/SQL的存储过程在结构上与函数有些类似,但是不带返回值。结构如下:

CREATE [OR REPLACE] PROCEDUREprocedure_name

[(parameter_name [IN | OUT | IN OUT] type[, ...])]

{IS | AS}

BEGIN

 < procedure_body >

END procedure_name;

         示例如下:

1. create or replace procedure sp_name (  

2.         -- 入参、出参列表, 逗号分隔。  

3.         uid in varchar2,                          -- 不能带长度信息  

4.         startDate in date,                        -- 第二个输入参数  

5.         defaultVar in varchar2 default "",        -- 默认参数,如果不传,要注意参数的顺序  

6.         isok out number,                          -- 输出参数  

7.         result out varchar2                       -- 第二个输出参数  

8. )  

9. as  

10.-- 变量声明,每个声明用分号结束。可以在声明的同时初始化  

11.var1 varchar2(11);  

12.var2 number(2) := 123;  

13.  

14.begin  

15.        -- 字符串拼接用 ||  

16.        dbms_output.put_line('isok:' || 'abc');  

17.         

18.        -- 调用其他存储过程  

19.        sub_sp_name(param1, prarm2, outParam1, outParam2);  

20.  

21.end;        -- 存储过程结束  

22./

 

引用:

http://www.tutorialspoint.com/plsql/plsql_procedures.htm

http://wen866595.iteye.com/blog/1733887

 

PL/SQL包

         PL/SQL包是包含类型、变量和子程序等的模式对象,类似于面向对象中类的概念。PL/SQL包包括包的声明(Package specification)和包体或定义(Package body or definition)两部分。包的声明例子如下:

CREATE PACKAGE cust_sal AS

  PROCEDURE find_sal(c_id customers.id%type);

END cust_sal;

/

包体的例子如下:

CREATE OR REPLACE PACKAGE BODY cust_sal AS

  PROCEDURE find_sal(c_id customers.id%TYPE) IS

  c_sal customers.salary%TYPE;

  BEGIN

     SELECT salary INTO c_sal

     FROM customers

     WHERE id = c_id;

     dbms_output.put_line('Salary: '|| c_sal);

  END find_sal;

END cust_sal;

/

包创建后,访问包中元素(变量、函数、存储过程等),使用package_name.element_name;使用上面包的程序如下:

DECLARE

   code customers.id%type := &cc_id;

BEGIN

   cust_sal.find_sal(code);

END;

/

引用:

http://www.tutorialspoint.com/plsql/plsql_packages.htm

 

PL/SQL触发器

         触发器存储了在某些事件发生时触发的程序。通常用于数据库操作(DELETE, INSERT, or UPDATE)、数据库定义(CREATE,ALTER, or DROP)和数据库操纵(SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)。它经常用于实现Oracle数据库的自增等。创建触发器的结构如下:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition) 

DECLARE

  Declaration-statements

BEGIN

  Executable-statements

EXCEPTION

  Exception-handling-statements

END;

引用:

http://www.tutorialspoint.com/plsql/plsql_triggers.htm

 

PL/SQL异常处理

         PL/SQL异常处理(EXCEPTION)用于捕捉抛出和处理异常。异常信息有两种:系统预定义和用户自定义。EXCEPTION写在BEGIN END块之间。在异常处理时,通常可以写ROLLBACK;语句来回滚。EXCEPTION的语法结构如下:

DECLARE

  <declarations section>

BEGIN

  <executable command(s)>

EXCEPTION

  <exception handling goes here >

  WHEN exception1 THEN -- exception1可以是系统预定义或用户自定义

      exception1-handling-statements

  WHEN exception2  THEN

     exception2-handling-statements

  WHEN exception3 THEN

     exception3-handling-statements

  ........

  WHEN others THEN

     exception3-handling-statements

END;

在程序执行中还可以使用RAISE主动报异常,尤其是用户自定义异常,其语法结构如下:

DECLARE

  exception_name EXCEPTION;

BEGIN

   IFcondition THEN

     RAISE exception_name;

  END IF;

EXCEPTION

  WHEN exception_name THEN

  statement;

END;

         一个EXCEPTION例子如下,这里既包含可系统预定义异常也包括了用户自定义异常,同时使用了RAISE和raise_application_error报异常。raise_application_error的好处在于可以把生涩的系统异常提示,写成软件相关的用户可理解的异常提示。

CREATE OR REPLACE PROCEDURE add_new_order

  (order_id_in IN NUMBER, sales_in IN NUMBER)

IS

  no_sales EXCEPTION;        -- 自定义异常类型变量

BEGIN

   IFsales_in = 0 THEN

     RAISE no_sales;            -- 报异常

  ELSE

     INSERT INTO orders (order_id, total_sales )

     VALUES ( order_id_in, sales_in );

  END IF;

EXCEPTION

  WHEN DUP_VAL_ON_INDEX THEN

     raise_application_error (-20001,'You have tried to insert a duplicateorder_id.');     -- 将系统预定义异常以一种用户可理解的提示报出来

  WHEN no_sales THEN

     raise_application_error (-20001,'You must have sales in order to submitthe order.');     -- 将用户自定义异常以一种用户可理解的提示报出来

  WHEN OTHERS THEN

     raise_application_error (-20002,'An error has occurred inserting anorder.');  -- 以通用消息报其他异常

END;

/

附Oracle预定义的异常:

Exception

Oracle Error

SQLCODE

Description

ACCESS_INTO_NULL

06530

-6530

It is raised when a null object is automatically assigned a value.

CASE_NOT_FOUND

06592

-6592

It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.

COLLECTION_IS_NULL

06531

-6531

It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.

DUP_VAL_ON_INDEX

00001

-1

It is raised when duplicate values are attempted to be stored in a column with unique index.

INVALID_CURSOR

01001

-1001

It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.

INVALID_NUMBER

01722

-1722

It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.

LOGIN_DENIED

01017

-1017

It is raised when s program attempts to log on to the database with an invalid username or password.

NO_DATA_FOUND

01403

+100

It is raised when a SELECT INTO statement returns no rows.

NOT_LOGGED_ON

01012

-1012

It is raised when a database call is issued without being connected to the database.

PROGRAM_ERROR

06501

-6501

It is raised when PL/SQL has an internal problem.

ROWTYPE_MISMATCH

06504

-6504

It is raised when a cursor fetches value in a variable having incompatible data type.

SELF_IS_NULL

30625

-30625

It is raised when a member method is invoked, but the instance of the object type was not initialized.

STORAGE_ERROR

06500

-6500

It is raised when PL/SQL ran out of memory or memory was corrupted.

TOO_MANY_ROWS

01422

-1422

It is raised when s SELECT INTO statement returns more than one row.

VALUE_ERROR

06502

-6502

It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.

ZERO_DIVIDE

01476

1476

It is raised when an attempt is made to divide a number by zero.

 

引用:

https://www.techonthenet.com/oracle/exceptions/when_others.php

http://www.tutorialspoint.com/plsql/plsql_exceptions.htm