pl/sql程序基础知识:
pl/sql(procedural language/sql)oracle在标准sql上面的扩展,不仅简单的sql语句,还具有一般语言的特性:变量,常量,流程控制和循环,错误处理机制.是一个功能完善强大的过程化语言.
它的编程基本单位是块,复杂的功能都是多个块组成
我们来看看它的实列代码:
块结构:
declear --定义部分,可选
/*定义部分--变量,常量,游标,异常,复杂数据类型*/
begin --执行部分
/*pl/sql语句和SQL语句;*/
exception --异常处理部分,风险程度.可选
/*异常处理部分*/
end;
/ --和java对比
pl/sql写的代码段实际上保存在数据库中的数据对象,根据功能不同分为过程,函数,触发器.这些代码端口,可以直接在java程序中直接调用
它的目的:将程序逻辑变为数据库逻辑,降低程序端复杂度,让更好的进行模块化编程
它的作用:作用:提高整个程序系统的性能,让模块化编程思想更简单实现,降低网络传输量.更安全
当然pl/sql也有缺点:它的移植性不好
存储过程:
我们来来说说原理:
执行特定的操作过程,有参数输入(in),有结果输出(out).,他们都在内部的传递过程要借助变量,把数据传递到执行部分,输出吧结果输出到调用部分
它是怎么增加的呢??请看下面代码
--=案例一
create or replace procedure mypro is --这里的命名不要和你的表名重合,ORA-04044: procedure, function, package, or type is not allowed here
begin
--执行部分
insert into c##scott.emp(empno,ename) values('20','donghot');
end;
/ --完成并执行
--注意权限不足的问题insufficient privileges:每个用默认情况下只能在自己的方案下执行PLSQL
/*注意: 1 你的数据要你的表结构匹配,(数据的个数要够,数据类型也要匹配):ORA-00947: not enough values
2 数据和你的约束匹配
*/
通过上面的代码我们简单的认识了添加存储过程的代码;
pl/sql函数:
函数:用于返回特定的数据,在建立函数时,其头部必须包含return子句,在函数体内部必须包含return语句返回数据
案例:
-- 创建函数实例
create or replace function myfunction1(v_ename varchar2) return
number is v_Ysal number(9,2);
begin
select sal*12+nvl(comm,0)*12 into v_Ysal from emp where ename=v_ename;
return v_Ysal;
end;
--PLSQL中调用函数
var v_rev number;
call myfunction1('SCOTT') into:v_rev;
print v_rev;
--java中调用函数
select myfunction1('SCOTT') from dual;
rs.getInt(1);--获取返回值
pl/sql包:
逻辑上组合过程和函数,有规范和包体两部分组成
它是怎么调用的呢:
当调用包中的过程或是函数时,在过程和函数前面需要带有包名,如果是其他方案的包,还需要包前面带方案名;
来看看pl/sql增加包的案例吧!!!
--创建包实例
--创建包规范
create or replace package mypackage1 is
procedure update_sal(name varchar2, newsal number);
function annul_income(name varchar2) return number;
end;
/
--此规范字包含了过程和函数的说明,但是没有过程和函数的实现代码.
--所以需要重新建立包体,包体用于实现规范中的过程和函数
create or replace package body mypackage1 is
procedure update_sal(name varchar2,newsal number)
is
begin
update c##scott.emp set sal = newsal where ename=name;
end;
--函数开始
function annul_income(name varchar2)
return number is
return_salary number;
begin
select sal*12+nvl(comm,0) into return_salary from emp where ename=name;
return return_salary;
end;
end;
/
--调用:
--call/exec:包名.过程名(参数);
call mypackage1.update_sal('SCOTT',199);
--调用函数
var v_rev number;
call mypackage1.annul_income('SCOTT') into:v_rev;--试验是call可以,exec不行
print v_rev;
触发器:
触发器是一个隐含执行的存储过程,当定义触发器是,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,触发操作对象实际是一个pl/sql块
怎么创建触发器??老样子,我们主要看代码:
create sequence seq01;
create or replace trigger trig01
after insert or delete or update on dept
begin
if inserting then
insert into emp(empno,ename,hiredate) values (seq01.nextval,'insert', sysdate);
elsif updating then
insert into emp(empno,ename,hiredate) values (seq01.nextval,'update', sysdate);
elsif deleting then
insert into emp(empno,ename,hiredate) values (seq01.nextval,'delete', sysdate);
end if;
end;
变量:
标量类型:
标量类型的定义方式:
定义一个变长字符串
v_name varchar(10);
定义一个小数,切默认值是3.14
v_sal2 number(6,2):=3.14
定义一个小数
v_sal number(6,2);
定义一个日期类型
v_hiredate date;
定义一个特布尔变量,不能为空,初始值为false
v_valid boolean not null default false
如以上,下面定义代码:
--通过员工编号,显示姓名,工资,个税
--税率为0.03
declare
--声明变量
c_tax_rate number(3,2):=0.03;
--v_name varchar2(5);--有可能小于查到的数据
v_name emp.ename%type;--和数据所在列的数据类型一样; 变量名 表名.列名%type
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_name,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('name:'||v_name||' sal:'||v_sal||' tax:'||v_tax_sal);
end;
符合变量类型:
数据集合变量有数组和集合;
pl/sql是干么用的??是用来保存多个值的变量,类和结构体的,如下可见....
declare
type emp_record_type is record( --定义一个数据类型(记录类型):包含3个数据,有三个数据及其类型
name emp.ename%type,
salary emp.sal%type,
job emp.job%type);
v_record emp_record_type; --定义一个变量,数据类型是emp_record_type
begin
select ename,sal,job into v_record from emp where empno=7788;
dbms_output.put_line('ename:'||v_record.name);
dbms_output.put_line('sal:'||v_record.salary);
dbms_output.put_line('job:'||v_record.job);
end;
sql表:相当于数组.但是plsql下标可以为负数,并且元素下标没有限制
参照类型:
参照变量:是指用于存放数值指针的变量.通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用空间
游标变量(ref cursor)(在我们工作中是用的最多的):
使用游标有声明和使用,声明时不需要指定相应的select语句,但是当使用游标时,需要指定select语句,从而和selct语句绑定
来个案例吧!!一看便知晓;条件:编写一个块,输入部门编号,显示部门所有员工和工资
对象类型变量(ref obj_type)
控制结构:
条件语句:
if...thien
if...thien...else
if...thien...elsif...else
create or replace procedure myProcedure08(sNo number) is
--create or replace procedure myProcedure
v_job emp.job%type;
begin
select job into v_job from emp where empno=sNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=sNo;
--elseif v_job='MANAGER' then
--else if v_job='MANAGER' then
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=sNo;
else
update emp set sal=sal+200 where empno=sNo;
end if;
end;
/
循环语句:
loop
while
for
这里我们就不代码举列了....
循序控制语句:
goto:goto语句(强烈建议不用),无用
null:null语句不会执行任何语句操作,并且会直接将执行传递到下一句;增加程序的可读性;
异常:
来看看处理Oracle常见的异常:
declare
v_ename emp.ename%type;
begin
--
select ename into v_ename from emp where empno=&eno;
dbms_output.put_line('name: '|| v_ename);
exception
when no_data_found then
dbms_output.put_line('no found a');
end;
例外:
处理与Oracle无关的例外
create or replace procedure ex_test(nu number)
is
begin
update emp set sal=sal+1000 where empno=nu;
end;
create or replace procedure ex_test(nu number)
is
--定义一个例外
myex exception;
begin
update emp set sal=sal+1000 where empno=nu;
--sql%不表示没有update
if sql%notfound then
raise myex; --SQL> --raise myex:触发myex
end if;
exception
when myex then
dbms_output.put_line('m没用用户更新');
end;
视图:
视图就是一张虚拟表其内容由查询定义(SQL查询定义).和真的数据表一样,包含一系列带有名称的列和行数据.但是,视图并不在数据库中,以储存的数据值集形式存在啊
视图特性:不需要占用磁盘空间,表的区别;视图不能添加索引;使用视图可以简化复杂查询(多表联查);有更高的安全性,不同级别看不同的视图;视图本质上一张内存缓存表,所以和表不能重名
增加视图:create view 视图名 as select 语句 with [read only]
修改视图:create or replace view 视图名 as select 语句 with [read only]
删除视图:drop view 名字
序列:
oracle特有的,产生唯一的不间断的数字序列,用作表的主键
PL/SQL分页:
下面就不啰嗦了,直接上代码实现:
--oracle分页:
select * from emp;
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
--分页模版
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
create or replace procedure Paging
(
tableName in varchar2, --查询的表的名称
rowCount out number, --查询到的总记录数
pageSize in number, --每页显示数
pageCount out number, --总页码数 := rowCount/pageSize;
pageNow in number, --当前页码数
page_cursor out PackagePaging.page_cursor --返回数据的结果集,jav边遍历然后输出到html页面中
) is
--定义变量
v_sql varchar2(1000);
--定义两个整数,用于查询时候的起始数据行和结束数据行
v_begin number:=pageSize*(pageNow-1)+1; --起始的那个rowCount,起始那个数据的下标
v_end number:=pageSize*pageNow; --当前页的最后一个数据
---------------------------------------------
begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;--||';';
--v_sql:='select * from emp';
--给当前SQL语句的结果集绑定一个游标
open page_cursor for v_sql;
--------------------------------------------
--查询一共有多少个结果
v_sql := 'select count(*) from '|| tableName;
--执行SQL语句,把返回的值赋值给rowCount
execute immediate v_sql into rowCount;
--计算pageCount
if mod(rowCount,pageSize)=0 then
pageCount:=rowCount/pageSize;
else
pageCount:=rowCount/pageSize+1;
end if;
--关闭游标
--close page_cursor;
end;
PL/SQL无限分类:
如下:
drop table category;
--要点一:子类的pid必须是父类等于父类的id
create table category(
id number(10) primary key,
name varchar2(50),
pid number(10),
isleaf number(1),--0 代表非叶子节点,1代表叶子节点,
clevel number(2)
);--cherub模拟数据
--要点二:模拟数据必须满足这个二维结构
insert into category values (1, 'book1',0,0,0);
insert into category values (2, 'book2',1,0,1);
insert into category values (3, 'book3',2,1,2);
insert into category values (4, 'book4',2,0,2);
insert into category values (5, 'book5',4,1,3);
insert into category values (6, 'book6',1,0,1);
insert into category values (7, 'book7',6,1,2);
insert into category values (8, 'book8',6,1,2);
insert into category values (9, 'book9',2,0,2);
insert into category values (10, 'book10',9,1,3);create or replace procedure myProCat (
v_pid category.pid%type,
v_level binary_integer
) is
cursor c is select * from category where pid = v_pid; -- 要点:每次去满足这个条件,然后再把每条条数据中id传入到自己作为pid去查得下面的数据
v_preStr varchar2(1024) :='';
begin
for i in 0..v_level loop
v_preStr := v_preStr || '--';
end loop;--要点三:递归函数的书写和调用
--要点四:pl/sql 增强for循环的问题
for v_category in c loop --就是一个增强的for循环,v_category的结果相当于是个集合
dbms_output.put_line( v_preStr||v_category.name);
if(v_category.isleaf=0) then
myProCat(v_category.id,v_level+1);
end if;
end loop;
end;
以上就是PL/SQL的基本原理与操作
