oracle表相关

create or replace function show(m number) return number
is
begin
return m*10;
end;


select show(5) from dual

create or replace package my is
function show(m number) return number;
end;

create or replace package body my is
create or replace function show(m number) return number
is
begin
return m*10;
end;
end;
select * from scott.emp

declare
name scott.emp.ename%type;
sal scott.emp.sal%type;
tax_rate constant number(3,2):=0.03;
tax_sal scott.emp.sal%type;
begin
  select ename,sal into name,sal from scott.emp where empno=&no;
  tax_sal:=sal-sal*tax_rate;
  dbms_output.put_line('姓名'||name);
   dbms_output.put_line('税后工资'||tax_sal);
   end;

--集合变量
declare
type emp_record_type is record (
ename scott.emp.ename%type,
ejob  scott.emp.job%type,
esal scott.emp.sal%type);
emp_type emp_record_type;
begin
  select ename,job,sal into emp_type from scott.emp where empno=&no;
  end;



--PL/SQL表
declare
type emp_table_type is table of scott.emp.ename%type index by binary_integer;
ename_table emp_table_type;
begin
  select ename into ename_table(-1) from scott.emp where empno=7698;
  dbms_output.put_line('姓名'||ename_table(-1));
end;



--嵌套表

create or replace type emp_type as object(
ename varchar2(15),
salary number(6,2)
);
create or replace type emp_array is table of emp_type;

create or replace table department(
deptno number(2),
dname varchar2(15),
employee emp_array
) nested table employee store as employee;
--引用变量
create or replace type home_type as object(
street varchar2(15),
city varchar2(10),
owner varchar2(10)
);

create table homes of home_type;
insert into homes values('x','cd','zhangsan');
insert into homes values('y','cd','lisi');
insert into homes values('z','cd','wangwu');

create table person(
pid number(2) primary key,
name varchar2(10),
addr ref home_type
);

insert into person select 1,'zhangsan',ref(h) from homes h where h.owner='lisi';
select * from person;


--if语句
declare
e_sal number(6,2);
begin
  select sal into e_sal from scott.emp where empno=&no;
  if e_sal<2000 then
    update scott.emp set sal=e_sal+500 where empno=&no;
    elsif e_sal<3000 then
     update scott.emp set sal=e_sal-500 where empno=&no;
     else
       dbms_output.put_line('没有此人或不能修改');
       end if;
       end;
select * from scott.emp

--case
declare
e_deptno scott.emp.deptno%type;
begin
  case e_deptno
    when 10 then
        update scott.emp set comm=255 where empno=&no;
    when 20 then
      
        update scott.emp set comm=599 where empno=&no;
          when 30 then
        update scott.emp set comm=998 where empno=&no;
  else
     dbms_output.put_line('不能修改');
     end case;
     end;