plsql例子

最新推荐文章于 2021-09-21 21:14:24 发布

liuya1985liuya

最新推荐文章于 2021-09-21 21:14:24 发布

阅读量2.7k

点赞数

分类专栏： oracle管理文章标签： integer exception insert table fun function

本文链接：https://blog.csdn.net/liuya1985liuya/article/details/1355137

版权

oracle管理专栏收录该内容

87 篇文章 0 订阅

订阅专栏

declare

--声明一个变量id----类型为number;
id number;
name varchar2(15);

begin
--给ID赋值‘：=类似JAVA的=’
id :=10;
name :='Hello World';

--将ID和NAME输出
dbms_output.put_ling('ID is'||id);
` dbms_output.put_line('data is :'||name);

end;
/

//输出
set serveroutput on
//执行
@HelloWorld

//新建一个

declare
      id constant number not null :=10 ;//not null约束放在类型的前面

       12id number;

name varchar2(15);
begin

name :='hi baby';
dbms_output.put_line('I' 'm dada');//不能有空格双‘’代表转义字符

dbms_output.put_line(name);
end;
/

//对数据库进行操作

declare

     type record_name is record(id number,
          name varchar2(15));

--对record 不可以对其进行直接操作，要通过别名
--test record_name就是给record_name定义了一个别名test

test record_name;

begin
--从数据库里边取出值存放到record变量里边
select id,name into test.id,test.name from adam
where id=1;

     dbms_output.put_line('id from db:'|| test.id);
     dbms_output.put_line('name from db :'|| test.name);
    end;
/

create table tagtest
(id number,name varchar2(15));

//rowtype的使用

declare
adam_type adam%rowtype;

begin
adam_type.id :=10;
adam_type.name := 'Hello World';

  dbms_output.put_line('id is :'||adam_type.id);
  dbms_output.put_line('name is :'||adam_type.name);
end;
/   //这一/一定要放在第一行

//引用表类型

declare
        type t_stutable is table of sqltest%rowtype index
        by binary_integer;
        v_sqlt t_stutable;

begin
select name into v_sqlt(1).name from sqltest where id=1;

dbms_output.put_line('value from sqltest'||v_sqlt(1).name);

v_sqlt(3).id :=10;
v_sqlt(4).name := 'hello world';

insert into sqltest values(v_sqlt(3).id,v_sqlt(4).name);
commit;

end;
/

//变量
declare
id number;

begin
id :=10;
--嵌套一个plsql,查看变量
declare
name varchar2(15);
begin
dbms_output.put_line('id is :'||:=id);
end;
dbms_output.put_line('name 's value is :'||name);
end;
/

//循环
declare
id number;
type table_name is table of sqltest%rowtype index by binary_integer;

test table_name;
begin
select id into test(1).id from sqltest where name ='tktk';

        if test(1).id = 10 then
                dbms_output.put_line('db''s id is '|| test(1).id);
        else
                dbms_output.put_line('id is empty!');
        end if;
end;
/

declare
id number;
type table_name is table of sqltest%rowtype index by binary_integer;

test table_name;
begin
select id into test(1).id from sqltest where name ='tktk';

        if test(1).id = 10 then
                dbms_output.put_line('db''s id is '|| test(1).id);
        elsif test(1).name = 'tttt' then
                dbms_output.put_line('name''is name is'||test(1).name);
        else
                dbms_output.put_line('id is empty!');
        end if;

end;

//循环

declare
id number := 1;
--设定要付值，否则会发生死循环造成缓存宜出
begin
loop
  id :=id+1;
  --如果oracle设置的缓存很小，会死
  dbms_output.put_line(id);
end loop;
end;
/

declare
id number;
begin
while true then loop
dbms_output.put_line('loop');
end loop;
end;
/
//内套循环
declare

id number := 1;
begin

loop

        dbms_output.put_line('id is : '||id);
        while true loop
        dbms_output.put_line('while loop ');
        exit;

        if id =20 then
                id := id + 1;
                exit;
                end if;
        end loop;
        exit;

end loop;
end;
/
//for循环

declare
id number;
begin
--for后面的i可以不用定义，它是用来丰放循环变量的
for i in l..100 loop
dbms_output.put_line('i is :'||i);
end loop;
end;
/

//goto循环
declare
id number;
begin
for i in 2..100 loop
  if i =20 then
   goto lable;
  end if;
end loop;
<<lable>>
dbms_output.put_line('goto end');//如果没有这句会出错
--眺出循环，不能直接跳到end 里.两个尖括号表示目的地

end;
/

//cursor游标、可以对数据库多条记录同时进行操作

declare
cursor cursor_name is select * from adam;
--给游标取别名
test cursor_name%rowtype;
begin
--打开游标，
open cursor_name;
--把游标的值赋予别名，每fetch一次，指针就往下移一次,游标开始是在第一条记录上
fetch cursor_name into test;
--对游标的值进行操作
dbsm_output.put_line(test.id);
close cursor_name;
end;
/
//对游标的属性进行操作

declare
cursor my_cursor is select * from adam;
test my_cursor%rowtype;
begin
if my_cursor%isopen then
  dbms_output.put_line('cursor has opened');
else
  dbms_output.put_line('curosr is close');
  open my_cursor;
  dbms_output.put_line('open cursor new');
end if;
//fount和notfount不能对空引用进行操作
if my_cursor%notfound then
  dbms_output.put_line('cursor has no data');
  fetch my_cursor into test;
  dbms_output.put_line('fetch data into cursor');
end if;
//循环把游标的值取出
while my_oursor%found loop
  dbms_output.put_line('cursor value : '||test.id||'----'||test.name||'----point: '||my_cursor%rowcount);
  fetch my_cursor into test;

end loop;
end;
/

// 带参数的cursor
declare
cursor cursor_name(t_id number) is select * from adam where id=t_id;
--别名
test cursor_name%rowtype;
begin
--传参数到游标
open cursor_name(10);
--指针下移
fetch cursor_name into test;

while cursor_name%found loop
dbms_output.put_line('name is : '||test.name);
--指针继续移动
fetch cursor_name into test;

end loop;

close cursor_name;
end;
/

//异常
declare
a exception;
b exception;
id number;

begin
id :=1;
for i in 1..100 loop

  if i = 10 then
--触发异常
   raise a;
  end if;

end loop;
--触发异常
raise b;
exception
--when other then 接收所有异常
when a then
  dbms_output.put_line('exception has accured!');
when b then
dbms_output.put_line('exception has accured b!');
end;
/

//练习从一章表里把记录复制到另一章表里
declare
cursor cursor_name is select * from adam;
test cursor_name%rowtype;
begin
open cursor_name;

fetch cursor_name into test;
while cursor_name%found loop
insert into adam_back values(test.id,test.name);
fetch cursor_name into test;
end loop;
commit;
close cursor_name;
end;
/

create or replace procedure adpro
as --好比declare
id number;
begin
id := 10;
dbms_output.put_line('hello world ','helo Procedure');
end adpro;
/
中有in /out表是只读、只写 in/out共用表可读可写
参数默认为in类型

create or replace procedure liupro(id int number,
name out varchar2,passwd in out varchar2)
as
t_id number;
t_name varchar2(15);
t_passwd varchar2(15);
begin
t_id := id;
dbms_output.put_line('收到的一个in参数：'||t_id);

if name is null then
dbms_output.put_line('out类型的参数为空‘);
else
dbms_output.put_line('out类型的参数不为空：'||name);
end if;

if passwd is null then
dbms_output.put_line('in out 类型的参数为空:');

else
dbms_output.put_line('in out 类型的参数不为空：'||passwd);
end if;

end liupro;//show error显示错误
/

declare
id number;
name varchar2(15);
passwd varchar2(150;
begin
id := 10;
name := 'hello nana';
passwd :='hello world';

--调用 create or replace procedure liupro(id int number,
name out varchar2,passwd in out varchar2)
as
t_id number;
t_name varchar2(15);
t_passwd varchar2(15);
begin
t_id := id;
dbms_output.put_line('收到的一个in参数：'||t_id);

if name is null then
dbms_output.put_line('out类型的参数为空‘);
else
dbms_output.put_line('out类型的参数不为空：'||name);
end if;

if passwd is null then
dbms_output.put_line('in out 类型的参数为空:');

else
dbms_output.put_line('in out 类型的参数不为空：'||passwd);
end if;

end liupro;
/

--调用procedure
declare
in number;
name varchar2(15);
passwd varchar2(15);
begin
id := 10;
name :='hello';
passwd :='hello world';
--调用procedure
adam_pro(id,name,passwd);
end;
/

procedure                                                      fountion
1、独立运行                                                   1、不能独立运行
2、没有返回值      2、必须有返回值

相同点
两者都存放在数据库中

funtion的返回值必须接，否则报错
create or replace function adam_fun return number
as
id number;
begin
adms_output.put_line('Hello World,Hello Function');
--返回值
return 100;
end;
/
declare
id number;
begin
id := adam_fun;
dbms_output.put_line('return value:'||id);

end;
/

create or replace function adam_fun(id in number,name out varchar2,passwd
         in out varchar2) return number
as
t_id number;
t_name varchar2(15);
t_passwd varchar2(15);
begin
--对只读数据进行修改
id := 10;

if name is null then
  dbms_output.put_line('out name is null');
  end if;

name :='hello name';

passwd :='hello passwd';

return id;

end adam_fun;
/

{
//创建包头(好比接口和类，包头的方法包体要实现)
create or replace package adam_pack as

procedure adam_pro;//方法(仅是在声明，事先不必有的)
function adam_fun return number;//方法

end adam_pack;
/(编释)
//包体(body说明是包体，后面的方法表明要实现的方法)
create or replace package body adam_pack as
  procedure adam_pro as
    id number;
   begin
    dbms_output.put_line('hello package');
   end adam_pro;

   function adam_fun return number as
    id number;
   begin
    id := 10;
    return id;
   end adam_fun;
end adam_pack;
/
}
{这是一个例子实现参数调用
create or replace package adam_pack as

  procedure adam_pro(id in number;
      name out varchar2,
      passwd in out varchar2);

function adam_fun(id in number,name out varchar2,passwd in out varchar2)

return varchar2;
end adam_pack;
/
//实现(相当于一个实现类)
create or replace package body adam_pack as
procedure adam_pro(id in number,name out varchar2,passwd in out varchar2) as

                i number:= 10;
                na varchar2(15) :='pro name';
                pass varchar2(15) :='pro passwd';
        begin
                dbms_output.put_line('return not procedure values');
        end adam_pro;

function adam_fun(id in number,name out varchar2,passwd in out varchar2) return varchar2 as

                   i number;
                   na varchar2(15) :='fun name';
                   pass varchar2(15) :='fun paswd';
        begin
                return name;
                return pass;

end adam_fun;
end adam_pack;

//创建脚本,来调用
declare
  id number;
  name varchar2(15);
  passwd varchar2(15);
begin
  id :=1;
  name := 'a';
  passwd := 'p';

adma_pack.adam_pro(id,name,passwd);
end;
/

}

{

//JDBC调用PL/SQL
create or repalce procedure adam_pro as
  id number;
begin
  insert into lianxi values(100,'a');
  commit;
end adam_pro;
/

create or replace procedure adam_pro(id number,name varchar2)as
        i number;
        na varchar2(15);
begin
        for i in 1..id loop
                insert into lianxi values(i,name||i);
        end loop;
        commit;
end adam_pro;
/

package lianxi;

import java.sql.*;
import java.sql.CallableStatement;

public class Procedure{
public static void main(String args[])throws Exception{
  ConnectionFactory factory=new ConnectionFactory();
//调用一个没有参数的procedure
  Connection con=factory.getConnection();
  CallableStatement cs=con.prepareCall("(class adam_pro(?,?))");
  //发送请求到数据库，执行该procedure
  cs.setInt(1,100);
  cs.setString(2,"hehe");

cs.execute();
}
}

package lianxi;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.sql.CallableStatement;

public class ConnectionFactory
{
private Properties pro = null;

public Connection getConnection()throws Exception{

this.init();

Class.forName(pro.getProperty("driver"));

  Connection con=DriverManager.getConnection(pro.getProperty("url"),
              pro.getProperty("username"),pro.getProperty("password"));
  return con;
}
//初始化属性，获得一个输入流，pro.load(InputStream)封装了
//对属性的操作，把属性按key---value的形式存放到Pproperties
public void init()throws Exception{
  pro = new Properties();
  InputStream is=getClass().getResourceAsStream("/jdbcinfo.properties");
  pro.load(is);
}
}

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@192.168.1.100:1521:tarena
username=tarena
password=tarena

}

//tigger

create or replace trigger adam_trigger before//创建一个trigger
insert or update or delete on bbe(表名)
declare
id number;
name varchar2(15);
begin
id :=10;
name :='hello';
dbms_output.put_line('解发器被出发了');
end adam_trigger;
/(在triger中不能写commit)

(监视表，对表进行多条操作利用cursor 和trigger,这里是对表进行删除，而同时在别一个表进行备份)
create or replace trigger a_trigger before
delete on adam for each row(加上for each row 对每一行进行监视)
declare
cursor c_n is select * from adam;
test c_n%rowtype;
begin
open c_n;
fetch c_n into test;
while c_n%found loop
  insert into adam_back values(test.id,test.name);
  fetch c_n into test;
end loop;
close c_n;
end a_trigger;
/
//对每一行进行操作
create or replace trigger a_trigger before
        insert or update or delete on liuliu for each row
declare
        cursor cn is select * from liuliu;
        test cn%rowtype;

        old_id number;
        old_name varchar2(15);
        new_id number;
        new_name varchar2(15);
begin
        old_id := :old.id;
        old_name := :old.name;

open cn;
fetch cn into test;

insert into liuliu1 values(old_id,old_name);

close cn;
end a_trigger;
/
~

用plsql执行DDL语句
create or replace procedure adam_pro as
id integer;
ret integer;
begin
--分配一块资源，并且指定一个标志符(dbms_sql oracle内置的一个包)
id := dbms_sql.open_cursor;
dbms_output.put_line('空间标志符：'||id);

--对sql语句进行解析，并存放到相应的空间
dbms_sql.parse(id,'create table kay(id number)',dbms_sql.native);

--执行该空间的sql语句(id所指向的空间)

ret := dbms_sql.exectue(id);

--回收资源
dbms_sql.close_cursor(id);
end adam_pro;
/

{对表整合同时建多张表这只是一例

create or replace procedure adam_pro
as
        id integer;
        ret integer;
begin
        id := dbms_sql.open_cursor;

        for i in 1..100 loop
                dbms_sql.parse(id,'create table t_detai_'||i||' (id number)',
                        dbms_sql.native);
                ret := dbms_sql.execute(id);
        end loop;
end adam_pro;
/

}

create or replace procedure liu_pro(id number,t_detai varchar2) as
t_id number;
ret number;
begin
t_id := id;
ret :=t_detai_;

for i in 1..100 loop
dbms_sql.parse(id,'create table t_detai_'||i||',dbms_sql.native);

ret := dbms_sql.execute(id);

end loop;
end liu_pro;
/

//整合模块plsql实现老师例1
create or replace procedure adam_pro(datal varchar2,id number,
data2 varchar2) as
cursor_id integer;
ret integer;
begin
cursor_id :=dbms_sql.open_cursor;
for i in 1..id loop
dbms_sql.parse(cursor_id,datal||i||data2,dbms_sql.native);

//dbms_output.put_line(datal||i||data2);

ret :=dbms_sql.execute(cursor_id);
end loop;
end adam_pro;
/

执行例
exec adam_pro('create table ktkt',10,'(id number,name varchar2(10))');

显示日期select to_char(trunc(sysdate-1/24,'hh24'),'yyyy:mm:dd:hh24:mi:ss') as time from dual;

//
取值的第2方法( 文件名和procedure名可相同“为了方便最好不同”   )
create or replace procedure liu_pro as
        cursor_id1 integer;
        cursor_id2 integer;
        ret integer;

        t_id number;
        t_name varchar2(12);
begin
        cursor_id1 :=dbms_sql.open_cursor;
        dbms_sql.parse(cursor_id1,'select id,name from liuliu',dbms_sql.native);

dbms_sql.define_column(cursor_id1,1,t_id);

        --在定义列空间的时候，对于varchar2类型，要指定长度
        --公针对varchar2类型
        dbms_sql.define_column(cursor_id1,2,t_name,12);

--执行完成一次操作，把查询的值勤存放到缓存里
ret :=dbms_sql.execute(cursor_id1);

        cursor_id2 :=dbms_sql.open_cursor;
        --把存放在缓存里的值，循环取出来
        loop

if dbms_sql.fetch_rows(cursor_id1)>0 then
----如果该指针指向有值，那么把这个记录的值取出来
dbms_sql.column_value(cursor_id1,1,t_id);

dbms_output.put_line('取出的t_id'||t_id);

dbms_sql.column_value(cursor_id1,2,t_name);

dbms_output.put_line('取出的name'||t_name);

dbms_sql.parse(cursor_id2,'insert into adam_back values(:id,:name)',dbms_sql.native);

dbms_sql.bind_variable(cursor_id2,':id',t_id);

dbms_sql.bind_variable(cursor_id2,':name',t_name);

ret :=dbms_sql.execute(cursor_id2);
else

exit;
end if;

end loop;
end liu_pro;
/

//一章表的值复制到别一张表(插入)
ed test_dbms
create or replace pracedure adam_pro as

cursor_id integer;
cursor_id2 integer;
ret integer;

name adam_detail_7.name%type;
logout_date adam_detail_7.logout_date%type;
time_duration adam_detail_7.time_duration%type;

begin
cursor_id1 :=dbms_sql.open_cursor;
dbms_sql.parse(cursor_id1,'select name,max(logout_date),
sum(time_duration) from adam_detail_7 where
logout_date between trunc(sysdate,''hh24'')
and trunc(sysdate+1/24,''hh24'') group by name,dbms_sql.native);

dbms_sql.define_column(cursor_id1,name,12);
dbms_sql.define_column(cousor_id1,2,logout_date);
dbms)sql.define_column(cursor_id1,3,time_duration);

ret :=dbms_sql.execute(cursor_id1);

--重新获取新空间，执行insert 操作
cursor_id2 :=dbms_sq1.open_cursor;
loop
  if dbms_sql.fetch_rows(cursor_id1)>0 then
   dbms_sql.column_value(cursor_id1,1,name);
   dbms_sql.column_value(cursor_id1,2,logout_date);
   dbms_sql.column_value(cursor_1,3,time_duration);

   dbms_sql.parse(cursor_id2,'insert into adam_day_7
   values(:name,:logout_date,:time_duration)',
   dbms_sql.native);
   dbms_sql.bind_variable(cursor_id2,':name',name);
   dbms_sql.bind_variable(cursor_id2,':logout_date',
   ogout_date);
   dbms_sql.bind_variable(cursor_id2,':time_duratrion',
   time_duration);

   ret :=dbms_sql.execute(cursor_id2);
   else exit;
   end if;
  end loop;
commit;

end adam_pro;
/

//获取得时间
select trunc(to_timestamp('07-07-200610:00:00',
'mm-dd-yyyyhh24:mi:ss'),'hh24') from dual
/

liuya1985liuya

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
plsql例子

declare--声明一个变量id----类型为number; id number; name varchar2(15);begin--给ID赋值‘：=类似JAVA的=’ id :=10; name :=Hello World;--将ID和NAME输出 dbms_output.put_ling(ID is||id);` dbms_output.put_
复制链接

扫一扫

专栏目录