SQl笔记第三部分（PL_SQL）
来源：互联网 发布：java集合常用的方法 编辑：程序博客网 时间：2024/06/05 19:36
额，学完了，整理一份笔记出来：
打开输出：set serveroutput on;
--最简单的语句块
begin
dbms_output.put_line(‘HelloWorld!’);
end;
set serveroutput on;
--简单的PL/SQL语句块
declare
v_namevarchar2(20);
begin
v_name:= ‘myname’;
dbms_output.put_line(v_name);
end;
--完整的语句块
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
--变量声明的规则 //PL_SQL中两个横线可以注释掉一行
1、变量声明不能够使用保留字，如from、select等
2、第一个字符必须是字母
3、变量名最多包含30个字符
4、不要与数据库的表或者列同名
5、每一行只能声明一个变量
--常用变量类型
1、binary_integer:整数，主要用来计数而不是用来表示字段类型，数组下标
2、number：数字类型
3、char：定长字符串
4、varchar2：变长字符串
5、date：日期
6、long：长字符串，最长2G
7、boolean：布尔类型，可以取值为true、false和null值，不给初值是null。布尔类型不可打印。
--变量声明
declare
v_tempnumber(1);
v_countbinary_integer := 0;
v_salnumber(7,2) := 4000.00;
v_datedate := sysdate;
v_piconstant number(3,2) :=3.14;
v_validboolean := false;
v_namevarchar2(20) not null := 'Myname';
begin
dbms_output.put_line('v_tempvalue:' || v_temp);
end;
--变量声明，使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test');
end;
--Table变量类型(数组)
declare
type type_table_emp_empno is table of emp.empno%type index bybinary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--Record变量类型（近似java中的类）（视频在此有缺失）
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' - ' || v_temp.dname);
end;
--使用%rowtype声明record变量（视频在此有缺失）
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' - ' || v_temp.dname);
end;
--SQL语句的运用
--select语句(不用游标必须有into)（有且只能返回一条记录，没有不行，多了也不行）
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_name, v_sal from emp where empno = 7369;
dbms_output.put_line(v_name || ' ' || v_sal);
end;
--select语句简单用法
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
--insert语句(记得commit ，否则没用)
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values(v_deptno, v_dname, v_loc);
commit;
end;
-- update/delete 用法同普通用法无异，只是也同insert一样可以与变量混用，要记得commit ，否则没用
-- sql%rowcount（sql代表上一条语句，rowcount代表上一条语句执行后值改变了多少条数据)
declare
v_deptno emp2.deptno%type := 10;
v_count number;
begin
--update emp2 set sal = sal/2 where deptno = v_deptno;
--select deptno into v_deptno from emp2 where empno = 7369;
select count(*) into v_count from emp2;
dbms_output.put_line(sql%rowcount || '条记录被影响');
commit;
end;
--DDL语句(需要加executeimmediate)
begin
execute immediate 'create table t (nnn varchar2(20) default ''aaa'')';
end;
--if语句
取出7369的薪水，如果<1200，输出'low'，如果<2000输出'middle'，否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno = 7369;
if(v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--练习
--循环
declare
ibinary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when (i >= 11);
end loop;
end;
---------
declare
jbinary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
endloop;
end;
-----------
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
--错误处理(去API查异常名称,一般不用pl_sql来处理异常，移植性低)
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多纪录了');
when others then
dbms_output.put_line('error');
end;
----------
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
---------
--创建事件日志表
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
--创建序列
create sequence seq_errorlog_id start with1 increment by 1
--实验
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE; （SQLCODE -- 出错代码关键字）
v_errmsg := SQLERRM; （SQLERRM -- 出错信息关键字）
insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);
commit;
end;
--游标(cursor)
declare
cursorc is
select * from emp;
v_temp c%rowtype;
begin
open c;
fetch c into v_temp;
dbms_output.put_line(v_temp.ename);
close c;
end;
PS：
1. 声明游标（但还未使用
2. open打开游标（此时才使用
------------------
Oracle游标常用属性：
%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。
------------------
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
----------------------
While循环：
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
-----------------
For循环：
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
PS:
1. for循环自动打开和关闭游标。
2. for开始时自动声明v_emp
3. 推荐使用。
--带参数的游标
declare
cursor c (v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
--可更新的游标
declare
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if (v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
ps:
1. 判断两个值相等时用的是‘=‘，而不是‘==’
----------------
--存储过程
create or replace procedure p
is
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if (v_temp.deptno = 10) then
update emp2 set sal = sal + 10 where current of c;
elsif (v_temp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
--执行
exec p;
或
begin
p;
end;
PS:
1. 用：
create orreplace procedure p
is
来替换掉原来的：
declare
其他与原来的语句块一样。
2. 创建时过程并未执行。
--带参数的存储过程(in传入 out传出 in out传入兼传出)
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
PS:
1. 存储过程时如果语句块错误，SQL只会报错但不会指明错误位置，需要用 show error 来查错误。
--实验
declare
v_anumber := 3;
v_bnumber := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
-------------------
--函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif (v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
--触发器(牢牢掌握住概念)
create table emp2_log
(
uname varchar2(20),
action varchar(10),
atime date
)
-----------
create or replace trigger trig
after insert or update or delete on emp2
begin
ifinserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
endif;
end;
1.for each now 每更新一行就触发一次
----------
update emp2 set sal = sal * 2 where deptno= 30;
--------
create or replace trigger trig
after insert or update or delete on emp2 for each row
begin
ifinserting then
insert into emp2_log values (USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values (USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
endif;
end;
-------------
--不提倡使用
create or replace trigger trig
after update on dept for each row
begin
update emp2 set deptno = :NEW.deptno wheredeptno = :OLD.deptno;
end;
----------------------
--树状结构的存储与展现
drop table article;
create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1), --0代表非叶子节点，1代表叶子节点
alevel number(2) – 级别
)
-------------
insert into article values (1, '蚂蚁大战大象', 0,0, 0);
insert into article values (2, '大象被打趴下了', 1,0, 1);
insert into article values (3, '蚂蚁也不好过', 2,1, 2);
insert into article values (4, '瞎说', 2, 0,2);
insert into article values (5, '没有瞎说', 4, 1,3);
insert into article values (6, '怎么可能', 1, 0,1);
insert into article values (7, '怎么没可能', 6, 1,2);
insert into article values (8, '可能性是很大的', 6,1, 2);
insert into article values (9, '大象进医院了', 2,0, 2);
insert into article values (10, '护士是蚂蚁', 9, 1,3);
commit;
---------
蚂蚁大战大象
大象被打趴下了
蚂蚁也不好过
瞎说
没有瞎说
大象进医院了
护士是蚂蚁
怎么可能
怎么不可能
可能性是很大的
--------------------------
create or replace procedure p (v_pidarticle.pid%type, v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
fori in 1..v_level loop
v_preStr := v_preStr || '****';
endloop;
forv_article in c loop
dbms_output.put_line(v_preStr || v_article.cont;
if (v_article.isleaf = 0)
then
p (v_article.id, v_level + 1);
end if;
endloop;
end;
-----------------------------
exec p(0,1);
- SQl笔记第三部分（PL_SQL）
- PL_SQL 复习笔记
- PL_SQL笔记(1)(转载)
- SQL大全（很强大）第三部分
- 《Effective STL》学习笔记（第三部分）
- 《Effective STL》学习笔记（第三部分）
- Linux笔记第三部分
- pl_sql
- pl_sql
- 尚学堂马士兵Oracle学习笔记之五：PL_SQL(过程化SQL语言)
- 小布老师—PL_SQL(第三讲)
- hive学习笔记第三部分
- SQL笔记第一部分（select基础）
- SQL学习笔记（基础部分）
- 分布式、并行计算语言Erlang 学习笔记（第三部分）
- 数据库系统概论 学习笔记（三）第三部分
- Linux Unix shell 编程指南学习笔记（第三部分）
- Windows 核心编程 学习笔记 （第三部分）
- Bootstrap switch.js 开关插件 api
- JSP中getParameter与setAttribute的区别
- [译]Oculus:"sensor fusion:Keeping It Simple"
- SD卡上挂载文件系统
- 高仿微信底部状态栏的轻量级库
- SQl笔记第三部分（PL_SQL）
- 欢迎使用CSDN-markdown编辑器
- cocos2d-c++ 添加iOS广告sdk遇到的问题(inmobile)
- 小试1解析
- 使用Alamofire进行XML参数的POST请求
- AndroidStudio使用之路
- Picasso设计详解
- android 控件 fragment baseFragment
- json按时间排序
