oracle 9i PLSQL

PL/SQL
PL/SQL概述
什么是PL/SQL？
在SQL的基础上，加了流程的控制，以及块的结构是ORACLE独有的一种语言
好处？
1、有了流程的概念，可以实现更加灵活的业务逻辑
2、一次编译，永久运行
3、将多条SQL语句放在一起运行，提高性能
4、作为后台程序运行，提高性能
5、方便数据库的移置(exp , copy , RMAN)
6、减少前端开发语言的代码量
注意：
PL/SQL中的SQL语言，语法有所变化
PL/SQL块的结构：
DECLARE
声明部分
BEGIN
语句执行体（必须的）
EXCEPTION
异常处理
END；
/
块的类型：
1、无名块
2、命名块：过程、函数、包、触发器
第一章声明变量
set serveroutput on
set verify off
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno=&1;
v_sal := v_sal*12;
dbms_output.put_line( v_sal);
exception
when no_data_found then
dbms_output.put_line('NO THIS MAN!!'); end;
/
desc dbms_output
v_1 emp.sal%type;
v_2 v_1%type;
v_3 emp%rowtype;
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp
where empno=&1;
dbms_output.put_line('年度工资：'||v_emp.sal);
exception
when no_data_found then
dbms_output.put_line('NO THIS MAN!!');
end;
变量类型：
1、标量: char varchar2 number date Boolean LONG
2、混合: 自定义的，是个集合record
3、参考：%type %rowtype
4、大对象：BLOB CLOB NCLOB BFILE
标识符命名规则：
1、字母开头
2、含有A----Z，a-----z，0-----9，_ $ #
3、不能ORACLE保留字
4、长度不超过30
BIND 变量使用
Conn hr/hr
VARIABLE RESULT NUMBER
BEGIN
SELECT SALARY*12 INTO :RESULT FROM employees WHERE employee_id = 144; END;
/
Print RESULT
Set serveroutput on
Declare
V_sal employees.salary%type;
Begin
Select salary * 12 into v_sal from employees where employee_id=144 ;
Dbms_output.put_line(v_sal );
End;
/
练习4：
Declare
v_char varchar2(20) :='42 is the answer';
v_num number;
Begin
v_num := substr(v_char,1,2);
dbms_output.put_line(v_char);
dbms_output.put_line(v_num); End;
/
第二章书写PL/SQL语句
单行函数：
1、字符型lower upper initcap concat substr instr
Length lpad rpad rtrim ltrim
2、数值型round trunc mod
3、日期型months_bwteen add_months round trunk
Last_day next_day
4、转换型to_number to_char to_date
5、其他nvl nvl2 nullif case decode
练习3：
DEFINE p_num1 = 2
DEFINE p_num2 = 4
Declare
V_1 number(1):= &p_num1;
V_2 number(2,1):= &p_num2;
Begin
V_2 := v_1/v_2+v_2;
Dbms_output.put_line(v_2);
End;
/
练习4：
declare
p_salary number(6,1) :=&1;
p_bonus number(3,1) :=&2;
p_result number(6,1);
begin
p_result := p_salary*p_bonus/100+p_salary;
dbms_output.put_line(p_result);
end;
/
第三章PL/SQL中的SQL
SELECT 。

INTO。

FROM 。

WHERE
只能返回一条记录
如果没有返回行：NO_DATA_FOUND
如果返回多行：TOO_MANY_TOWS
解决办法，通过异常处理：
Declare
V_sal employees.salary%type;
Begin
Select salary * 12 into v_sal from employees
where employee_id=844 ;
Dbms_output.put_line(v_sal );
exception
when no_data_found then
Dbms_output.put_line('没有这个员工'); End;
打印某个部门的平均工资，部门号键盘输入10，
没有这个部门，给出信息‘没有这个部门’
有这个部门，给出信息‘10号部门平均工资4500’
Declare
V_dept employees.department_id%type := &1;
V_avgsal number(6);
Begin
Select round(avg(salary)) into v_avgsal from employees Where department_id = v_dept;
If v_avgsal is null then
Dbms_output.put_line(‘没有这个部门’);
else
Dbms_output.put_line(v_dept||’号部门平均工资’||v_avgsal); End if;
End;
/
隐含游标SQL的使用
create table e1 as select * from employees;
begin
delete from e1 where department_id =90;
dbms_output.put_line(‘删除记录行数：’|| sql%rowcount); end;
/
Rollback;
隐含游标：
SELECT结果是一行
UPDATE、INSERT 、DELETE 记录行数可以多条
隐含游标的名字：SQL，这个是固定的
隐含游标的属性：%found %notfound %isopen %rowcount 可以直接使用，写法sql%属性
Rooms:
Id seats
1 100
2 200
Update rooms set seats=50 where id=9;
如果没有9号房间，需要INSERT 9 50
create table rooms(
id number(1) primary key,
seats number(3));
insert into rooms values(1,100);
insert into rooms values(2,200);
commit;
begin
update rooms set seats=50 where id=9;
if sql%notfound then
insert into rooms values(9,50);
end if;
commit;
end;
/
练习：
declare
v_maxdept departments.department_id%type;
begin
select max(department_id) into v_maxdept from departments; dbms_output.put_line(v_maxdept);
end;
/
--------------------------------------
declare
v_maxdept employees.department_id%type;
begin
select max(department_id) into v_maxdept from departments; insert into departments values
(v_maxdept+10,'EDUCATION',null,null);
commit;
end;
/
begin
update departments set location_id=1800 where department_id=280;
if sql%notfound then
dbms_output.put_line('ERROR!!');
end if;
commit;
end;
/
begin
delete from departments where department_id=&1;
if sql%rowcount <> 1 then
dbms_output.put_line('ERROR!!');
else
dbms_output.put_line('one row was deleted!!');
end if;
commit;
end;
/
第四章流程控制语言
条件语句：
IF 条件1 THEN
语句1；
ELSIF条件2 THEN
语句2；
ELSE
语句3；
END IF；
嵌套IF语句
IF condition1 THEN
statement1;
ELSE
IF condition2 THEN
statement2;
END IF;
END IF;
IF condition1 THEN
statement1;
ELSIF condition2 THEN
statement2;
END IF;
键盘给出员工的ID号码，查询他的工资和LAST_NAME，输出工资等级
1----7999 D
8000----12999 C
13000---19999 B
20000--- A
输出格式：SCOTT的工资等级是：B或者：没有这个员工！
set serveroutput on
set verify off
declare
v_id employees.employee_id%type:=&1;
v_name st_name%type;
v_sal employees.salary%type;
begin
select last_name,salary into v_name, v_sal from employees where employee_id = v_id;
if v_sal < 8000 then
dbms_output.put_line(v_name||' 的工资等级是：D');
elsif v_sal < 13000 then
dbms_output.put_line(v_name||' 的工资等级是：C');
elsif v_sal < 20000 then
dbms_output.put_line(v_name||' 的工资等级是：B');
else
dbms_output.put_line(v_name||' 的工资等级是：A');
end if;
exception
when no_data_found then
dbms_output.put_line('没有这个员工！');
end;
/
键盘给出员工的ID号码，输出他的税钱
1----7999 5%
8000----15999 10%
16000---23999 15%
24000--- 20%
PL/SQL
SQL decode
select last_name,(trunc(salary/8000)+1)*0.05*salary a
from employees
where employee_id=144;
select last_name,
decode(trunc(salary/8000),0,0.05,1,0.1,2,0.15,0.2)*salary a from employees where employee_id=144;
declare
v_id employees.employee_id%type:=&1;
v_sal employees.salary%type;
begin
select salary into v_sal from employees
where employee_id = v_id;
if v_sal < 8000 then
dbms_output.put_line(v_sal*0.05);
elsif v_sal < 13000 then
dbms_output.put_line(v_sal*0.1);
elsif v_sal < 20000 then
dbms_output.put_line(v_sal*0.15);
else
dbms_output.put_line(v_sal*0.2);
end if;
exception
when no_data_found then
dbms_output.put_line('没有这个员工！');
end;
/
CASE语法：
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1;]
END;
select last_name,
case trunc(salary/8000)
when 0 then 0.05*salary
when 1 then 0.10*salary
when 2 then 0.15*salary
else 0.20*salary
end a
from employees
where employee_id=144;
x := 5;
y := NULL;
...
IF x != y THEN
sequence_of_statements; -- not executed END IF;
False and null -------false
True or null --------true
If v1 is null then
…..
End if;
If v1 is not null then
…..
End if;
循环语句三种
简单循环
LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;
LOOP1：
ID TITLE
1LOOP
2LOOP。

10LOOP
declare
v1 number(2):=1;
begin
loop
insert into loop1 values(v1,'LOOP'); v1 := v1 + 1;
exit when v1 > 10;
end loop;
commit;
end;
/
WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;
LOOP1：
ID TITLE
1 WHILE
2 WHILE。

10WHILE
declare
begin
WHILE v1 < 11 loop
insert into loop1 values(v1,'WHILE');
v1 := v1 + 1;
end loop;
commit;
end;
/
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1;
statement2;
. . .
END LOOP;
begin
for v1 in 1..10 loop
insert into loop1 values(v1,'FOR');
end loop;
commit;
end;
/
begin
for v1 in reverse 10..15 loop
insert into loop1 values(v1,'FOR');
end loop;
commit;
end;
/
练习1：
create table message (a number(2));
begin
for i in 1..10 loop
if i <> 6 and i <>8 then
insert into message values(i);
end if;
end loop;
commit;
end;
练习2：
declare
v_id employees.employee_id%type:=&1;
v_sal employees.salary%type;
begin
select salary into v_sal from employees
where employee_id = v_id;
if v_sal is null then
dbms_output.put_line(0);
elsif v_sal < 5000 then
dbms_output.put_line(v_sal * 0.1);
elsif v_sal between 5000 and 10000 then
dbms_output.put_line(v_sal * 0.15);
else
dbms_output.put_line(v_sal * 0.2);
end if;
exception
when no_data_found then
dbms_output.put_line('NO THIS MAN!');
end;
/
练习3：
Create table emp as select * from employees;
Alter table emp add stars varchar2(50);
练习4：
declare
v_char varchar2(20);
v_sal emp.salary%type;
begin
select salary into v_sal from emp where employee_id=&&1; for i in 1..trunc(v_sal/1000)+1 loop
v_char:=v_char||'*';
end loop;
update emp set stars=v_char where employee_id=&1;
commit;
exception
dbms_output.put_line('NO THIS MAN!');
end;
/
SQL实现：
红色是错误的，为什么？
update emp set stars=null;
commit;
update emp set stars=lpad(stars,trunc(salary/1000)+1,'*'); commit;
select salary ,stars from emp;
正确写法：
update emp set stars=null;
commit;
update emp set
stars=lpad(nvl(stars,'*'),trunc(salary/1000)+1,'*'); commit;
select salary ,stars from emp;
1、RECORD
declare
type t_emp is record
(name st_name%type,
sal employees.salary%type,
job employees.job_id%type);
v_emp t_emp;
begin
select last_name,salary,job_id into v_emp
from employees where employee_id=144;
dbms_output.put_line
(v_||' '||v_emp.sal||' '||v_emp.job);
end;
/
变量名。

域名
2、ROWTYPE
declare
v_emp employees%rowtype;
begin
select * into v_emp
from employees where employee_id=144;
dbms_output.put_line
(v_st_name||' '||v_emp.salary||
' '||v_emp.job_id);
end;
/
变量名。

列名
3、INDEX BY TABLE
Declare
TYPE t_sal IS TABLE OF
employees.salary%TYPE
INDEX BY BINARY_INTEGER;
V_sal t_sal;
Begin
For I in 100..104 loop
Select salary into v_sal(i)
From employees where employee_id = I;
Dbms_output.put_line(101||' '||v_sal(101));
End;
/
FOR i IN my_emp_table.FIRST..my_emp_ST LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
4、INDEX BY TABLE OF RECORDS
TYPE emp_table_type is table of
employees%ROWTYPE INDEX BY BINARY_INTEGER;
my_emp_table emp_table_type;
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name)
练习1：
declare
v_1 countries%rowtype;
begin
select * into v_1 from countries
where country_id = '&1';
dbms_output.put_line(v_1.country_id||' '||v_1.country_name ||' '||v_1.region_id);
exception
when no_data_found then
dbms_output.put_line('NO THIS COUNTRY!');
END;
练习2：
Declare
TYPE t_name IS TABLE OF
Departments.department_name%TYPE
INDEX BY BINARY_INTEGER;
V_name t_name;
Begin
For I in 1..7 loop
Select department_name into v_name(i)
From departments where department_id = I*10;
End loop;
For I in v_name.first..v_st loop
Dbms_output.put_line(i||' '||v_name(i));
End;
练习3：
Declare
TYPE t_name IS TABLE OF
Departments%ROWTYPE
INDEX BY BINARY_INTEGER;
V_name t_name;
Begin
For I in 1..7 loop
Select * into v_name(i)
From departments where department_id = I*10;
End loop;
For I in v_name.first..v_st loop
Dbms_output.put_line(i||' '||v_name(i).department_name|| ' '|| v_name(i).manager_id|| ' '||v_name(i).location_id);
End loop;
End;
第六章显式游标
游标：是指向一个结果集的指针
类型：隐式和显式
隐式游标：SQL
SELECT 结果只有一行
UPDATE、DELETE、INSERT
显式游标：四个步骤
定义、打开、提取（循环）、关闭
使用游标，显示前10 个部门ID和名字
Declare
Type t_dept is record(
Id departments.department_id%type,
Name departments.department_name%type);
V_dept t_dept;
Cursor c_dept is select department_id,department_name From departments;
Begin
Open c_dept;
Loop
Fetch c_dept into v_dept;
Exit when c_dept%notfound or c_dept%rowcount > 10; Dbms_output.put_line(v_dept.id|| v_);
End loop;
Close c_dept;
End;
/
Declare
Type t_dept is record(
Id departments.department_id%type,
Name departments.department_name%type);
V_dept t_dept;
Cursor c_dept is select department_id,department_name From departments;
Begin
Open c_dept;
Fetch c_dept into v_dept;
While c_dept%found and c_dept%rowcount < 11 loop
Dbms_output.put_line(v_dept.id|| v_);
End loop;
Close c_dept;
End;
/
Declare
Cursor c_dept is select department_id,department_name From departments;
Begin
For I in c_dept loop
If c_dept%rowcount = 11 then
Exit;
else
Dbms_output.put_line(i.department_id||
i.department_name);
End if;
End loop;
End;
/
不需要写：OPEN FETCH（不需要变量的定义） CLOSE
如果不定义游标：
Begin
For I in （select department_id,department_name
From departments ） loop
Dbms_output.put_line(i.department_id||
i.department_name);
End loop;
End;
/
第七章高级显式游标
一、带参数的游标
CURSOR cursor_name[(parameter_name datatype, ...)] IS select_statement;
OPEN cursor_name(parameter_value,.....) ;
使用这样的游标，显示10---100号部门，各自人的名字
10 号部门：A B
20 号部门： C D。

40号部门： E F G
DECLARE
Cursor c_emp(id employees.department_id%type)
Is select last_name from employees
Where department_id = id;
V_char varchar2(250);
V_a number(3):= 10;
Begin
While v_a < 110 loop
For I in c_emp(v_a) loop
V_char:=v_char || ' ' ||st_name;
If length(v_char)>240 then
Dbms_output.put_line(v_a||'部门的人员'||v_char);
V_char:=null;
End if;
End loop;
Dbms_output.put_line(v_a||'部门的人员'||v_char);
Dbms_output.put_line('----------------------------------
-------------------------------------------');
v_char := null;
V_a := v_a + 10;
End loop;
End;
/
二、游标定义中有 FOR UPDATE
CYRSOR C1 IS SELECT ...FROM ...
FOR UPDATE [OF column_reference][NOWAIT];
DECLARE
CURSOR C1 IS SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
FOR UPDATE OF SALARY NOWAIT;
三、使用WHERE CURRENT OF 游标
将10---100号部门人员的工作年限，回填到 a 列
Declare
Cursor c_emp (id emp.department_id%type) is
Select hire_date from emp
Where department_id = id
For update of a nowait;
V_1 number(3) := 10;
Begin
While v_1 < 110 loop
For I in c_emp(v_1) loop
Update emp set
a=round(months_between(sysdate,i.hire_date)/12)
where current of c_emp;
end loop;
v_1:= v_1+10;
end loop;
commit;
end;
/
四、游标定义中有子查询
DECLARE
CURSOR my_cursor IS
SELECT t1.department_id, t1.department_name,t2.staff FROM departments t1,
(SELECT department_id,COUNT(*) AS STAFF FROM employees GROUP BY department_id) t2
WHERE t1.department_id = t2.department_id
AND t2.staff >= 3;
练习：
显示各个部门的：
要求按照部门名字排序
Declare
Cursor c1 is
Select d.department_name, avgsal
From departments d ,
(select department_id,round(avg(salary)) avgsal
From employees
Group by department_id ) t
Where d.department_id = t.department_id
Order by 1 ;
Begin
For I in c1 loop
Dbms_output.put_line(i.department_name||’: ’||i.avgsal); End loop;
End;
/
练习1：
Declare
Cursor c_emp(id employees.department_id%type) is
Select last_name,job_id,salary from employees
Where department_id = id;
V_name departments.department_name%type;
V_num char(2):= 10;
Begin
While v_num < 100 loop
Select department_name into v_name
From departments
Where department_id = v_num;
Dbms_output.put_line(v_num||' '||v_name);
For I in c_emp(v_num) loop
Dbms_output.put_line(st_name||' '||i.job_id|| ' '||i.salary);
End loop;
Dbms_output.put_line('------------------------- ');
V_num := v_num + 10;
End loop;
End ;
练习2：
Declare
Cursor c_emp(id emp.department_id%type) is
Select salary from emp
Where department_id = id
For update of stars nowait;
v_stars varchar2(20);
Begin
For I in c_emp(&1) loop
v_stars := null;
Update emp set
stars=lpad(nvl(v_stars,'*'),trunc(i.salary/1000)+1,'*') where current of c_emp;
end loop;
commit;
end;
/
第八章异常处理
类型：
1、预定义异常（ORACLE ERRORS）
1）异常的名字ORACLE，已经定义好了，各自对应一个ERROR
2）异常的名字ORACLE，没有定义好，需要我们去初始化，调用PRAGMA EXCEPTION_INIT
2、自定义异常（LOGICAL）
Declare。

Begin。

Exception
When no_data_found then。

End;
/
Declare
E_1 exception;
PRAGMA EXCEPTION_INIT (e_1, -2292);
Begin。

Exception
When e_1 then。

End;
/
自定义的分三个步骤：
1）定义
2）扑获（IF）
3）处理
Declare
E_1 exception;
Begin。

If 。

then
End if;。

Exception
When e_1 then。

When others then
Dbms_output.put_line(sqlcode||’‘||
substr(sqlerrm,1,50))
End;
/
declare
e_1 exception;
begin
update employees set salary=1000 where employee_id = &1; if sql%notfound then
raise e_1;
end if;
dbms_output.put_line('$$$$$');
exception
when e_1 then
dbms_output.put_line('NO MAN!');
end;
/
declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = &1;
exception
when others then
dbms_output.put_line(sqlcode||' '||
substr(sqlerrm,1,100));
end;
/
declare
e_1 exception;
begin
update employees set salary=1000 where employee_id = &1; if sql%notfound then
raise e_1;
dbms_output.put_line('$$$$$'); exception
when e_1 then
RAISE_APPLICATION_ERROR (-20201,
’Manager is not a valid employee.’); end;
自定义的ERROR号码范围： 20000-----20999
第九章创建存储过程
命名块：
1）过程：描述一组有关联的操作
2）函数：做算术运算，有返回值
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS|AS
PL/SQL Block;
参数模式：
1）IN 将数值带入到过程里面
2）OUT 将数值带出到过程外面
3）IN OUT 将数值带入到过程里面，还可以将数值带出到过程外面
写一个存储过程，通过ID 查询员工的工资信息
Create or replace procedure pro1
(id in employees.employee_id%type,
Sal out employees.salary%type)
Is
Begin
Select salary into sal from employees
Where employee_id = id;
Exception
When no_data_found then
Dbms_output.put_line(‘NO MAN!’);
End;
/
declare
v_1 employees.salary%type;
begin
pro1(&1,v_1);
dbms_output.put_line(v_1);
end;
/
Variable a number
Exec pro1(&1,:a)
Print a
使用IN OUT模式的参数，写一个存储过程，计算一个数的 3次方
调用： 1。

30 各个数的3次方
1 1
2 8。

30 27000
Create or replace procedure pro2(a in out number) Is
Begin
A:=a*a*a;
End;
/
Declare
V1 number(6);
Begin
For I in 1..30 loop
V1:= I;
Pro2(v1);
Dbms_output.put_line(i||' ' ||v1);
End loop;
End;
/
CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7);
END format_phone;
/
declare
begin
format_phone(v1);
dbms_output.put_line(v1);
end;
/
Create or replace procedure pro1
(id in employees.employee_id%type,
Sal out employees.salary%type)
Is
Begin
Select salary into sal from employees Where employee_id = id;
Exception
When no_data_found then
Dbms_output.put_line(‘NO MAN!’);
End;
/
Create or replace procedure pro2
Is
v1 number(7,2);
Begin
Pro1(1,v1);
Dbms_output.put_line('*************'); Exception
When no_data_found then
Dbms_output.put_line('&&&&&&&&&&&&'); End;
Exec pro2
结果：
NO MAN!
*************
Create or replace procedure pro1
(id in employees.employee_id%type,
Sal out employees.salary%type)
Is
Begin
Select salary into sal from employees
End;
/
Create or replace procedure pro2
Is
v1 number(7,2);
Begin
Pro1(1,v1);
Dbms_output.put_line('*************'); Exception
When no_data_found then
Dbms_output.put_line('&&&&&&&&&&&&'); End;
Exec pro2
结果：
&&&&&&&&&&&&
删除：
DROP PROCEDURE raise_salary;
练习：
做一个存储过程
1 ABCD。

Z
2 Z 。

DCBA
其他数值错误信息
create or replace procedure pro4 (v1 in number) Is
V2 varchar2(30) :='A';
begin
if v1 = 1 then
for i in 1..25 loop
v2:=v2||chr(ascii('A')+i);
end loop;
dbms_output.put_line(v2);
elsif v1 = 2 then
for i in 1..25 loop
v2:=chr(ascii('A')+i)||v2;
dbms_output.put_line(v2);
else
dbms_output.put_line('V1 MUSET 1 OR 2!');
end if;
end;
/
exec pro4(1)
exec pro4(2)
exec pro4(3)
create or replace procedure pro5 (v_a in number) is
v_char varchar2(30);
begin
if v_a=1 then
for i in 65..90 loop
v_char :=v_char||chr(i);
end loop;
elsif v_a=2 then
for i in reverse 65..90 loop
v_char :=v_char||chr(i);
end loop;
else
dbms_output.put_line('NO DATA FOUND');
end if;
dbms_output.put_line(v_char);
end;
/
第十章创建函数
特点：
1）有返回值
2）调用要在表达式中
3）适合做算术运算
语法：
CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
RETURN datatype
IS|AS
PL/SQL Block;
Mode： IN ， OUT ，IN OUT
CREATE OR REPLACE FUNCTION get_sal
(p_id IN employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_id;
RETURN v_salary;
Exception
When no_data_found then
Dbms_output.put_line(‘NO THE MAN!’);
END get_sal;
/
调用：
declare
v_1 employees.salary%type;
begin
v_1 := get_sal(&1);
Dbms_output.put_line(v_1);
end;
/
select get_sal(144) from dual; 测试
CREATE OR REPLACE FUNCTION get_sal2
(p_id IN employees.employee_id%TYPE,
P_job out employees.job_id%type)
RETURN NUMBER
IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary,job_id INTO v_salary , p_job FROM employees
WHERE employee_id = p_id;
RETURN v_salary;
Exception
When no_data_found then
Dbms_output.put_line('NO THE MAN!');
END get_sal2;
/
调用：
declare
v_1 employees.salary%type;
v_2 employees.job_id%type;
begin
v_1 := get_sal2(&1,v_2);
Dbms_output.put_line(v_1);
Dbms_output.put_line(v_2);
end;
/
删除：
DROP FUNCTION get_sal;
练习：
Create or replace function func1(a number) Return number
Is
Begin
Return a*a*a;
End;
begin
for i in 1..30 loop
dbms_output.put_line(i||' '||func1(i));
end;
/
第十一章管理子程序
系统权限：
Create any procedure
Alter…
Drop
Execute
对象权限
Execute
Grant …. To…
查询HR用户创建的存储过程名字
Select object_name
From user_objects
Where object_type='PROCEDURE';
查询HR用户创建的函数名字
Select object_name
From user_objects
Where object_type='FUNCTION';
查询某个函数的定义
select text from user_source
where name='FUNC1'
order by line;
查看PKG中过程和函数说明
Desc dbms_output
查询函数、过程的编译错误
SELECT line || ’/’ || position POS, text FROM user_errors
WHERE name = ’LOG_EXECUTION’
ORDER BY line;
Desc func1
Desc proc1
查看自己的系统权限
Select * from session_privs;
查看自己的角色
Select * from SESSION_ROLES;
如果是开发人员需要的系统角色：
CONNECT
RESOURCE
GRANT CONNECT,RESOURCE TO SCOTT;
第十二章包
包：是过程和函数的封装
包分为两个部分：包和包体
类型：系统定义（DBMS_ ** ）和自定义
包的定义：
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
public type and item declarations
subprogram specifications
END package_name;
/
包体的定义：
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
private type and item declarations
subprogram bodies
END package_name;
/
CREATE OR REPLACE PACKAGE PKG1
IS
PROCEDURE add_job
( p_emp_id job_history.employee_id%type
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type );
FUNCTION sal
(p_id IN employees.employee_id%TYPE)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY PKG1
IS
PROCEDURE add_job
, p_start_date job_history.start_date%type
, p_end_date job_history.end_date%type
, p_job_id job_history.job_id%type
, p_department_id job_history.department_id%type
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date,
end_date,
job_id, department_id)
VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job;
FUNCTION sal
(p_id IN employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary employees.salary%TYPE :=0;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = p_id;
RETURN v_salary;
Exception
When no_data_found then
Dbms_output.put_line('NO THE MAN!');
END sal;
END;
/
调用：
SELECT PKG1.SAL(144) FROM DUAL;
EXEC PKG1.ADD_JOB(144,SYSDATE-7,SYSDATE,'ST_CLERK',10) 写一个包 PKG2，并且调用：
FUN1 ： a+100
FUN2： a-100
PRO1:打印今天的日期和时间
Create or replace package pkg2
Is
Function fun1(a number)
Return number;
Function fun2(a number)
Return number;
Procedure pro1;
End;
/
Create or replace package body pkg2
Is
Function fun1(a number)
Return number
Is
begin
Return a+100;
End;
Function fun2(a number)
Return number
Is
begin
Return a-100;
End;
Procedure pro1
Is
Begin
Dbms_output.put_line(to_char(sysdate,’yyyy-mm-dd:hh24:mi: ss’));
End;
End;
/
Select pkg2.fun1(300),pkg2.fun2(300) from dual;
Exec pkg2.pro1
select object_name from user_objects
where object_type='PACKAGE';
set heading off
select text from user_source
where name='PKG2'
order by line;
desc pkg2
只有包的定义，没有包体的定义
CREATE OR REPLACE PACKAGE global_consts IS
mile_2_kilo CONSTANT NUMBER := 1.6093;
kilo_2_mile CONSTANT NUMBER := 0.6214;
yard_2_meter CONSTANT NUMBER := 0.9144;
meter_2_yard CONSTANT NUMBER := 1.0936;
END global_consts;
/
EXECUTE DBMS_OUTPUT.PUT_LINE(’20 miles = ’||20* global_e_2_kilo||’ km’)
全局变量可以用于所有的过程、函数和包（整个数据库范围）
CREATE OR REPLACE PROCEDURE meter_to_yard
(p_meter IN NUMBER, p_yard OUT NUMBER)
IS
BEGIN
p_yard := p_meter * global_consts.meter_2_yard; END meter_to_yard;
/
VARIABLE yard NUMBER
EXECUTE meter_to_yard (1, :yard)
变量作用的范围：
1、某个函数或者存储过程
2、某个包（一些函数或者存储过程）
3、整个数据库
删除包：
删除包体：
DROP PACKAGE BODY package_name;
Create or replace package pkg3
Is
Procedure check_date(a date);
Procedure check_mgr(
a employees.employee_id%type,
b employees.manager_id%type);
END;
/
Create or replace package body pkg3
Is
Procedure check_date(a date)
Is
Begin
If a between add_months(sysdate,-600) and
add_months(sysdate,3) then
Dbms_output.put_line('OK');
Else
Dbms_output.put_line('ERROR');
END IF;
End;
Procedure check_mgr(
a employees.employee_id%type,
b employees.manager_id%type)
is
v_a employees.department_id%type;
v_b employees.department_id%type;
begin
select department_id into v_a from employees where employee_id = a;
select department_id into v_b from employees where employee_id = b;
if v_a = v_b then
Dbms_output.put_line('OK');
Else
Dbms_output.put_line('NOT SAME DEPT');
End if;。