pl/sql学习

pl/sql是运行在服务器上的，可以增强维护性和安全性，也减少了客户端和服务端的网络开销，提升了性能。

pl/sql程序单元包含:

1匿名块

2存储程序单元（存储过程，函数，和包）

3触发器

匿名块

匿名块没有名字，包含可选的声明部分，一个执行部分，和一个或多个可选异常处理部分。声明部分声明变量，异常和游标，执行部分包含pl/sql代码和sql语句，可以包含嵌套块。异常处理部分包含出现异常时候的处理，或是作为预定义的异常，或是自己定义的异常。

下面是一个打印名字的匿名块

DECLARE
   Last_name    VARCHAR2(10);
   Cursor       c1 IS SELECT last_name
                       FROM employees
                       WHERE department_id = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Last_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Last_name);
   END LOOP;
END;
/

下面的匿名块处理了预定义的异常

DECLARE
   Emp_number   INTEGER := 9999;
   Emp_name     VARCHAR2(10);
BEGIN
   SELECT Ename INTO Emp_name FROM Emp_tab
      WHERE Empno = Emp_number;   -- no such number
   DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;

下面的例子定义了一个自己的异常

DECLARE
   Emp_name           VARCHAR2(10);
   Emp_number         INTEGER;
   Empno_out_of_range EXCEPTION;
BEGIN
   Emp_number := 10001;
   IF Emp_number > 9999 OR Emp_number < 1000 THEN
      RAISE Empno_out_of_range;
   ELSE
      SELECT Ename INTO Emp_name FROM Emp_tab
         WHERE Empno = Emp_number;
      DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
END IF;
EXCEPTION
   WHEN Empno_out_of_range THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||
       ' is out of range.');
END;

匿名块通常用来调用存储过程或打开游标变量

存储的程序单元

有下面的部分

1有个名字

2可以 接受参数，返回值

3存放在数据字典中

4可以被很多用户调用

下面是一个有输入参数的存储过程的例子

PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
   Emp_name       VARCHAR2(10);
   CURSOR         c1 (Depno NUMBER) IS
                     SELECT Ename FROM Emp_tab
                        WHERE deptno = Depno;
BEGIN
   OPEN c1(Dept_num);
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN C1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
   CLOSE c1;
END;

参数的模式

有3种类型的模式，in,out和in out。要避免在函数中使用out和in out。%type和%rowtype属性，使用这2个属性来约束参数。比如Get_emp_names存储过程可以写成下面这样：

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)

这让dept_num参数有与emp_tab表中deptno列相同的数据类型。推荐使用%type，因为表中的列改变的时候，就不必去修改应用程序的代码了。

使用%rowtype属性来创造一个包含指定表所有列的集合。下面的例子定义了get_emp_rec存储过程，会返回emp_tab表所有列。

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

在pl/sql块中调用这个存储过程
DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;

 参数的默认值

PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...

or

PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...

创建存储过程和函数

CREATE PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                              Emp_ret     OUT Emp_tab%ROWTYPE) AS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;
/
pl/sql包，下面的是一个例子

 

CREATE PACKAGE BODY Employee_management AS
   FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2,
      Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
      Deptno NUMBER) RETURN NUMBER IS
       New_empno    NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the call to this function.

   BEGIN
      SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual;
      INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr,
         Hiredate, Sal, Comm, Deptno);
      RETURN (New_empno);
   END Hire_emp;

   PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

   BEGIN
      DELETE FROM Emp_tab WHERE Empno = Emp_id;
      IF SQL%NOTFOUND THEN
      Raise_application_error(-20011, 'Invalid Employee
         Number: ' || TO_CHAR(Emp_id));
   END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update
-- salary with increase.

   BEGIN
      UPDATE Emp_tab
         SET Sal = Sal + Sal_incr
         WHERE Empno = Emp_id;
      IF SQL%NOTFOUND THEN
         Raise_application_error(-20011, 'Invalid Employee
            Number: ' || TO_CHAR(Emp_id));
      END IF;
   END Sal_raise;
END Employee_management;

 

什么时候使用bulk binds

一涉及到集合的dml语句

forall关键字能提高insert update,或delete语句的性能，例如

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN

-- Efficient method, using a bulk bind
   FORALL i IN Id.FIRST..Id.LAST   -- bulk-bind the VARRAY
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);

-- Slower method, running the UPDATE statements within a regular loop
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);
   END LOOP;
END;

涉及到集合的select语句

bulk collect into语句能提高涉及到集合的查询语句的性能，例如

-- Find all employees whose manager's ID number is 7698.
DECLARE
   TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   Empno VAR_TAB;
   Ename VAR_TAB;
   Counter NUMBER;
   CURSOR C IS
      SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;
BEGIN

-- Efficient method, using a bulk bind
    SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
        FROM Emp_Tab WHERE Mgr = 7698;

-- Slower method, assigning each collection element within a loop.

   counter := 1; 
   FOR rec IN C LOOP 
      Empno(Counter) := rec.Empno;
      Ename(Counter) := rec.Ename;
      Counter := Counter + 1;
   END LOOP;
END;

涉及到集合和returing into语句的for循环

For example, the following PL/SQL block updates the Emp_tab table by computing bonuses for a collection of employees; then it returns the bonuses in a column calledBonlist. The actions are performed both with and without using bulk binds:

DECLARE
   TYPE Emplist IS VARRAY(100) OF NUMBER;
   Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698);
   TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE;
   Bonlist_inst BONLIST;
BEGIN
   Bonlist_inst := BONLIST(1,2,3,4,5);

   FORALL i IN Empids.FIRST..empIDs.LAST
      UPDATE Emp_tab SET Bonus = 0.1 * Sal
      WHERE Empno = Empids(i)
      RETURNING Sal BULK COLLECT INTO Bonlist;

   FOR i IN Empids.FIRST..Empids.LAST LOOP
      UPDATE Emp_tab Set Bonus = 0.1 * sal        
         WHERE Empno = Empids(i)
       RETURNING Sal INTO BONLIST(i);
   END LOOP;
END;

 游标变量

游标是一个静态对象，游标变量是一个指向游标的指针，因为游标变量是指针，他们可以传递给存储过程和函数，并返回。一个游标变量也可以和别的不同游标相关联。

声明和打开游标变量

在应用程序中使用allocate语句来分配游标变量使用的内存，下面是游标的使用例子

下面的包定义了一个pl/sql游标变量emp_val_cv_type，和2个存储过程，第一个存储过程open_emp_cv在where语句中使用绑定变量打开了游标变量。第二个存储过程fetch_emp_data使用游标变量获取了行

CREATE OR REPLACE PACKAGE Emp_data AS
  TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
  PROCEDURE Open_emp_cv (Emp_cv          IN OUT Emp_val_cv_type,
                         Dept_number     IN     INTEGER);
  PROCEDURE Fetch_emp_data (emp_cv       IN     Emp_val_cv_type,
                            emp_row      OUT    Emp_tab%ROWTYPE);
END Emp_data;

CREATE OR REPLACE PACKAGE BODY Emp_data AS
  PROCEDURE Open_emp_cv (Emp_cv      IN OUT Emp_val_cv_type,
                         Dept_number IN     INTEGER) IS
  BEGIN
    OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number;
  END open_emp_cv;
  PROCEDURE Fetch_emp_data (Emp_cv      IN  Emp_val_cv_type,
                            Emp_row     OUT Emp_tab%ROWTYPE) IS
  BEGIN
    FETCH Emp_cv INTO Emp_row;
  END Fetch_emp_data;
END Emp_data;

下面是在pl/sql块中调用emp_data的例子

DECLARE
-- declare a cursor variable
   Emp_curs Emp_data.Emp_val_cv_type;
   Dept_number Dept_tab.Deptno%TYPE;
   Emp_row Emp_tab%ROWTYPE;

BEGIN
   Dept_number := 20;
-- open the cursor using a variable
   Emp_data.Open_emp_cv(Emp_curs, Dept_number);
-- fetch the data and display it
   LOOP
     Emp_data.Fetch_emp_data(Emp_curs, Emp_row);
     EXIT WHEN Emp_curs%NOTFOUND;
     DBMS_OUTPUT.PUT(Emp_row.Ename || '  ');
     DBMS_OUTPUT.PUT_LINE(Emp_row.Sal);
   END LOOP;
END;

处理pl/sql的编译错误

当在使用sql*plus来提交pl/sql代码，当代码中包含错误，你会收到通知，但是没有明确的信息指出是哪里出错了。例如你在文件proc1.sql文件中提交了一个单独的存储过程proc1

SQL> @proc1

And, if there are one or more errors in the code, then you receive a notice such as the following:

MGR-00072: Warning: Procedure proc1 created with compilation errors

在这个例子中，使用show errors语句获得一系列的错误提示，输出中能看到哪行，哪列出现了错误。

SQL> SHOW ERRORS PROC1
SQL> SHOW ERRORS PROCEDURE PROC1

在使用show errors语句的之前，使用set linesize语句来设置输出的长度。

源码可以再数据字典中下面的视图中查看出来；all_soure,user_source和dba_source;

调用远程的存储过程

Call remote procedures using an appropriate database link and the procedure name. The following SQL*Plus statement runs the procedure Fire_emp located in the database and pointed to by the local database link named BOSTON_SERVER:

EXECUTE fire_emp1@boston_server(1043);

PRAGMA RESTRICT_REFERENCES--为了向后兼容

在oracle8.1.5之前，程序员使用 RESTRICT_REFERENCES 来断定子程序的纯正级别。

You can remove RESTRICT_REFERENCES from your code. However, this pragma remains available for backward compatibility in situations where one of the following is true:
 
 
It is impossible or impractical to edit existing code to remove RESTRICT_REFERENCES completely. If you do not remove it from a subprogram S1 that depends on another subprogram S2, then RESTRICT_REFERENCES might also be needed in S2, so that S1 will compile.
Replacing RESTRICT_REFERENCES in existing code with hints parallel-enable and deterministic would negatively affect the behavior of new, dependent code. Use RESTRICT_REFERENCES to preserve the behavior of the existing code.
An existing PL/SQL application can thus continue using the pragma even on new functionality, to ease integration with the existing code. Do not use the pragma in a wholly new application.
If you use the pragma RESTRICT_REFERENCES, place it in a package specification, not in a package body. It must follow the declaration of a subprogram (function or procedure), but it need not follow immediately. Only one pragma can reference a given subprogram declaration.
 
 

  
  


   
   
Note:
The pragma RESTRICT_REFERENCES applies to both functions and procedures. Purity levels are important for functions, but also for procedures that are called by functions.

  
  
 
 
To code the pragma RESTRICT_REFERENCES, use the following syntax: 
PRAGMA RESTRICT_REFERENCES ( 
    Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] ); 

Where:
 
 

  
  
Keyword
Description
WNDS
The
 subprogram writes no database state (does not modify database tables).
RNDS
The
 subprogram reads no database state (does not query database tables).
WNPS
The
 subprogram writes no package state (does not change the values of packaged variables).
RNPS
The
 subprogram reads no package state (does not reference the values of packaged variables).
TRUST
The other restrictions listed in the pragma are not enforced; they are simply assumed to be true. This allows easy calling from functions that have RESTRICT_REFERENCES declarations to those that do not.


 
 

静态sql与动态sql的不同

静态插入，更新，删除和报表不违反RNdS如果这些陈述没有明确阅读任何数据库的状态，如表的一列。然而，动态insert,update,delete语句总是违法RNDS，不管语句是否明确的读数据库状态。下面的insert如果动态执行就违法了RNDS,但是如果静态执行就没有违法RNDS.
INSERT INTO my_table values(3, 'SCOTT'); 

下面的update总是违法RNDS，因为它明确的读取了my_table的name列
UPDATE my_table SET id=777 WHERE name='SCOTT';