PL/SQL知识点(一)

(一)  简介

1、过程，函数，触发器是有pl/sql编写的
2、过程，函数，触发器是在oracle中的
3、pl/sql是非常强大的数据库过程化语言
4、过程，函数可以再java 程序中调用

优点：
1、节省带宽
2、安全
3、高效
缺点：
移植性不好

(二)  案例

1、添加记录

        a、创建表

create table mytest(name varchar2(30),passwd varchar2(30));

       b、创建过程

SQL> create or replace procedure sp_pro1 is
  2  begin
  3   insert into mytest values('hanshunping','1234567');
  4  end;
  5  /    创建过程以反斜杠来结束
Procedure created.

      c、执行
exec 过程名(参数列表...);
或call 过程名(参数列表...);

SQL> exec sp_pro1;
PL/SQL procedure successfully completed.
SQL>

 2、添加记录

create or replace procedure sp_pro2 is 
 begin 
 delete from mytest where name='hanshunping';
 end;
SQL> call sp_pro2;
call sp_pro2
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

SQL> exec sp_pro2;
PL/SQL procedure successfully completed.
SQL>

(三)  块

SQL>
SQL> begin
  2  dbms_output.put_line('hello,world');
  3  end;
  4  /

PL/SQL procedure successfully completed.


declare
v_name varchar2(5);
 begin 
select ename into v_name from emp where empno=&no;
dbms_output.put_line('his name is '||v_name);
end;

执行结果：

SQL> declare
v_name varchar2(5);
 begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line('his name is '||v_name);
end;  2    3    4    5    6
  7  /
Enter value for no: 7788
old   4: select ename into v_name from emp where empno=&no;
new   4: select ename into v_name from emp where empno=7788;
his name is SCOTT

PL/SQL procedure successfully completed.

SQL>

把用户的编号也显示出来

SQL> declare
v_name varchar2(5);
v_sal number(7,2);
 begin
select ename,sal into v_name,v_sal from emp where empno=&no;
dbms_output.put_line('his name is '||v_name||'  salary' || v_sal);
end;  2    3    4    5    6    7
  8  /
Enter value for no: 7788
old   5: select ename,sal into v_name,v_sal from emp where empno=&no;
new   5: select ename,sal into v_name,v_sal from emp where empno=7788;
his name is SCOTT  salary3000

PL/SQL procedure successfully completed.

(四)  异常处理

declare
v_name varchar2(5);
v_sal number(7,2);
 begin 
select ename,sal into v_name,v_sal from emp where empno=&no;
dbms_output.put_line('his name is '||v_name||'  salary' || v_sal);
exception
 when no_data_found then 
dbms_output.put_line('your input is wrong');
end;

执行结果

SQL> declare
  2  v_name varchar2(5);
  3  v_sal number(7,2);
  4   begin
  5  select ename,sal into v_name,v_sal from emp where empno=&no;
  6  dbms_output.put_line('his name is '||v_name||'  salary' || v_sal);
  7  exception
  8   when no_data_found then
  9  dbms_output.put_line('your input is wrong');
 10  end;
 11  /
Enter value for no: 88
old   5: select ename,sal into v_name,v_sal from emp where empno=&no;
new   5: select ename,sal into v_name,v_sal from emp where empno=88;
your input is wrong

PL/SQL procedure successfully completed.

(五)  过程
 案例4：

create or replace procedure sp_rol3(spName varchar2,newSal number) is 
 begin 
 update emp set sal=newSal where ename=spName;
end;

SQL> create or replace procedure sp_rol3(spName varchar2,newSal number) is
 begin
 update emp set sal=newSal where ename=spName;
end;  2    3    4
  5  /

Procedure created.

SQL>
SQL> exec sp_rol3('SCOTT',4678);

PL/SQL procedure successfully completed.

(六)  函数
案例：输入雇员的姓名，返回改雇员的年薪

create or replace function sp_fun2(spName varchar2) return number is yearSal number(7,2) ;
begin 
select sal * 12 + nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;

执行结果：

SQL> create or replace function sp_fun2(spName varchar2) return number is yearSal number(7,2) ;
begin
select sal * 12 + nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;  2    3    4    5
  6  /

Function created.

调用

SQL> var abc number;
SQL> call sp_fun2('SCOTT') into:abc;

Call completed.

(七)  包

案例1
--创建包

create or replace package sp_package is 
 procedure update_sal(name varchar2,newsal number);
 function annual_income(name varchar2) return number;
 end;

---创建包体

create or replace package body sp_package is 
procedure update_sal(name varchar2,newsal number)
 is 
begin 
update emp set sal =newsal where ename =name;
end;
function annual_income(name varchar2)
return number is annual_salary number;
 begin 
 select sal*12 +nvl(comm,0) into annual_salary from emp 
where ename =name;
return annual_salary;
end;
end;

调用：

SQL> exec sp_package.update_sal('SCOTT',120);

PL/SQL procedure successfully completed.

案例2：变量--标量
输入员工号，显示雇员姓名，工资，个人所得税（0.03税率）

declare 
c_tax_rate number(3,2) :=0.03 ;
--三个变量
v_ename varchar2(5);--也可以这样定义 v_ename emp.ename%type;//比较安全，也不会溢出
v_sal number(7,2);--可以这样 v_sal emp.sal%type;
v_tax_sal number(7,2);
begin 
--执行
 select ename,sal into v_ename,v_sal from emp where empno=&no;
 --计算
 v_tax_sal := v_sal*c_tax_rate;
 dbms_output.put_line('name is '||v_ename||'  salary is ' || v_sal ||' tax is '||v_tax_sal);
end;

执行结果

SQL> set serveroutput on;
SQL> declare
c_tax_rate number(3,2) :=0.03 ;
  2    3  --....
  4  v_ename varchar2(5);
  5  v_sal number(7,2);
  6  v_tax_sal number(7,2);
  7  begin
  8  --..
  9   select ename,sal into v_ename,v_sal from emp where empno=&no;
 10   --..
 11   v_tax_sal := v_sal*c_tax_rate;
 12   dbms_output.put_line('name is '||v_ename||'  salary is ' || v_sal ||' tax is '||v_tax_sal);
 13  end;
 14  /
Enter value for no: 7788
old   9:  select ename,sal into v_ename,v_sal from emp where empno=&no;
new   9:  select ename,sal into v_ename,v_sal from emp where empno=7788;
name is SCOTT  salary is 120 tax is 3.6

PL/SQL procedure successfully completed.

(八)  复合变量
 

declare 
type emp_record_type is record(name emp.ename%type ,salary emp.sal%type,title emp.job%type);
sp_record emp_record_type;
begin select 
ename ,sal,job into sp_record from emp where empno='7788';
dbms_output.put_line(sp_record.name);
end;

执行结果

SQL> declare
type emp_record_type is record(name emp.ename%type ,salary emp.sal%type,title emp.job%type);
sp_record emp_record_type;
begin select
ename ,sal,job into sp_record from emp where empno='7788';
dbms_output.put_line(sp_record.name);
end;  2    3    4    5    6    7
  8  /
SCOTT

PL/SQL procedure successfully completed.

表实例

declare --定义一个表类型，该类型用于存放emp.ename%type
-- index by binary_integer 表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type ;
begin 
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('name is '|| sp_table(0));
end;

执行结果

SQL> declare --...............emp.ename%type
-- index by binary_integer .......
  2    3  type sp_table_type is table of emp.ename%type index by binary_integer;
  4  sp_table sp_table_type ;
  5  begin
  6  select ename into sp_table(0) from emp where empno=7788;
  7  dbms_output.put_line('name is '|| sp_table(0));
  8  end;
  9  /
name is SCOTT

PL/SQL procedure successfully completed.