Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器
来源:互联网 发布:淘宝卖家查询工具 编辑:程序博客网 时间:2024/05/22 05:14
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 Worlddeclare ---说明部分(变量、光标和例外)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 %notfoundset 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。declarecursor c_student(v_id binary_integer) isselect * from student where id>v_id;beginfor v_student in c_student(10) loopdbms_output.put_line('name: '||v_student.name);end loop;end;-- 给对应级别的员工涨工资,key是empno
set serveroutput ondeclare -- 定义光标代表给哪些员工涨工资 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 EXACTcursor_space_for_time boolean FALSEopen_cursors integer 300session_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转成数字2225.Timeout_on_resource (等待资源时发生超时,分布式数据库)--系统例外:no_data_found
--系统例外:no_data_founddeclare 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 涨工资人数:count3、循环推出的条件: 工资总额>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 过程名(参数列表)asbegin..........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 numberas --定义变量接收薪水和奖金 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 )asbeginselect ename,sal,empjob into pename,psal,pjob from emp where mpno=eno;end在out参数中使用光标
·申明包结构包头(申明)包体(实现)·案例:查询某个部门中所有员工的所有信息 //ref(reference引用) cursor(光标)#包头
create or replace package mypackage astype empcursor is ref cursor;procedure queryEmpList(dno in number,empList out empcursor);end mypackage;#包体
create or replace package body mypackage asprocedure queryEmpList(dno in number,empList out empcursor) asbeginopen empList for select * from emp where deptno=dno;end queryEmpList;end mypackage;***********包体需要实现包头中声明的所有方法*********************触发器
应用场景:
1.复杂的安全性的场景(涉及到权限的问题);例子:下班时间不能插入数据库;2.数据的确认(涉及数据是否合理问题);3.数据的审计(涉及到数据的增、删、改的操作记录)---Oracle自身已经实现了审计;例子:涨工资越涨越高,低了就不能修改;4.数据的备份和同步(备份和同步重要);例子:把操作的时间、帐户等信息记录下来;例子:不同的数据表间进行同步备份什么是触发器:
数据库触发器是一个与表相关联的,存储的PL/SQL程序,每当一个特定的数据库操作语句(insert ,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义得语句序列触发器:
第一个触发器:每当成功插入新员工后,自动打印“成功插入新员工”触发器单词:trigger1.创建触发器的语法
create or replace trigger 触发器名称 before (after) delete (insert update) [of 列名] --of 列名表示该列发生变化时,触发该触发器 on 表名 [for each row[when条件]] --行级触发器的关键字 PLSQL块2.触发器的两种类型
语句级触发器:不管这条语句影响多少行,只执行一次(针对表) 行级触发器:每影响一行,都被触发一次。行级触发器中使用:old :new伪记录变量(针对行)create 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 securityempbefore 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_salarybefore update on empfor each rowbegin if :new.sal<:odl.sal thenraise_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_salaryafter update on empfor 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_salaryafter update on empfor each row beginupdate 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 1START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLECACHE 50 NOORDER### 建自动更新的触发器 ###CREATE OR REPLACE TRIGGER "SPORTS"."SPORTS_LINEUP_ID_TRIGGER"BEFORE INSERT ON "SPORTS"."LINEUP"FOR EACH ROWDECLAREnext_id NUMBER;BEGIN--Get the next id number from the sequenceSELECT sports_lineup_id_seq.NEXTVALINTO next_idFROM 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 ROWBEGINRAISE_APPLICATION_ERROR (-20000,'sports_lineup_id_update_trigger : Updates of the ID field'|| 'are not allowed. ');END
0 0
- Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器
- Oracle存储过程,函数,触发器
- Oracle数据库的存储过程、存储函数与触发器
- 数据库 day62 Oracle(pl/sql,存储过程,触发器)
- Oracle数据库操作大全(十三)——pl/sql编程(存储过程、函数、触发器)
- Oracle PL/SQL存储过程,函数,包,触发器的使用
- oracle中pl/sql编程---存储过程,函数,触发器,包
- PL/Sql Dev调试Oracle存储过程、触发器、函数
- Oracle PL/SQL存储过程,函数,包,触发器的使用
- PL/Sql Dev调试Oracle存储过程、触发器、函数
- ORACLE PL/SQL语法应用:游标,存储过程,触发器,函数
- Oracle数据库之视图、索引、存储过程、触发器、事务、函数
- Oracle数据库(触发器、存储过程、函数、包)
- Oracle数据库(触发器、存储过程、函数、包)
- oracle数据库if 循环 存储过程 函数 触发器
- Oracle存储过程,存储函数,触发器
- Oracle 存储过程、存储函数、触发器
- Oracle发布免费数据库开发工具SQL Developer 1.2
- 定时任务
- HDU-1039-Easier Done Than Said?(简单字符串处理)
- Kolya and Tanya [组合数学]
- poj 1094 拓扑排序
- 如何写一个简单的Servlet
- Oracle数据库开发--oracle sql developer工具/存储过程/函数/触发器
- org.springframework.core.task.TaskRejectedException:Executor[]did not accept task异常解决
- maven的两种打包方式
- TCP 的三次握手和四次挥手
- 数组类 【】 == !=运算符重载
- 第一个SDK程序:显示系统当前进程
- 变量与内存的关系
- 51nod-1358-浮波那契(构造矩阵)
- Postgresql 索引