牛刀小试PLSQL编程之筑基篇PL/SQL编程 --编写一个存储过程，该过程可以向某表中添加记录
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 -----------------------------------------------------------------------------------------------------------------
--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();
函数 --返回指定雇员的年薪
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);
触发器 --管理触发器(使用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; 定义并使用变量 --标量的案例
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;
|
|
|
