1.PLSQL入门helloworld
declare
#变量的声明
i integer;
#方法的开始
begin
#类似于 java System.out.println('hello world'),put()输出;put_lint()换行输出;
Dbms_Output.put_line('hello world!');
#方法结束
end;
2.变量
PLSQL编程中常见的变量分两大类:
1.普通数据类型(char,varcher2,date,number,boolea,long等)
2.特殊变量类型(引用型变量,记录型变量)
引用型指变量的类型取决于表中字段的类型。
记录型指这个变量接受的不是一个字段的值而是一整条记录的值。
声明变量的方式:
#变量名 变量类型(变量长度)
v_name varcher(20);
2.1普通变量
变量赋值的方式有两种:
1.直接赋值语句:
v_name := ‘zhangsan’
2.语句赋值,使用select…into…赋值:(语法select 值 into 变量),通常这个值都来自于表中查询的结果。
#打印员工个人信息,包括:姓名/薪水/地址
declare
-- #姓名
v_name VARCHAR(20) := '张三';
v_sal NUMBER;
v_addr VARCHAR(200);
begin
-- #直接赋值
v_sal := 2000;
-- #语句赋值
select '中国' into v_addr from dual;
-- #打印输出
dbms_output.put_line('姓名:' || v_name || ',薪水:' || v_sal || ',地址:' || v_addr);
end;
注意:
一般不要把变量名声明和表中字段名完全一样,如果这样可能得到不正确的结果:
例:
##该例子将会删除所有的记录,而不是KING这条记录。
declare
ename varcher2(20) := 'KING';
beding
delete from emp where ename=ename;
end;
建议的命名方法:
标识符 | 命名规则 | 例子 |
---|---|---|
程序变量 | v_name | v_name |
程序常量 | c_name | c_company_name |
游标变量 | name_cursor | emp_cursor |
异常标识 | e_name | e_too_many |
表类型 | name_table_type | emp_record_type |
表 | name_table | emp |
记录类型 | name_record | emp_record |
sql*plus替代变量 | p_name | p_sql |
绑定变量 | g_name | g_year_sal |
2.2引用型变量
变量的类型及长度取决于表中字段的类型和长度。
通过表明.列名%TYPE指定变量的类型和长度,例如:v_name emp.ename%TYPE。
#查询emp表中7839号员工的个人信息,打印姓名和薪水
declare
#同表中的类型和字段长度相同
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
select ename,sal into v_name,v_sal from emp where empno = 7839;
dbms_output.put_line('姓名:' || v_name || ',薪水:' || v_sal );
end;
引用型变量的好处,需要知道表中列的类型,此时使用引用类型,不需要考虑列的类型,使用%TYPE就可以知道,因为它使得PL/SQL更加灵活,更加适应于对数据库定义的更新。
2.3记录型变量
接收表中的一整行记录,相当于java中的一个对象。
语法: 变量名称 表名%ROWTYPE,例如:v_emp emp%rowtype;
#查询并打印7839号员工的个人信息
declare
#声明记录型变量
v_emp emp%rowtype
begin
select * into v_emp from emp where empno = 7839;
dbms_output.put_line('姓名:' || v_emp.ename || ',薪水:' || v_emp.sal );
end;
记录型变量要慎用,如果一个表有100个字段,程序中使用这100个字段的话,此时可以使用记录型变量,避免使用引用型变量一个个的声明。
注意:
1.记录型变量只能存储一个完整的行数据。
否则返回的行太多了,记录型变量接受不了。
2.必须select *,不能select ename,sal,否则会报错,因为无法装全v_emp变量。
3.流程控制
3.1条件分支
语法:
begin
if 条件 then 执行1
elseif 条件 then 执行2
else 执行3
end if;
end;
例:判断emp表中记录是否超过20条,10-20之间,或者10以下。
declare
#声明变量接收emp表中的记录数
v_count NUMBER;
begin
select count(1) into v_count from emp;
if v_count>20 then
dbms_output.put_line('emp表中记录数超过了20条,为:'|| v_count);
elseif v_count>=10 then
dbms_output.put_line('emp表中记录数在10-20条,为:'|| v_count);
else
dbms_output.put_line('emp表中记录数为10条以下,为:'|| v_count);
end if;
end;
3.2循环
在oracle中有三种循环方式
loop循环
语法:
begin
loop
exit when 退出循环条件
end loop;
end;
例:打印数字1-10
declare
-- #声明循环变量
v_num NUMBER := 1;
begin
loop
exit when v_num>10;
Dbms_Output.put_line(v_num);
-- #循环变量的自增
v_num := v_num+1;
end loop;
end;
4.游标
游标是用来临时存储一个查询返回的多行数据(结果集,类似于java的jdbc连接返回的resultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据。
游标的使用方式:声明——>打开——>打开——>读取——>关闭
语法:
#游标声明
cursor 游标名[(参数列表)] is 查询语句;#即查询语句的结果被放置在游标中
#游标的打开
open 游标名;
#游标的取值
fetch 游标名 into 变量列表;
#游标的关闭
close 游标名;
4.1游标的属性
%rowcount #获得fetch语句返回的数据行数。返回值类型:整形
%found #最近的fetch语句返回一行数据则为真,否则为假。返回值类型:布尔型
%notfound #与%found属性返回值相反。返回值类型:布尔型,一般用作推出循环判断。
%isopen #游标已经打开时为真,否则为假。返回值类型:布尔型
4.2创建和使用
例:使用游标查询emp表中所有员工的姓名和工资,并将其一次打印出来。
declare
#此时游标c_emp中放的就是查询到的emp表中所有员工的姓名和工资
cursor c_emp is select ename,sal from emp;
#需要遍历将所有的值打印出来,这样就需要一个值去接。声明变量去接收游标中的元素
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
#打开游标
open c_emp;
#通过循环遍历游标
loop
#获取游标中的数据,注意对应的变量顺序,个数
fetch c_emp into v_name,v_sal;
#推出循环条件
exit when c_emp%notfound;
dbms_output.put_line(v_name||'-'||v_sal);
end loop;
#关闭游标
close c_emp;
end;
4.3带参数的游标
例:使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入
declare
#传参,某部门作为参数
cursor c_emp(v_deptno emp.deptno%type) is select ename,sal from emp where deptno = v_deptno;
#声明变量接收游标中的数据
v_name emp.ename%type;
v_sal emp.sal%type;
begin
#打开游标,传具体的值
open c_emp(10);
#遍历游标
loop
#获取游标中数据
fetch c_emp into v_name,v_sal;
#推出循环条件
exit when c_emp%notfound;
dbms_output_put_line(v_name||'-'||v_sal);
end loop;
#关闭游标
close c_emp;
end;
注意:循环之后第一件事情就是fetch,然后再判断。
5.存储过程
PL/SQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复使用。
可以理解为之前的代码全部编写在了main方法中,是匿名程序,java可以通过封装对象和方法来解决复用问题。
将一个个的业务处理过程存储起来进行服用,这些被存储起来的PLSQL程序称之为存储过程。
作用:
在开发程序中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭时很耗费资源的),需要对数据库进行多次I/O读写,性能比较低。如果把这些业务放到PLSQL中,在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率。
语法:
creat or replace procedure 过程名称[(参数列表)] is
begin
end [过程名称];
根据参数类型,我们将其分为3类讲解:
1.不带参数的
2.带输入参数的
3.带输入输出参数(返回值)的
5.1无参存储过程
创建存储过程:
create or replace procedure p_hello is
begin
Dbms_Output.put_line('hello');
end p_hello;
通过PLSQL调用
begin
-- plsql调用存储过程
p_hello;
end;
注意:
1.is和ad时可以互用的。
2.过程中没有declare关键字,declare用在语句块中。
5.2带输入参数的存储过程
例:查询并打印某个员工(如7839号员工)的姓名和薪水存储过程:要求,调用时传入员工编号,自动控制台打印。
#in标识输入的
creat or replace procedure p_querynameandsal(i_empno in emp.empno%type) is
#声明变量接收查询结果
v_name emp.ename%type;
v_sal emp.sal%type;
begin
#根据用户传递的员工号查询姓名和薪水
select ename,sal into v_ename,v_sal from emp where empno=i_empno;
#打印结果
dmbs_output.put_line('姓名:'||v_ename||',薪水:'||v_sal)
end p_querynameandsal;
PLSQL程序调用
begin
-- plsql调用存储过程
p_querynameandsal(7839);
end;
``
5.3带输入输出参数的存储过程
例:输入员工号查询某个员工(7839号员工)信息,要求:将薪水作为返回值输出,给调用的程序使用。
#in是输入参数;out是输出参数
creat or replace procedure p_querysal_out(i_empno in emp.empno%type,o_sal out emp.sal%type) is
begin
select sal into o_sal from emp where empno=i_empno;
end;
PLSQL程序调用
declare
#声明变量,接收存储过程的输出参数
v_sal emp.sal%type;
begin
p_querysal_out(7839,v_sal);
dbms_output.put_line(v_sal);
end;
6.for循环和游标结合使用
declare
#此时游标c_emp中放的就是查询到的emp表中所有员工的姓名和工资
cursor c_emp is select ename,sal from emp;
begin
#使用for通过循环遍历游标,将循环的对象放到tmp中,tmp可以自己随意命名
for tmp in c_emp loop
#可以通过tmp.列名调用
dbms_output.put_line(tmp.ename||'-'||tmp.sal);
end loop;
end;