pl/sql程序语言的组成:
程序由块结构组成,包含变量,各种不同的程序控制结构,异常处理模块,子程序(过程,函数,包),触发器等
pl/sql 注释:
/* 和 */ 之间多行注释
以--开始的单行注释
基本结构:
4个基本结构:声明, 执行体开始,异常处理,执行体结束
declare
.......
begin
.....
exception
......
end;
(1) 定义,声明部分
declare开始,到begin结束。 定义程序所需要变量,常量,数据类型,游标,异常处理名称等
定义,声明部分是可选的
(2) 执行部分
begin开始,end结束,(异常处理部分,那么从exception结束)
实现功能部分,通过一系列语句和流程控制,实现数据查询,数据操作,事务控制,游标处理等
这部分是必须的
(3) 异常处理部分
以exception开始,到end结束
可选的部分
(4)执行体结束
PLSQL中的SQL语句有:
select、dml、commit、rollback、savepoint、cursor
特殊强调:PLSQL不支持DCL,不要问为什么。(DBMS_SQL package allows you to issue DDL and DCL statements.)
SELECT
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
编程pl/sql,输出empno为7800的记录姓名:
SQL> grant create procedure to scott; -->授权
SQL> set serveroutput on -->使结果显示
SQL> declare
2 v_name varchar2(10);
3 begin
4 select ename into v_name from emp where empno=7900;
5 dbms_output.put_line('7900 is name is '||v_name);
6 end;
7 /
-----------------------------------------
SQL> set serveroutput on
declare
v_name varchar2(10);
empno number;
begin
select ename into v_name from emp where empno=&empno;
dbms_output.put_line('employees name is '||v_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line('not found record,please try again :) ');
end;
/
命名块:
指一次编辑多次执行的pl/sql程序,包括自定义函数,过程,函数,包,触发器
语法要素:
包括字符集,标识符,文本,分隔符,注释等
字符集:
大小写英文字母,a-z,A-Z
0~9
空白符,制表符,空格,回车
特殊字符,~,!@#$%^&*()_ +,=,;,./' 等 '
数值型,字符型,日期型,布尔型文字(true,flase,null)
分隔符 ---->算术运算,数值比较,给变量赋值
+ - * / **(乘方)
= <= >= < > != ^= <> ~=(这4个均为不等于)
:=(赋值) ` (列表分隔符)
@(数据库连接分隔符)
; (语句结束)
|| 字符串连接
=> 给其他传递参数
% 属性标识器 type rowtype
流程控制:
1.分支结构
(1) 多分支的IF
IF condition1 THEN
statements1;
ELSIF condition2 THEN
statements2;
......
ELSE
statement;
END IF;
作用:判断condition1是否成立,如果成立,执行statements1
如果不成立,判断condition2是否成立,如果成立,执行statements2,...
如果都不成立,执行statement;
(2)等值比较的CASE多分支语句
CASE expression
WHEN result1 THEN
statement1;
WHEN result2 THEN
statement2;
......
ELSE
statement;
END CASE;
(3)条件比较的CASE多分支
CASE <---这里没有表达式
WHEN condition1 THEN
statement1;
WHEN condition2 THEN
statement2;
.......
ELSE
statement;
END CASE;
eg:
CASE
WHEN v_sal>2000 and v_sal<2501 THEN
eg:调薪水
job ---manager sal*1.1
job----salesman sal*1.2
sal*2
ly---if----------------------
set serveroutput on
declare
vjob emp.job%type;
vsal emp.sal%type;
vip emp.empno%type;
news emp.sal%type;
begin
vip:=&inp;
select job,sal into vjob,vsal from emp where empno=vip;
if vjob='NAMAGER' then
update emp set sal=sal*1.1 where empno=vip;
news:=vsal*1.1;
elsif vjob='SALESMAN' then
update emp set sal=sal*1.2 where empno=vip;
news:=vsal*1.2;
else update emp set sal=sal*2 where empno=vip;
news:=vsal*2;
end if;
dbms_output.put_line('empno: '||vip||' |old sal: '||vsal||' |new sal:'||news);
exception
when no_data_found then
dbms_output.put_line('not record');
end;
/
---------------------------
ly--------case----------------
set serveroutput on
declare
vjob emp.job%type;
vsal emp.sal%type;
vip emp.empno%type;
news emp.sal%type;
begin
vip:=&inp;
select job,sal into vjob,vsal from emp where empno=vip;
case vjob
when 'MANAGER' then
news:=vsal*1.1;
when 'SALESMAN' then
news:=vsal*1.2;
else
news:=vsal*2;
end case;
update emp set sal=news where empno=vip;
dbms_output.put_line('empno: '||vip||' |old sal: '||vsal||' |new sal:'||news);
exception
when no_data_found then
dbms_output.put_line('not record');
end;
/
--------------------------
2、循环结构
(1)LOOP循环
LOOP
statement;
EXIT WHEN condition;
END LOOP;
作用: 一直执行statement直到condition成立
set serveroutput on
declare
v_n int:=1;
v_s int:=0;
begin
loop
v_s:=v_s+v_n;
exit when v_n>=100;
v_n:=v_n+1;
end loop;
dbms_output.put_line('1+2+3+.....100='||v_s);
end;
/
(2)WHILE 循环
WHILE condition LOOP
statement;
END LOOP;
作用: 判断condition是否成立,成立执行statement
set serveroutput on
declare
v_n int:=1;
v_s int:=0;
begin
while v_n<=100 loop
v_s:=v_s+v_n;
v_n:=v_n+1;
end loop;
dbms_output.put_line('1+2+3+.....100='||v_s);
end;
/
(3)FOR 循环
FOR var_name IN [reverse] lowest_value ..higest_value LOOP
statement;
END LOOP;
作用: 让var_name 在IN 后面指定的范围内进行循环
每执行一次,var_name会自动的加1。
set serveroutput on
declare
v_n int:=1;
v_s int:=0;
begin
for v_n in 1..100 loop
v_s:=v_s+v_n;
end loop;
dbms_output.put_line('1+2+3+.....100='||v_s);
end;
/
set serveroutput on
declare
v_n int:=1;
v_sum int:=0;
begin
FOR v_n IN reverse 1..10 LOOP
v_sum:=v_sum+v_n;
dbms_output.put_line(v_n);
END LOOP;
end;
/
(4)GOTO 跳转语句
格式:GOTO label_name
使用GOTO语句注意:
GOTO后的label_name必须存在
标号后至少要有一条可执行语句
不能从一个IF语句外,跳到其内部
不能从一个循环体外跳到其内
不能从一个子程序外跳到其内
(5)NULL语句
NULL语句被称为空语句,不执行任何操作,有些场合有要求,必須有要执行语句,但此时
还不知道要执行的语句是什么,可用NULL语句来替代,避免语法错误
==================================================
编程pl/sql
eg:输出empno为7800的记录姓名:
set serveroutput on
set verify off -->赋值前后不比较,默认为on
declare
v_name varchar2(8); --> 声明变量
pi constant number:=3.1415 --> 声明常量
empno number;
begin
select ename into v_name from emp where empno=&empno; --> where后面读输入,&后面可接任意值
dbms_output.put_line('ename is '||v_name );
exception
when no_data_found then
dbms_output.put_line('not founder record,please try again :)');
end;
/
============================
eg : 在dept表查询部分编号为10的部门,将部门名和工作地点显示出来
declare
dname dept.dname%type; --> %type变量声明
loc dept.loc%type;
begin
select dname,loc into dname,loc from dept where deptno=&v_id; --> into后面跟多个值
dbms_output.put_line('10 name is '||dname||' and loc is '||loc);
exception
when no_data_found then
dbms_output.put_line('not found record,please try again :)');
end;
/
=============================
eg : 查询7788记录的信息:7900 7834 7788
declare
a emp%rowtype; --> %rowtype变量声明
v_no emp.empno%type;
begin
v_no:=&emp_id; --> begin赋值时候读输入
select * into a from emp where empno=v_no;
dbms_output.put_line(v_no||':'||a.ename||' sal is '||a.sal); --> 输出%rowtype变量
exception
when no_data_found then
dbms_output.put_line('not found record,please try again :)');
end;
/
=============================
eg:插入记录
declare
empno emp.empno%type:=&empno; --> 声明的时候赋值读输入
ename emp.ename%type:='&ename'; -->注意引号
begin
insert into emp(empno,ename) values(empno,ename); -->dml语句
end;
/
=============================
--------------------
declare
vtotal date:=sysdate +7;
type va is record -->声明记录类型record
(a number not null:=100,
b emp.ename%type,
c emp.job%type);
emp_record va; -->声明为定义的类型
....
--------------------
============================
set serveroutput on
declare
ab int:=1;
vsum int:=0;
begin
loop
exit when ab>100;
vsum:=vsum+ab;
ab:=ab+1;
end loop;
dbms_output.put_line(vsum);
end;
/
============================
set serveroutput on
declare
ab int:=1;
vsum int:=0;
begin
while ab<=100 loop
vsum:=vsum+ab;
ab:=ab+1;
end loop;
dbms_output.put_line(vsum);
end;
/
============================
set serveroutput on
declare
ab int:=1;
vsum int:=0;
begin
for ab in 1..100 loop -->ab 可以不声明直接用
vsum:=vsum+ab;
end loop;
dbms_output.put_line(vsum);
end;
/
============================
set serveroutput on
declare
ab int:=1;
begin
for ab in reverse 1..5 loop -->reverse 是反向取值
dbms_output.put_line(ab);
end loop;
end;
/
============================
declare
var emp.ename%type;
cursor mycur is -->游标声明
select ename from emp where deptno=10;
begin
if not mycur%isopen
then
open mycur;
end if;
loop
fetch mycur into var;
dbms_output.put_line(var||' '||mycur%rowcount);
exit when mycur%notfound;
end loop;
close mycur;
end;
/
============================
declare
vename emp.ename%type;
vsal emp.sal%type;
cursor mycur is
select ename,sal from emp where sal>2500;
begin
if not mycur%isopen
then
open mycur;
end if;
loop
fetch mycur into vename,vsal;
dbms_output.put_line(mycur%rowcount||vename||' sal is '||vsal);
exit when mycur%notfound;
end loop;
close mycur;
end;
/
=======================
游标FOR循环
declare
vename emp.ename%type;
vsal emp.sal%type:=&sal;
cursor mycur is
select ename from emp where sal>vsal;
begin
for vename in mycur loop -->隐式进行了游标的open、fetch和close
dbms_output.put_line(vename.ename||' '||mycur%rowcount); -->注意这里输出不是直接用vename
end loop;
end;
/
----------------
declare
vename emp.ename%type;
vsal emp.sal%type:=&sal;
begin
for vename in (select ename from emp where sal>vsal) loop -->甚至可以不声明游标,称为显式游标的自动化
dbms_output.put_line(vename.ename);
end loop;
end;
/
======================
set serveroutput on
Declare
result integer;
begin
result:=10+3*4-20+5**2;
dbms_output.put_line('运算结果是:'||result);
end;
/
======================
PLSQL异常处理
*****************************************
1、声明异常
异常名 EXCEPTION;
2、抛出异常
RAISE 异常名
3、处理异常
抛出异常后的逻辑代码不会被继续执行
异常的定义使用
―――――――――――――――――――――――――――――――――――――
begin
dbms_output.put_line(1/0);
exception
when others then
dbms_output.put_line('error');
end;
declare
e_myException exception;
begin
dbms_output.put_line('hello');
raise e_myException; --raise 抛出异常,用此关键字,抛出后转到自定义的e_myException ,执行其里面的putline函数后,再跳到end处,结束PL/SQL块,raise接下面的2句不会继续执行。
dbms_output.put_line('world');
dbms_output.put_line(1/0);
exception
when e_myException then
dbms_output.put_line(sqlcode); --当前会话执行状态,错误编码
dbms_output.put_line(sqlerrm); --当前错误信息
dbms_output.put_line('my error');
when others then
dbms_output.put_line('error');
end;
―――――――――――――――――――――――――――――――――――――
<触发器>
特定的事件,启动数据库,登录数据库,关闭数据库等系统事件,执行dml和ddl等操作
触发器分3类:
dml触发器---依据于基本表或者简单视图建立的触发器
instead of 触发器-- 依据于复杂视图建立的触发器
系统事件触发器-- 系统事件建立的触发器
组成:
(1)触发事件:
引起触发代码执行的事件,这些事件可以启动和关闭例程,用户登录和断开会话,oracle错误信息
特定表和视图的dml操作
(2)触发条件:
由when子句指定的逻辑表达式,可选,如指定when子句,当触发事件发生时候,when条件必须为true,触发器代码开始执行
(3) 触发时刻 :
操作前还是操作后触发
after | before
单一触发事件的DML触发器
DML触发器是基于表的触发器,当对某个表进行DML操作时会激活该类触发器。建立的语法格式:
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER trigger_event [OF column_name]
ON table_name [FOR EACH ROW] [WHEN trigger_condition]
[DECLARE]
BEGIN
trigger_body
END [trigger_name]
eg:change emp 9-18
create or replace trigger change_emp
before insert or update or delete on emp
begin
if (to_char(sysdate,'FMHH24') NOT between 9 and 18) then
raise_application_error(-20000,'change only at 9 -18,please try again :)');
end if;
end change_emp;
/
SQL> alter trigger CHANGE_EMP disable;--->禁用trigger
SQL> alter trigger CHANGE_EMP enable;--->开启trigger
SQL> drop trigger CHANGE_EMP; --->删除trigger:
SQL> select trigger_name,trigger_type,triggering_event,table_owner,base_object_type,status from user_triggers; --->查看trigger
查看错误信息:
SQL> show errors 命令
建立一个emp中sal中修改记录的表,将修改前后的sal,修改时间,修改empno记录下来
CREATE or replace trigger emp_gc
after update of sal on emp for each row
begin
insert into emp_c values(:old.empno,:old.sal,:new.sal,sysdate);
end emp_gc;
/
多个触发事件的DML触发器:
在3种DML操作中,指定一种以上的操作为触发事件,这样的DML触发器称为多个触发事件的DML触发器。
当在触发器中同时包含多种触发事件(INSERT、UPDATE、DELETE),并且需要根据事件的不同进行
不同的操作时,则可以在触发器代码中使用下面3个条件谓词加以区别:
(1)INSERTING:当触发事件为INSERT时,条件谓词返回TRUE,否则返回FALSE。
(2)UPDATING:当触发事件为UPDATE时,条件谓词返回TRUE,否则返回FALSE。
(3)DELETING:当触发事件为DELETE时,条件谓词返回TRUE,否则返回FALSE。
例:建一触发器,禁止在非工作时段,增加、修改、删除emp中数据,根据不同操作,给出不同错误。
CREATE OR REPLACE TRIGGER emp_tg_change
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF (TO_CHAR(SYSDATE,'FM HH24') NOT BETWEEN 10 AND 18) THEN
CASE
WHEN INSERTING THEN
RAISE_APPLICATION_ERROR(-20001,'insert only at 9:00-18:00');
WHEN UPDATING THEN
RAISE_APPLICATION_ERROR(-20002,'update only at 9:00-18:00');
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20003,'delete only at 9:00-18:00');
END CASE;
END IF;
END emp_tg_change;
/
系统事件触发器
系统事件触发器基于数据库或模式。触发事件包括数据库事件(如STARTUP、SHUTDOWN等)、DDL事
件(如CREATE、ALTER、DROP等)。建立系统触发器的语法格式如下:
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER trigger_event
ON DATABASE | SCHEMA [WHEN trigger_condition]
BEGIN
trigger_body
END [trigger_name];
例:建立系统事件触发器 sys_drop_tg,当在用户模式下执行DROP操作时,将删除的对象信息存入
event_drop表中。
CREATE TABLE event_drop
(
user_name VARCHAR2(15),
object_name VARCHAR2(15),
object_type VARCHAR2(10),
object_owner VARCHAR2(15),
create_date DATE
);
CREATE OR REPLACE TRIGGER sys_drop_tg
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO event_drop VALUES(USER,ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_OWNER,SYSDATE);
END sys_drop_tg;
/
<procedure> 存储过程
create or replace procedure procedure_name
is |as
begin
body;
end procedure_name;
/
过程调用:
call|execute procedure_name
eg1:查看当前时间
SQL> create or replace procedure display_time
2 as
3 begin
4 dbms_output.put_line('current time is '||sysdate);
5 end display_time;
6 /
SQL> set serveroutput on
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --->当前会话生效
SQL> execute display_time();
current time is 2010-08-05 14:46:21
我们发现,存储过程本质上就是对sql语句的封装。sql语句以程序块的形式被封装在了数据库中了。
写好了以后,无论是 java,还是 .net ,抑或其他开发语言,可以像使用sql语句那样随意调用。这就是数据库开发。
函数与过程很相似,但有差别:1、函数有返回值,过程没有。2、函数调用在一个表达式中,过程则是作为pl/sql程序的一个语句
存储过程入参,不论类型,缺省 情况下值都为null ,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间.
同时,存储过程中可以再调用其它的存储过程,如果要保证存储过程之间的事务处理不受影响,可以定义为自治事务。
查看过程:dba_procedures、user_procedures
SQL> select owner,object_name,procedure_name from dba_procedures where owner='SCOTT'; -->查看过程
SQL> select text from dba_source where name='DISPLAY_SAL'; -->查看过程内容
SQL> drop procedure scott.display_sal; -->删除过程
有参数过程:
带有参数的过程,指定参数名字,模式,数据类型 调用需要给出对应参数
有三种模式:
in 只能将行参传递到程序内部
out 行参在子程序内部被赋值,然后将其传递给实参
in out 同时具有以上2种参数特性
输入参数in,输入参数不能进行:=赋值,但可以将它赋给as后面定义的变量;
输入参数in,可以作为变量进行条件判断;
默认不写就是in;
______________________
create or replace procedure say_hello(v_name in varchar2)
as
begin
--v_name:='a'; --存储过程入参v_name不能做为赋值目标
dbms_output.put_line('hello '||v_name);
end;
_______________________
存储过程输入参数作为变量进行条件判断
create or replace procedure say_hello(
i_name in varchar2
)
as
v_name varchar2(100);
begin
if i_name is null then
v_name :='0';
else
v_name :=i_name;--将入赋值给定义变量
end if;
dbms_output.put_line('hello '||v_name);
end;
调用:
exec myp('dsag'); -->用exec调用
或:exec myp('');
_______________________
多个参数的存储过程
create or replace procedure say_hello(
v_first_name in varchar2,
v_last_name in varchar2)
as
begin
dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
end;
_______________________
out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值
create or replace procedure say_hello(
v_name in varchar2,
v_content out varchar2
)
as
begin
v_content:='hello'||v_name;
end;
调用:
declare
v_con varchar2(200);
v_in varchar2(20):='wang';
begin
say_hello(v_in,v_con); -->注意这里调用时没用exec
dbms_output.put_line(v_con);
end;
_______________________
in out参数,既赋值又取值
create or replace procedure say_hello(v_name in out varchar2)
as
begin
v_name:='hi '||v_name;
end;
调用:
declare
v_inout varchar2(20):='wangsu';
begin
say_hello(v_inout); -->先将值赋给定义好的变量,调用时用变量作参数
dbms_output.put_line(v_inout);
end;
_______________________
对存储过程入参赋缺省值
create or replace procedure say_hello(
v_name varchar2 default 'susu',
v_content varchar2 default 'hello'
)
as
begin
dbms_output.put_line(v_name||' '||v_content);
end;
调用:(用指明形参名的方式调用更好)
begin
say_hello();
end;
或
begin
say_hello('cheng');
end;
或
begin
say_hello(v_name=>'cheng');
end;
_______________________
eg:
定义一个过程display_sal,以job为参数,查询并输出该job的平均工作和最高工资和最低工资
create or replace procedure display_sal(v_job emp.job%type)
as
v_avg_sal emp.sal%type;
v_min_sal emp.sal%type;
v_max_sal emp.sal%type;
begin
select avg(sal) into v_avg_sal from emp where job=v_job;
select max(sal) into v_max_sal from emp where job=v_job;
select min(sal) into v_min_sal from emp where job=v_job;
dbms_output.put_line(v_job||' avg sal is '||v_avg_sal);
dbms_output.put_line(v_job||' max sal is '||v_max_sal);
dbms_output.put_line(v_job||' min sal is '||v_min_sal);
exception
when no_data_found then
dbms_output.put_line('not found ~~~~~');
end display_sal;
/
调用过程:
SQL> set serveroutput on
SQL> EXECUTE display_sal('CLERK');
CLERK avg sal is 3022
CLERK max sal is 8888
CLERK min sal is 800
_______________________
eg:
定义一个修改记录的过程,一个输入参数,2个输出参数,输入id,之后将修改结果返回
create or replace procedure oo_emp(v_no in emp.empno%TYPE,
V_NAME out emp.ename%TYPE,
V_SAL OUT EMP.sal%TYPE)
AS
v_job emp.job%TYPE;
begin
select job into v_job from emp where empno=v_no;
CASE
WHEN v_job='CLERK' then
update emp set sal=sal*2 where empno=v_no;
when v_job='MANAGER' then
update emp set sal=sal*3 where empno=v_no;
END CASE;
select ename,sal into v_name,v_sal from emp where empno=v_no;
END oo_emp;
/
调用过程:
SQL> variable v_name varchar2(30);
SQL> variable v_sal number;
SQL> execute oo_emp(7900,:v_name,:v_sal);
SQL> print v_name
V_NAME
--------------
JAMES
SQL> print v_sal;
V_SAL
----------
17776
_______________________
SQL CURSOR
游标是一个独立SQL工作区,有两种性质的游标:
隐式游标: 当PARSE 和EXECUTE 时使用隐式游标。
显式游标: 是由程序员显式声明的。
游标的属性:
SQL%ROWCOUNT:一个整数值,最近SQL语句影响的行数。
SQL%FOUND BOOLEAN属性,如果为TRUE,说明最近的SQL STATEMENT有返回值。
SQL%NOTFOUND 与SQL%FOUND相反
SQL%ISOPEN 在隐式游标中经常是FALSE,因为执行后立即自动关闭了。
SQL> variable row_de number
SQL> r
1 declare
2 v_deptno number:=10;
3 begin
4 delete from emp where
5 deptno=v_deptno;
6 :row_de:=sql%rowcount;
7* end;
PL/SQL 过程已成功完成。
SQL> print row_de --这是一个SQL PLUS变量
ROW_DE
----------
4
这时其实并没有真正的删除,而是需要 COMMIT或ROLLBACK,来完成事务。
oracle02_pl/sql
最新推荐文章于 2024-02-25 09:00:00 发布