--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;