oracle学习记录之十五

pl/sql

存储过程

create table mytest(name varchar2(20), passwd varchar2(20));

SQL> create or replace procedure spro1 is
  2  begin
  3  insert into mytest values('xiaoming', 'm123');
  4  end;
  5  /

 exec spro1;

SQL> create or replace procedure spro2 is
  2  begin
  3  delete from mytest where name='xiaoming';
  4  end;
  5  /

set serveroutput on;    -- set serveroutput off; 打开或关闭输入选项

SQL> begin
  2  dbms_output.put_line('hello world');
  3  end;
  4  /

SQL> declare
  2  v_ename varchar2(5);
  3  begin
  4  select ename into v_ename from emp where empno=&no;
  5  dbms_output.put_line('雇员名：'||v_ename);    --在控制台输出
  6  end;

  7  /

SQL> declare
  2  v_ename varchar2(5);
  3  v_sal number(10,2);
  4  begin
  5  select ename,sal into v_ename, v_sal from emp where empno=&no;
  6  dbms_output.put_line('姓名：'||v_ename||' 薪水：'||v_sal);
  7  end;
  8  /

SQL> declare
  2  v_ename varchar2(5);
  3  v_sal number(10,2);
  4  begin
  5  select ename,sal into v_ename, v_sal from emp where empno=&no;
  6  dbms_output.put_line('姓名：'||v_ename||' 薪水：'||v_sal);
  7  exception
  8  when no_data_found then
  9  dbms_output.put_line('编号输入有误，请重输');
 10  end;
 11  /

带参数的存储过程

SQL> create or replace procedure spro4 (spname varchar2, spsal number) is
  2  begin
  3  update emp set sal=spsal where ename=spname;
  4  end;
  5  /

SQL> exec spro4('SCOTT',1234);

SQL>  create or replace function fun1(name varchar2) return number is  yearsal number(7,2);
  2  begin
  3  select sal*12 into yearsal from emp where ename=name;
  4  return yearsal;
  5  end;
  6  /

SQL> var income number
SQL> call fun1('SCOTT') into : income;

SQL> print income

create or replace package mypackage is

procedure  spro4 (spname varchar2, spsal number);

end;

 create or replace package body mypackage is
 procedure spro4(spname varchar2, spsal number)
 is 
 begin
 update emp set sal=spsal where ename=spname;
 end;
 end;

create or replace package mypackage is

procedure  spro4 (spname varchar2, spsal number);

function fun1(name varchar2) return number;

end;

create or replace package body mypackage is 

 procedure spro4(spname varchar2, spsal number)
 is 
 begin
 update emp set sal=spsal where ename=spname;
 end;

function fun1(name varchar2) return number is  yearsal number(7,2);

begin
 select sal*12 into yearsal from emp where ename=name;
return yearsal;
end;

end;