java中sql去除游标_游标sql语句

最新推荐文章于 2023-06-20 13:10:04 发布

原创最新推荐文章于 2023-06-20 13:10:04 发布 · 201 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java中sql去除游标

本文介绍了一个使用PL/SQL进行数据库操作的例子，包括定义游标、使用游标遍历数据以及通过存储过程插入和删除记录。示例展示了如何打开游标、获取数据并关闭游标，同时提供了向EMP表添加和删除记录的过程。

declare

cursor emp_cursor (pno in number default 7369)

is select * from emp where empno=pno;

emp_row emp%rowtype;

begin

open emp_cursor(7934);

fetch emp_cursor into emp_row;

dbms_output.put_line(emp_row.ename);

close emp_cursor;

end;

declare

cursor emp_cursor (pno in number default 7369)

is select * from emp where empno=pno;

begin

for emp_row in emp_cursor(7934) loop

dbms_output.put_line(emp_row.ename);

end loop;

end;

declare

type emp_cname is ref cursor return emp%rowtype;

ecname emp_cname;

emp_row emp%rowtype;

begin

dbms_output.put_line('开始');

open ecname for select * from emp;

loop

fetch ecname into emp_row;

exit when ecname%notfound;

dbms_output.put_line(emp_row.ename);

end loop;

close ecname;

dbms_output.put_line('结束');

end;

//向emp表中添加一条记录

create procedure insert_emp as

begin

insert into emp(empno,ename,job,mgr,sal,comm,deptno)

values('7777','redarmy','teacher','7369',9000,1000,20);

commit;

end insert_emp;

set serveroutput on;

begin

insert_emp;

end;

create or replace procedure insert_emp as

begin

insert into emp(empno,ename,job,mgr,sal,comm,deptno)

values('7777','redarmy','teacher','7369',9000,1000,20);

commit;

end insert_emp;

create or replace procedure insert_emp(

cempno in number,

cename in varchar2,

cjob in varchar2,

cmgr in number,

chiredate in date,

csal in number,

ccomm in number,

cdeptno in number

) as

begin

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

values(cempno,cename,cjob,cmgr,chiredate,csal,ccomm,cdeptno);

end insert_emp;

set serveroutput on;

begin

insert_emp(cempno=>7377,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);

end;

set serveroutput on;

begin

insert_emp(3333,'mjjj','stu',7777,to_date('2011-01-01','YYYY-MM-dd'),5000,1000,20);

end;

set serveroutput on;

begin

insert_emp(9999,cename=>'mjjj',cjob=>'stu',cmgr=>7777,chiredate=>to_date('2011-01-01','YYYY-MM-dd'),csal=>5000,ccomm=>1000,cdeptno=>20);

end;

declare

begin

dbms_output.put_line('开始');

delete from emp where empno=7934;

dbms_output.put_line('结束');

end;