Oralce_PL

Oralce_PL_SQL

--PL/SQL中的输出
--打印hello world
begin
dbms_output.put_line('Hello world');
end;
--PL/SQL中的变量
--声明temp变量数字类型 1位
--声明count变量整数类型初值0
--声明sal变量数字类型共7位，小数点后2位初值4000.00
--声明date变量日期类型初始为当前系统时间
--声明pi变量常量数字类型共3位，小数点后2位初值3.14
--声明valid变量布尔类型初始false
--声明name变量可变字符串 20个字符不为空初值 MyName
declare
v_temp number(1);
v_count binary_integer :=0;
v_sal number(7,2):=4000.00;
v_date date :=sysdate;
v_pi constant number(3,2):=3.14;
v_valid boolean :=false;
v_name varchar2(20) not null :='My Name';
begin
v_temp:=8;
dbms_output.put_line(v_count||' '||v_sal||' '||v_temp);
end;

--声明empno变量和 emp中empno的类型一样

declare
v_empno emp.empno%type;
begin
v_empno:=7890;
dbms_output.PUT_LINE(v_empno);
end;

--定义type_table_empno类型 table类型，存储emp表中empno的数组
declare
--class Person[] is array of Person index by int
type type_table_empno is table of emp.empno%type index by binary_integer;
--Person[] persons
--persons Person[]
empnoArray type_table_empno;
begin
--persons[0];
empnoArray(0):=1234;
empnoArray(1):=2345;
empnoArray(-1):=3456;
dbms_output.put_line(empnoArray(0));
end;

--定义type_record_dept类型 record类型，存储dept表的每一行记录
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_dept type_record_dept;
begin
v_dept.deptno:=50;
v_dept.dname:='DEVELOPER';
v_dept.loc:='BEI JING';
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
end;

declare
v_dept dept%rowtype;
begin
v_dept.deptno:=50;
v_dept.dname:='DEVELOPER';
v_dept.loc:='BEI JING';
dbms_output.put_line(v_dept.deptno||' '||v_dept.dname||' '||v_dept.loc);
end;

--PL/SQL中的赋值语句
--简单变量的赋值
--组合变量的赋值

--PL/SQL中的select语句
--将雇员号为7369的雇员名和薪水查询出来，赋值给变量v_ename和v_sal并打印出来
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=7369;
dbms_output.put_line(v_ename||' '||v_sal);
end;
--PL/SQL中的DML语句
--向dept表中插入一条数据
begin
insert into dept values (50,'DEPT1','SHANG HAI');
commit;
end;
--更新emp表将deptno为10的雇员薪水翻倍
begin
update emp2 set sal=sal*2 where deptno=10;
dbms_output.put_line(sql%rowcount);
end;
--PL/SQL中的DDL语句
--创建表BB
begin
execute immediate 'create table BB (id number(8),name varchar2(20))';
end;
--PL/SQL中的条件判断
--将雇员号为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;

--PL/SQL中的循环语句
--用do while输出1到10
declare
v_i binary_integer :=1;
begin
loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
exit when (v_i>10);
end loop;
end;
--用while输出1到10
declare
v_i binary_integer :=1;
begin
while (v_i<=10) loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
end loop;
end;
--用for输出1到10
declare
v_i binary_integer :=1;
begin
for v_i in reverse 1..10 loop
dbms_output.put_line(v_i);
end loop;
end;

--PL/SQL中的错误处理
--异常others， no_data_found，too_many_rows
declare
v_name emp.ename%type;
v_number number(2);
begin
select ename into v_name from emp where empno=7369;
v_number :=4/0;
exception
when too_many_rows then
    dbms_output.put_line('too many rows');
when no_data_found then
    dbms_output.put_line('no data found');
when others then
    dbms_output.put_line('error');
end;
--创建errorlog表，包含字段id，errcode，errmsg，errdate
--在deptno中删除deptno为10的记录，成功则提交，不成功则回滚，并记录到日志中
create table errorlog
(
id number(8),
errcode number(6),
errmsg varchar2(200),
errdate date default sysdate
)

create sequence seq_errorlog_id start with 1 increment by 1;

declare
v_errmsg errorlog.errmsg%type;
v_errcode errorlog.errcode%type;
begin
delete from dept2 where deptno=10;
--update dept2 set dept2.loc='' where dept2.deptno=66;
commit;
exception
when others then
rollback;
v_errmsg:=SQLERRM;
v_errcode:=SQLCODE;
insert into errorlog (id,errcode,errmsg) values (seq_errorlog_id.NEXTVAL,v_errcode,v_errmsg);
commit;
end;

--游标
--取出emp表中的第一条记录
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
if(c%found) then
dbms_output.put_line(v_emp.ename);
end if;
close c;
end;
--循环取出emp表中所有记录
--Loop循环遍历游标
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
end loop;
close c;
end;
--While循环遍历游标
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while (c%found) loop
dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
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;

--带参数的游标
--查询特定部门特定工种的雇员信息
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type )
is select * from emp where deptno=v_deptno and job=v_job;
begin
for v_emp in c(20,'MANAGER') loop
dbms_output.put_line(v_emp.ename);
end loop;
end;

--使用游标更新结果集
--薪水<2000 更新薪水翻倍
--其他删除记录
declare
cursor c is select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.sal<2000) then
update emp2 set sal =sal*2 where current of c;
else
delete from emp2 where current of c;
end if;
end loop;
end;
--存储过程
--循环变量
--加薪部门号为10的加10，部门号为20的加20，其他加50
create or replace procedure p1 as
cursor c is select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno=10) then
update emp2 set sal=sal+10 where current of c;
elsif (v_emp.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
end;

begin
p1;
end;

exec p1;
--带参数的存储过程
--输入a，b，temp 输出c和temp c是a和b的最大值。temp是原来的值+1
create or replace procedure p2(a in number,b in number,c out number,temp in out number) as
begin
if(a>b) then
c:=a;
else
c:=b;
end if;
temp:=temp+1;
end;

declare
c number;
temp number :=4;
begin
p2(2,56,c,temp);
dbms_output.put_line(c);
dbms_output.put_line(temp);
end;
--函数
--创建计算税率的方法
--规则：薪水<2000 税率0.1
--薪水<2750 税率0.2
--其他税率0.5

--触发器
--创建emp_ddl_log,包含用户名，操作名和操作时间
--创建触发器，每当用户对emp进行更新操作，都记录在emp_ddl_log中。