存储过程

--用Scott用户
conn scott/tiger
--根据输入的员工编号统计该部门的员工人数
--oracle的存储过程
--创建或者修改存储过名字可以汉字，定义变量，exception异常处理no_date_found查无数据
create or replace procedure pro_count_dept(depno number)
as 
    n_count number;
begin
    select count(*) into n_count from emp where deptno=depno;
    dbms_output.put_line('部门人数为：'||n_count);
exception
    when no_data_found then
    dbms_output.put_line('查无数据');
end;
--调用存储过程
1.
set severoutput on;
exec  pro_count_dept(10);
2.
begin
    pro_count_dept(30);
end;

exec  pro_count_dept(10);
exec  pro_count_dept(20);
exec  pro_count_dept(30);
exec  pro_count_dept(50);
exec  pro_count_dept(999);
exec  pro_count_dept('name');
exec  pro_count_dept(&n);

--------------------------------------------------------------
---------------------------------------------------------------
8.4--8.8：page186页：存储过程
create or replace procedure 姓名薪水职位(sfindno emp.empNo%type )
as 
    vname emp.ename%type;
    vjob emp.job%type;
    vsal emp.sal%type;
begin
    select ename,job,sal into vname,vjob,vsal from emp where empno=sfindno;
    dbms_output.put_line('工号'||sfindno||'名字'||    vname||'职位'||vjob||'薪水:'||vsal);
exception
    when no_data_found then
    dbms_output.put_line('查无数据');
    when too_many_rows then
    dbms_output.put_line('数据太多');
    when others then
    dbms_output.put_line('错误');
end;
--调用
exec 姓名薪水职位('7499');

-----------------------------
create or replace procedure 入职日期(n_empno emp.empno%type,d_hiredate out emp.hiredate%type )
as 
begin
    select hiredate into d_hiredate from emp where empno=n_empno;
    dbms_output.put_line('入职日期'||d_hiredate );
exception
    when no_data_found then
    dbms_output.put_line('查无数据');
    when too_many_rows then
    dbms_output.put_line('数据太多');
    when others then
    dbms_output.put_line('错误');
end;
--调用
var d_hiredate varchar2(20);
exec 入职日期('7900',:d_hiredate);
-----------------------------
create or replace procedure 交换数据(n1 in out number,n2 in out number )
as 
    temp number;
begin
    temp:=n1;
    n1:=n2;
    n2:=temp;
end;
--调用
declare 
num1 number:=3;
num2 number:=4;
begin
    交换数据(num1,num2);
    dbms_output.put_line('num1的值是'||num1);
    dbms_output.put_line('num2的值是'||num2);
end;


--------------------------------------------------------------
---------------------------------------------------------------
--oracle函数
select max(sal) from emp;
select sysdate from dual;
select length('123456') from dual;
--截取字段第4位截取3个
select substr('abcsdfg',4,3)from dual;
--空值变成100
select ename,nvl(comm,100)from emp;
--显示字符的ascii码
select ascii('1') from dual;
--创建一个函数，用雇员编号查询雇员薪水
create or replace function getsal(sno number)
    return number
is
    vsal number;
begin
    select sal into vsal from emp where empno=sno;
    return vsal;
    exception
    when no_data_found then
    dbms_output.put_line('数据太多');
    when others then
    dbms_output.put_line('错误');
end;
--调用函数
1.
set severout on;
declare
    sal number;
begin
    sal:=getsal(7902);
    dbms_output.put_line(sal);
end;
2.通过匿名调用getsal函数
select getsal(7499) from dual;



select getsal(7902) from dual;
select getsal(7369) from emp;





--------------------------------------------------------------
---------------------------------------------------------------
--oracle触发器
conn scott/tiger;

create table error_log
(
timestamp date,
username varchar2(30),
instance number,
database_name varchar2(50),
error_stack varchar2(2000)
);
--授予权限
conn system/123456;
grant administer database trigger to scott;
conn scott/tiger;
--建立数据库级触发器(管理员不能创建触发器)
create or replace trigger logerrors
after servererror on database
begin
insert into error_log values
(sysdate,sys.login_user,sys.instance_num,sys.database_name,dbms_utility.format_error_stack);
end logerrors;
--测试触发器
conn hr/hre

--------------------------
8.10-8.11p188页
col object_name for a30
col object_type for a30
select object_name,object_type from user_objects where object_type='PROCEDURE';

col line for 999
col text for a50
select line,text from user_source where name='交换数据'