PL/SQL例子

以下是经测验能正确运行的:

 

create    table t_emp_log212
(
who varchar2(10) not null,
action varchar2(10) not null,
actime date
);

create or replace trigger tri_emp_insert
before insert
on t_emp
begin
insert into t_emp_log(who,action,actime)values(user,'insert',sysdate);
end;
-------------------------------------------------------------------------------------------

set serveroutput on
declare
 v_empno emp.empno%type:=&empno;
 v_ename emp.ename%type;
 begin
select empno, ename into v_empno,v_ename from emp where empno=v_empno;
dbms_output.put_line(v_ename);

exception
 when others then
 dbms_output.put_line(' your empno is null,ename is null');
end;
-------------------------------------------------------------------------------------------

SET SERVEROUTPUT ON
declare
type tabletype1 is table of varchar2(10) index by binary_integer;
type tabletype2 is table of varchar2(10) index  by binary_integer;
table1 tabletype1;
table2 tabletype2;
begin
 table1(1):='大学';
 table1(2):='大专';
  table2(1):=88;
  table2(2):=55;
dbms_output.put_line(table1(1)||table2(1));
dbms_output.put_line(table1(2)||table2(2));
end;

-------------------------------------------------------------------------------------------

set serveroutput on;
declare
   v_grade char(10):=upper('&p_grade');/*upper将字符串全变大写 必须加引号 因为赋值以后显示为upper（'A'）而upper（A）A作为常量了*/
   v_result varchar2(20);

begin
 v_result:=
  case v_grade
  when 'A' then '90 分以上'
  when 'B' then '80分到90分之间'
  when 'C' then '60分到80分之间'
  else '不及格'
end;
dbms_output.put_line('grade'||v_grade||'result'||v_result);
end;

-------------------------------------------------------------------------------------------

set serverout on

declare
 v_item number(3):=1;
 begin

loop
  dbms_output.put_line(v_item);
    v_item:=v_item+1;
  exit when v_item>100;
end loop;
end;
/*
begin
   for v_item in 1..100
  loop
  dbms_output.put_line(v_item);
end loop;

 

end;
*/

-------------------------------------------------------------------------------------------

create or replace trigger tir_t_emp2
before update on t_emp
referencing new as newrow  old as oldrow
for each row
begin 
INSERT INTO emp_NEW(EMPNO,ENAME,JOB,DEPTNO)    update  set对某一个数据进行更新 更新之前的数据存在一个表里 更新后的数据也放在一个表里
values
(:NEWROW.empno,:NEWROW.ename,:NEWROW.job,:NEWROW.deptno);更新后的数据保存在一个新的表中

INSERT INTO emp_OLD(EMPNO,ENAME,JOB,DEPTNO)
values
(:OLDROW.empno,:OLDROW.ename,:OLDrow.job,:OLDROW.deptno);更新前的数据保存在一个新的表中

end tir_t_emp2;

-------------------------------------------------------------------------------------------

create or replace trigger tri_emp2
after insert or update or delete
on t_emp
begin
if updating then
 insert into t_emp_log2(who,action,actime)values(user,'update',sysdate);
elsif deleting then
 insert into t_emp_log2(who,action,actime)values(user,'delete',sysdate);
elsif inserting then
 insert into t_emp_log2(who,action,actime)values(user,'insert',sysdate);
end if;
end;

/**create table t_emp_log2
(
who varchar2(10) not null,
action varchar2(10) not null,
actime date

);**/

-------------------------------------------------------------------------------------------

create or replace trigger tri_emp4
after update of ename
on t_emp
referencing new as newrow

for each row

begin
if updating then
 insert into t_emp_log(who,action,actime)values(user,'update',sysdate);
 insert into emp_new(ename)VALUEs(:newrow.ename);

end if;
end;

-------------------------------------------------------------------------------------------

declare
i number(3):=1;
j number(3):=2;
begin

loop

 

insert into t_user values(i,'a'||i,'a'||i,1,to_date('2007-01-01','yyyy-mm-dd'),1,i,'sdd');

insert into t_user values(j,'a'||j,'a'||j,1,to_date('2007-01-01','yyyy-mm-dd'),2,j,'sdd');

 

i:=i+2;
j:=j+2;
exit when i>600 and j>600;

end loop;

end;

-------------------------------------------------------------------------------------------

/**
create view t1_t2
as
select e.empno,e.ename,e.job,d.dname from t_emp e,t_dept d where d.deptno=e.deptno;
**/

 

create or replace view t1_t2
as
select e.empno,e.ename,e.job,d.dname from t_emp e,t_dept d where d.deptno=e.deptno;
create  or replace trigger t1_t2
instead of update on t1_t2
referencing new as newrow
for each row

begin

update t_emp  e set empno=:newrow.empno,ename=:newrow.ename,job=:newrow.job where empno=:newrow.empno;
/**
where指的是假如视图更新一个人的名字了 那么也要更改基表 怎么更改了 不加where全部都改了 加了where 假如视图更改的是1号 为刘德华 基表也要变成刘德华 那么更改到基表 用where判断基表号为1号 更新ename
**/

update t_dept d set dname=:newrow.dname where deptno=(select deptno from t_emp where empno=:newrow.empno);
end;

-------------------------------------------------------------------------------------------

declare
v_funretvalue boolean;
v_name varchar2(20);
begin

v_name:='&姓名';
v_funretvalue:=check_user(v_name);
if  v_funretvalue then
dbms_output.put_line('用户'||v_name||'存在');
else
dbms_output.put_line('用户'||v_name||'不存在');
end if;
end;

-------------------------------------------------------------------------------------------

create or replace function check_user
(
v_ename varchar2
)
return boolean
as
v_count number;
begin

  select  count(ename) into v_count from emp where ename=v_ename;
if v_count>0 then
   return true;
else
return false;
end if;
end;

-------------------------------------------------------------------------------------------

set serveroutput on;
declare
t_empno t_emp.empno%type;
cursor mycursor is
 select e.empno,e.ename from t_emp e where empno>t_empno;
cursored mycursor%rowtype;
begin
    t_empno:=1000;
    open mycursor;
if mycursor%isopen then
  fetch mycursor into cursored;
dbms_output.put_line(to_char(cursored.ename));
else
dbms_output.put_line('no open!!');
end if;
while mycursor%found
loop
dbms_output.put_line(t_empno||':'||to_char(cursored.ename));
if mycursor%rowcount=4 then
exit;
end if;
fetch mycursor into t_empno,cursored.ename;
end loop;
close mycursor;
end;

-------------------------------------------------------------------------------------------

set serveroutput on;

declare
eno emp.empno%type;
e_name emp.ename%type;
cursor mycurs1(var_name varchar2) is
 select e.empno,e.ename from emp e where e.ename like '%'||var_name||'%';

begin
if mycurs1%isopen=false then
 open mycurs1('%A%');
end if;
fetch mycurs1 into eno,e_name;
while mycurs1%found loop
dbms_output.put_line(eno||':'||e_name);
if mycurs1%rowcount=4 then
exit;
end if;
fetch mycurs1 into eno,e_name;
end loop;
close mycurs1;
end;

-------------------------------------------------------------------------------------------

set serveroutput on;

declare

cursor mycurs1 is
 select e.empno,e.ename,e.deptno from t_emp e where e.deptno=20;

begin
for employee in mycurs1
 loop
 if mycurs1%isopen then
   dbms_output.put_line('学号:'||employee.empno||'姓名:'||employee.ename||'部门:'||employee.deptno);
 end if;
 end loop;
end;

/**
for
游标

**/

-------------------------------------------------------------------------------------------

create or replace package t_1
is
 procedure sayHello(v_name varchar2);
end;
/**

/**
定义一个包 过程也是定义的 并没有真正赋予过程
**/

/**
create or replace package body t_1
is
procedure sayHello(v_name varchar2)/**后面不加分号**/
is
begin
dbms_output.put_line('hello'||v_name);
end;
end;
/**
然后在包里面对过程进行真正的定义；
**/

**/

/**
create or replace package t_2
is
 function sayHello2(v_deptno number);
end;不能用function
**/
/**
create or replace paclage body t_2
is
function sayHello2(v_deptno varchar2)
is
begin
dbms_output.put_line('hello'||v_name);
end;
end;
**/
**/

-------------------------------------------------------------------------------------------

CREATE TABLE T_DEPT
(
 ID VARCHAR2(32) NOT NULL,
 DNAME VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_DEPT_ID PRIMARY KEY(ID)
);

CREATE TABLE T_ROLE
(
 ID VARCHAR2(32) NOT NULL,
 RNAME VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_ROLE_ID PRIMARY KEY(ID)
);
CREATE TABLE T_EMPLOYEE
(
 ID VARCHAR2(32) NOT NULL,
 ENAME VARCHAR2(20) NOT NULL,
 ESEX CHAR(2) NOT NULL,
 DEPTID VARCHAR2(32) NOT NULL,
 ROLEID VARCHAR2(32) NOT NULL,
 EDIR_LEADER VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY(ID),
 CONSTRAINT  FK_EMPLOYEE_DEPTID  FOREIGN KEY(DEPTID)  REFERENCES T_DEPT(ID),
 CONSTRAINT FK_EMPLOYEE_ROLEID  FOREIGN KEY(ROLEID) REFERENCES T_ROLE(ID)
);

CREATE TABLE T_PRESENT
(
 ID VARCHAR2(32) NOT NULL,
 PMORNING_WORK DATE NOT NULL,
 PNOON_WORKOUT DATE NOT NULL,
 EMPLOYEEID VARCHAR2(32) NOT NULL,
 CONSTRAINT FK_PRESENT_EMPLOYEEID FOREIGN KEY(EMPLOYEEID)  REFERENCES T_EMPLOYEE(ID)
);

-------------------------------------------------------------------------------------------

create sequence seq_db
start with 1
increment by 1
nomaxvalue
nocycle
cache 2;
 
create or replace procedure pro_dept_insert
as
v_id number:=1;
begin
loop
insert into t_dept(id,dname)values
('d000000000'||seq_db.nextval,'dept'||seq_db.nextval);
v_id:=v_id+1;
exit when v_id>20;
end loop;
end;

-------------------------------------------------------------------------------------------

create sequence seq_1_20_2
start with 1
increment by 1
nomaxvalue
nocycle
cache 2;
 
create or replace procedure pro_dept_insert2
as
v_id number:=1;
begin
loop
insert into t_employee(id,deptid,roleid)values
(seq_1_20_2.nextval,'d0000000001','r0000000001');
v_id:=v_id+1;
exit when v_id>200001;
end loop;

loop
insert into t_employee(id,deptid,roleid)values
(seq_1_20_2.nextval,'d0000000002','r0000000002');
v_id:=v_id+1;
exit when v_id>400001;
end loop;

loop
insert into t_employee(id,deptid,roleid)values
(seq_1_20_2.nextval,'d0000000003','r0000000003');
v_id:=v_id+1;
exit when v_id>600001;
end loop;

loop
insert into t_employee(id,deptid,roleid)values
(seq_1_20_2.nextval,'d0000000004','r0000000004');
v_id:=v_id+1;
exit when v_id>800001;
end loop;

loop
insert into t_employee(id,deptid,roleid)values
(seq_1_20_2.nextval,'d0000000005','r0000000005');
v_id:=v_id+1;
exit when v_id>1000001;
end loop;
end;

 

 

 

以下是经测验不能正确运行的:

 

set serveroutput on
declare
  type table_id is table of emp.empno%type index by binary_integer;
type table_sal is table of emp.sal%type index by binary_integer;
    tableid table_id;
     tablesal table_sal;
v_char varchar2(30);

begin

  for i in 1..5000 loop
  tableid(i):=(select empno into tableid from emp where 1=1);
  tablesal(i):=(select sal into tablesal from emp where 1=1);
end loop;

  loop
   case tablesal(i);
i:=1;
   when tablesal(i)<1000 then
   v_char:='sal is low';
  when tablesal(i)>=1000 and tablesal(i)<=3000 then
v_char:='sal is normal';
when tablesal(i)>3000 then
v_char:='sal is high';
else v_char:='lost data';
end case;
exit when i:=10000;
i:=i+1;
end loop;
dbms_output.put_line('id'||tableid||'sal'||v_char);
END;

 

 

 

 

 

 

 

 

 

 

 

 

 

/*set serverout on;
declare
v_name emp.ename%type;
v_sal emp.sal%type;
v_char varchar2(30);
begin
   select ename,sal into v_name,v_sal from emp ;
loop 
 
if v_sal<1000 then
   v_char:='sal is low';
  elsif  v_sal>=1000 and v_sal<=3000 then
v_char:='sal is normal';
elsif   v_sal>3000 then
v_char:='sal is high';
else v_char:='lost data';
end if;

exit when
end loop;
dbms_output.put_line('name'||v_name||'sal'||v_char);
end;
*/

-------------------------------------------------------------------------------------------

set serveroutput on
declare
v_name emp.ename%type;
v_sal emp.sal%tpye:=&sal;
v_char varchar2(30);
begin
   select ename,sal into v_name,v_sal from emp  where sal=v_sal;
   if v_sal < 1000 then
   v_char:='sal is low';
  elsif  (v_sal> = 1000 and v_sal < = 3000) then
v_char:='sal is normal';
else   v_sal > 3000 then
v_char:='sal is high';
else v_char:='lost data';
end if;
dbms_output.put_line('name'||v_name||'sal'||v_char);
end;

-------------------------------------------------------------------------------------------

/**触发器编译没错误 可是换成不同的用户 不会显示挂出异常
**/
create or replace trigger tri_emp_insert
before delete
on t_emp
declare
user varchar2(15);
begin
if (user='scott') then
  raise_application_error(-20001,'你不能访问或修aaa改这个表');
  insert into t_emp_log(who,action,actime)values(user,'delete',sysdate);

else
insert into t_emp_log(who,action,actime)values(user,'delete',sysdate);
end if;

end;

-------------------------------------------------------------------------------------------

create or replace trigger tri_emp_insert
before insert
on t_emp
begin
if user!='scott' then
raise_application_error(-20001,'你不能访问或修aaa改这个表');
end if;
end;

-------------------------------------------------------------------------------------------

create or  replace procedure  p_c
(

v_id in number,
v_name in number,
v_psw in number,
v_address in number,
)
as
o_id number:=1;
begin

loop

insert into t_user(id,name,psw,address)values
('id'||v_id,'name'||v_name,'psw'||v_psw,'address'||v_address);

o_id:=o_id+1;

exit when o_id>200;

end loop
end;