存储过程与函数
存储过程的优势
存储过程
/*
多行注释
*/
-- 单行注释
//展示错误信息
show errors
//创建用户test 密码abc 默认用户表空间 表空间缓存 100m大小
create user test
identified by abc
default tablespace users
temporary tablespace temp
quota 100m on users
/
grant connect,resource to test;
conn test/abc
create table tbl_employee
(
emp_no int primary key,
emp_name varchar2(30) not null,
emp_age int not null
);
create sequence seq_emp_no;
create procedure sp_new_emp is
begin
insert into tbl_employee values(seq_emp_no.nextval,'mary',23);
end;
/
create or replace procedure sp_new_emp is
begin
insert into tbl_employee values(seq_emp_no.nextval,'mary',23);
end;
/
exec sp_new_emp;
打印语句
//开启打印语句
set serveroutput on
create or replace procedure sp_add_emp(emp_name in varchar,emp_age in int) is
begin
insert into tbl_employee values(seq_emp_no.nextval,emp_name,emp_age);
dbms_output.put_line('员工'||emp_name||'信息构建完毕!');
end sp_add_emp;
exec sp_add_emp('emp',23);
选择语句
create or replace procedure sp_add_emp(emp_name in varchar,emp_age in int) is
begin
if emp_age>=18 and emp_age<=60 then
insert into tbl_employee values(seq_emp_no.nextval,'mary',23);
dbms_output.put_line('员工'||emp_name||'信息构建完毕!');
else
dbms_output.put_line('员工'||emp_name||'不符合录取条件,被拒绝了!');
end if;
end sp_add_emp;
exec sp_add_emp('emp',23);
函数
create or replace function getScopedSum(begin_scope in number, end_scope in number) return number is
v_sum number := 0;
begin
for v_i in begin_scope .. end_scope loop
v_sum := v_sum + v_i;
end loop;
return(v_sum);
end getScopedSum;
select getscopedsum(1,100) from dual;
计算使用资源
set autotrace on
set timing on
select count(*) from tbl_employee;
desc tbl_employee;
select count(emp_no) from tbl_employee;