Oracle数据库 PLSQL编程基础

PL/SQL介绍

什么是PL/SQL？

pl/sql(procedural language/sql)是Oracle在标准的sql语言上的扩展. pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误,这样使得它的功能变得更加强大。

pl/sql的缺点：移植性差。

开发工具

1. SQL Pus

2. PLSQL Developer

PLSQL基础知识

PLSQL块结构

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

declare
/*定义部分-----定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分-----要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分----处理运行的各种错误*/
end;

PLSQL过程

过程用于执行特定的操作。当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境.在SQL Plus中可以使用create procedure命令来建立过程。

创建两个过程：

create or replace procedure MYDEMO(name out varchar, id in int) is
begin
  dbms_output.put_line('ID: ' || id);
  select 'Xiao Ming' into name from dual;
end MYDEMO;

create or replace procedure EXEC_MYDEMO is
  name varchar(10);
  id int;
begin
  mydemo(name=>name, id=>10);
  dbms_output.put_line('Name: ' || name);
end EXEC_MYDEMO;

在命令窗口输入以下语句：

SQL> set serveroutput on
SQL> exec exec_mydemo

PLSQL函数

函数用于返回特定的数据,当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数。

创建一个函数：

create or replace function CACL_SALARY(name varchar2) return number is
  v_salary number(7, 2);
begin
  select sal+nvl(comm,0) into v_salary from emp where ename = name;
  return(v_salary);
end CACL_SALARY;

在命令窗口中调用函数：

sql> var income number
sql> call calc_salary('SCOTT') into:income;
sql> print income

PLSQL包

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

使用create package命令创建包：

create or replace package sp_package is
	procedure update_sal(name varchar2,newsal number);
	function annual_income(name varchar2) return number;
end;

使用create package body命令创建包体：

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

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

调用包的过程或函数：

sql> call sp_package.update_sal('SCOTT',1500);

PLSQL触发器

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert、update、delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。

PLSQL标量（scalar）

pl/sql中定义变量和常量的语法如下:

名称 [初始值常量] 数据类型 [not null] [:=| default pl/sql表达式]

使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。格式：标识符名 表名.列名%type

复合类型类似与高级语言中的结构体，例如：

type emp_record_type is record(
    name emp.ename%type,
    salary emp.sal%type,
    title emp.job%type);
sp_record emp_record_type;

参照变量是指用于存放数值指针的变量.通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor) 和对象类型变量。

type c_page is ref cursor;