Pl/sql语法
Pl/sql的数据类型分为：
标量类型
可以简单的理解就是oracle数据库的类型
常见的标量类型举例
（1） 定义一个变长字符串
v_ename varchar2(10);
（2） 定义一个小数范围-9999.99~9999.99
v_sal number(6,2)
（3） 定义一个小数并给一个初始值为5.4:=是pl/sql的赋值好
V_sal2 number(6,2):=5.4
(4) 定义一个日期类型的数据
V_hiredate date
(5) 定义一个布尔变量，不能为空，初始值为false
V_valid Boolean:=false
注：pl/sql在定义一个变量的时候，如果要赋初值，则需要使用：=
比如 v_ename varchar2(32):=‘顺平’；
案例：以输入员工号，显示雇员姓名，工资，个人所得税（税率为0.03）。
Createor replace procedure pro1(v_in_empno in number) is
--定义变量
V_tax_ratenumber(3,2):=0.03;
V_salnumber;
V_enamevarchar(32);
V_taxnumber;
Begin
Selectename,sal into v_ename,v_sal from emp where empno=v_in_empno;
--计算个人所得税
V_tax:=v_sal*v_tax_rate;
Dbms_output.put_line(v_ename||’工资是=’||v_sal||’个人所得税是：’||v_tax);
End;
%type类型
为了在pl/sql编程中，让变量的类型和大小与表的列的大小和类型一致可以使用%type,
也就是自适应，自己选择相应类型。
例：旧：v_sal number; 改为：v_salemp.sal%type;
旧：v_ename varchar(32); 改为：v_ename emp.ename%type;
复合变量
pl/sql记录
基本语法
Type 自己定义的记录名称 is record(
变量，变量的类型，
变量，变量的类型，
)
注：把record可以想象为，里面存了一些变量的类。
使用记录的案例：
编写一个过程，该过程可以接收一个用户编号，并显示该用户的名字，薪水，工作岗位，（注意：要求用pl/sql记录实现）。
Create or replace procedure pro1(v_in_empno in number)
Is
--定义一个记录数据类型
Type hsp_emp_record is record(
V_ename emp.ename%type,
V_sal emp.sal;%type,
V_job emp.job%type
);
--定义一个变量，类型是hsp_emp_record
V_emp_record hsp_emp_record;
Begin
Select ename,sal,job into v_emp_record from emp whereempno=v_in_empno;
Dbms_output.put_line(‘名字:’||v_emp_record.v_ename||’工资:’||v_emp_record.v_sal);
End;
Pl/sql表（了解）
相当于java中的数组
基本语法：
Type自己定义的pl/sql数据类型 is table ofemp.sal%type index by binary_integer;
定义一个这样的变量
变量名 自己定义的pl/sql数据类型;
参照变量
1.游标变量
通过游标，我们可以取得返回结果集(这个结果集，往往是select语句的结果)的任何一行数据，从而提供共享的效率。
案例：
（1） 请使用pl/sql编写一个过程，可以输入部门号，并显示该部门所有员工姓名和他的工资。
Create or replace procedure pro1(v_in_deptnonumber)is
--先定义一个游标变量类型，一个类型可以定义多个游标变量
Type hsp_emp_cursoris ref cursor;
--定义一个游标变量
v_emp_cursor hsp_emp_cursor;
--定义两个变量
V_ename emp.ename%type;
V_sal emp.sal%type;
Begin
--执行语句
Open v_emp_cursorfor select ename,salfrom empwheredeptno=v_in_deptno;
--取出游标指向的每行数据，用循环语句
Loop
--这句话，会引起v_emp_cursor向下走
Fetchv_emp_cursor into v_ename,v_sal;
--游标到达末尾，什么都不返回时退出
Exit whenv_emp_cursor%notfound;
--输出
Dbms_output.put_line(‘用户名’||v_ename||’薪水:’||v_sal);
End loop;
--关闭游标【完后一定要关闭游标】
Closev_emp_cursor;
End;
(2)
在(1)基础上，如果某个员工的工资低于200元，就增加100元。加if语句
Createor replace procedure pro1(v_in_deptno number)is
Type hsp_emp_cursoris ref cursor;
v_emp_cursor hsp_emp_cursor;
V_ename emp.ename%type;
V_sal emp.sal%type;
V_empno emp.empno%type;
Begin
Open v_emp_cursorfor select ename,sal,empnofrom empwheredeptno=v_in_deptno;
Loop
Fetchv_emp_cursor into v_ename,v_sal,v_empno;
Exit whenv_emp_cursor%notfound;
Dbms_output.put_line(‘用户名’||v_ename||’薪水:’||v_sal);
--添加if判断语句
Ifv_sal<200then
Updateempsetsal=sal+100whereempno=v_empno;
End if;
End loop;
Closev_emp_cursor;
End;
Pl/sql的控制语句
条件语句
分为三种 if---then/if---then---else/if---then---elsif---else
if---then
基本语法:
If 条件表达式 then 注：条件表达式不管多复杂结果只是真或假
--执行语句
--执行语句
End if;
快速入门：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%。
Create or replace procedure pro1(v_in_ename varchar2) is
--定义工资变量
V_sal emp.sal%type;
Begin
Select sal into v_sal from emp where ename=v_in_ename;
If v_sal<2000 then
Update emp set sal=sal*1.1 where ename=v_in_ename;
End if;
End;
if---then---else
二重分支：
基本语法：
If 条件表达式 then
--执行语句
--执行语句
Else
--执行语句
End if;
快速入门：编写一个过程，可以输入一个雇员，如果该雇员的补助不是0就在原来的基础上增加100，如果是就把补助改为200。
Create or replace procedure pro1(v_in_ename varchar2) is
--定义补助
V_comm emp.comm%type;
Begin
--查询补助
Select comm into v_comm from emp where ename=v_in_ename;
If v_comm<>0 then
Update emp set comm=comm+100 where ename=v_in_ename;
Else
Update emp set comm=200 whereename=v_in_ename;
End if;
End;
多重分支
语法：
If条件表达式 then
--执行语句
Elsif条件语句 then
--执行语句
[这里可以有多个elsif…]
Else[else 可以没有]
--执行语句
Endif;
注：elsif 确实没有e。
快速入门：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是president
就给他的工资加1000，如果该雇员的职位是manager就给他的工资加500，其他职位的雇员工资加200。
Createot replace procedure pro1(v_in_empno number) is
--定义职位变量
V_jobemp.job&type;
Begin
Selectjob into v_job from emp where empno=v_in_empno;
Ifjob=’president’ then
Updateemp set sal=sal+1000 where empno=v_empno;
Elsifjob=’manager’ then
Updateemp set sal=sal+500 where empno=v_in_empno;
Else
Updateemp set sal=sal+200 where empno=v_in_empno;
Endif;
End;
注：pl/sql中字符串的比较是 = 比如‘abc’=’ttt’
Case 选择
语法： 和Java中的switchcase 语句相似
Case
When 条件语句 then 执行语句
When 条件语句 then 执行语句
……
End case;
循环语句（循环控制结构）
loop
基本语法：
Loop
--执行语句
Exit when 条件表达式 注：条件表达式一旦为真就退出，为假继续执行
End loop;
快速入门：编写一个过程，可输入用户名和添加用户的个数n；循环添加n个用户到users表中，用户编号从1开始增加，直到n。
表的结构是：
Create tableusers5(
Id numberprimary key,
Namevarchar2(32));
--过程
Create orreplace procedure pro1(v_in_ename varchar2,n number) is
--定义需要的变量
V_empnonumber:=1; --赋初值
Begin
Loop
--执行添加任务
Insert intousers5 values(v_empno,v_in_ename);
Exit whenv_empno=n; --退出的是当前这层loop
--v_empno 需要自增
V_empno:=v_empno+1; 注意赋值是：=
End loop; 注：exit只能在loop中使用
End;
使用：exec pro1(‘顺平’，100)；
While
基本语法
While 条件表达式 loop
--执行语句
End loop;
快速入门：编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从200开始增加。
Create or replace procedure pro1(v_in_enamevarchar2,v_in_n number) is
V_ename number:=200;
Begin
While v_ename<=200+v_in_n loop
Insert into users5values(v_empno,v_in_ename);
V_ename:=v_empno+1;
End loop;
End;
思考两道题的对错：
Create or replace procedure sp_pro6(spnamevarchar2) is
V_test varchar2(40);
V_test:=’aaa’;
Begin
Dbms_output.put_line(v_test);
End;
结论：上面的过程是错误的，原因是在is…begin 间不能对变量赋值，如果要重新赋值，则需要在begin…end 间。
Create or replace procedure sp_pro6(spnamevarchar) is
V_test varchar2(40):=’aaa’;
Begin
Spname:=’你好’;
Dbms_output.put_line(v_testspname);
End;
结论：pl/sql中，不能对输入参数的值，重新赋值。
For循环语句不建议使用。
顺序控制-goto,null
Goto
Goto语句用于跳转到特定标号去执行语句
标号的格式：<< >>
案例：
I number:=1:
Begin
<<start_loop>>
Loop
Dbms_output.put_line(‘输出i=’||i);
If i=12 then
Goto end_loop;
End if;
I:=i+1;
If i=10 then
Goto start_loop;
End if;
End loop;
<<end_loop>>
Dbms_output.put_line(‘循环结束’);
End;
说明：
1. pl/sql支持goto和标号的使用
2. goto不要轻易使用
null
主要的用处是提高代码的可读性，其实它什么都不做
使用pl/sql编写分页过程
案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名
--v_out_ename out varchar2 表示 v_out_ename是输出变量，out是关键字，varchar2表示输出变量的类型
Create or replace procedure por1(v_in_empnoin number,v_out_enameout varchar2) is
Begin
Select ename into v_out_ename from empwhere empno=v_in_empno;
End;
调用返回列表值（集合/表）
案例：编写一个过程，输入部门号，返回该部门所有雇员信息
1. 创建一个包，该包中定义一个游标类型
Create or replace package pack1 is
--定义一个游标数据类型
Type my_cursor is ref cursor;
End;
2. 编写过程‘
Create or replace procedure pro1
(v_in_deptno in number,v_out_result out pack1.my_cursor) is
Begin
Open v_out_resultforselect * from emp wheredeptno=v_in_deptno;
End;
练习：编写一个存储过程，要求可以输入表名，每页显示记录数，当前页，返回返回的结果集。
--建包，使用游标类型
--如果有这个游标数据类型，则可以使用
Create or replace package pack1 is
--定义一个游标数据类型
Type my_cursor is ref cursor;
End;
--编写过程
Create or replace procedurefenyepro(v_in_table in varchar2,v_in_pagesize in number,
v_in_pagenow in number,v_our_result outpack1.my_cursor) is
--定义变量
V_sql varchar2(2000);
V_start number;
V_end number;
Begin
--执行代码
--回忆分页语句
--计算v_start和v_end是多少
V_start:=v_in_pagesize*(v_in_pagenow-1)+1;
V_end:=v_in_pagesize*v_in_pagenow;
V_sql:=’select t2.* from (selectt1.*,rownum rn from (select * from’||v_in_table||’)t1 where rownum<=’||v_end||’)t2 where rn >=’||v_start;
--打开游标，让游标指向结果集
Open v_out_resultfor v_sql;
End;