基础语法2 总结
--四章节1.什么是游标2.游标的分类3.游标的四个属性4.显示游标的操作步骤5.如何定义和使用显示游标修改数据1.过程2.函数3.程序包 --最简单的存储过程create or replace procedure p1isbegindbms_output.put_line('你好,.....');end;--调用exec[ute] p1;--IN 参数create or replace procedure p2(eno number)as v_name emp.ename%type;beginselect ename into v_name from emp where empno=eno;dbms_output.put_line(v_name);end;/--执行execute p2(7788); --按位置传参execute p2(eno=>7788); --按名称传参call p2(7788);--pl/sql调用beginp2(&no);end;--OUT 参数create or replace procedure p3(eno IN number,name OUT varchar2)asbeginselect ename into name from emp where empno=eno;end;/--调用declare v_name varchar2(20);begin --p3(name=>v_name,eno=>7788); p3(7788,name=>v_name); dbms_output.put_line(v_name);end;--IN OUT 参数create or replace procedure p4(num1 IN OUT number,num2 IN OUT number)is num3 number;beginnum3 := num2; num2 := num1;num1 := num3;end;/--调用declare v_1 number:=10; v_2 number:=20;beginp4(v_1,v_2);dbms_output.put_line(v_1||' '||v_2);end;--授予执行权限 grant execute on p1 to aniu;--简单的函数create or replace function fun1return varchar2isbeginreturn '你好,朋友....';end;--调用select fun1 from dual;--pl/sql调用declare v_n varchar(20);begindbms_output.put_line(fun1);v_n := fun1;dbms_output.put_line(v_n);end;--调用VAR v varchar2(20) --声明全局变量exec :v:= fun1;print :v;--带参数的函数create or replace function fun2(eno number)return varchar2is v_name varchar2(20);beginselect ename into v_name from emp where empno = eno;return v_name;end;--调用declare v_name varchar2(20);beginv_name:= fun2(&no);dbms_output.put_Line(v_name);end;--授权grant execute on fun1 to scott;--程序包规范create or replace package pack1isCURSOR cur_fenye(page number,line number) return emp%rowtype;--声明游标function fun2(eno number) return varchar2; --声明函数procedure p1(eno number); --声明过程procedure proc_fenye(page number,line number);--分页的存储过程end pack1;--程序包主题create or replace package body pack1is --实现游标 CURSOR cur_fenye(page number,line number) return emp%rowtype IS select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from( select rownum r,e.* from emp e where rownum<=page*line ) a where a.r>(page-1)*line; --实现函数function fun2(eno number)return varchar2is v_name varchar2(20);beginselect ename into v_name from emp where empno = eno;return v_name;end;--实现过程procedure p1(eno number)is v_name varchar2(20);begin select ename into v_name from emp where empno=eno; dbms_output.put_Line(v_name);end;--实现分页的存储过程procedure proc_fenye(page number,line number)is v_emp emp%rowtype;begin open cur_fenye(page,line);--打开 loop fetch cur_fenye into v_emp; exit when cur_fenye%notfound; dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' ' ||v_emp.sal); end loop; close cur_fenye;end;end pack1;--调用select pack1.fun2(7788) from dual;exec pack1.p1(7788);--调用分页exec pack1.proc_fenye(4,4);--格式化列的现实COLUMN LINE FORMAT 999COLUMN TEXT FORMAT A70SELECT line,text FROM USER_SOURCE WHERE NAME=UPPER('P2');