oracle实战第六天--pl/sql介绍和基础
来源:互联网 发布:centos 开源网盘 编辑:程序博客网 时间:2024/05/18 02:12
内容介绍
1.pl/sql的介绍
2.pl/sql的基础
期望目标
1.理解oracle的pl/sql概念
2.掌握pl/sql编程技术(包括编写过程、函数、触发器…)
Pl/sql的介绍
Pl/sql是什么?
Pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。Pl/sql不仅允许嵌入sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得更加强大。
为什么学?
学习的必要性:
1.提高应用程序的运行性能。
2.模块化的设计思想。
3.减少网络传输量。
4.提高安全性。
不好的地方:
1.移植性不好。
用什么编写pl/sql
Sqlplus开发工具
Sqlplus开发工具是oracle公司提供的一个工具,这个因为我们在以前介绍过的:
举一个简单的案例:
编写一个存储过程,该过程可能向某表中添加记录
create table mytest(
name varchar2(30),
passwd varchar2(30)
);
--创建一个存储过程
create or replace procedure sp_addUser
(v_name varchar2,v_passwd varchar2)
is
begin
insert into mytest values(v_name,v_passwd);
end;
--查看错误
show error;
--调用存储过程的两种方式
exec sp_addUser('zhangsan','123');
call sp_addUser('lisi','123');
Pl/sql developer开发工具
Pl/sql developer是用于开发pl/sql块的集成开发环境(ide),他是一个独立的产品,而不是oracle的一个附带品。
举一个例子:
编写一个存储过程,该过程可以删除某表记录。
create procedure sp_delUser
(v_name varchar2)
is
begin
delete from mytest where name = v_name;
end;
Pl/sql基础
开发人员使用pl/sql编写应用模块时,不仅需要掌握sql语句的编写方法,还要掌握pl/sql语句及语法规则。Pl/sql编程可以使用变量和逻辑控制语句,从而可以编写非常有用的功能模块。比如:分页存储过程模块、订单处理存储过程模块、转帐存储过程模块..而且如果使用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;
Pl/sql块
块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能,可能只需要编写一个pl/sql块;但是如果想实现复杂的功能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。
Pl/sql块的结构
pl/sql由三个部分组成:定义部分、执行部分、例外处理部分。
如下所示:
Declare
/*定义部分――定义常量、变量、游标、例外、复杂数据类型*/
Begin
/*执行部分――要执行的pl/sql语句和sql语句*/
Exception
/*例外处理部分――处理运行的各种错误*/
end;
特别说明:
定义部分是从declare开始的,该部分是可选的。
执行部分是从begin开始的,该部分是必须的。
例外处理部分是从exception开始的,该部分是可选的。
实例1――只包括执行部分的pl/sql块
set serveroutput on;--打开输出选项
begin
dbms_output.put_line('Hello,World');
end;
相关说明:
Dbms_output是oralce所提供的包(类似java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程。
实例2――包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp
where empno = &no;
dbms_output.put_line('雇员名:'||v_ename);
end;
多个变量的例子:
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp
where empno = &no;
dbms_output.put_line('雇员名:'||v_ename||' 薪水: '||v_sal);
end;
相关说明:
& 表示要接收从控制台输入的变量
实例3――包含定义部分、执行部分和例外处理部分
为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能的错误进行处理,这个很有必要:
1)比如在实例中,如果输入了不存在的雇员号,应当做例外处理。
2)有时出现异常,希望用另外的逻辑处理
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp
where empno = &no;
dbms_output.put_line('雇员名:'||v_ename||' 薪水: '||v_sal);
exception
when no_data_found
then dbms_output.put_line('您输入的编号有误!');
end;
相关说明:
oracle事先预定义了一些例外,no_data_found就是找不到数据的例外。
过程
过程用于执行特定的操作。当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out)。通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下:
1)写一个过程,可以输入雇员名,新工资,可修改雇员的工资。
create procedure sp_updateUser
(spName varchar2,newSal number)
is
--定义过程要使用的变量
begin
update emp set sal = newSal where ename = spName;
end;
2)如何调用过程有两种方法:
Exec sp_update(‘scott’,1350);
Call sp_update(‘scott’,1650);
3)如何在java程序中调用一个存储过程
package com.anllin.jdbc.oracle;
import java.sql.*;
public class NoReturnValProc
{
public static void main(String[] args)
{
Connection conn = null;
CallableStatement stmt = null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager
.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl",
"scott", "tiger");
stmt = conn.prepareCall("{call sp_addBooks(?,?,?)}");
stmt.setInt(1, 1);
stmt.setString(2, "水是最好的药");
stmt.setString(3, "人民出版社");
stmt.execute();
}
catch (Exception e)
{
throw new RuntimeException(e);
}
finally
{
try
{
if (null != stmt)
stmt.close();
if (null != conn)
conn.close();
}
catch (Exception ex)
{
throw new RuntimeException(ex);
}
}
}
}
特别说明:
对于过程我们会在后面做更详细的说明,这里先简单介绍一下。
函数
函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据,我们可以使用create function来建立函数,实际案例:
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中调用函数
var income number
call annual_income('SCOTT') into:income;
print income;
同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;
可以通过rs.getInt(1)得到返回的结果。
包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
1)我们可以使用create package命令来创建包。
实例:
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码。包体用于实现规范中的过程和函数
2)建立包体可以使用create package body命令
create package body sp_package is
--implement procedure update_sal
procedure update_sal(name varchar2,newsal number)
is
begin
update emp
set sal=newsal
where ename=name;
end;
--implement function annual_income
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;
3)如何调用包的过程或是函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果要访问其它方案的包,还需要包名前加方案名。
如:
call sp_package.update_sal('SCOTT',1500);
特别说明:
包是pl/sql中非常重要的部分,我们在使用过程从页时,将会再次体验它的威力。
触发器
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作,常用 的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql块,可以使用create trigger来建立触发器。
特别说明:
我们会在后面详细为大家介绍触发器的使用,因为触发器是非常有用的,可维护数据库的安全和一致性。
定义并使用变量
介绍:
在编写pl/sql程序时,可以定义变量和常量;在pl/sql包括有:
1)标量类型(scalar)
2)复合类型(composite)
3)参照类型(reference)
4)lob(large object)
标量――常用类型
在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。
Pl/sql中定义变量和常量的语法如下:
Indentifier [constant] datatype [not null] [:=| default expr]
Identifier:名称
Constant:指定常量。需要指定它的初始值,且其值是不能改变的
Datatype:数据类型
Not null:指定变量值不能为null
:= 给变量或是常量指定初始值。
Default 用于指定初始值。
Expr: 指定初始值的pl/sql表达式,可是文本值,其它变量,函数等。
标量定义的案例:
1)定义一个变长的字符串
v_ename varchar2(10);
2)定义一个小数范围-9999.99~9999.99
v_sal number(6,2);
3)定义一个小数并给一个初始值为5.4 。:= 是pl/sql的赋值号
v_sal2 number(6,2) := 5.4;
4)定义一个日期类型的数据。
v_hiredate date;
5)定义一个布尔变量,不能为空,初始值为false
v_valid boolean not null default false;
标量――使用标量
在定义好变量后,就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言,需要在等号前加冒号(:=)
下面以输入员工号,显示雇员姓名,工资,个人所得税(税率为0.03)为例。说明变量的使用,看看如何编写。
Set serveroutput on;
declare
c_tax_rate number(3,2) := 0.03;
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal := v_sal * c_tax_rate;
dbms_output.put_line('name:'||v_ename||'sal:'||v_sal||' tax_sal:'||v_tax_sal);
end;
标量――使用%type类型
对于上面的 pl/sql块有一个问题:
就是如果员工的姓名超过了5个字符的话,就会有错误,为为降低pl/sql程序的维护工作量,可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的变量的类型和长度。
标识符号 表名.列名%type
Set serveroutput on;
declare
c_tax_rate number(3,2) := 0.03;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal := v_sal * c_tax_rate;
dbms_output.put_line('name:'||v_ename||'sal:'||v_sal||' tax_sal:'||v_tax_sal);
end;
复合变量(composite)--介绍
用于存放多个值的变量。主要包括这几种:
1)pl/sql记录
2)pl/sql表
3)嵌套表
4)varray
复合类型—pl/sql记录
类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须加记录变量作为前缀(记录变量.记录成员)如下:
declare
--declare record type
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type
);
--reference record type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp
where empno=7788;
dbms_output.put_line('name: '||sp_record.name);
end;
复合类型—pl/sql表
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制。实例如下:
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0)
from emp
where empno=7788;
dbms_output.put_line('name : ' || sp_table(0));
end;
说明:
Sp_table_type 是pl/sql表类型
emp.ename%type 指定了表元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素。
复合变量――嵌套表
复合变量――变长数组
参照变量
介绍
参照变量是指用于存放数值指针的变量。通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
游标变量(ref cursor)
使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(open时)需要指定select 语句,这样一个游标就与一个select语句结合了。实例如下:
1)请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资。
declare
type sp_emp_cursor is ref cursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open sp_cursor
for
select ename,sal
from emp
where deptno=&no;
loop
fetch sp_cursor into v_ename,v_sal;
exit when sp_cursor%notfound;
dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
end loop;
end;
2)在1)的基础上,如果某个员工的工资低于200元,就增加100元。
declare
type sp_emp_cursor is ref cursor;
sp_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open sp_cursor
for
select ename,sal
from emp
where deptno=&no;
loop
fetch sp_cursor into v_ename,v_sal;
if
v_sal<200
then
update emp set sal=sal+100
where ename = v_ename;
end if;
exit
when sp_cursor%notfound;
dbms_output.put_line('name: ' || v_ename || ' sal: ' || v_sal);
end loop;
end;
- oracle实战第六天--pl/sql介绍和基础
- Oracle PL/SQL实战
- Oracle PL/SQL开发基础(第六弹:简单查询)
- ORACLE PL/SQL 介绍
- ORACLE PL/SQL 基础
- ORACLE PL/SQL 基础
- oracle pl/sql 基础
- oracle pl/sql 基础
- Oracle PL/SQL 基础
- Oracle PL/SQL 基础
- oracle PL/SQL 基础
- Oracle PL/SQL基础
- Oracle--PL/SQL基础
- Oracle-PL/SQL基础
- 《Oracle PL/SQL实战教程》
- oracle笔记——第六天:pl/sql 编程
- Oracle中的PL/SQL介绍
- Oracle PL/SQL语言基础
- 使用Objective C建立UUID
- s3c2410触摸屏驱动的介绍
- Editor窗体根据编辑状态设定控件状态
- 写完文件之后最好调用fflush,否则有可能fclose之后,发现文件的最后有一部分没有写入完整。
- 形参、实参以及值调用和引用调用的区别
- oracle实战第六天--pl/sql介绍和基础
- C54x DSP的软件开发环境与工具,开发流程—编译链接
- Oracle中Decode()函数使用技巧
- asp.net mvc3 MySql数据库,保存数据库乱码
- org.apache.jasper.JasperException: File "/oscache" not found
- Release模式禁用NSLog
- 基于Gstreamer的TI DSP视频处理框架
- sql时间格式转换大全
- 利用TreeSet进行排序操作