PLSQL例子

DECLARE
petName number := 3;
BEGIN
if petName>= 3 then
dbms_output.put_line('技术防范');
else
dbms_output.put_line('防范');
end if;
END;


declare
i number :=1;
begin
loop

dbms_output.put_line(i);
if i>=10 then
exit;
end if;
i:=i+1;
end loop;
end;

begin
for i in reverse 1..10 loop
dbms_output.put_line('循环进行 第'||i||'次');
end loop;

end;
create table temp_tab(
tid number primary key,
tname varchar2(20)
);

begin
for i in 1..15 loop
insert into temp_tab values(i,'测试数据');
end loop;
end;
select * from temp_tab
begin
select tid into v_sal from temp_tab;
end;

begin
execute immediate 'create table temp_table(idd number ,namee varchar2(20))';
end;


declare
plsql varchar2(200);
t_name varchar2(20) :='jjasj';
t_id number :='101';
begin
plsql :='insert into temp_table values (:1,:2)';
execute immediate plsql using t_id,t_name;
end;

select * from temp_table;


declare
t_id number :='101';
t_name varchar2(20) :='孙悟空';
plsql varchar2(200);
begin
plsql :='update temp_table set namee=:1 where idd=:2 returning idd,namee into :r1,:r2';
execute immediate plsql using t_name,t_id returning into t_id,t_name;
dbms_output.put_line('编号：'||t_id||' 姓名：'||t_name);
end;



declare
tem_ex exception;--定义一个异常
n integer;
begin
select count(idd) into n from temp_table where idd='101';
if(n>=1) then
raise tem_ex;--抛出异常
end if;
dbms_output.put_line('该用户不存在，可以创建');
exception
when tem_ex then
dbms_output.put_line('该用户已存在');

end;


declare
num_ex exception;
num_ex2 exception;
n1 integer;
n2 integer;
begin
n1 :=3;
n2 :=999;
if n1=0 then
raise num_ex;
elsif n2=0 then
raise num_ex2;
end if;
dbms_output.put_line(n1||'除以'||n2||'结果是'||n1/n2);
exception
when num_ex then
dbms_output.put_line('被除数不能为0');
when num_ex2 then
dbms_output.put_line('除数不能为0');
end;


--游标

declare
CURSOR c_sss is select * from temp_table where idd='201';
idd number ;
namee varchar2(20);
notfoundex exception;
n integer :=0;
begin

FOR c_u in c_sss LOOP
n := n+1;
idd := c_u.idd;
namee := c_https://www.360docs.net/doc/f37345169.html,e;
DBMS_OUTPUT.put_line('ID是：'||idd||'名字是：'||namee);
END LOOP;

if n=0 then
raise notfoundex;
end if;

EXCEPTION
when notfoundex then
DBMS_OUTPUT.put_line('数据库中没有记录');
end;

--循环搞一搞

DECLARE
CURSOR c_sss is select * from temp_table;
idd number ;
namee varchar2(20);
BEGIN
FOR c_u in c_sss LOOP
idd := c_u.idd;
namee := c_https://www.360docs.net/doc/f37345169.html,e;
DBMS_OUTPUT.p

ut_line('ID是：'||idd||'名字是：'||namee);
END LOOP;
END;


--存储过程1
CREATE OR REPLACE PROCEDURE pro_1
IS
cursor cur_1 is select * from temp_table;
BEGIN
FOR cc in cur_1 LOOP
DBMS_OUTPUT.PUT_LINE(cc.idd||' '||https://www.360docs.net/doc/f37345169.html,e);
END LOOP;
END;
--存储过程2
CREATE OR REPLACE PROCEDURE pro_2
(r in number,
s out number
)
AS
sss number(11,2);
BEGIN
sss:=r*r*3.1415927;
s := sss;
END;

--调用1
BEGIN
pro_1;
END;
--调用2
declare
aaa number := 20;
ssss number;
BEGIN

pro_2(aaa,ssss);
dbms_output.put_line(ssss);
END;
--删除2
drop procedure pro_2



--函数
CREATE OR REPLACE FUNCTION fun_1 (score in number)
return number
IS
zong number;
BEGIN
zong :=score * 10;
return zong;
END;

--调用函数
declare
zong number;
begin
zong :=fun_1(98);
dbms_output.put_line('十门课的总成绩是：'||zong);
end;



--创建包头-----------
create or replace package pac_1 as
--------声明一个存储过程-------------
procedure addc(
chetype varchar2,
pail number,
prise number,
nianfen date,
chenum number
);
-------声明一个函数-----
function addd(chetype varchar2,
pail number,
prise number,
nianfen date,
chenum number) return number;
end pac_1;
--创建包体--------------
create or replace package body pac_1 as
----------实现包头声明的存储过程-------------
procedure addc(
chetype varchar2,
pail number,
prise number,
nianfen date,
chenum number
)as
begin
insert into che values(chetype,pail,prise,nianfen,chenum);
end addc;--结束

----------实现包头声明的函数-------------
function addd(chetype varchar2,
pail number,
prise number,
nianfen date,
chenum number) return number
as
numbb number;
begin
insert into che values(chetype,pail,prise,nianfen,chenum);
numbb :=sql%rowcount;
commit;
return numbb;
end addd;--结束
end pac_1;

--调用包------------
declare
nunn number;
begin
nunn := pac_1.addd('NHX-888',200,1000000,to_date('2014-12-23','yyyy-mm-dd'),9001);
dbms_output.put_line('影响的行数是：'||nunn);
end;
------查看结果--------------
select typ as 车型,pl as 排量,jg as 价格,nf as 出厂年份,bh as 编号 from che;


create or replace view view_1 as
select namee from temp_table;

select * from view_1;



CREATE database link lianjie connect to bertoon identified by bertoon
using '(DESCRIPTION =
(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(POST = 1521)))
(CONNECT_DATA = (SERVICE_NAME=orcl))
)';


drop database link lianjie;