Pl/sql
Pl/sql是oracle 在标准化语言上的扩展,pl/sql不仅允许嵌入sql语句,还可以动议变量,常量,允许使用条件语句和循环语句,允许使用例外处理各种问题
优点:
1. 提高应用程序的性能
2. 模块话设计思想
3. 减少网络传输
4. 提高安全
缺点:移植性不好
存储过程在表中插入一条记录
create table mytest(name varchar2(20),passwd varchar(18));//创建一张表
create or replace procedure sp_prol isbegin
--执行部分
inster into mytest values (‘likang’,’123456’);
end;
/ 执行
查看错误信息:show error;
调用该过程:
1. exec 过程名(参数…);
2. call 过程名(参数…);
create or procedure sp_pro is
begin
--执行
delete from mytest where name=’likang’;
eng;
单行注释--多行注释/* */
标识符号的命名规范
定义变量建议用v_作为前缀 v_sal;
定义常量建议用c_作为前缀 c_sal;
定义游标建议用 cursor作为后缀emp_cursor
定义例外建议用 e_作为前缀e_error
块:识pl/sql 的基本单元,编写pl/sql程序实际上就是pl/sq块
块的结构:declear –定义常量,变量,游标,复杂数据类型
begin--执行部分
exception—处理运行各种错误
end;
declare
v_enamevarchar2(10);--定义变量
begin
selectename ,sal into v_ename ,v_sal from emp where empno=&no;--输入值
dbms.output.put_line(‘姓名:’||v_ename ||’ 工资:’||v_sal);
end;
/
异常捕获exception when no_data_found then
dbms_output.line(‘提示’)
过程:执行特定的操作,当建表识指定输入参数,也可以指定输出参数。通过在过程中的使用输入参数,可以将数据传到执行部分。通过输出参数,可以将执行部分的数据传输到应用环境中。create procedure 建立过程调用过程exec,call
create procedure sp_proemp(enamevarchar2,esal number) is
begin
update emp set sal=esal where ename=ename;
end;
函数
create function sp_func(v_ename varchar2)return number is Sal(7,2);
begin
--执行
select sal into Sal from emp whereeanme=v_eanme;
--返回
return Sal;
end;
包:用于在逻辑上组合过程和函数,它由规范和包体两部分组成
create pachage lk_package is
procedureupdate_sal(bane varchar ,newsal number);
functionlk_func(name varchar2) return;
end;
包的规范只包含过程和函数的说明,没有实现
创建包体
create package body lk_package is
--过程的具体执行
procedure lk_pro(name varchar2,newSalnumber) is
begin
updateemp set sal=newSal where enae=namel
end;
--函数的具体执行
function lk_func(name varchar2) returnnumber is S_sal number(7,2)
begin
select sal intoS_sal from emp where ename=name;
end;
end;
在调用包的过程或者函数,需要带包名。
触发器:隐含的执行的存储过程,定义触发器时,必须要指定触发器的事件和触发器的操作,常用的触发器包括inster ,update,delete语句,触发器实际上就是pl/sql块,可以使用create trigger 来创建触发器
定义变量和常量
标量(scalar)
定义边唱的字符串:v_name varchar2(10); 小数:v_sal number(6,2):=8.8
定义日期类型: v_birthdy date;
Boolean: v_valid Boolean not null defaultfalse;
Declare
--定义变量
c_tax_rate number(3,2):=0.03; v_eanme emp.enam%type;v_sal number(7,2);v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal fromemp where empno=&no;
v_tax_sal:=v_sal*c_tax_rate;
dbms_oupput.put_line(‘name:’||v_ename||’sal:’||v_sal||’tax:’||v_tax_sal);
end;
%type解决溢出与匹配的数据类型相同
复合变量(composite)
记录用于存放多个值,和结构体类似
decare
type emp_record_type is
record(
name emp.ename%type,
salary emp.sal%type,
title emp.jop%type);
lk_record emp_record_type;
begin
select ename,sal,jop into lk_record fromemp where empno=7788;
dbms_output.put_line(‘员工’||lk_record.name);
end;
表类似于数组
declare
type lk_table_type is table ofemp.ename%type index by binary_integer;//下表是整数
lk_table lk_table_type;
begin
select ename into lk_table(0) from empwhere empno=7788;
dbms_output.put_line(‘员工’||lk_table(0));
end;
参照变量
用于存放数组指针的变量,通过使用参照变量,可以时的应用程序共同使用相同的变量,从而降低占用的空间。分为游标变量(ref_cursor)和对象类型变量(ref obj_type)两种参照变量
declare
--定义
type lk_emp_cursor is ref cursor;//定义游标类型
lk_cursor lk_emp_cursor;//定义游标变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
open lk_cursor for select ename,sal fromemp where dept=&no;
loop//循环取出
fetchlk_cursor into v_ename,v_sal;
exit when lk_cursor%nutfound;//退出条件
dbms.output.put_line(v_ename||”+”v_sal);
end loop;
close lk_cursor;
end;
pl/sql中应用if –then,if—then—else.if—thenelsif==else
编写一个过程,输入员工号,如果该员工的工资低了2000,那么就给他的工资增加1000;
create procedure lk_pro(lkname varcahar2)is
--定义
v_esal emp.sal%type;
begin
--执行
select sal into v_easl fromemp whereeanme=lkname;
if v_esal<2000 then
update emp set sal=sal+100 where ename=lkname;
end if;
end;
二重条件分支 if--then--else
编写过程,可以输入一个员工号,如果该雇员的补足不是0就加100,如果为0 就设定为200
create procedure lk_pro(name varchar2) is
--定义
v_comm emp.sal %type;
begin
--执行
select nvl(com,0) minto v_comm from emp whereename=name;
if v_comm<>0 then
update emp set comm+=1000 where ename=name;
else
update emp set comm+=500 where ename=name;
end if;
end;
多重条件分支
if –then—elsif—else
编写过程,可以输入一个员工号,根据角色不同分别增加不同的工资
create procedure lk_pro(eno number) is
--定义
v_job emp.jop%type;
--执行
begin
select jop into v_jop from emp whereempno=eno;
if (v_jop=’总经理’) then
update sem set sal=sal+100 where empno=eno;
elsif (v_jop=’经理’) then
update sem set sal=sal+500 where empno=eno;
elsif(v_jop=’组长’)then
update sem set sal=sal+1000 whereempno=eno;
else
update sem set sal=sal+2000 whereempno=eno;
end if;
end;
循环语句-loop(类似与do..while)
编写过程,向表中一次添加多条数据
create or replace procedure lk_pro(usernamevarchar2,endnum number) is
--定义循环变量
v_index number:=0;
--执行
loop
insert into user values(v_insex,username);
--结束条件
exit when v_insex<endnum;
v_index:=v_index+1;
end loop;
end;
while循环
create procudere lk_pro(usernamevarchar2,endnum number) is
--定义变量
v_index number:=10;
begin
while(v_index<endnum) loop
insert into user values(v_insex,username);
v_index=v_index+1;
end loop;
end;
for循环
begin
fori in reverse 1..10 loop
--sql语句
endloop;
end;
//控制变量在隐含条件中不停增加
goto语句用于跳转到指定标号的语句中执行。
null;相当于空语句;