第一步:打开Oracle监听
$ lsnrctl start
第二步:进入sqlplus
$ sqlplus /nolog
SQL>
第三步:使用sysdab角色登录sqlplus
SQL> conn /as sysdba
第四步:启动数据库
SQL> startup
--------------------------------------------
一.常见的访问oracle的技术
1.1 plsql 过程化sql (存储过程)
1.2 proc/c++ 使用c或者c++语言访问oracle数据库
1.3 ODBC/ADO vc中访问数据库的技术
1.4 OCI oracle 底层的连接接口
1.5 SQLJ/JDBC java访问数据库的技术
二.plsql 对sql进行了扩展
2.1 变量和类型
2.2 控制语句
2.3 过程和函数
2.4 对象和方法
三.plsql 程序结构
declare
/* 声明区 申明区
定义变量 定义类型
*/
-- 这是单行注释
begin
/* 执行区
执行sql语句或者plsql语句的 */
exception
/* 异常处理区
处理正常流程之外的代码 */
end;
四.第一个plsql程序
set serveroutput on
begin
update s_emp set salary=1200 where id=1;
commit;
dbms_output.put_line('hello plsql!');
end;
/
五.开发环境
sqlplus 命令行下的开发工具
sqldeveloper 图形化的开发工具
六 变量
6.1 如何定义变量
在申明区定义变量
变量名 类型;
6.2 sql 中的数据类型
number
varchar2(n)
date
declare
var_id number:=1;
var_name varchar2(30);
begin
/* 给变量赋值 */
var_id:=100;
var_name:='test';
dbms_output.put_line(var_id||':'||var_name);
end;
6.3 变量的修饰
constant 类型 not null
declare
var_id constant number:=1;
var_name varchar2(30) not null:='a';
begin
/* 给变量赋值 */
-- var_id:=100;
var_name:='test';
dbms_output.put_line(var_id||':'||var_name);
end;
constant 和 not null 修饰的变量必须赋初始值
一个变量定义之后不赋值 默认是NULL值
6.4 使用sql语句 对变量进行赋值
declare
var_name varchar2(30);
var_salary number;
var_id number:=1;
begin
select first_name,salary into var_name,
var_salary from s_emp where id=var_id;
dbms_output.put_line(var_name||':'||
var_salary);
end;
七.plsql 中可以使用的数据类型
7.1 标量类型
number
varchar2(n)
char(n)
date
binary_integer
boolean
7.2 复合类型
record 记录类型
table 表类型
cursor 游标类型
7.3 参考类型
ref 类型
7.4 大类型 (存储数据路径)
BLOB 大二进制 0-4g
CLOB 大字符类型 0-4g
BFILE 文件类型
八.plsql 中类型的使用
8.1 boolean 和 binary_integer的使用
declare
var_f boolean;
var_count binary_integer;
begin
-- var_f:=true;
var_f:=false;
var_count:=1000;
if var_f then
dbms_output.put_line(var_count);
end if;
end;
8.2 获得字段对应的类型
8.2.1定义两个变量 分别和s_emp 表中的id 和
first_name 字段的类型相同 并且使用sql语句
把id=1 的对应的值赋值给这两个变量。
declare
var_id s_emp.id%type;
var_name varchar2(25);
begin
select id,first_name into var_id,var_name from s_emp where id=1;
dbms_output.put_line(var_id||':' ||var_name);
end;
8.2.2 使用表名.字段名%type
可以获得字段对应的类型
declare
var_id s_emp.id%type;
var_name s_emp.first_name%type;
begin
select id,first_name into var_id,var_name from s_emp where id=1;
dbms_output.put_line(var_id||':' ||var_name);
end;
8.2.3 使用record类型
8.2.3.1 语法
record类型 相当于struct 类型
type 记录类型名 is record(
字段名 类型,
字段名 类型,
字段名 类型
);
8.2.3.2 举例
把 id first_name salary 包装成一个类型
declare
/* 定义一个record类型 */
type emptype is record(
id s_emp.id%type,
first_name s_emp.first_name%type,
salary s_emp.salary%type
);
/* 使用类型定义变量 */
var_emp emptype;
begin
select id,first_name,salary into var_emp from s_emp where id=2;
dbms_output.put_line(var_emp.id||':'|| var_emp.first_name||':'||var_emp.salary);
end;
8.2.3.3 如果查询的字段数 少于记录类型字段数
declare
/* 定义一个record类型 */
type emptype is record(
id s_emp.id%type,
first_name s_emp.first_name%type,
salary s_emp.salary%type
);
/* 使用类型定义变量 */
var_emp emptype;
begin
select id,salary into var_emp.id,var_emp.salary from s_emp where id=2;
dbms_output.put_line(var_emp.id||':'|| var_emp.first_name||':'||var_emp.salary);
end;
8.2.3.4 记录类型的变量赋值
declare
/* 定义一个record类型 */
type emptype is record(
id s_emp.id%type,
first_name s_emp.first_name%type,
salary s_emp.salary%type
);
/* 使用类型定义变量 */
var_emp emptype;
var_emp2 emptype;
begin
select id,first_name,salary into var_emp2 from s_emp where id=2;
/* 整体赋值
var_emp:=var_emp2; */
/* 选择某些字段赋值 */
var_emp.first_name:=var_emp2.first_name;
dbms_output.put_line(var_emp.id||':'|| var_emp.first_name||':'||var_emp.salary);
end;
8.2.4 思考如何把一个表的所有字段包装成一个记录
类型?
表名%rowtype 获得表的一行对应的类型
这个类型实际上就是字段名 和 字段顺序完全和
表头相同的记录类型。
s_dept%rowtype
s_emp%rowtype
把s_emp 表中 id=1 所有数据放入一个记录类型
的变量中 输出id first_name salary
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=1;
dbms_output.put_line(var_emp.id||':'|| var_emp.first_name||':'||var_emp.salary);
end;
8.2.5 table 类型 相当于c的数组类型
8.2.5.1 table 类型的语法
type 表类型名 is table of 元素类型
index by binary_integer;
8.2.5.2 使用table类型的变量
把id=1 或者 id=3 或者id=7 的工资放入一个
table类型的变量中
declare
/* 定义table类型 */
type numstype is table of
s_emp.id%type index by binary_integer;
/* 使用类型定义变量 */
var_nums numstype;
/* 存储下标的变量 */
var_ind binary_integer;
begin
var_nums(11):=300;
select salary into var_nums(1) from s_emp where id=1;
select salary into var_nums(2) from s_emp where id=3;
select salary into var_nums(3) from s_emp where id=7;
var_ind:=1;
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_ind+1;
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_ind+1;
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_ind+1;
dbms_output.put_line(var_nums(var_ind));
end;
8.2.5.3 下标不连续时 如何遍历table中的数据
迭代器思想
first() 得到第一个元素对应的下标
next(n) 根据一个元素下标n 得到下一个元素
的下标
last() 得到最后一个元素的下标
declare
/* 定义table类型 */
type numstype is table of
s_emp.id%type index by binary_integer;
/* 使用类型定义变量 */
var_nums numstype;
/* 存储下标的变量 */
var_ind binary_integer;
begin
var_nums(11):=300;
select salary into var_nums(1) from s_emp where id=1;
select salary into var_nums(2) from s_emp where id=3;
select salary into var_nums(3) from s_emp where id=7;
var_ind:=var_nums.first();
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_nums.next(var_ind);
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_nums.next(var_ind);
dbms_output.put_line(var_nums(var_ind));
var_ind:=var_nums.next(var_ind);
dbms_output.put_line(var_nums(var_ind));
end;
九.变量的作用域和可见性
declare
var_m number:=10000;
begin
declare
var_n number:=100;
begin
/* 局部区 可以访问全局的数据 */
end;
/* 全局区不能访问局部区 */
end;
<<outerblock>>
declare
var_m number:=10000;
begin
declare
var_m number:=100;
begin
/* 局部区 可以访问全局的数据 */
dbms_output.put_line(var_m);
dbms_output.put_line(outerblock.var_m);
end;
/* 全局区不能访问局部区 */
end;
十.plsql中控制语句
10.1 分支语句
10.1.1 c的分支和plsql分支语句的不同
if(a>b){
}
if a>b then
end if;
if(a>b){
}else{
}
if a>b then
else
end if;
if(a>b){
}else if(a>c){
}else if(a>d){
}
if a>b then
elsif a>c then
elsif a>d then
end if;
if(a>b){
}else if(a>c){
}else if(a>d){
}else{
}
if a>b then
elsif a>c then
elsif a>d then
else
end if;
10.1.2 举例
10.1.2.1 定义三个整数变量 给这三个变量赋值
输出这三个变量的最大值
declare
var_a number;
var_b number;
var_c number;
begin
var_a:=&var_a;
var_b:=&var_b;
var_c:=&var_c;
if var_a<var_b then
if var_b<var_c then
dbms_output.put_line(var_c);
else
dbms_output.put_line(var_b);
end if;
else
if var_a<var_c then
dbms_output.put_line(var_c);
else
dbms_output.put_line(var_a);
end if;
end if;
end;
declare
var_a number;
var_b number;
var_c number;
var_max number;
begin
var_a:=&var_a;
var_b:=&var_b;
var_c:=&var_c;
var_max:=var_c;
if var_max<var_a then
var_max:=var_a;
end if;
if var_max<var_b then
var_max:=var_b;
end if;
dbms_output.put_line(var_max);
end;
10.1.2.2 NULL 值的判断
declare
var_a number;
var_b number;
begin
if var_a<var_b then
dbms_output.put_line('var_a<var_b');
elsif var_a>var_b then
dbms_output.put_line('var_a>var_b');
elsif var_a=var_b then
dbms_output.put_line('var_a=var_b');
elsif var_a is null and var_b is null then
dbms_output.put_line('var_a is null and var_b is null');
else
NULL;/*这是plsql中的NULL语句*/
end if;
end;
10.2 循环语句
10.2.1 简单循环
loop
/* 循环执行的代码 或者语句 */
end loop;
10.2.2 如何退出当前循环?
exit when 循环的结束条件;
if 退出条件 then
exit;
end if;
10.2.3 使用简单循环 从1 输出到 10
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
exit when var_i=10;
var_i:=var_i+1;
end loop;
end;
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
if var_i=10 then
dbms_output.put_line('loop over');
exit;
end if;
var_i:=var_i+1;
end loop;
end;
10.2.4 while 循环
while 循环条件 loop
/* 循环代码 */
end loop;
10.2.5 使用while 循环输出 1 到 10
declare
var_i number:=1;
begin
while var_i< 11 loop
dbms_output.put_line(var_i);
var_i:=var_i+1;
end loop;
end;
10.2.6 for 循环(智能循环)
for 变量名 in a..b loop
/* 循环代码 变量在循环中不允许修改 */
end loop;
10.2.7 使用for 循环输出1 到 10
begin
for var_a in 1..10 loop
dbms_output.put_line(var_a);
end loop;
end;
10.2.8 使用for 循环输出10 到 1
begin
for var_a in reverse 1..10 loop
dbms_output.put_line(var_a);
end loop;
end;
10.3 goto语句
10.3.1 语法
goto 标签名;
10.3.2 使用goto 语句 输出 1 到 10 达到循环的
效果。
declare
var_i number:=1;
begin
<<myloop>>
if var_i<11 then
dbms_output.put_line(var_i);
var_i:=var_i+1;
goto myloop;
end if;
end;
10.3.3 退出多重循环
begin
<<outerloop>>
for a in 1..4 loop
for b in 1..4 loop
dbms_output.put_line(b);
if b=2 then
exit outerloop;
end if;
end loop;
end loop;
end;
begin
for a in 1..4 loop
for b in 1..4 loop
dbms_output.put_line(b);
if b=2 then
goto outerloop;
end if;
end loop;
end loop;
<<outerloop>>
NULL;
end;
一.sql 在plsql中的使用
1.1 select 语句 要和into 结合使用
select first_name,salary into var_name,var_sal from s_emp where id=1;
1.2 dml(insert delete update)
tcl(commit rollback savepoint )
可以直接在plsql中使用
1.3 ddl(create drop alter )不能直接在plsql中使用 需要使用动态sql
二.动态sql
2.1 动态sql的概念
sql语句在程序执行的过程中可以发生改变。
2.2 ddl的动态sql
declare
sqlstr varchar2(100);
begin
sqlstr:='create table testdsql200(id number
primary key)';
sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
sqlstr:=sqlstr||','||'name varchar2(30))';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
2.3 dml 的动态sql
'insert into testdsql200 values(1,''test'')'
declare
sqlstr varchar2(100);
begin
sqlstr:='insert into testdsql200 values(1,''test'')';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
commit;
end;
/* 以冒号开头的叫占位符 */ "hello %d%lf"
declare
sqlstr varchar2(100);
var_id number:=2;
var_name varchar2(30):='test2';
begin
sqlstr:='insert into testdsql200
values(:b0,:b1)';
dbms_output.put_line(sqlstr);
execute immediate sqlstr using var_id,var_name;
commit;
end;
2.4 select 语句的动态sql 在参考游标中完成
三 游标
3.1 概念
用来处理多行数据的一个数据结构。
3.2 使用游标的步骤
3.2.1 声明游标
declare
cursor 游标名 is select语句;
3.2.2 打开游标
open 游标名;
3.2.3 提取数据
fetch 游标名 into 变量;
3.2.4 关闭游标
close 游标名;
3.3 把s_emp 表中所有的数据放入一个游标中
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
close empcursor;
end;
3.4 思考如何提取游标中的所有的数据
游标的属性
游标名%found 在提取数据时提取到新数据就返回真,提取不到新数据 则返回假。
游标没有打开 使用found属性就返回非法游标,打开但是没有fetch
则返回NULL。
游标名%notfound 在提取数据时 提取到新数据就返回假,提取不到新数据就返回真。
游标没有打开 使用notfound属性就返回非法游标,打开但是没有fetch
则返回NULL值。
游标名%isopen 游标是否处于打开状态,打开就返回真, 关闭返回假
打开的游标不能再打开,关闭的游标不能再关闭
游标名%rowcount 游标指针偏移量
3.5 使用简单循环 结合notfound 属性遍历游标
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
loop
fetch empcursor into var_emp;
/* 当提取不到新数据 就结束循环 */
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name);
end loop;
close empcursor;
end;
3.6 使用while 循环 结合found 属性遍历游标
declare
cursor empcursor is select * from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
while empcursor%found loop
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name);
fetch empcursor into var_emp;
end loop;
close empcursor;
end;
3.7 for 循环遍历游标(智能循环)
declare
cursor empcursor is select * from s_emp;
begin
for var_emp in empcursor loop
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name);
end loop;
end;
3.8 带参游标
plsql 中的参数 不能加任何长度修饰但可以使用%type
参数名 不要和字段名重名,需要打开游标时传入参数
declare
cursor empcursor(var_id s_emp.id%type)
is select * from s_emp where id>var_id;
begin
for var_emp in empcursor(10) loop
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name);
end loop;
end;
使用简单循环 结合notfound 遍历上面的带参游标
declare
cursor empcursor(var_id s_emp.id%type)
is select * from s_emp where id>var_id;
var_emp s_emp%rowtype;
begin
open empcursor(15);
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||
var_emp.first_name);
end loop;
close empcursor;
end;
3.9 参考游标 (引用游标)
游标对应的sql 语句是一条字符串
3.9.1 定义参考游标类型
type myrefcursor is ref cursor;
3.9.2 使用参考游标类型定义 游标变量
empcursor myrefcursor;
3.9.3 打开游标时 关联到字符串
open empcursor for sqlstr;
declare
sqlstr varchar2(100):='select * from s_emp';
type myrefcursor is ref cursor;
empcursor myrefcursor;
var_emp s_emp%rowtype;
begin
/* 关联游标 到字符串 */
open empcursor for sqlstr;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'|| var_emp.first_name);
end loop;
close empcursor;
end;
declare
sqlstr varchar2(100):='select * from s_emp
where id>:b0';
type myrefcursor is ref cursor;
empcursor myrefcursor;
var_emp s_emp%rowtype;
var_id s_emp.id%type:=15;
begin
/* 关联游标 到字符串 */
open empcursor for sqlstr using var_id;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
end loop;
close empcursor;
end;
四 plsql 中的异常
4.1 系统提供的异常以及处理
declare
var_name s_emp.first_name%type;
var_id s_emp.id%type:=-1;
begin
select first_name into var_name
from s_emp where id=var_id;
dbms_output.put_line(var_name);
exception
when no_data_found then
dbms_output.put_line('myprocess'||SQLCODE||':'|| SQLERRM);
end;
declare
var_name s_emp.first_name%type;
var_id s_emp.id%type:=-1;
begin
select first_name into var_name
from s_emp where id>var_id;
dbms_output.put_line(var_name);
exception
when no_data_found then
dbms_output.put_line('myprocess'||SQLCODE||':'||SQLERRM);
when too_many_rows then
dbms_output.put_line('too many emps');
when others then
dbms_output.put_line('other exeception');
end;
4.2 用户自定义异常
4.2.1 根据实际情况定义异常
4.2.2 根据条件抛出异常
4.2.3 捕获异常
4.2.4 处理异常
declare
too_many_emp exception;
begin
if 1=2 then
raise too_many_emp;
end if;
dbms_output.put_line('app continue!');
exception
when too_many_emp then
dbms_output.put_line('catch emp exception');
end;
五.存储过程
5.1 对一组sql 语句 起的一个逻辑名。
5.2 如何 建立存储过程
create or replace procedure 存储过程名(参数列表)
is
begin
end;
5.3 设计一个存储过程 传入两个整数参数 输出
两个参数的最大值
create or replace procedure getmaxnum(
var_x number,var_y number)
is
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
end;
5.4 查看
desc 存储过程名;
desc getmaxnum;
SQL> desc getmaxnum;
PROCEDURE getmaxnum
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAR_X NUMBER IN
VAR_Y NUMBER IN
参数的模式:
in 负责给存储过程传入值
out 负责给存储过程传出值
in out 既负责又负责传出值
参数的默认值
create or replace procedure getmaxnumg(
var_x number,var_y number:=100)
is
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
end;
/
desc getmaxnum;
5.5 调用存储过程
begin
getmaxnumg(100,200);
end;
begin
getmaxnumg(1);
end;
declare
var_x number:=1000;
var_y number:=1001;
begin
getmaxnumg(var_x,var_y);
end;
desc user_source;
select text from user_source where
name='GETMAXNUMG';
5.6 写一个存储过程 设计三个整数参数
输出前两个参数的最大值 并把两个参数的和
放入第三个参数中
create or replace procedure getmaxandsum(
x in number,y in number,z out number)
is
begin
if x<y then
dbms_output.put_line(y);
else
dbms_output.put_line(x);
end if;
z:=x+y;
end;
declare
var_z number:=0;
begin
getmaxandsum(1,9526,var_z);
dbms_output.put_line(var_z);
end;
5.7 设计一个存储过程 有两个整数参数
输出这两个参数的最大值 然后把两个参数的
和存入第二个参数中。
create or replace procedure getmaxandsum(
x in number,y in out number)
is
begin
if x<y then
dbms_output.put_line(y);
else
dbms_output.put_line(x);
end if;
y:=x+y;
end;
/
declare
var_y number:=99;
begin
getmaxandsum(1,var_y);
dbms_output.put_line(var_y);
end;
5.8 设计一个存储过程 有两个整数参数
第一个参数是一个传入的整数
第二个参数负责返回 1 加到第一个参数的和
create or replace procedure getnumn(x in number,
y in out number)
is
begin
for i in 1..x loop
y:=i+y;
end loop;
end;
/
create or replace procedure getnumn(x in number,
y out number)
is
begin
y:=(1+x)*x/2;
end;
/
declare
var_y number:=0;
begin
getnumn(10,var_y);
dbms_output.put_line(var_y);
end;
六 .函数
函数和存储过程的不同
6.1 关键字不同 procedure function
6.2 函数有返回值 和 返回值类型
存储过程没有
6.3 调用方式不同 存储过程可以直接在plsql
中调用 函数必须组成表达式。
写一个函数 传入两个整数参数 返回两个整数
参数的最大值。
create or replace function fungetmax(
x in number,y in number)return number
is
begin
if x<y then
return y;
end if;
return x;
end;
show errors
declare
var_res number;
begin
var_res:=fungetmax(1,9527);
dbms_output.put_line(var_res);
end;
初学者Oracle数据库:plsql的使用与示例
最新推荐文章于 2024-08-07 16:29:33 发布