存储过程

来源：互联网发布：nginx ip限制编辑：程序博客网时间：2024/05/17 00:33

create tablespace yutou
datafile
    'c:/abc.dbf'
    size 5m
     segment space management auto


create view abc as select e.empno ,e.ename from emp e

select * from abc

CREATE INDEX admininfo_index ON admininfo (logdate)

select * from admininfo_index

delete admininfo

declare
   abc varchar2(30); --声明变量
begin

   select e.code --查询数据并且放入到变量abc中
   into abc
   from admininfo e;
   dbms_output.put_line(abc);
   exception
   when no_data_found then --如果没有查询到数据打印异常
   dbms_output.put_line('没有数据');
end;

begin
for i in 1..9
loop
for j in 1..i
   loop
   if (i*j<10) then
   dbms_output.put(j||'*'||i||'='||i*j||'   ');
   else
   dbms_output.put(j||'*'||i||'='||i*j||' ');
   end if;
   end loop;
dbms_output.put_line('');
   end loop;
end;

declare
topprice toys.toyprice%type;
cursor test is --定义游标的值
    select t.toyprice from toys t where t.toyprice>200;
begin
    open test;
   loop
    fetch test into topprice ;--把游标的值赋给变量
    exit when test%notfound;--无值则退出循环
    DBMS_OUTPUT.PUT_LINE
          ('TOYPRICE=:玩具单价=：'||topprice);

    end loop;
close test;
end ;

declare
type topprice is ref cursor
    return toys%rowtype;
toys_curvar topprice ;--定义游标类型
toys_rec toys%rowtype;
begin
open toys_curvar for
select * from toys;--游标赋值
loop --循环从游标中取值
fetch toys_curvar into toys_rec;
exit when toys_curvar%notfound;
dbms_output.put_line(toys_rec.toyname);
end loop;
close toys_curvar;--先关闭循环后关闭游标
end;

select * from toys

CREATE OR REPLACE FUNCTION
f_queryadminpwd (v_code varchar2)
RETURN VARCHAR2 AS
v_pwd varchar2(20);
BEGIN
SELECT pwd
INTO v_pwd
FROM admininfo where code = v_code;
return v_pwd;
EXCEPTION
WHEN NO_DATA_FOUND THEN
     return -1;
WHEN TOO_MANY_ROWS THEN
     return -2;
   WHEN OTHERS THEN
     return -3;
END;

--过程通过plsql块来访问
BEGIN
DBMS_OUTPUT.PUT_LINE(f_queryadminpwd(10));
END;

--函数是具有返回值的子程序

create or replace function test_sum(data1 number,data2 number)
return number
is
sum number ;
begin
sum = data1+data2;
return sum ;
end ;

select test_sum(5,6) from dual ;

CREATE TABLE TEST_TRG
(ID NUMBER, NAME VARCHAR2(20));

--语句触发器
CREATE OR REPLACE TRIGGER trgdemo
   AFTER INSERT OR UPDATE OR DELETE or select
   ON test_trg
BEGIN
   IF UPDATING THEN
     DBMS_OUTPUT.PUT_LINE('已更新 test_trg 中的数据');
   ELSIF DELETING THEN
     DBMS_OUTPUT.PUT_LINE('已删除 test_trg 中的数据');
   ELSIF INSERTING THEN
     DBMS_OUTPUT.PUT_LINE('已在 test_trg 中插入数据');
-- ElsIf selecting then
-- DBMS_OUTPUT.PUT_LINE('正在查询 test_trg 数据');
   END IF;
END;

--行级触发器
CREATE OR REPLACE TRIGGER BI_TEST_TRG
BEFORE INSERT OR UPDATE OF ID
ON TEST_TRG
FOR EACH ROW
BEGIN
     IF INSERTING THEN
       SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
     ELSE
       DBMS_OUTPUT.PUT_LINE('不允许更新ID值！');
       RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
     END IF;
END;

create or replace trigger yutoutri after insert
on itemfile for each row
begin
   if(NEW.qty_hand = 0) then
   dbms_output.put_line('操作已执行，但未插入数据');
   else
   dbms_output.put_line('成功插入数据');
   end if ;
end ;

begin
insert into itemfile(itemcode,itemrate,qty_hand) values('i209',600,2);
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);
end;

select * from test_trg

insert into test_trg values(1,'225');

sys所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。

system用户用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。

创建视图

create view abc as select e.empno ,e.ename from emp e

1. 支持 SQL，在 PL/SQL 中可以使用：
  - 数据操纵命令
  - 事务控制命令
  - 游标控制
  - SQL 函数和 SQL 运算符
1. 支持面向对象编程 (OOP)
2. 可移植性，可运行在任何操作系统和平台上的Oralce 数据库
3. 更佳的性能，PL/SQL 经过编译执行，将整个语句块发送给 Oracle
4. 与 SQL 紧密集成，简化数据处理。
  - 支持所有 SQL 数据类型
  - 支持 NULL 值
  - 支持 %TYPE 和 %ROWTYPE 属性类型
1. 安全性，可以通过存储过程限制用户对数据的访问，触发器可以审核用户的操作

PL/SQL优势：

declare

abc varchar2(30); --声明变量

begin

select e.code --查询数据并且放入到变量abc中

into abc

from admininfo e;

dbms_output.put_line(abc);

exception

when no_data_found then --如果没有查询到数据打印异常

dbms_output.put_line('没有数据');

end;

打印乘法口诀表

begin

for i in 1..9

loop

for j in 1..i

loop

if (i*j<10) then

dbms_output.put(j||'*'||i||'='||i*j||' ');

else

dbms_output.put(j||'*'||i||'='||i*j||' ');

end if;

end loop;

dbms_output.put_line('');

end loop;

end;

游标

在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
1. %FOUND – SQL 语句影响了一行或多行时为 TRUE
2. %NOTFOUND – SQL 语句没有影响任何行时为TRUE
3. %ROWCOUNT – SQL 语句影响的行数
4. %ISOPEN - 游标是否打开，始终为FALSE

declare

topprice toys.toyprice%type;

cursor test is --定义游标的值

select t.toyprice from toys t where t.toyprice>200;

begin

open test;

loop

fetch test into topprice ;--把游标的值赋给变量

exit when test%notfound;--无值则退出循环

DBMS_OUTPUT.PUT_LINE

('TOYPRICE=:玩具单价=：'||topprice);

end loop;

close test;

end ;

1. REF 游标和游标变量

1）REF 游标和游标变量用于处理运行时动态执行的 SQL 查询

2）创建游标变量需要两个步骤：

声明 REF 游标类型

声明 REF 游标类型的变量

3）用于声明 REF 游标类型的语法为：

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

declare

type topprice is ref cursor

return toys%rowtype;

toys_curvar topprice ;--定义游标类型

toys_rec toys%rowtype;

begin

open toys_curvar for

select * from toys;--游标赋值

loop --循环从游标中取值

fetch toys_curvar into toys_rec;

exit when toys_curvar%notfound;

dbms_output.put_line(toys_rec.toyname);

end loop;

close toys_curvar;--先关闭循环后关闭游标

end;

子程序：

命名的 PL/SQL 块，编译并存储在数据库中。
子程序的各个部分：
1. 声明部分
2. 可执行部分
3. 异常处理部分(可选)
子程序的分类：
1. 过程－执行某些操作
2. 函数－执行操作并返回值

过程是完成特定功能的子程序

函数是可以返回值的命名的 PL/SQL 子程序

定义函数的限制：

1) 函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数

2) 形参不能是 PL/SQL 类型

3) 函数的返回类型也必须是数据库类型

访问函数的两种方式：

使用 PL/SQL 块

使用 SQL 语句

触发器

对DML语句修改的每个行执行一次

1、定义语句中包含FOR EACH ROW子句

2、在BEFORE……FOR EACH ROW触发器中，用户可以引用受到影响的行值。

CREATE OR REPLACE TRIGGER trgdemo

AFTER INSERT OR UPDATE OR DELETE or select

ON test_trg

BEGIN

IF UPDATING THEN

DBMS_OUTPUT.PUT_LINE('已更新 test_trg 中的数据');

ELSIF DELETING THEN

DBMS_OUTPUT.PUT_LINE('已删除 test_trg 中的数据');

ELSIF INSERTING THEN

DBMS_OUTPUT.PUT_LINE('已在 test_trg 中插入数据');

-- ElsIf selecting then

-- DBMS_OUTPUT.PUT_LINE('正在查询 test_trg 数据');

END IF;

END;