牛刀小试PLSQL编程之筑基篇

原创 2012年03月21日 12:30:07

完整版见https://jadyer.github.io/2012/03/21/oracle-plsql-block/




PL/SQL编程
概述:它是Oracle在标准的SQL语言上的扩展,它不仅支持允许嵌入SQL语言,还可以自定义常量和变量
           允许使用条件语句、循环语句、逻辑控制语句等,允许使用例外处理各种错误,这样使得它的功能变得更加强大
优势:1)提高应用程序的运行性能----省去了数据库编译Java程序发送过来的SQL语句的时间
           2)模块化的设计思想----------比如分页存储过程模块、订单处理存储过程模块、转账存储过程模块等等
           3)减少网络传输量------------不必再用Java程序发送那么多的SQL语句了
           4)提高安全性-----------------直接在Java程序中调用存储过程名,省去了暴露SQL的危险

劣势:移植性差----------------------移植到其它数据库时,可能甚至要重写
规范:定义变量时,建议用v_作为前缀,如v_sal
           定义常量时,建议用c_作为前缀,如c_rate
           定义例外时,建议用e_作为前缀,如e_error
           定义游标时,建议用_cursor后缀,如emp_cursor

分类:它主要是块编程,简单可以分为过程、函数、触发器、包等等

--编写一个存储过程,该过程可以向某表中添加记录
create table student(name varchar2(10),password varchar2(30));
create or replace procedure mypro is --replace表示如果已存在名字为mypro的存储过程,则将之替换
	begin
		insert into student values('张起灵','zhang22'); --执行部分
	end;
exec mypro; --调用名字为mypro的存储过程。格式为exec procedure_name(param1,param2,...)
call mypro; --也可以使用call命令调用



block
概述:块是PL/SQL的基本程序单元,编写PL/SQL程序实际上就是编写PL/SQL块
           要完成一个相对简单的功能,可能只需要编写一个PL/SQL块;但要想实现复杂的功能,可能需要在一个PL/SQL块中嵌套其它的PL/SQL块
结构:PL/SQL块由三个部分构成,定义部分、执行部分、例外处理部分
           declare         /*定义部分-------该部分是可选的。定义常量、变量、例外、游标、复杂数据类型*/
           begin           /*执行部分-------该部分是必须的。要执行的PL/SQL语句和SQL语句*/
           exception    /*例外处理部分---该部分是可选的。处理运行的各种错误*/
           end;

-----------------------------------------------------------------------------------------------------------------
--1)只包括执行部分的PL/SQL块
set serveroutput on --打开输出选项
--dbms_output是Oracle所提供的包,类似Java的开发包,该包中包含一些过程,put_line就是该包中的一个过程
begin
	dbms_output.put_line('my name is jadyer'); --在控制台输出my name is jadyer字符串
end;

-----------------------------------------------------------------------------------------------------------------
--2)包含定义部分、执行部分的PL/SQL块
declare
	v_ename varchar2(5); --定义字符串变量
	v_sal number(7,2);
begin --into表示将查询到的信息,放入到v_ename变量中。注意这里ename、sal和v_ename、v_sal的顺序是相匹配的
	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块
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 --Oracle预定义了一些例外,其中no_data_found即找不到数据的例外
	dbms_output.put_line('您输入的员工编号不存在。');
end;
-----------------------------------------------------------------------------------------------------------------



存储过程
概述:用于执行特定的操作。建立过程时,既可以指定输入参数,也可以指定输出参数
           通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境

--修改指定用户的工资

create procedure emp_pro(currName varchar2, newSal number) is --类似于Java定义方法时的参数,故不可以指定参数类型的长度
	begin
		update emp set sal=newSal where ename=currName;
	end;

--调用存储过程。效果是将SCOTT的工资变动为23456
exec emp_pro('SCOTT',23456);

--在Java程序中调用该存储过程
CallableStatement cstmt = java.sql.Connection.prepareCall("{call emp_pro(?,?)}");
cstmt.setString(1, "SCOTT");
cstmt.setInt(2, 23456);
cstmt.execute();



函数
概述:用于返回特定的数据。建立函数时,在函数头部必须包含return子句,而函数体必须包含return语句返回的数据

--返回指定雇员的年薪
create function emp_fun(currName varchar2) return number is yearSal number(7,2);
	begin
		select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=currName;
		return yearSal;
	end;

--在sqlplus中调用函数时,需要以下三步
var NianXin number;
call emp_fun('SCOTT') into:NianXin;
print NianXin;

Java>//在Java程序中调用该函数,然后使用rs.getInt(1)即得到返回的结果
Java>select emp_fun('SCOTT') from dual;




概述:用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
           包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数

--1)使用create package命令创建包
create or replace package emp_pack is
	procedure emp_pro(currName varchar2, newSal number); --声明该包中有一个过程
	function emp_fun(currName varchar2) return number;   --声明该包中有一个函数
end;

--2)使用create package body命令创建包体
create package body emp_pack is
	procedure emp_pro(currName varchar2, newSal number) is
		begin
			update emp set sal=newSal where ename=currName;
		end;
	function emp_fun(currName varchar2) return number is yearSal number;
		begin
			select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=currName;
			return yearSal;
		end;
end;

--3)调用包的过程或函数时,在过程和函数前需要带有包名;如果要访问其它方案的包,还需要在包名前加方案名
call emp_pack.emp_pro('SCOTT',400800);



触发器
概述:触发器是指存放在数据库中,被隐含执行的存储过程。触发器由触发事件、触发条件、触发操作三部分构成
分类:DML触发器、DDL触发器、系统触发器(后两个触发器通常由系统管理员创建、即conn system/xxx as sysdba)

--管理触发器(使用system登录)
alter trigger trigger_name disable; --禁用触发器(让触发器临时生效)
alter trigger trigger_name enable;  --激活触发器
alter table table_name disable all triggers; --禁用表的所有触发器
alter table table_name enable all triggers;  --激活表的所有触发器
drop trigger trigger_name;                   --删除触发器
--DML触发器的基本语法
create [or replace] trigger trigger_name
{before|after}
{insert|delete|update [of column [,column ...]]}
on [schema.] table_name
[for each row]    --代表行级触发器,没有它则代表表级触发器
[when condition]  --代表触发条件
begin
	trigger_body;
end;

--举例
--1)在表中添加一条数据时,提示"添加了一条数据"
create or replace trigger trigger_blog after insert on scott.blog
begin
	dbms_output.put_line('添加了一条数据');
end;

--2)在表中修改多条数据时,提示多次"修改了数据"
create or replace trigger trigger_blog after update on scott.blog for each row
begin
	dbms_output.put_line('修改了数据');
end;

--3)禁止在休息日修改表数据,开发人员可以建立before语句触发器,从而实现数据的安全
create or replace trigger trigger_blog before insert or update or delete on scott.blog
begin
	if to_char(sysdate,'day') in ('星期六','星期日') then
		--dbms_output.put_line('不能在休息日操作数据');  --这样只会提示,而不能阻止该操作
		--raise_application_error()是Oracle提供的一个过程,只要PLSQL碰到它,PLSQL就会停止执行
		--PROCEDUER raise_application_error(error_number_in IN NUMBER,error_msg_in IN VARCHAR2)
		--error_number_in是从-200000到-20999之间的,这样就不会与Oracle的任何错误代码发生冲突了
		--而error_msg_in的长度也不要超过2000,否则Oracle会自动截取前2000个字符
		raise_application_error(-20001,'不能在休息日操作数据');
	end if;
end;

--4)为了区分触发器中所包含的多个触发事件,可以使用三个条件:inserting,updating,deleting
create or replace trigger trigger_blog before insert or update or delete on scott.blog
begin
	if to_char(sysdate,'day') in ('星期六','星期日') then
		case
			when inserting then raise_application_error(-20002,'请不要在休息日添加数据');
			when updating then raise_application_error(-20003,'请不要在休息日修改数据');
			when deleting then raise_application_error(-20004,'请不要在休息日删除数据');
		end case;
	end if;
end;

--5)修改雇员薪水时,确保雇员工资不能低于原工资,也不能高出原工资的20%,并显示薪水修改前和修改后的值
create or replace trigger trigger_blog before update on scott.blog for each row
begin
	--由于我们的触发器是针对emp表的,所以PLSQL就知道这里的sal是blog表的字段
	--':new'修饰符用于访问操作完成后列的值,':old'修饰符用于访问操作完成前列的值
	if (:new.sal<:old sal="" or="" :new="" sal="">:old.sal*1.2) then
		raise_application_error(-20005,'修改后的工资不能低于原工资,也不能高出原工资的20%');
	else
		dbms_output.put_line('原来的工资:'||:old.sal||'    现在的工资:'||:new.sal);
	end if;
end;

--6)删除表记录时,自动将删除掉的记录备份到另外一张表中
create or replace trigger trigger_blog before delete on scott.blog for each row
begin
	insert into blog_bak values (:old.id, :old.name, :old.sal);
end;
--DDL触发器
create [or replace] trigger trigger_name
after ddl on 方案名.schema --这里的'.schema'是固定写法,如scott.schema
begin
	trigger_body;
end;

--记录某个用户进行的DDL操作
create table log_ddl(uname varchar2(20), ddl_event varchar2(20), ddl_time date);
create or replace trigger trigger_ddl after ddl on scott.schema
begin
	insert into log_ddl values(ora_login_user, ora_sysevent, sysdate);
end;
--7)系统触发器是指基于Oracle事件(如logon,startup)所建立的触发器
--  在创建系统触发器时,需要使用事件属性函数,常用的事件属性函数,如下
--  ora_client_ip_address      --返回客户端IP(Windows上面返回的IP可能为空)
--  ora_database_name          --返回数据库名
--  ora_login_user             --返回登陆的用户名
--  ora_sysevent               --返回触发触发器的系统事件名
--  ora_des_encrypted_password --返回用户DES加密后的密码
--系统触发器的基本语法
create [or replace] trigger trigger_name
after[before] logon[logoff] on database --固定写法,这就不存在for each row属性了,因为它是针对数据库的
begin
	trigger_body;
end;

--示例
create table log_sysevent(uname varchar2(20), logon_time date, logoff_time date, ip varchar2(20));
--登录触发器
create or replace trigger trigger_logon after logon on database --登录之后记录
begin
	insert into log_sysevent(uname,logon_time,ip) values(ora_login_user, sysdate, ora_client_ip_address);
end;
--退出触发器
create or replace trigger trigger_logoff before logoff on database --退出之前记录
begin
	insert into log_sysevent(uname,logoff_time,ip) values(ora_login_user, sysdate, ora_client_ip_address);
end;



定义并使用变量
分类:在编写PL/SQL程序时,可以定义变量和常量。在PL/SQL程序中包括以下四种常见类型
           1)标量类型(scalar)
           2)复合类型(composite)
           3)参照类型(reference)
           4)lob(large object)

标量的语法:PL/SQL中定义变量和常量的语法,如下
                     identifier  [constant]  datatype  [not  null]  [:=|  default  expr]
                     identifier-----名称
                     constant-----指定常量。需要指定它的初始值,且其值是不能改变的
                     datatype-----数据类型
                     not  null------指定变量不能为null
                     :=-------------给变量或常量指定初始值
                     default-------用于指定初始值
                     expr----------指定初始值PL/SQL表达式,可以是文本值、其它变量、函数等

--标量的案例
v_ename varchar2(10);                   --定义一个变长字符串
v_sal number(6,2);                      --定义一个小数,范围是-9999.99~~9999.99
v_sal number(6,2):=5.4                  --定义一个小数并给定初始值为5.4
v_hiredate date;                        --定义一个日期型数据
v_valid boolean not null default false; --定义一个布尔变量,其不能为空,且初始值为false

--标量的使用
--这里需要说明的是,PL/SQL块为变量赋值不同于其它的编程语言,需要在等号前加冒号,即(:=)
--下面以输入员工号,显示员工姓名、工资、个人所得税(税率为0.03)为例
declare
	v_ename varchar2(5);
	v_sal number(7,2);
	c_tax_rate number(3,2):=0.03;
	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; --计算所得税,PL/SQL中允许直接进行运算
	dbms_output.put_line('姓名:'||v_ename||' 工资:'||v_sal||' 交税:'||v_tax_sal);
end;

--若员工姓名超过5个字符,就会出现错误。那么为了降低PL/SQL程序的维护工作量,可以使用(%type)属性定义变量
--这样它会按照数据库列,来确定变量类型和长度,格式为(标识符 表名.列名%type),
v_ename emp.ename%type;
--复合变量(composite)
--用于存放多个值的变量,主要包括PL/SQL记录、PL/SQL表、嵌套表(nested table)、动态数组(varray)等

--复合类型之PL/SQL记录
--类似于高级语言中的结构体
--注意:当引用PL/SQL记录成员时,必须加上记录变量作为前缀,即(记录变量.记录成员)
declare
	--定义一个PL/SQL记录类型,类型的名字是emp_record_type,该类型包含三个数据:name、salary、title
	type emp_record_type is record(currName emp.ename%type, salary emp.sal%type, title emp.job%type);
	--定义一个变量,变量的名字是my_record,这个变量的类型是emp_record_type
	my_record emp_record_type;
begin
	select ename,sal,job into my_record from emp where empno=7788; --该变量my_record就可以接收三个数据
	dbms_output.put_line('员工名:'||my_record.currName||' 工资:'||my_record.salary);
end;

--复合类型之PL/SQL表
--相当于高级语言中的数组
--注意:高级语言中数组下标不能为负数,而PL/SQL是可以为负数的,且表元素的下标没有限制
declare
	--定义一个PL/SQL表类型,类型的名字是my_table_type,该类型用于存放emp.ename%type类型的数组
	--其中index by binary_integer表示该数组下标是按整数排序的,故其下标可以为负数,因为负整数也是整数
	type my_table_type is table of emp.ename%type index by binary_integer;
	--定义一个变量,变量的名字是my_table,这个变量的类型是my_table_type
	--PL/SQL中总是将变量名字写在前面,变量类型写在后面
	my_table my_table_type;
begin
	select ename into my_table(0) from emp where empno=7788;
	dbms_output.put_line('员工名:'||my_table(0));
end;
--参照变量
--用于存放数值指针的变量。通过使用参照变量,可使得应用程序共享相同对象,从而降低占用的空间
--编写PL/SQL程序时,可使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型

--参照变量之游标变量
--定义游标时,不需要指定相应的select语句
--但在使用游标(open)时需要指定select语句,这样一个游标就与一个select语句结合了

--使用PL/SQL编写一个块,要求输入部门号,显示该部门所有员工的姓名和工资
declare
	type my_emp_cursor is ref cursor; --定义一个游标类型
	test_cursor my_emp_cursor;        --定义一个游标变量,该变量的类型是my_emp_cursor
	v_ename emp.ename%type;           --定义变量,用于接收select到的ename值
	v_sal emp.sal%type;
begin
	open test_cursor for select ename,sal from emp where deptno=&no; --把test_cursor和一个select结合
	loop                                                             --使用(loop...end loop)循环取出数据
		fetch test_cursor into v_ename,v_sal;                    --使用fetch取出test_cursor游标指向的数据,并放到变量中
		exit when test_cursor%notfound;                          --判断test_cursor是否为空。若其为空,则退出循环
		dbms_output.put_line('员工名:'||v_ename||' 工资:'||v_sal);
	end loop;
end;

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

spark筑基篇-00-Spark集群环境搭建

说明 1 效果图 2 实验环境 3 配置ssh免登陆 集群搭建 1 配置环境变量 2 配置hadoop 21 core-sitexml 22 hdfs-sitexm 23 mapred-sitexml...
  • hylexus
  • hylexus
  • 2016年10月15日 18:35
  • 547

也学Oracle之筑基篇

完整版见https://jadyer.github.io/2012/03/20/oracle-user/
  • jadyer
  • jadyer
  • 2012年03月20日 17:09
  • 1063

docker筑基篇-00-docker简介

docker简介 docker组件 1 客户端和服务端 2 镜像 3 Registry 4 容器 docker的特点 和传统虚拟化技术的对比 1 docker简介docker是一个能够把开发的应用程序...
  • hylexus
  • hylexus
  • 2016年10月17日 00:39
  • 387

scala筑基篇-02-集合类型

集合库概览 继承层次图 说明 序列 List ListBuffer Array ArrayBuffer Queue Stack StringRichString Set 普通Set 有序的Set Ma...
  • hylexus
  • hylexus
  • 2016年10月06日 03:28
  • 441

spark筑基篇-01-Eclipse开发Spark HelloWorld

Spark这么火,越来越多的小伙伴开始搞大数据。 通过多方查阅资料,这个单机版的Spark的HelloWorld终于跑出来了。 此HelloWorld非彼HelloWorld,并不是打印出Hell...
  • hylexus
  • hylexus
  • 2016年09月21日 12:39
  • 2708

docker筑基篇-04-使用Dockerfile构建自己的镜像

构建自己的镜像 1 构建Dockerfile上下文 2 Dockerfile文件内容 3 构建镜像 4 启动容器 构建过程中的几个问题 1 Dockerfile大致流程 2 缓存上一篇文章介绍了使用d...
  • hylexus
  • hylexus
  • 2016年10月18日 23:54
  • 1763

JavaScript筑基篇(二)->JavaScript数据类型

科技优家 2016-10-17 22:27 说明 介绍JavaScript数据类型 目录 前言 参考来源前置技术要求 JavaScript的6种数据类型 哪6种数...

docker筑基篇-01-docker基础命令及常用选项

docker 基础命令 info version ps inspect rm start restart stop attach logs top exec run命令 常用选项 示例一 示例二 ...
  • hylexus
  • hylexus
  • 2016年10月17日 03:18
  • 376

docker筑基篇-05-Dockerfile常用指令

CMD 1 语法格式 2 示例 3 注意点 ENTRYPOINT 1 语法格式 2 示例 3 注意点 4 CMD和ENTRYPOINT的交互 WORKDIR 1 语法格式 2 示例 3 注意点 ENV...
  • hylexus
  • hylexus
  • 2016年10月22日 00:20
  • 572

scala筑基篇-01-List操作

List简介 特性 创建列表 操作 list的基本操作 list类的一阶方法 连接 长度 reverse apply indices zip mkString list类的高阶方法 foreach ...
  • hylexus
  • hylexus
  • 2016年09月13日 19:08
  • 456
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:牛刀小试PLSQL编程之筑基篇
举报原因:
原因补充:

(最多只允许输入30个字)