oracle 11g 学习笔记 11_4

pl/sql基础
1、pl/sql概念
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。


.编写一个存储过程，该过程可以向某表中添加记录
1. 创建一个简单表
create table mytest(name varchar2(30),passwd varchar2(30));
2. 创建存储过程
create or replace procedure sp_pro2 is 
begin
--执行部分
insert into mytest values('pangzi','m123');
end;


replace:
如何查看错误信息：show error;
如何调用
1)exec 过程名（参数值1，参数值2...）
2)call 过程名（参数值1，参数值2...）
二、变量，复合类型
1、变量
1.标量(scalar)
pl/sql块为变量赋值不同于其它编程语言，需要在等号前加冒号( := );

--案例
--输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）
declare
v_name emp.ename%type;
v_sal emp.sal%type;
v_tax_sal number(7,2);--个人所得税
c_tax_rate number(7,2) :=0.03; --给税率赋值
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('雇员姓名:'||v_name ||' 工资：'||v_sal ||' 个人所得税:' ||v_tax_sal);
end;

2。复合变量
符合变量用于存放多个值的变量，主要包括这几种：
pl/sql 记录
pl/sql 表
嵌套表

i.记录
记录类似于高级语言中的结构体，当引用pl/sql记录成员时，引用的规则是：记录变量.记录成员

--案例2
--定义一个记录类型，用来存放雇员姓名，工资，工作
--
eclare 
type emp_record_type is record(
        name emp.ename%type,
        salary emp.sal%type,
        myJob emp.job%type
);
--定义一个是emp_record_type 类型的变量sp_record
sp_record emp_record_type;
begin
  --执行
  select ename,sal,job into sp_record from emp where empno =&no;
  --打印信息
  dbms_output.put_line(
       '雇员姓名:' ||sp_record.name ||' 工资:'||sp_record.salary ||' 工作是:'||sp_record.myJob);
end;

2.表
表类似于高级语言中的数组，但是需要注意的是在高级语言中数组的下表不能为负，而pl/sql是可以为负数的，并且表元素的下表没有限制.

--案例3
--定义一个pl/sql表类型sp_table_type
declare 
type sp_table_type is table of emp.ename%type
index by binary_integer;--表的下标是正数
--定义一个sp_table 类型是sp_table_type
sp_table sp_table_type;
begin 
  select ename into sp_table(-1) from emp where empno =7788;
  --输出
  dbms_output.put_line('雇员姓名:' ||sp_table(-1));
end;

3.参照变量--ref cursor 游标变量
使用游标时，当定义游标时不需要指定的select语句，但是当使用游标时（open时）需要指定select语句，这样一个游标就与一个select语句结合了。

--实例4
--编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资
--如果某个员工的工资低于200元，就添加100元
declare
--定义游标sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量，用于存储员工姓名和工资
v_ename emp.ename%type;
v_sal emp.sal%type;
--emp.sal%type 表示的是v_sal的类型和emp表的sal的类型一样.
begin 
--执行部分
open test_cursor for select ename,sal from emp where deptno =&no;
--循环取出
loop
--取出并放入变量
fetch test_cursor into v_ename,v_sal;
--循环结束条件是
exit when test_cursor%notfound;
--输出
dbms_output.put_line('雇员姓名:'||v_ename||'工资:'||v_sal);
end loop;
end;