自己学存储过程做的笔记

来源：互联网发布：音质好头戴式耳机知乎编辑：程序博客网时间：2024/04/27 21:03

create or replace procedure DEMO is

* created by zhangbo

* date:2008/04/01

* 主要的数据类型转换函数:to_char,to_date,to_number

* like运算符用来对字符串进行模式匹配:_精确匹配单个字符，%匹配零个字符或多个字符

* goto语句：goto 标签<<标签名称>> note:1。外层跳转到内层是非法的

* ----------------------------------------2。从If子句跳转到另一个子句中也是非法的

* ----------------------------------------3。从异常处理块内部跳转到当前快也是非法的

* ----------------------------------------4。循环本身是可以设置标签的

* 显式的指明不进行任何操作：null 语句

* 事务处理：提交commit保存savepoint;回滚rollback

* 函数部分

*CHR() ASCII()互为反函数

*CONCAT() 等同于||

*INITCAP() 每个单词的第一个字符大写后返回结果

*LOWER() 小写

*UPPER() 大写

*LPAD() 在字符串左边补充指定字符，如果没有指定，默认空格

*RPAD()

*LTRIM() 删除指定字符，默认空格

*RTRIM()

*NLS_INITCAP()

*NLS_LOWER()

*NLS_UPPER()

*REAPLACE() 替换指定的字符串

*SOUNDEX()

*SUBSTR()

*SUBSTRB()

*TRANSLATE() 与replace()有区别

*ASCII()

*INSTR() 查找指定字符串在当前字符串中的位置，参数为正，从左边开始，参数为负，从右边开始

*INSTRB()

*LENGTH() 返回指定字符串的长度

*LENGTHB()

*NLSSORT()

*ABS()

*ACOS()

*ASIN()

*ATAN()

*ATAN2()

*CELL() 返回大于或等于的最小整数

*COS()

*COSH()

*EXP()

*FLOOR()

*LN()

*LOG()

*MOD()

*POWER()

*ROUND()

*SIGN()

*SIN()

*SINH()

*SQRT()

*TAN()

*TANH()

*TRUNC()

* 日期函数

* add_months()

* last_day()

* months_between()

* new_time()

* next_day()

* round()

* sysdate()

* trunc()

* 转换函数

* chartorowid()

* convert()

* hextoraw()

* rawtohex()

* rowidtochar()

* to_char()

* to_date()

* to_label()

* to_mutli_byte()

* to_number()

* to_single_byte()

* 分组函数

* avg()

* count()

* glb()

* lub()

* max()

* min()

* stddev()

* sum()

* variance

* 其他函数

* bfilename()

* decode()

* dump()

* empty_clob()/empty_blob()

* greatest()

* greatest_lb()

* least()

* least_ub()

* nvl()

* uid()

* user()

* userenv()

* vsize()

* 游标

* %found %notfound %isopen %rowcount

* 参数化游标

* no_date_found 仅仅被select ...into 语句触发

* %notfound 显示游标/update/insert

* 子程序

* 如果对子程序有很多参数要定义，可以考虑定义一个记录

* 包

* 包头包主体

v_ErrorCode number; --//错误代码

v_ErrorMsg varchar2(200); --//错误信息

v_CurrentUser varchar2(8); --//当前用户

v_Information varchar2(100); --//信息

v_Date varchar(20); --//日期

v_loopCounter number := 1; --//计数器

v_FirstName varchar(4000); --//姓

v_LastName varchar(4000); --//名

v_Data dbms_output.chararr; --//用于dbms_output.get_lines(v_data,v_lines)

/* subtype v_loopCounterII is number; --//用户自定义子类型

v_loop v_loopCounterII;*/

--//定义游标

Cursor c_product_info is

select *

from product_information;

v_productInfo product_information%rowtype ; --//定义表结构相同类型

v_productName product_information.product_name%type; --//定义与表中某列所拥有的一切

--//声明pl/sql表

type t_testtable is table of varchar2(10)

index by binary_integer;

Type t_date is table of date

index by binary_integer;

Type t_product is table of product_information%RowType

index by binary_integer;

v_product t_product;

Type t_number is table of number

index by binary_integer;

v_number t_number;

v_temp t_number;

v_total number;

v_Counter number;

--//定义序列

v_pro_id number;

--//参数化游标

/* v_productid product_information.product_id%type;

v_productN product_information.product_name%type;*/

--//参数化游标

Cursor c_product( p_productid product_information.product_id%type,

p_productN product_information.product_name%type) is

select * from product_information

where product_id = p_productid

and product_name = p_productN;

--//声明游标变量

type t_productREF is ref Cursor

return product_information%rowtype;

begin

--//参数化游标

open c_product('11','11');

--//利用定义的Pl/sql表

for v_Counter in 1..50 loop

v_number(v_Counter):= v_Counter;

end loop;

v_total := v_number.count;

dbms_output.put_line(v_total);

v_number.delete(20);

--//v_number:=v_temp;

如果需要删除整个表，可以将一个空表赋值给它

<<lineOne>> --//标签

for v_Counter in 1..50 loop

if v_Counter=20 then

--goto lineOne; --//引用标签

null;

else

dbms_output.put_line(v_number(v_counter));

end if;

end loop;

dbms_output.put_line('begin output:');

dbms_output.put_line(v_number.first);

dbms_output.put_line(v_number.last);

dbms_output.put_line('end output');

dbms_output.put_line('ip address');

dbms_output.put_line(dbms_standard.client_ip_address);

dbms_output.put_line('ip address');

--//将原标扩展为原来的两倍

--insert into product_information select * from product_information;

--commit;

--//测试序列SEQ_PRO_ID

dbms_output.put_line('测试序列SEQ_PRO_ID');

--select seq_pro_id.nextval into v_pro_id from dual;

dbms_output.put_line(v_pro_id);

-- select seq_pro_id.currval into v_pro_id from dual;

dbms_output.put_line(v_pro_id);

--//事务处理示例

*insert 1

*insert 2

*savepoint A;

*select sysdate from dual;

*savepoint B;

*select seq_pro_id.currval from dual;

*savpoint C;

*Exception

* when others then

* rollback to B;

--//行标识符

--//select rowid from product_information;

--select * into v_product from product_information where PRODUCT_ID=1;

select to_char(sysdate, 'yyyyMMdd') into v_Date from dual;

--//如果没有发现数据

if sql%notfound then

select to_char(sysdate, 'yyyyMMdd') into v_Date from dual;

end if;

--//循环的两种用法

loop

select to_char(sysdate, 'yyyyMMdd') into v_Date from dual;

exit when v_loopCounter <= 50;

end loop;

--//reverse关键字从最大值向最小值进行迭代

for v_loopCounter in reverse 1 .. 50 loop

select to_char(sysdate, 'yyyyMMdd') into v_Date from dual;

end loop;

--//if的用法

/* if then

elsif

else

end if;*/

--//游标--用来处理从数据库检索的多个行

begin

--//打开游标

open c_product_info;

loop

fetch c_product_info into v_productInfo;

dbms_output.put_line(v_productInfo.Product_Name);

exit when c_product_info%notfound;

end loop;

--//关闭游标

close c_product_info;

Exception

when others then

v_ErrorCode := sqlcode; --//错误代码

end;

--//dump 内置函数用来监测某一数据库列的确切内容

--//select dump(PRODUCT_NAME) from product_information;

--//异常

Exception

when others then

v_ErrorCode := sqlcode; --//错误代码

v_ErrorMsg := sqlerrm; --//错误信息

v_CurrentUser := user; --//当前用户

v_Information := 'Error encountered on ' || to_char(sysdate) ||

'by database user ' || v_CurrentUser;

--//dbms_output包用于调试输出

dbms_output.put_line(v_information);

end DEMO;