PLSQL中如何强制关闭执行的存储过程
展开全部
试试用系统管理员登录Oracle Enterprise Manager Console,干掉存储过程的session看可以不
本回答被提问者和网友采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
2010-12-30
展开全部
试试用系统管理员登录Oracle Enterprise Manager Console,干掉存储过程的session看可以不
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
唉,又是大学生吧,自己不动脑筋.发个例子你看看.
declare
v_num number := &input;
v_last_num number := 0;
v_result varchar(2000) := '';
begin
loop
v_last_num := mod(v_num,10);
v_result := v_result || to_char(v_last_num);
v_num := v_num - v_last_num;
exit when v_num=0;
v_num := v_num / 10;
end loop;
dbms_output.put_line(v_result);
end;
第六章 游标(cursor)管理
create SYNONYM emp for scott.emp;
--作用:
declare
v_ename emp.ename%type;
begin
select ename into v_ename
from emp;
dbms_output.put_line(v_ename);
end;
--显式游标的定义和遍历
--通过无条件的循环来遍历游标
declare
--1.定义游标
cursor c_emp_cursor
is select ename from emp;
v_ename emp.ename%type;
begin
--2.打开游标
open c_emp_cursor;
--3.遍历游标
loop
fetch c_emp_cursor into v_ename; --rs.next();
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
end loop;
--4.关闭游标
close c_emp_cursor;
end;
--用WHILE循环来遍历游标
declare
cursor c_emp_cursor
is select * from emp;
v_row emp%rowtype;
begin
open c_emp_cursor;
fetch c_emp_cursor into v_row;
while c_emp_cursor%FOUND loop
dbms_output.put_line(v_row.ename || ' ' || v_row.empno);
fetch c_emp_cursor into v_row;
end loop;
close c_emp_cursor;
end;
--FOR循环遍历游标的方式
declare
cursor c_emp_cursor
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno || ' ' || v_row.ename);
end loop;
end;
--定义参数类型的游标
declare
cursor c_emp_cursor(p_deptno emp.deptno%type)
is select ename from emp where deptno=p_deptno;
v_ename emp.ename%type;
begin
open c_emp_cursor(&input);
loop
fetch c_emp_cursor into v_ename;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
end loop;
close c_emp_cursor;
end;
--为游标指定返回类型1-rowtype
declare
cursor c_emp_cursor
return emp%rowtype --强制类型
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno);
end loop;
end;
--为游标指定返回类型2-记录(Record)类型
declare
--定义一个记录类型
type t_emp_record is record
(
empno emp.empno%type,
ename emp.ename%type
);
v_emp_record t_emp_record;
cursor c_emp_cursor
return v_emp_record --强制类型
is select empno,ename from emp;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_emp_record;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_emp_record.empno);
dbms_output.put_line(v_emp_record.ename);
end loop;
close c_emp_cursor;
end;
--如何通过游标来修改数据
declare
--通过游标修改数据要上行级锁
cursor c_emp_cursor
is select * from emp where deptno=20 for update;
begin
for v_row In c_emp_cursor loop
delete from emp
where current of c_emp_cursor;
end loop;
commit;
end;
select * from emp;
--REF引用类型的游标描述
declare
type t_ref_cursor is ref cursor;
v_ref_cursor t_ref_cursor;
begin
open v_ref_cursor
for select * from emp;
close v_ref_cursor;
open v_ref_cursor
for select * from dept;
close v_ref_cursor;
end;
--1.隐式游标(SQL)的使用
--用途:因为数据库系统在做insert,delete,update,select这些操作的时候
--都是通过游标去完成的,所以,在做操作之前我们的数据库系统都会隐式
--的打开一个游标,在执行操作完毕之后隐式的关闭游标,那么每次的操作的结果
--都会保存在一个叫做SQL的隐式游标中,我们可以通过操作这个隐式游标来获取
--数据库操作的信息
begin
delete from emp where deptno=10;
dbms_output.put_line(SQL%ROWCOUNT);
end;
第七章 存储过程(Procedure)和包(Package)
SQLServer - 存储过程 (output参数 return)
Oracle -存储过程(out参数) 函数(return)
--1.存储过程
create or replace procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;
--1.1 调用存储过程
execute print('helloworld');
--1.2
create or replace procedure findEmp(p_empno emp.empno%type)
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp where empno=p_empno;
print(v_ename);
exception
when no_data_found then
print('未发现指定员工.');
end;
--1.3 传递参数
create or replace procedure myabs(p_num1 IN number,p_num2 OUT number)
is
begin
if p_num1 >0 then
p_num2 := p_num1;
else
p_num2 := (0 - p_num1);
end if;
end;
--调用
declare
v_result number;
begin
myabs(-987,v_result);
print(v_result);
end;
create or replace procedure change_num(p_num1 IN OUT number,p_num2 IN OUT number)
is
v_temp number;
begin
v_temp := p_num1;
p_num1 := p_num2;
p_num2 := v_temp;
end;
--调用
declare
v_num1 number :=111;
v_num2 number :=222;
begin
change_num(v_num1,v_num2);
print(v_num1);
print(v_num2);
end;
--2.函数(FUNCTION)
create or replace function myabs1(p_num1 number)
return number
is
begin
if p_num1 >= 0 then
return p_num1;
else
return (0 - p_num1);
end if;
end;
begin
print(myabs1(-999));
end;
create or replace function findEmp1(p_empno emp.empno%type)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;
return v_ename;
exception
when no_data_found then
print('未发现员工');
end;
begin
print(findEmp1(7499));
end;
--3.包(Package)-包(package-定义)和包体(package body-实现)
create or replace package mypkg
is
procedure print(msg varchar2);
function findEmp(p_empno number)
return emp.ename%type;
end;
create or replace package body mypkg
is
procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;
function findEmp(p_empno number)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;
return v_ename;
exception
when no_data_found then
print('未发现员工');
end;
end;
--4.通过函数返回结果集
create or replace function findAllEmps
return SYS_REFCURSOR
is
v_ref_cursor SYS_REFCURSOR;
begin
open v_ref_cursor
for 'select * from emp';
return v_ref_cursor;
end;
我上学时候练习用的
declare
v_num number := &input;
v_last_num number := 0;
v_result varchar(2000) := '';
begin
loop
v_last_num := mod(v_num,10);
v_result := v_result || to_char(v_last_num);
v_num := v_num - v_last_num;
exit when v_num=0;
v_num := v_num / 10;
end loop;
dbms_output.put_line(v_result);
end;
第六章 游标(cursor)管理
create SYNONYM emp for scott.emp;
--作用:
declare
v_ename emp.ename%type;
begin
select ename into v_ename
from emp;
dbms_output.put_line(v_ename);
end;
--显式游标的定义和遍历
--通过无条件的循环来遍历游标
declare
--1.定义游标
cursor c_emp_cursor
is select ename from emp;
v_ename emp.ename%type;
begin
--2.打开游标
open c_emp_cursor;
--3.遍历游标
loop
fetch c_emp_cursor into v_ename; --rs.next();
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
end loop;
--4.关闭游标
close c_emp_cursor;
end;
--用WHILE循环来遍历游标
declare
cursor c_emp_cursor
is select * from emp;
v_row emp%rowtype;
begin
open c_emp_cursor;
fetch c_emp_cursor into v_row;
while c_emp_cursor%FOUND loop
dbms_output.put_line(v_row.ename || ' ' || v_row.empno);
fetch c_emp_cursor into v_row;
end loop;
close c_emp_cursor;
end;
--FOR循环遍历游标的方式
declare
cursor c_emp_cursor
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno || ' ' || v_row.ename);
end loop;
end;
--定义参数类型的游标
declare
cursor c_emp_cursor(p_deptno emp.deptno%type)
is select ename from emp where deptno=p_deptno;
v_ename emp.ename%type;
begin
open c_emp_cursor(&input);
loop
fetch c_emp_cursor into v_ename;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
end loop;
close c_emp_cursor;
end;
--为游标指定返回类型1-rowtype
declare
cursor c_emp_cursor
return emp%rowtype --强制类型
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno);
end loop;
end;
--为游标指定返回类型2-记录(Record)类型
declare
--定义一个记录类型
type t_emp_record is record
(
empno emp.empno%type,
ename emp.ename%type
);
v_emp_record t_emp_record;
cursor c_emp_cursor
return v_emp_record --强制类型
is select empno,ename from emp;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_emp_record;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_emp_record.empno);
dbms_output.put_line(v_emp_record.ename);
end loop;
close c_emp_cursor;
end;
--如何通过游标来修改数据
declare
--通过游标修改数据要上行级锁
cursor c_emp_cursor
is select * from emp where deptno=20 for update;
begin
for v_row In c_emp_cursor loop
delete from emp
where current of c_emp_cursor;
end loop;
commit;
end;
select * from emp;
--REF引用类型的游标描述
declare
type t_ref_cursor is ref cursor;
v_ref_cursor t_ref_cursor;
begin
open v_ref_cursor
for select * from emp;
close v_ref_cursor;
open v_ref_cursor
for select * from dept;
close v_ref_cursor;
end;
--1.隐式游标(SQL)的使用
--用途:因为数据库系统在做insert,delete,update,select这些操作的时候
--都是通过游标去完成的,所以,在做操作之前我们的数据库系统都会隐式
--的打开一个游标,在执行操作完毕之后隐式的关闭游标,那么每次的操作的结果
--都会保存在一个叫做SQL的隐式游标中,我们可以通过操作这个隐式游标来获取
--数据库操作的信息
begin
delete from emp where deptno=10;
dbms_output.put_line(SQL%ROWCOUNT);
end;
第七章 存储过程(Procedure)和包(Package)
SQLServer - 存储过程 (output参数 return)
Oracle -存储过程(out参数) 函数(return)
--1.存储过程
create or replace procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;
--1.1 调用存储过程
execute print('helloworld');
--1.2
create or replace procedure findEmp(p_empno emp.empno%type)
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp where empno=p_empno;
print(v_ename);
exception
when no_data_found then
print('未发现指定员工.');
end;
--1.3 传递参数
create or replace procedure myabs(p_num1 IN number,p_num2 OUT number)
is
begin
if p_num1 >0 then
p_num2 := p_num1;
else
p_num2 := (0 - p_num1);
end if;
end;
--调用
declare
v_result number;
begin
myabs(-987,v_result);
print(v_result);
end;
create or replace procedure change_num(p_num1 IN OUT number,p_num2 IN OUT number)
is
v_temp number;
begin
v_temp := p_num1;
p_num1 := p_num2;
p_num2 := v_temp;
end;
--调用
declare
v_num1 number :=111;
v_num2 number :=222;
begin
change_num(v_num1,v_num2);
print(v_num1);
print(v_num2);
end;
--2.函数(FUNCTION)
create or replace function myabs1(p_num1 number)
return number
is
begin
if p_num1 >= 0 then
return p_num1;
else
return (0 - p_num1);
end if;
end;
begin
print(myabs1(-999));
end;
create or replace function findEmp1(p_empno emp.empno%type)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;
return v_ename;
exception
when no_data_found then
print('未发现员工');
end;
begin
print(findEmp1(7499));
end;
--3.包(Package)-包(package-定义)和包体(package body-实现)
create or replace package mypkg
is
procedure print(msg varchar2);
function findEmp(p_empno number)
return emp.ename%type;
end;
create or replace package body mypkg
is
procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;
function findEmp(p_empno number)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;
return v_ename;
exception
when no_data_found then
print('未发现员工');
end;
end;
--4.通过函数返回结果集
create or replace function findAllEmps
return SYS_REFCURSOR
is
v_ref_cursor SYS_REFCURSOR;
begin
open v_ref_cursor
for 'select * from emp';
return v_ref_cursor;
end;
我上学时候练习用的
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询