oracle-02plsql

Love The Way You Lie 2022-04-08 14:41 145阅读 0赞

pl/sql编程——过程、函数、包

### 1.注释 ###

单行注释 --

select \* from emp where empno=7788; --取得员工信息

多行注释 /\*...\*/来划分

### 2.标志符号的命名规范 ###

1).当定义变量时，建议用v\_作为前缀v\_sal

2).当定义常量时，建议用c\_作为前缀c\_rate

3).当定义游标时，建议用\_cursor作为后缀emp\_cursor

4).当定义例外时，建议用e\_作为前缀e\_error

### 3.块结构示意图 ###

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。

如下所示：

declare

/\*定义部分——定义常量、变量、游标、例外、复杂数据类型\*/

begin

/\*执行部分——要执行的pl/sql语句和sql语句\*/

exception

/\*例外处理部分——处理运行的各种错误\*/

end;

定义部分是从declare开始的，该部分是可选的；

执行部分是从begin开始的，该部分是必须的，至少要写null，不能不写；

例外处理部分是从exception开始的，该部分是可选的。

& 表示要接收从控制台输入的变量。

###  4.过程 ###

过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，

通过在过程中使用输入参数，可以将数据传递到执行部分；

通过使用输出参数，可以将执行部分的数据传递到应用环境。

在sqlplus中可以使用create procedure命令来建立过程。

\--只有输入参数的存储过程

create or replace procedure proc01(eno number) --in 输入参数  out输出参数

begin

update emp set sal = sal+500 where empno = eno;

---select sal into money from emp where empno=eno;

end proc01;

在oracle中调用存储过程：

第一种：call proc01(7654);

第二种：在plsql块中调用

SQL> begin

3  proc01(7654);

5  end;

6  /

\--既有输入又有输出参数的存储过程

create or replace procedure proc02(eno in number,money out number) is
    
    begin
    
      update emp set sal=sal+1000 where empno=eno;
    
      select sal into money from emp where empno=eno;
    
      commit;
    
      exception
    
    rollback;
    
    end proc02;

在oracle中调用存储过程：

SQL> set serveroutput on;

SQL> declare money number;

2  begin

3  proc02(7654,money);

4  dbms\_output.put\_line(money);

5  end;

6  /

5.JAVA中调用存储过程

Java代码

1. CallableStatement cs = con.prepareCall("\{call emp\_pro(?,?)\}");

2. //4.给?赋值

3. cs.setString(1,"SMITH");

4. cs.setInt(2,10);

5. //5.执行

6. cs.execute();

### 6.函数 ###

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。

而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例：

Sql代码

1. --编写函数 返回指定人员的年薪

2. create function annual\_income(name varchar2)

3. return number is

4. annual\_salary number(7, 2);

5. begin

6.   select sal\*12 + nvl(comm, 0) into annual\_salary from emp where ename = name;

7.   return annual\_salary;

8. end;

在sqlplus中调用函数

Sql代码

1. SQL> var income number

2. SQL> call annual\_incomec('scott') into :income; --注意:和income之间不能有空白

3. SQL> print income

同样我们可以在java程序中调用该函数 select annual\_income('SCOTT') from dual;

### 7.包 ###

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

1).我们可以使用create package命令来创建包。

Sql代码

1. --使用create package命令创建包

2. create package pkg\_sal is

3.   procedure update\_sal(name varchar2, new\_sal number);

4.   function annual\_income(name varchar2) return number;

5. end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。

2).建立包体可以使用create package body命令

Sql代码

1. --使用create package body创建包体

create or replace package body pkg_sal is  
    
       procedure update_sal(name varchar2, new_sal number) is  
    
         begin  
    
          update emp set sal = new_sal where ename = name;  
    
         end;  
    
      function annual_income(name varchar2) return number is  
    
         income number(7, 2);  
    
         begin  
    
           select sal*12 + nvl(comm, 0) into income from emp where ename = name;  
    
           return income;  
    
         end;  
    
     end;

3).如何调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。

Example

Creating a Package: Example

CREATE OR REPLACE PACKAGE emp_mgmt AS
    
       FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,
    
          manager_id NUMBER, salary NUMBER,
    
          commission_pct NUMBER, department_id NUMBER)
    
          RETURN NUMBER;
    
       FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
    
          RETURN NUMBER;
    
       PROCEDURE remove_emp(employee_id NUMBER);
    
       PROCEDURE remove_dept(department_id NUMBER);
    
       PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
    
       PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
    
       no_comm EXCEPTION;
    
       no_sal EXCEPTION;
    
    END emp_mgmt;

Creating a Package Body: Example

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
    
       tot_emps NUMBER;
    
       tot_depts NUMBER;
    
    FUNCTION hire
    
       (last_name VARCHAR2, job_id VARCHAR2,
    
        manager_id NUMBER, salary NUMBER,
    
        commission_pct NUMBER, department_id NUMBER)
    
       RETURN NUMBER IS new_empno NUMBER;
    
    BEGIN
    
       SELECT employees_seq.NEXTVAL
    
          INTO new_empno
    
          FROM DUAL;
    
       INSERT INTO employees
    
          VALUES (new_empno, 'First', 'Last','first.example@oracle.com',
    
                  '(415)555-0100','18-JUN-02','IT_PROG',90000000,00,
    
                  100,110);
    
          tot_emps := tot_emps + 1;
    
       RETURN(new_empno);
    
    END;
    
    FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
    
       RETURN NUMBER IS
    
          new_deptno NUMBER;
    
       BEGIN
    
          SELECT departments_seq.NEXTVAL
    
             INTO new_deptno
    
             FROM dual;
    
          INSERT INTO departments
    
             VALUES (new_deptno, 'department name', 100, 1700);
    
          tot_depts := tot_depts + 1;
    
          RETURN(new_deptno);
    
       END;
    
    PROCEDURE remove_emp (employee_id NUMBER) IS
    
       BEGIN
    
          DELETE FROM employees
    
          WHERE employees.employee_id = remove_emp.employee_id;
    
          tot_emps := tot_emps - 1;
    
       END;
    
    PROCEDURE remove_dept(department_id NUMBER) IS
    
       BEGIN
    
          DELETE FROM departments
    
          WHERE departments.department_id = remove_dept.department_id;
    
          tot_depts := tot_depts - 1;
    
          SELECT COUNT(*) INTO tot_emps FROM employees;
    
       END;
    
    PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
    
       curr_sal NUMBER;
    
       BEGIN
    
          SELECT salary INTO curr_sal FROM employees
    
          WHERE employees.employee_id = increase_sal.employee_id;
    
          IF curr_sal IS NULL
    
             THEN RAISE no_sal;
    
          ELSE
    
             UPDATE employees
    
             SET salary = salary + salary_incr
    
             WHERE employee_id = employee_id;
    
          END IF;
    
       END;
    
    PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
    
       curr_comm NUMBER;
    
       BEGIN
    
          SELECT commission_pct
    
          INTO curr_comm
    
          FROM employees
    
          WHERE employees.employee_id = increase_comm.employee_id;
    
          IF curr_comm IS NULL
    
             THEN RAISE no_comm;
    
          ELSE
    
             UPDATE employees
    
             SET commission_pct = commission_pct + comm_incr;
    
          END IF;
    
       END;
    
    END emp_mgmt;

DROP TABLE accounts; -- in case it exists
    
    CREATE TABLE accounts (
    
      acctno   INTEGER,
    
      balance  NUMBER
    
    );
    
    
    
    INSERT INTO accounts (acctno, balance)
    
    VALUES (12345, 1000.00);
    
    
    
    CREATE OR REPLACE PACKAGE finance AS
    
      FUNCTION compound_ (
    
        years  IN NUMBER,
    
        amount IN NUMBER,
    
        rate   IN NUMBER
    
       ) RETURN NUMBER;
    
      PRAGMA RESTRICT_REFERENCES (compound_, WNDS, WNPS, RNDS, RNPS);
    
    END finance;

CREATE PACKAGE BODY finance AS
    
      FUNCTION compound_ (
    
        years  IN NUMBER,
    
        amount IN NUMBER,
    
        rate   IN NUMBER
    
       ) RETURN NUMBER
    
       IS
    
       BEGIN
    
         RETURN amount * POWER((rate / 100) + 1, years);
    
       END compound_;
    
      -- No pragma in package body
    
    END finance;

DECLARE
    
      interest NUMBER;
    
    BEGIN
    
      SELECT finance.compound_(5, 1000, 6)
    
      INTO interest
    
      FROM accounts
    
      WHERE acctno = 12345;
    
    END;

CREATE OR REPLACE PACKAGE p IS
    
      PROCEDURE java_sleep (milli_seconds IN NUMBER)
    
      AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
    
      PRAGMA RESTRICT_REFERENCES(java_sleep,WNDS,TRUST);
    
      
    
      FUNCTION f (n NUMBER) RETURN NUMBER;
    
    END p;

CREATE OR REPLACE PACKAGE BODY p IS
    
      FUNCTION f (
    
        n NUMBER
    
       ) RETURN NUMBER
    
       IS
    
       BEGIN
    
         java_sleep(n);
    
         RETURN n;
    
       END f;
    
    END p;

Example 6-22 PRAGMA RESTRICT REFERENCES with TRUST on Invoker

CREATE OR REPLACE PACKAGE p IS
    
      PROCEDURE java_sleep (milli_seconds IN NUMBER)
    
      AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
    
      
    
      FUNCTION f (n NUMBER) RETURN NUMBER;
    
      PRAGMA RESTRICT_REFERENCES(f,WNDS,TRUST);
    
    END p;

CREATE OR REPLACE PACKAGE BODY p IS
    
      FUNCTION f (
    
        n NUMBER
    
      ) RETURN NUMBER
    
      IS
    
      BEGIN
    
        java_sleep(n);
    
         RETURN n;
    
      END f;
    
    END p;