创建存储过程

【1.创建存储过程】
create or replace procedure proc_insert_stu
is
begin
insert into student values(seq_stu.nextval,'姓名','性别',年龄,'班级');
exception
when dup_val_on_index then
dbms_output.put_line("发生主键重复异常");
when others then
dbms_output.put_line('发生其他异常');
end proc_insert_stu;
【2.执行存储过程】
exec proc_insert_stu;
或者
begin
proc_insert_stu;
end;
[3.查看存储过程]
select * from user_source;
[4.练习：存储过程嵌套：查询比最晚入职的员工工资还低的员工信息]
create ot replace procadrue
proc_child_proc
is
theSal number;
procdure get_sal
is
begin
select sql into theSal from emp where hiredate=(select max(hiredate) from emp);
end;
begin
get_sal;
for theRow in (select * from emp where sal<theSal)
loop
dbms_output.put_line('编号：'||theRow.empno||',姓名：'||theRow.ename);
end loop;
end proc_child_proc;
[5.使用in out输出拼接电话号码]
proc_inout(phone in out varchar2)
is
begin
phone:='0731'||phone;
end proc_inout;
declare
my_num varchar2(20);
begin
my_num:='&tt';
proc_output.put_line('转换后的电话号码：'||my_num);
end;
[6.使用out输出emp中员工数量]
create or replace procedure
proc_out_demo(totalEmp out number)
is
begin
select count(*) into totalEmp from emp;
end proc_out_demo;
--执行：
declare
v_out number;
begin
proc_out_demo(v_out);
dbms_output.put_line('总人数：'||v_out);
end;
[7.通过名字来加工资，如果名字相同，需要返回编号，再者选择]
create or replace procedrue
proc_add_sal(theName varchar2,theAddSal number default 100)
is
theRow emp%rowtype;
begin
select * into theRow from emp where ename=theName;
update emp set sal=sal+theAddSal where ename=theName;
if(theRow.sal<theRow.sal+theAddSal) then
dbms_output.put_line('工资上涨了');
elsif(theRow.sal>theRow.sal+theAddSal) then
dbms_output.put_line('工资下降了');
else
dbms_output.put_line('工资保持不变');
end if;
commit;
exception
when too_many_rows then
dbms_output.put_line('有同名员工');
when no_data_found then
dbms_output.put_line('没有该员工');
when others then
dbms_output.put_line('其他异常');
rollback;
end proc_add_sal;