dept,employee和《oracle常用语句,带详细的说明》中的表一致!
/*作用:sql编程 说明:declare 申明变量,constant 常量,语言块要放在begin end 中 ,dbms_output.put_line 打印, exception when ZERO_DIVIDE then 当出现ZERO_DIVIDE错误,将执行 dbms_output.put_line('被零除!'); */
declarepi constant float:=3.1415; /*一个float类型的常量*/
r1 int:=12; /*变量*/
r2 int:=0;
begin
dbms_output.put_line('面积:'||(pi*r1*r1));
dbms_output.put_line('除法:'||(r1/r2));
exception when ZERO_DIVIDE then
dbms_output.put_line('被零除!');
end;
/*作用:一个简单的登录sql编程 说明:if流程判断, if then,elsif then 相当于java中的else if ,else, end if ,<> 不等于*/
declare
v_name nvarchar2(20):='christine';
v_password nvarchar2(40):='pual';
begin
if v_name <> 'christine'then
dbms_output.put_line('用户名错误!');
elsif v_password <> 'pual' then
dbms_output.put_line('密码错误!');
else
dbms_output.put_line('欢迎'||v_name||'登录!');
end if;
end;
/*作用:求1+2+3+……+10的和 说明:Loop循环,loop循环开始, exit when 退出循环的条件, end loop 结束loop循环 */
declare
var_count int:=0;
var_sum int:=0;
begin
loop
var_count:=var_count+1;
var_sum:=var_count+var_sum;
exit when var_count=10;
end loop;
dbms_output.put_line('sum:'||var_sum);
end;
/*作用:求1+2+3+……6的和 说明:while循环,while loop循环条件,end loop 结束循环 */
declare
var_count int:=0;
var_sum int:=0;
begin
while var_count<6 loop
var_count:=var_count+1;
var_sum:=var_count+var_sum;
end loop;
dbms_output.put_line('sum:'||var_sum);
end;
/*作用;求1+2+3+……6的和 说明:for循环 for in loop 在某某闭区间循环 */
declare
var_sum int:=0;
begin
for var_count in 1..6 loop
var_sum:=var_count+var_sum;
end loop;
dbms_output.put_line('sum:'||var_sum);
end;
/*作用:特殊类型变量演示 针对一列 说明:var_salary employee.salary%type var_salary的类型与employee.salary中的类型一致*/
declare
var_salary employee.salary%type;
begin
select e.salary into var_salary from employee e
where e.empId=2;
dbms_output.put_line('var_salary:'||var_salary);
end;
/*作用:特殊类型变量演示 针对一行 说明:var_employee employee%rowtype var_employee相当于一个javaBean 包含Bean中所有的属性*/
declare
var_employee employee%rowtype;
begin
select * into var_employee from employee
where empid=2;
dbms_output.put_line(var_employee.empName||':('||var_employee.empId||','||var_employee.birthday||','||var_employee.sex||')');
end;
/*作用;单一类型游标演示 说明:employSalary 申明的游标,在使用游标之前要打开游标,事后关闭,fatch into 读取游标中的值*/
declare
var_salary employee.salary%type;
var_sum int:=0;
cursor employSalary is select salary from employee;
begin
open employSalary;
loop
fetch employSalary into var_salary;
exit when employSalary%notfound;
var_sum:=var_salary+var_sum;
end loop;
close employSalary;
dbms_output.put_line('sum:'||var_sum);
end;
/*作用:行集类型游标演示 说明;for会自动打开游标,事后关闭游标*/
declare
cursor employeeCursor is select * from employee ;
begin
for emp in employeeCursor loop
dbms_output.put_line('employee:'||emp.empName||'('||emp.empId||emp.sex||')');
end loop;
end;
/*作用:带参数的游标演示*/
declare
cursor employeeSex (var_sex employee.sex%type) is select * from employee where sex=var_sex;
paraSex employee.sex%type;
begin
paraSex:='Male';
for emp in employeeSex(paraSex) loop
dbms_output.put_line('employee:'||emp.empName||'('||emp.empId||emp.sex||')');
end loop;
end;
/*作用:触发器演示 在修改和添加employee记录时会触发 说明:replace 替换之前存在的tri_emp,before insert or update of salary on employee 在修改和添加之前取得employee中的sslary,
referencing old as old_sal new as new_sal 引用新 旧两个变量,*/
create or replace trigger tri_empbefore insert or update of salary on employee
referencing old as old_sal new as new_sal
for each row when(new_sal.salary<0)
begin
dbms_output.put_line('只有new_sal.salsry小于0时:行集触发器才会被执行!');
if:old_sal.salary is null then
dbms_output.put_line('当添加的new_sal.salsr小于0时:salary将被修改变0');
:new_sal.salary:=0;
else
dbms_output.put_line('当修改的new_sal.salsr小于0时:修改不成功!');
:new_sal.salary:=old_sal.salary;
end if;
end;
/*作用;测试触发器*/
update employee set salary=-1000 where empid=1;
insert into employee(empId,empName,sex,salary) values(seqEmployee.NextVal,'a','femal',-80);
select * from employee;
/*作用:查询某一部门员工的总薪水 存储过程*/
create or replace procedure pro_employee
(did in employee.deptid%type,sum_salary out number)
as
temp_salary employee.salary%type;
cursor emp_cur is
select salary from employee e
where e.salary is not null and e.deptid=did;
begin
sum_salary:=0;
open emp_cur;
loop
fetch emp_cur into temp_salary;
exit when emp_cur%notfound;
sum_salary:=sum_salary+temp_salary;
end loop;
close emp_cur;
end;
/*作用:测试存储过程*/
declare
deptid employee.deptid%type;
sum_salary employee.salary%type;
begin
deptid:=1;
pro_employee(deptid,sum_salary);
dbms_output.put_line('sum:'||sum_salary);
end;
/*作用:函数*/
create or replace function fun_employee
(did in employee.deptid%type)
return employee.salary%type is
temp_salary employee.salary%type;
sum_salary employee.salary%type;
cursor emp_cur is
select salary from employee e
where e.salary is not null and e.deptid=did;
begin
sum_salary:=0;
open emp_cur;
loop
fetch emp_cur into temp_salary;
exit when emp_cur%notfound;
sum_salary:=sum_salary+temp_salary;
end loop;
close emp_cur;
return sum_salary;
end;
/*函数测试*/
select fun_employee(1) from dual;