储存过程，游标的使用

--创建简单 存储过程
--or replace替换以前名字
create or replace procedure Thh_sdd
is
begin
  dbms_output.put_line('procedure');
end;
--执行存储过程
begin
  Thh_sdd;
end;
--创建带参数 的存储过程
create or replace procedure my_procedure
(eno in number default 3,ename_p in out nvarchar2 ) --default默认是3的值
is
begin
 select  ename into ename_p from emp where empno=7369; 
end;
--调试 my_procedure
declare 
ename nvarchar2(10);
eno number(1);
begin
  eno:=1;
  my_procedure(ename_p=>ename); -- 指定 形参 实参
  dbms_output.put_line(ename);
end;
--练习 动态 取值
declare  
empinfo emp%rowtype;
begin
  select * into empinfo  from emp where empno=7369;--sql语句 into 赋值
  dbms_output.put_line(empinfo.ename);
end;
--游标 使用
declare 
--定义游标变量,同时绑定查询语句
cursor cs is select * from emp;
empinfo emp%rowtype;
begin
  --打开游标
  open cs;
  loop
    --把抓取到的值放人到into的变量中去
    fetch  cs into empinfo;
    exit when cs%notfound;
    dbms_output.put_line(empinfo.ename);
  end loop;
  --关闭游标
  close cs;
end;
--游标赋值 变量
declare  
emp_name emp.ename%type;
cursor my_cursor is select ename from emp;
begin
  open my_cursor;
  loop
    fetch my_cursor into emp_name;--提取变量值
    exit when  my_cursor%notfound;
    dbms_output.put_line(emp_name);     
  end loop;
  close my_cursor;
end;
--带参数的游标 使用
declare  
emp_name emp.ename%type;
cursor my_cursor(dno number:=30) is select ename from emp where deptno=dno;--dno 参数
begin
  open my_cursor(&no); --接收 参数
  loop
    fetch my_cursor into emp_name;--提取变量值
    exit when  my_cursor%notfound;
    dbms_output.put_line(emp_name);     
  end loop;
  close my_cursor;
end;
--循环游标
begin
  --自动打开,自动抓取,自动判断终止,自动关闭
  for empinfo in (select * from emp where deptno=&no) loop
    dbms_output.put_line(empinfo.ename);
  end loop;
end;
--ref游标(弱类型动态游标)
declare 
type my_cursor is ref cursor; --创建 类型 type
cs  my_cursor;
mep_info emp%rowtype;
begin
  --动态 输入sql 语句
  open cs for select * from emp;
  loop
   fetch cs into mep_info;
   exit when cs%notfound;
   dbms_output.put_line(mep_info.ename); 
  end loop;
  close cs;
end;
--ref游标(强类型动态游标)
declare
type my_cursor is ref cursor return emp%rowtype; --有返回值类型 强制限定类型必须一样 
mep_info emp%rowtype;
cs my_cursor;
begin
  open cs for select * from emp;
  loop
   fetch cs into mep_info;
   exit when cs%notfound;
   dbms_output.put_line(mep_info.ename); 
  end loop;
  close cs;
end;


 --总结
 --cursor  游标的使用 
 --create procedure  创建存储过程
 --fetch cursor into table ;循环取值
 --loop  end loop;   exit when %notfound --判断还有数据没有
 --type cursor 创建类型游标 
 --如果不是循环游标就要手动打开,关闭游标
 --游标的强弱就是在 后面加不加 返回值类型
create or replace procedure my_dept (pno number ,epno out nvarchar2,saltu out nvarchar2 )
is
  empinfo emp%rowtype; 
begin
       epno:=select depno from emp where empno=pno;
     -- dbms_output.put_line('55'); 

end;

create or replace function addSal(sal number, crement number)
return number
as
begin
  return sal+crement;
end;


select addSal(sal, nvl(comm, 0)) from emp;


declare
  "sum" number(5);
begin
  dbms_output.put_line(addSal(1, 2));
  "sum" := addSal(5, 5);
  dbms_output.put_line('sum-------->' || "sum");
end;


--函数的权限控制
create user zs identified by aaa;
grant connect, resource to zs;
grant execute on scott.addsal to zs;


select scott.addSal(3, 6) from dual;


SELECT * from all_source where name='ADDSAL'


--自主事务
create or replace procedure testpd
is
   pragma autonomous_transaction;     --自主事务一定要显示结束
begin
   insert into emp(empno) values(8888);
   --rollback;
   update emp set ename='Tom' where empno=8000;
   commit;
end;
--测试结果
begin
  insert into emp(empno) values(8000);
  testpd;
  --只插人了数据没有更改名字
  rollback;
  dbms_output.put_line('执行完毕');
end;


select * from emp;


--简单触发器
create or replace trigger insertemp
after insert
on emp
--取别名字   before after 时间段区别 执行前触发 ,执行后触发
referencing new as inserted
for each row--循环每一行数据
begin
  dbms_output.put_line('新增了一条员工记录, 新员工编号为:' || :inserted.empno);
end;


insert into emp(empno, ename) values(5752, 'Jack');


--老板的工资不能少
create trigger checkSal
before update of sal
on emp
for each row
--在when中使用old new 就不用:
when (old.mgr is null)
begin
  if :new.sal<:old.sal then
    :new.sal := :old.sal;
  end if;
end;


update emp set sal=2900 where ename='JONES';


--自动利用序列填充主键
--创建序列器
create sequence empsq start with 9000;


create or replace trigger generationPK
before insert
on emp
for each row
begin
  --触发器不能直接使用DML语句,避免无限递归
  --insert into emp(empno) values(empsq.nexval);
  select empsq.nextval into :new.empno from dual;
end;


--测试 插人主键值
insert into emp(ename) values('Marry');


  declare
      result1 clob;
      xmlstr varchar(32767);
      strline varchar(2000);
      line_no number := 1;
  begin
      result1 := dbms_xmlquery.getXML('select * from emp');
      xmlstr := substr(result1,1,32767);
      loop
          exit when xmlstr is null;
          strline := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
          dbms_output.put_line(line_no||':'||strline);
          xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
          line_no := line_no + 1;
      end loop;
  end;