Oracle数据库的函数，存储过程，程序包，游标，触发器

--创建不带参数函数，返回t_book中书的数量
create function getBookCount return number as
begin
  declare book_count number;
  begin
    select count(*) into book_count from t_book;
    return book_count;
  end;
end getBookCount;

--函数调用
set serveroutput on;
begin
  dbms_output.put_line('表t_book中有'||getBookCount()||'本书');
end;

--创建带参数函数，查找某个表的记录数
create function getTableCount(table_name varchar2) return number as
begin
  declare recore_count number;
          query_sql varchar2(300);--定义sql语句
  begin
    query_sql:='select count(*) from '||table_name;
    --execute immediate：立即执行该SQL语句
    execute immediate query_sql into recore_count;
    return recore_count;
  end;
end getTableCount;

--函数调用
set serveroutput on;
begin
  dbms_output.put_line('表中有'||getTableCount('t_book_log')||'条数据');
end;

CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)
/* 参数、指定返回类型 */
RETURN varchar2
AS
/* 定义局部变量 */
min_price NUMBER;
max_price NUMBER;
BEGIN
SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price
FROM itemfile;
IF price >= min_price AND price <= max_price THEN
RETURN '输入的单价介于最低价与最高价之间';
ELSE
RETURN '超出范围';
END IF;
END;

匿名块执行函数
p NUMBER := 300;
MSG varchar2(200);
BEGIN
MSG := item_price_range(p);
DBMS_OUTPUT.PUT_LINE(MSG);
END;

SELECT查询调用（因为函数必须有返回值）
SELECT myfunction FROM dual;

create [or replace] procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;
变量2 类型（值范围）;
Begin
    Select count(*) into 变量1 from 表A where列名=param1；
 
    If (判断条件) then
       Select 列名 into 变量2 from 表A where列名=param1；
       Dbms_output.Put_line(‘打印信息’);
    Elsif (判断条件) then
       Dbms_output.Put_line(‘打印信息’);
    Else
       Raise 异常名（NO_DATA_FOUND）;
    End if;
Exception
    When others then
       Rollback;
End;

-- Created on 2018/9/9 by LENOVO 
--声明变量，声明一个人的姓名，薪水，地址
 
declare 
  -- Local variables here
  --姓名
  v_name varchar2(50) :='张三丰';
  --薪水
 /* v_salary number(6,2);*/
 v_salary emp.ename%type;
  --地址
  v_address varchar2(100);
  
  v_stu student%rowtype;
  
  
begin
  -- Test statements here
  --dbms_output.put_line('hello world');
   v_salary:=1500;
   --语句赋值操作。

-- Created on 2018/9/9 by LENOVO 
--声明变量，声明一个人的姓名
 
declare 
  -- Local variables here
  --姓名
  v_name varchar2(50) ';
  
begin
v_name:=:='张三丰;
dbms_output.put_line('姓名---〉'||v_name);
  
end;

declare
vcc number;
begin
vcc:=1;
loop
  exit when vcc>10;
  dbms_output.put_line(vcc);
  vcc:=vcc+1;
  end loop;
  end; 

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
    --执行体
END 存储过程名字;

BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    xxxx;
END;

IF V_TEST = 1 THEN
   BEGIN
      do something
   END;
 END IF;

WHILE V_TEST=1 LOOP
BEGIN
  XXXX
END;
END LOOP;

V_TEST := 123;

declare 
      realsal emp.sal%type; 
      realname varchar(40); 
      realjob varchar(40); 
begin   //过程调用开始 
      realsal:=1100; 
      realname:=''; 
      realjob:='CLERK'; 
      runbyparmeters(realsal,realname,realjob);－－必须按顺序 
      DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB); 
END;  //过程调用结束

declare 
     realsal emp.sal%type; 
     realname varchar(40); 
     realjob varchar(40); 
begin    //过程调用开始 
     realsal:=1100; 
     realname:=''; 
     realjob:='CLERK'; 
     －－指定值对应变量顺序可变 
     runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);          
    DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB); 
END;  //过程调用结束

create or replace procedure NoParPro 
 as  //声明 
 ; 
 begin // 执行 
 ; 
 exception//存储过程异常 
 ; 
 end;

create or replace procedure queryempname(sfindno emp.empno%type)  
as 
   sName emp.ename%type; 
   sjob emp.job%type; 
begin 
       .... 
exception 
       .... 
end;

create or replace procedure runbyparmeters   
    (isal in emp.sal%type,  
     sname out varchar, 
     sjob in out varchar) 
 as  
    icount number; 
 begin 
      select count(*) into icount from emp where sal>isal and job=sjob; 
      if icount=1 then 
        .... 
      else 
       .... 
     end if; 
exception 
     when too_many_rows then 
     DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
     when others then 
     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); 
end;

--创建存储过程，在t_book表中插入数据，且判断插入的数据是否已经存在
create procedure addBook(bName in varchar2,typeid in number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    commit;
  end;
end addBook;

--调用存储过程
execute addBook('vs',1);

--创建存储过程，在t_book表中插入数据，输出插入前后数据条数
create procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    --n1存储执行前数据条数
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      select count(*) into n2 from t_book;
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    --n2存储执行后数据条数
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook;

--返回存储执行前后数据条数
declare n1 number;
        n2 number;
begin
  addBook('c+++',1,n1,n2);
  dbms_output.put_line('n1='||n1||',n2='||n2);
end;

CREATE OR REPLACE PROCEDURE test (value IN varchar2, value2 OUT NUMBER)
/* 参数，不需指定长度或精度 */
IS
/* 局部变量，省略 DECLARE 关键字，需有长度 */
identity NUMBER;
BEGIN
SELECT ITEMRATE INTO identity
FROM itemFile
WHERE itemcode = value;
IF identity < 200 THEN
value2 := 200;
ELSE
value2 :=50;
END IF;
END;



匿名块执行过程
tvalue2 NUMBER;
BEGIN
test('i202', tvalue2);
DBMS_OUTPUT.PUT_LINE('value2的值为：' || TO_CHAR(value2));
END;

单独执行
EXECUTE myproc('0001');

--创建包，放置存储过程、函数
create package pkg_book as
       function getBookCount return number;
       procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out number);
end pkg_book;


--创建包体，实现方法
create package body pkg_book as
function getBookCount return number as
begin
  declare book_count number;
  begin
    select count(*) into book_count from t_book;
    return book_count;
  end;
end getBookCount;

procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    --n1存储执行前数据条数
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      select count(*) into n2 from t_book;
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    --n2存储执行后数据条数
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook;
end pkg_book;


--调用
begin
  dbms_output.put_line('表中有'||pkg_book.getBookCount||'条记录');
end;

 --声明变量接受游标中的数据。
  v_name student.name%type;
 
  v_num student.age%type;
  begin
  --打开游标
  open cur;
  --遍历取值
  loop
  --获取游标中德数据，如果有则赋值变量，否则退出
  fetch cur  into v_name v_num;
 
  exit when cur%notfound dbms_output.put_line('姓名:' || v_name || '年龄:' ||v_num);
 
end loop;
 
--关闭游标
close cur;

--显示cursor的处理
declare 
---声明cursor,创建和命名一个sql工作区
cursor cursor_name is 
    select real_name from account_hcz;
    v_realname varchar2(20);
begin
    open cursor_name;---打开cursor,执行sql语句产生的结果集
    fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
    dbms_output.put_line(v_realname);
    close cursor_name;--关闭cursor
end;

DECLARE
/* 定义带参数游标 */
CURSOR cur_para(id varchar2) IS
SELECT books_name FROM books WHERE books_id = id;
BGEIN
/* 调用带参数游标，并以 FOR 循环方式处理 */
FOR cur IN cur_para('0001') LOOP
DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
END LOOP;
END;

as //定义(游标一个可以遍历的结果集)  
CURSOR cur_1 IS  
  SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn, 
         SUM(usd_amt)/10000 usd_amt_sn  
  FROM BGD_AREA_CM_M_BASE_T  
  WHERE ym >= vs_ym_sn_beg  
       AND ym <= vs_ym_sn_end  
  GROUP BY area_code,CMCODE;  
 
begin //执行（常用For语句遍历游标）      
FOR rec IN cur_1 LOOP  
  UPDATE xxxxxxxxxxx_T  
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn  
   WHERE area_code = rec.area_code  
   AND CMCODE = rec.CMCODE  
   AND ym = is_ym;  
END LOOP;

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
FETCH C_USER INTO V_NAME;
  EXIT WHEN FETCH C_USER%NOTFOUND;
CLOSE C_USER;

BEGIN
FROM cur IN (SELECT name FROM deptment) LOOP
DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
END LOOP;
END;

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。