Oracle PL/SQL习题使用

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;
我上学时候练习用的