【2017年整理】存储过程,方法,触发器,视图
PL/SQL 编程 /*procedural language/sql*/-1、过程、函数、触发器是 pl/sql 编写的-2、过程、函数、触发器是在 oracle 中的-3、pl/sql 是非常强大的数据库过程语言-4、过程、函数可以在 java 程序中调用-提高效率：优化 sql 语句或写存储过程-pl/sql 移植性不好-IDE(Integration Develop Environment)集成开发环境-命令规则：-变量(variable) v_-常量(constant) c_-指针、游标(cursor) _cursor-例外、异常(exception) e_-可定义的变量和常量：-标量类型：scalar-复合类型：composite -存放记录、表、嵌套表、varray-参照类型：reference-lob(large object)PL/SQL 基本语法-例：创建存储过程create or replace procedure pro_addisbegininsert into mytest values('韩 xx','123');end;exec pro_add; -调用-查看错误信息show error;-调用过程exec 过程(c1,c2,.);call 过程(c1,c2,.);-打开/关闭输出选项set serveroutput on/off-输入&-块结构示意图declare -定义部分，定义常量、变量、游标、例外、复杂数据类型begin -执行部分，执行 pl/sql 语句和 sql 语句exception -例外处理部分，处理运行的各种错误end; -结束-实例演示declarev_ival number(4) :=100; -声明并初始化变量-v_dtm date;v_dtm syslogs.dtm%type; -取表字段类型v_content varchar(512);beginv_ival := v_ival * 90; -赋值运算insert into syslogs values(seq_syslogs.nextval,10,sysdate,'v_ival='|v_ival,user);-数据库存储dbms_output.put_line('v_ival'|v_ival);select count(*) into v_ival from syslogs;-使用 select 查询赋值-select ename,sal into v_name,v_sal from emp where empno=&aa;insert into syslogs values (seq_syslogs.nextval,10,sysdate,'日志条数='|v_ival,user);dbms_output.put_line('日志条数'|v_ival);-获取日志序号=11 的日志时间和日志内容select dtm , contentinto v_dtm,v_contentfrom syslogswhere logid=14;insert into syslogs values (seq_syslogs.nextval,'10',sysdate,'v_dtm='|v_dtm|'v_content='|v_content,user);dbms_output.put_line('v_dtm='|v_dtm|'v_content='|v_content);-修改日志序号=11 的日志记录人update syslogsset whois='PL/SQL.'|v_ival where logid = 14;-delete syslogs where logid=15;-分支流程控制if v_ival>50 thendbms_output.put_line('日志需要清理了');elsedbms_output.put_line('日志空间正常！');end if;-Loop 循环v_ival :=0;loopexit when v_ival>3;-循环体v_ival := v_ival+1;dbms_output.put_line('loop 循环：'|v_ival);end loop;-While 循环v_ival := 0;while v_ival :old.title thenraise_application_error(-20001,'不允许修改日志类型名称数据！'); -抛出异常end if;insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行更新数据！',user);elsif deleting thenraise_application_error(-20001,'不允许删除表中的数据！');insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行删除数据！',user);end if;end ;-test!insert into logtypes values(30,'test log');delete from logtypes where tid = 30;update logtypes set title = 'test log' where tid = 30;select * from syslogs order by dtm desc;select * from logtypes ;案例-创建表create table emp2 (name varchar2(30),sal number(8,2);insert into emp2 values('simple',99999);insert into emp2 values(&a,&b);-存储过程案例：-修改员工工资create or replace procedure pro_input(t_name in varchar2,t_sal in number)isbeginupdate emp2 set sal = t_sal where name=t_name; end;-Test!declarebeginpro_input('simple',2000);end;select * from emp2;-函数案例：create or replace function fun_test(t_name varchar2) return number is yearSal number(7,2);beginselect sal*12 into yearSal from emp2 where name = t_name;return yearSal;end;-包案例：create package pac_test is -创建一个包 pac_testprocedure pro_input(t_name varchar2,t_sal number); -声明该包有一个过程 pro_inputfunction fun_test(t_name varchar2) return number; -声明该包有一个函数 fun_testend;-包体案例:create package body pac_test isprocedure pro_input(t_name in varchar2,t_sal in number)isbeginupdate emp2 set sal = t_sal where name=t_name; end;function fun_test(t_name varchar2) return number is yearSal number(7,2);beginselect sal*12 into yearSal from emp2 where name = t_name;return yearSal;end;end ;-调用包中的函数或过程call pac_test.pro_input('summer',1000);call pac_test.fun_testselect pac_test.fun_test('simple') from dual;-案例：select * from emp2;-下面以输入员工工号，显示雇员姓名、工资、个人所得税-税率（0.03）。declarec_tax_rate number(3,2):=0.03; -常量，税率-v_name varchar2(30);v_name emp2.name%type;-v_sal number(8,2);v_sal emp2.sal%type;v_tax_sal number(8,2);begin-执行select name,sal into v_name,v_sal from emp2 where name = &na;-计算所得税v_tax_sal:=v_sal*c_tax_rate;-输出dbms_output.put_line('姓名：'|v_name|' 工资'|v_sal|' 交税'|v_tax_sal); end;-pl/sql 记录实例declare-定义一个 pl/sql 记录类型 emp_record_type ，类型包含 2 个数据，t_name,t_saltype emp_record_type is record(t_name emp2.name%type,t_sal emp2.sal%type);-定义一个 record_test 变量，类型是 emp_record_typerecord_test emp_record_type;beginselect name,sal into record_test from emp2 where name = 'simple'dbms_output.put_line('员工工资:'|record_test.t_sal);end;-pl/sql 表实例declare-定义了一个 pl/sql 表类型 emp_table_type 该类型是用于存放 emp.name%type 元素类型 的数组- index by binary_integer 下标是整数type emp_table_type is table of emp2.name%type index by binary_integer;-定义一个 table_test 变量table_test emp_table_type;begin -table_test(0)下标为 0 的元素select name into table_test(0) from emp2 where name='summer'dbms_output.put_line('员工:'|table_test(0);end;-案例-显示该部门的所有员工和工资declare-定义游标类型 emp_cursortype emp_cursor is ref cursor;-定义一个游标变量cursor_test emp_cursor;-定义变量v_name emp2.name%type;v_sal emp2.sal%type;begin-执行-把 cursor_test 和一个 select 结合open cursor_test
