oracle存储过程、函数、PL/SQL块、动态游标

-- Create table
create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);

-- Create table
create table TB_ORACLE
(
  USERID   VARCHAR2(10),
  USERNAME VARCHAR2(10),
  USERPASS VARCHAR2(10)
);

 

------pl/sql块----------
------%type-------------
declare
emp_no emp.empno%type:=7369;--类型为emp表的empno字段的类型，并赋值为7369
emp_name varchar2(10);
emp_sal emp.sal%type;
begin
   select ename, sal into emp_name, emp_sal from emp where empno = emp_no;
   dbms_output.put_line(emp_name||'--'||emp_sal);
end;

------%rowtype-------------
declare
emp_no emp.empno%type:=7369;
emp_tail emp%rowtype;--emp_tail为emp表中的一行记录
begin
   select * into emp_tail from emp where empno = emp_no;
   dbms_output.put_line(emp_tail.ename||'--'||emp_tail.sal);
end;

----procedure---------------------
----增删查改------------------------------
--增加---
create or replace procedure test_add(uid varchar2, uname varchar2, upass varchar2)
is
begin
  insert into tb_oracle values(uid, uname, upass);
  commit;
end;

--执行存储过程test_add
begin
  test_add('1','scott','tiger','90');
end;

--删除---------------
create or replace procedure test_delete(uid varchar2)
is
num number;
inva exception;
begin
  select count(*) into num from tb_oracle where userid = uid;
  if num > 0 then
     delete tb_oracle where userid = uid;
     commit;
  else
     raise inva;
  end if;
  exception
  when inva then
    dbms_output.put_line('要删除的记录不存在');
end;

--执行存储过程test_delete
begin
  test_delete('2');
end;

--查找---------------
create or replace procedure test_findbyid(uid in varchar2, uname out varchar2, upass out varchar2)
is
type type_cursor is ref cursor;
c_test type_cursor;
sqlStr varchar2(200);
num number;
inva exception;
begin
  sqlStr:='select username,userpass from tb_oracle where userid <='||' '||uid||' ';
  --dbms_output.put_line(sqlStr);
  select count(*) into num from tb_oracle where userid <= uid;
  if num > 0 then
     open c_test for sqlStr;
     loop
       fetch c_test into uname,upass;
       exit when c_test%notfound;
       dbms_output.put_line(uname||'----'||upass);
     end loop;
     close c_test;
  else
     raise inva;
  end if;
  exception
  when inva then
    dbms_output.put_line('查询记录不存在');
end;

--执行存储过程test_findbyid
declare
uname varchar2(10);
upass varchar2(10);
begin
  test_findbyid('2',uname,upass);
end;

--修改
--要修改的id存在
--修改后的出版社id存在
create or replace procedure proc_book_update(bid number,bname varchar2,
bauthor varchar2,bprice float,bdate date,bnum number,pid number
)
is
num number;
cou number;
inva exception;
exe exception;
begin
select count(*) into num from tb_book where book_id=bid;
select count(*) into cou from tb_publish where publish_id=pid;
if num>0 then --表示修改的id存在
   if cou>0 then --表示修改后的出版社id存在
      update tb_book set book_name=bname,book_author=bauthor,book_price=bprice,book_date=bdate,
      book_num=bnum,publish_id=pid where book_id=bid;
      commit;
   else
      raise exe;
   end if;
else
  raise inva;
end if;
exception
when inva then
  dbms_output.put_line('修改的记录不存在!');
when exe then
  dbms_output.put_line('该出版社不存在！');
end;

--执行存储过程proc_book_update
begin
proc_book_update(2,'Thinking in java','李四',180,'15-5月-87',100,55);
end;

--存储过程、游标
create or replace procedure test_xg(uid in varchar2,x out varchar2)
is
cursor c_cursor is select * from tb_oracle where userid <= uid;
begin
  for cursor_result in c_cursor loop
   begin
    --dbms_output.put_line(cursor_result.username||''||cursor_result.userpass);
    x:=cursor_result.username;
    dbms_output.put_line(x);
   end;
  end loop;
end;

--执行
declare
x varchar2(100);
begin
  test_xg('2',x);
end;

--函数-----------
create or replace function fun_ora(uid varchar2)
return varchar2
is
uname varchar2(10);
begin
  select username into uname from tb_oracle where userid=uid;
  return uname;
end;

--执行函数fun_ora
select fun_ora('2') from dual;

select * from tb_oracle

create or replace function HelloWorld
return varchar2
is
begin
  return '你好';
end;

--执行函数HelloWorld
select HelloWorld from dual;