oracle-02plsql-向日葵屋

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代码

CallableStatement cs = con.prepareCall(“{call emp_pro(?,?)}“);
//4.给?赋值
cs.setString(1,”SMITH”);
cs.setInt(2,10);
//5.执行
cs.execute();

6.函数

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

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

Sql代码

—编写函数返回指定人员的年薪
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 ename = name;
return annual_salary;
end;

在sqlplus中调用函数

Sql代码

SQL> var income number
SQL> call annual_incomec(‘scott’) into :income; —注意:和income之间不能有空白
SQL> print income

同样我们可以在java程序中调用该函数 select annual_income(‘SCOTT’) from dual;

7.包

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

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

Sql代码

—使用create package命令创建包
create package pkg_sal is
procedure update_sal(name varchar2, new_sal number);
function annual_income(name varchar2) return number;
end;

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

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

Sql代码

—使用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;

oracle-02plsql

1.注释

2.标志符号的命名规范

3.块结构示意图

4.过程

6.函数

7.包

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle PLSQL编程

相关 oracle PLSQL编程

相关 Oracle-plsql注册

相关 oracle32 plsql,plsql32 链接oracle64

相关 plsql连接 oracle instantclient_11_2 本地机器不安装oracle_02

相关 Oracle：PLSQL——例外

相关 Oracle：PLSQL——游标

相关 Oracle：PLSQL——类型

相关 Oracle：PLSQL——概述

相关 oracle-02plsql

随便看看

CentOS 启动流程

SpringCloud中feign调用：status 401 reading xxxxxx#报错解决

docker 相关命令的使用

mac·os---BurpSuite安装sqlmapapi无法启动【sqlmap api is not running】解决办法

C++ day7 if else, switch,条件运算符，逻辑运算符

平衡二叉搜索树的创建

教程文章

热评文章

1江湖小白之一起学Python （二）爬取数据的保存

2Java Shiro：简化身份验证和授权的安全框架

3Java中try()catch{}的使用方法

4Swagger注解-@ApiModel 和 @ApiModelProperty

5windows下强制杀死tomcat进程

6uni-app 条形码(一维码)/二维码生成实现

标签列表