Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器
什么是PL/SQL程序?
(1)PL/SQL( Procedure Language / SQL)
(2)PLSQL是Oracle对sql预言的过程化扩展
-- 指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL 语言具有过程处理能力。
oracle sql developer图形化工具
linux的安装.sh的文件用户名、口令:scott/tiger如果以sys登录,角色要选成SYSDBA主机名是服务器的IP地址,端口是1521;SID是数据库名字,orcl//打开行号:工具-首选项-代码编辑器-行装订线-显示行数//连接mysql:工具-首选项-第三方JDBC驱动程序-添加条目-mysql...-bin.jar
PL/SQL语法
---打开输出开关(默认关闭)
set serveroutput on
---打印Hello World
declare
---说明部分(变量、光标和例外)
begin
--程序体
dbms_output.put_line('Hello World');//调用内部存储过程
end;
备注--查看程序包的结构,以dbms_output为例:
desc dbms_output
定义基本变量(名字再前面,类型再后面)
基本类型:char,varchar2(长度),date,number(有效位,小数位),boolean,long
举例:var1 char(15);
引用型变量:emp%type (引用型变量:表%type 代表类型)
引用型变量
举例:my_name emp.ename%type;
引用emp表中ename列的类型作为变量my_name的类型,并且变量的类型始终与其保持一致
赋值方式两种: := 和into关键字
定义(引用),赋值(select S1,S2 into X1,X2 from emp where ),使用dbms_output.put_line(X1||'的薪水'||X2);
记录型变量:emp%rowtype (代表表中的一行的类型,)
记录型变量 代表表中的一行
举例:emp_rec emp%rowtype;
记录型变量分量的引用
emp_rec.ename := 'ADAMS';
declare说明部分——变量定义
--使用基本变量类型
declare
--基本数据类型
pnumber number(7,2);//
--字符串变量
pname varchar2(20);
--日期变量
pdate date;
begin
pnumber :=1;
pname:='Tom';
pdate:=sysdate;
DBMS_OUTPUT.PUT_LINE(pnumber);
DBMS_OUTPUT.PUT_LINE(pname);
DBMS_OUTPUT.PUT_LINE(pdate);
--计算明天的日期
DBMS_OUTPUT.PUT_LINE(pdate+1);
end;
if..then .. elsif
/*
判断用户从键盘输入的数字
1、如何使用if语句
2、接收一个键盘输入(字符串)
*/
set serveroutput on
--接收一个键盘输入
--num:地址值,含义是:在该地址上保存了输入的值
accept num prompt'请输入一个数字';
declare
--定义变量保存用户从键盘输入的数字
pnum number := #
begin
--执行if语句进行条件判断
if pnum = 0 then dbms_output.put_line("您输入的数字是0");
elsif pnum = 1 then dbms_output.put_line("您输入的数字是1");
elsif pnum = 2 then dbms_output.put_line("您输入的数字是2");
else dbms_output.put_line("其他数字");
end if;
end;
循环(while,loop,for)推荐使用loop循环,它对于操作光标有优势
oracle中没有自增++ 自身=自身+1即可。
WHILE 条件 LOOP ... END LOOP;
LOOP EXIT WHEN 条件 ... END LOOP;
FOR I IN 1..5 (必须为连续区间)LOOP ... ;END LOOP;
光标--就是一个结果集(Result Set)
cursor 光标名[(参数名 数据类型[,参数名 数据类型].....)]
is select 语句:
光标的属性:
%found %notfound 此为boolen类型
%isopen 判断光标是否打开
%rowcount 影响的行数
--查询并打印员工的姓名和薪水
--光标的属性 %found %notfound
set serveroutput on
declare
--定义一个光标
cursor cemp is select ename,sal from emp;
--为光标定义对应的变量(引用变量)
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open cemp;
loop
--抓取一条记录
fetch cemp into pename,psal;
exit when cemp%notfound;
dbms_output.putline(pename||'的薪水是'||pssal);
end loop;
--关闭光标
close cemp;
end;
--for 循环遍历,最简单,用的最多,不需要声明 v_student、 打开关闭游标、 fetch。
declare
cursor c_student(v_id binary_integer) is
select * from student where id>v_id;
begin
for v_student in c_student(10) loop
dbms_output.put_line('name: '||v_student.name);
end loop;
end;
-- 给对应级别的员工涨工资,key是empnoset serveroutput on
declare
-- 定义光标代表给哪些员工涨工资
cursor cemp is select empno,empjob from emp;
pempno emp.empno%type;
pempjob emp.empjob%type;
begin
rollback; //事务回滚
--打开光标
open cemp;
--loop fetch ** into ** ; exit when condition;block;end loop;
loop
--取出一个员工
fetch cemp into pempno,pempjob;
--loop 退出条件
exit when cemp%notfound;
-- 判断员工的职位
if pempjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
-- if condition then block ;elsif condition then block;else block;end if;
elsif pempjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
-- 关闭光标
close cemp;
-- oracle的默认事务隔离级别是read committed
--事务的ACID 原子性、一致性、隔离性、持久性
commit;
end ;
--使用 show parameter cursors;语句查看包含cursors的参数设置
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
--默认的一个会话最多可以打开300个光标
修改光标数的限制:
alter system set open_cursors=400 scope = both;
其中scope的取值:both,memory,spfile
memory:表示只更改当前实例,不更改参数文件
spfile:表示只更改参数文件,不更改当前示例,数据库服务需要重启
both:表示上边两个同事更改
例外
系统例外: 1.No_date_found(没有找到数据)
2.Too_many_rows (select...into语句匹配多个行)
3.Zero_Divide (被零除)
4.Value_error (算术或转换错误) 负数开平方等and abc转成数字222
5.Timeout_on_resource (等待资源时发生超时,分布式数据库)
--系统例外:no_data_found
--系统例外:no_data_found
declare
pename emp.ename%type;
begin
select ename into pename from emp where empno=222222;
SYS.DBMS_OUTPUT.PUT_LINE(pename);
exception
when no_data_found then sys.dbms_output.put_line('没有对应的记录');
when others then sys.dbms_output.put_line('其它例外');
end;
瀑布模型
1.需求分析
2.设计
2.1概要设计
2.2详细设计
3.编码coding
4.测试Testing
5.上线(部署)
案例2:涨工资问题,从最低工资的员工开始涨起,每人涨10%,工资总额不能超过50000,返回涨工资的人数和涨后的工资总额
1、用到的sql语句:
select empno,sal from emp order by sal;
select sum(sal) into totalsal from emp;
2、需要声明的变量:
工资总额:totalsal 涨工资人数:count
3、循环推出的条件:
工资总额>5W or 全部员工都涨完工资
开启光标>开启循环>取值>退出条件>结束循环>关闭光标*/set serveroutput on;
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
pcount number:=0;
psum number;
begin
select sum(sal) into psum from emp;
open cemp;
loop
fetch cemp into pempno,psal;
exit when cemp%notfound or psum>250000 or psum+psal*0.1>250000;
update emp set sal=psal*1.1 where empno=pempno;
psum:=psum+psal*0.1;
pcount:=pcount+1;
end loop;
close cemp;
commit;
dbms_output.put_line('涨后的工资总额为'psum);
dbms_output.put_line('涨工资的总人数为'pcount);
end;
存储过程和存储函数
数据库存储过程:指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数
·相同点:完成特定功能的程序
·不同点:是否用return语句返回值。存储函数可以return返回值。存储过程不可以通过return语句返回函数值。
1.创建存储过程
create or replace procedure 过程名(参数列表)
as
begin
..........PLSQL子程序体;
end;
2.执行存储过程
--1.exec 存储过程名();
--2.begin
存储过程名();
end;
带参数的存储过程
举例:为指定的员工,涨100块钱的工资;并且打印涨前和涨后的薪水
1、创建一个带参数的存储过程:
给执行的员工涨100块钱的工资,并且打印涨前和涨后的薪水
create or replace procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的薪水
psal emp.sal%type;
begin
--得到员工涨前的薪水
select sal into psal from emp where empno=eno;
--给该员工涨100
update emp set sal=sal+100 where empno=eno;
--需不需要commit?
--注意:一般不在存储过程或存储函数中,commit和rollback
--打印
dbms_output.put_line('涨前:'||psal||'涨后:'(pasl+100))
end;
/
2、如何调用:
begin
raisesalary(7839);
raisesalary(7566);
commit;
end;
存储过程的调试
1.调试存储过程最好放到Oracle数据库所在的系统或虚拟机上,解压SQL developer ,双击运行。
2.为了确保存储过程或函数是可调试的,右键“以编译并进行调试”,点击红色按钮“调试”
3.利用已写好的调用函数进行调试。
1.函数的定义
是一个命名的存储程序,可带参数,并返回一个计算值。必须有return 子句,用于返回函数值。
2.创建存储函数语法
create or replace function 函数名(参数列表)
return 函数值类型
as
begin
PLSQL子程序体;
end;
3.表达式中某个字段为空时,表达式返回值为空。为防止含有表达式的返回值错误,在可能为空的字段上加上NVL(字段名,0)。
--查询某个员工的年收入
create or replace function queryemp_income(eno number) return number
as
--定义变量接收薪水和奖金
p_sal emp.sal%type;
p_comm emp.comm%type;
begin
select sal,comm into p_sal,p_comm from emp where empno=eno;
--nvl为遇空函数,如果p_comm为空则返回0
return nvl(p_comm,0)+p_sal*12;
end;
/
1.存储过程和存储函数的区别
存储函数可以有一个返回值,存储过程没有返回值
2.in out 参数
存过和函数都可以通过out 指定一个或多个输出参数。可以利用out参数,实现多个返回值。
3.使用存过和存储函数的原则
只有一个返回值的话,用存储函数;否则,用存储过程。
create or replace procedure query
(eno in numbr,
pename out varchar2,
psal out number,
pjob out varchar2
)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where mpno=eno;
end
在out参数中使用光标
·申明包结构
包头(申明)
包体(实现)
·案例:查询某个部门中所有员工的所有信息 //ref(reference引用) cursor(光标)
#包头
create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;
#包体
create or replace package body mypackage as
procedure queryEmpList(dno in number,empList out empcursor) as
begin
open empList for select * from emp where deptno=dno;
end queryEmpList;
end mypackage;
***********包体需要实现包头中声明的所有方法*********************
触发器
应用场景:
1.复杂的安全性的场景(涉及到权限的问题);
例子:下班时间不能插入数据库;
2.数据的确认(涉及数据是否合理问题);
例子:涨工资越涨越高,低了就不能修改;
3.数据的审计(涉及到数据的增、删、改的操作记录)---Oracle自身已经实现了审计;
例子:把操作的时间、帐户等信息记录下来;
4.数据的备份和同步(备份和同步重要);
例子:不同的数据表间进行同步备份
什么是触发器:
数据库触发器是一个与表相关联的,存储的PL/SQL程序,
每当一个特定的数据库操作语句(insert ,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义得语句序列
触发器:
1.创建触发器的语法
create or replace trigger 触发器名称
before (after)
delete (insert update) [of 列名] --of 列名表示该列发生变化时,触发该触发器
on 表名
[for each row[when条件]] --行级触发器的关键字
PLSQL块
2.触发器的两种类型
语句级触发器:不管这条语句影响多少行,只执行一次(针对表)
行级触发器:每影响一行,都被触发一次。
行级触发器中使用:old :new伪记录变量(针对行)
第一个触发器:每当成功插入新员工后,自动打印“成功插入新员工”触发器单词:triggercreate trigger saynewem //创建触发器名称
after insert //在插入操作以后
on emp //针对emp的表
declare //操作体
begin
//触发器操作的内容
end;
触发器案例一 : 复杂的安全性检查
例如禁止在非工作时间插入数据/**
1.周末: to_char(sysdate,'day') in ('星期六',‘星期日’)
2.上班前,下班后: to_number(to_char(sysdate,'hh24')) not between 9 and 18
/
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六', '星期日') or
to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
触发器案例二: 数据的确认
涨工资不能越涨越少
:old 表示操作该行之前这一行的值
:new 表示操作该行之后这一行的值
create or replace trigger check_salary
before update
on emp
for each row
begin
if :new.sal<:odl.sal then
raise_application_error(-20002,'涨后薪水不能少于涨前薪水。 涨后薪水为:'||:new.sal ||'涨前的薪水:'||:old.sal);
end if;
end;
触发器案例三:基于值的审计
例子:给员工涨工资,当涨后的薪水超过6000块时候,审计该员工的信息
--创建表,用于保存审计信息
create table audit_info(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
if :new.sal>6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
触发器应用场景四: 数据的备份和同步
例子:当给员工涨完工资后,自动备份新的工资资料到备份表中
create or replace trigger trigger_sync_salary
after update
on emp
for each row
begin
update emp_back set sal=:new.sal where empno=:new.empbo;
end;
触发器自增主键: 使用序列和触发
### 建表 ###
CREATE TABLE "SPORTS"."LINEUP"
("ID" NUMBER NOT NULL,
"TYPE" NUMBER(3) NOT NULL,
"BODY" VARCHAR2(100) NOT NULL,
"HITS" NUMBER(10) DEFAULT 0 NOT NULL,
PRIMARY KEY("ID"))
TABLESPACE "TS_SPORTS"
### 建序列 ###
CREATE SEQUENCE "SPORTS"."SPORTS_LINEUP_ID_SEQ"
INCREMENT BY 1
START WITH 1
MAXVALUE 1.0E28
MINVALUE 1 NOCYCLE
CACHE 50 NOORDER
### 建自动更新的触发器 ###
CREATE OR REPLACE TRIGGER "SPORTS"."SPORTS_LINEUP_ID_TRIGGER"
BEFORE INSERT ON "SPORTS"."LINEUP"
FOR EACH ROW
DECLARE
next_id NUMBER;
BEGIN
--Get the next id number from the sequence
SELECT sports_lineup_id_seq.NEXTVAL
INTO next_id
FROM dual;
--Use the sequence number as the primary key
--for the record being inserted.
:new.id := next_id;
END;
### 建保护 PRIMARY KEY 的触发器 ###
CREATE OR REPLACE TRIGGER "SPORTS"."LINEUP_ID_UPDATE_TRIGGER"
BEFORE UPDATE OF "ID" ON "SPORTS"."LINEUP"
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR (-20000,
'sports_lineup_id_update_trigger : Updates of the ID field'
|| 'are not allowed. ');
END