游标sql语句
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; /