全面解析Oracle Procedure 基本语法

2024-03-01 06:12:03 57

关键字:oracle存储过程

1.基本结构

CREATEORREPLACEPROCEDURE存储过程名字
(
参数1INNUMBER,
参数2INNUMBER
)IS
变量1INTEGER:=0;
变量2DATE;
BEGIN

END存储过程名字

2.SELECTINTOSTATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN
SELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
xxxx;
END;
...

3.IF判断

IFV_TEST=1THEN
BEGIN
dosomething
END;
ENDIF;

4.while循环

WHILEV_TEST=1LOOP
BEGIN
XXXX
END;
ENDLOOP;

5.变量赋值

V_TEST:=123;

6.用forin使用cursor

...
IS
CURSORcurISSELECT*FROMxxx;
BEGIN
FORcur_resultincurLOOP
BEGIN
V_SUM:=cur_result.列名1+cur_result.列名2
END;
ENDLOOP;
END;

7.带参数的cursor

CURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;
OPENC_USER(变量值);
LOOP
FETCHC_USERINTOV_NAME;
EXITFETCHC_USER%NOTFOUND;
dosomething
ENDLOOP;
CLOSEC_USER;

8.用pl/sqldeveloperdebug

连接数据库后建立一个TestWINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

转载：

oracle存储过程

关键字:oracle存储过程

存储过程创建语法：

createorreplaceprocedure存储过程名（param1intype，param2outtype）

变量1类型（值范围）;

变量2类型（值范围）;

Begin
Selectcount(*)into变量1from表Awhere列名=param1；
If(判断条件)then
Select列名into变量2from表Awhere列名=param1；
Dbms_output。Put_line(‘打印信息');
Elsif(判断条件)then
Dbms_output。Put_line(‘打印信息');
Else
Raise异常名（NO_DATA_FOUND）;
Endif;
Exception
Whenothersthen
Rollback;
End;

注意事项：

1，存储过程参数不带取值范围，in表示传入，out表示输出

2，变量带取值范围，后面接分号

3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，用select。。。into。。。给变量赋值

5，在代码中抛异常用raise+异常名

以命名的异常

命名的系统异常产生原因

ACCESS_INTO_NULL未定义对象
CASE_NOT_FOUNDCASE中若未包含相应的WHEN，并且没有设置
ELSE时
COLLECTION_IS_NULL集合元素未初始化
CURSER_ALREADY_OPEN游标已经打开
DUP_VAL_ON_INDEX唯一索引对应的列上有重复的值
INVALID_CURSOR在不合法的游标上进行操作
INVALID_NUMBER内嵌的SQL语句不能将字符转换为数字
NO_DATA_FOUND使用selectinto未返回行，或应用索引表未初始化的
TOO_MANY_ROWS执行selectinto时，结果集超过一行
ZERO_DIVIDE除数为0
SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或VARRAY的最大值
SUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或VARRAY时，将下标指定为负数
VALUE_ERROR赋值时，变量长度不足以容纳实际数据
LOGIN_DENIEDPL/SQL应用程序连接到oracle数据库时，提供了不
正确的用户名或密码
NOT_LOGGED_ONPL/SQL应用程序在没有连接oralce数据库的情况下
访问数据
PROGRAM_ERRORPL/SQL内部问题，可能需要重装数据字典＆pl./SQL
系统包
ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量的返回类型不兼容
SELF_IS_NULL使用对象类型时，在null对象上调用对象方法
STORAGE_ERROR运行PL/SQL时，超出内存空间
SYS_INVALID_ID无效的ROWID字符串
TIMEOUT_ON_RESOURCEOracle在等待资源时超时

语法及示例：

1、存储过程创建存储过程的语法：

CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter_list)]{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[procedure_name];

其中：procedure_name是过程的名称。

parameter_list是参数列表。

local_declarations是局部声明。

executable_statements是可执行语句。

exception_handlers是异常处理程序。

示例1：

演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、INOUT参数赋予默认值)

createorreplaceprocedurefind_emp(emp_noinnumber:=7900)asempnamevarchar2(20);beginselectenameintoempnamefromempwhereempno=emp_no;dbms_output.put_line('雇员姓名是'||empname);exceptionwhenno_data_foundthendbms_output.put_line('雇员编号未找到');endfind_emp;

调用过程：

EXECUTEprocudure_name(parameters_list);

也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。

示例2：演示创建带OUT参数的过程

createorreplaceproceduretest(value1varchar2,value2outnumber)isidentitynumber;beginselectsalintoidentityfromempwhereempno=value1;ifidentity<2000thenvalue2:=1000;elsevalue2:=500;endif;end;

调用带OUT参数的过程：

declarevalue2number;begintest('7900',value2);dbms_output.put_line(value2);end;

示例3：

演示创建带INOUT参数的过程

createorreplaceprocedureswap(p1inoutnumber,p2inoutnumber)isv_tempnumber;beginv_temp:=p1;p1:=p2;p2:=v_temp;end;

调用带INOUT参数的过程：

declarenum1number:=100;num2number:=200;beginswap(num1,num2);dbms_output.put_line('num1='||num1);dbms_output.put_line('num2='||num2);end;

示例4：将过程的执行权限授予其他用户

GRANTEXECUTEONfind_empTOscott;GRANTEXECUTEONswapTOPUBLIC;

将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。

删除过程语法：

DROPPROCEDUREprocudure_name;

2、函数定义函数的语法如下：

CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter_list)]RETURNdatatype{IS|AS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[function_name];

其中：function_name是函数的名称。

parameter_list是参数列表。

local_declarations是局部声明。

executable_statements是可执行语句。

exception_handlers是异常处理程序。

使用函数时注意：形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型。函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。

示例5：

演示如何创建函数

createorreplacefunctionfun_helloreturnvarchar2isbeginreturn'朋友，您好';end;

调用函数：

selectfun_hellofromdual;

函数的授权：同过和的授权一样具体请看示例4。

删除函数：

DROPFUNCTIONfunction_name

过程和函数的差异过程函数作为PL/SQL语句执行作为表达式的一部分调用在规范中不包含RETURN子句必须在规范中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN语句

3、程序包创建包规范的语法：

CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publictypeanditemdeclarations][Subprogramspecifications]END[package_name];

其中：package_name是包的名称。

Publictypeanditemdeclarations是声明类型、常量、变量、异常和游标等。Subprogramspecifications声明PL/SQL子程序。

示例6：

演示创建程序包规范

createorreplacepackagepack_opisprocedurepro_print_ename(idnumber);procedurepro_print_sal(idnumber);functionfun_re_date(idnumber)returndate;end;

创建包主体的语法：

CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Publictypeanditemdeclarations][Subprogrambodies][BEGINInitialization_statements]END[package_name];

其中：package_name是包的名称。

Publictypeanditemdeclarations是声明类型、常量、变量、异常和游标等。

Subprogrambodies是定义公共和私有PL/SQL子程序。

示例7：演示创建程序包主体

createorreplacepackagebodypack_opisprocedurepro_print_ename(idnumber)isnameemp.ename%type;beginselectenameintonamefromempwhereempno=id;dbms_output.put_line('职员姓名：'||name);endpro_print_ename;procedurepro_print_sal(idnumber)issalaryemp.sal%type;beginselectsalintosalaryfromempwhereempno=id;dbms_output.put_line('职员工资：'||salary);endpro_print_sal;functionfun_re_date(idnumber)returndateisbedateemp.hiredate%type;beginselecthiredateintobedatefromempwhereempno=id;returnbedate;endfun_re_date;endpack_op;

示例8：调用程序包中创建的过程和函数

execpack_op.pro_print_ename(7900);execpack_op.pro_print_sal(7900);selectpack_op.fun_re_date(7900)fromdual;

示例9：演示程序包中的游标创建包规范

createorreplacepackagepack_empiscursorcur_empreturnemp%rowtype;procedurepro_cur;endpack_emp;

创建包主体

createorreplacepackagebodypack_empiscursorcur_empreturnemp%rowtypeisselect*fromemp;procedurepro_curisrec_empemp%rowtype;beginopencur_emp;loopfetchcur_empintorec_emp;exitwhencur_emp%notfound;ifrec_emp.sal<1000thendbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');elsifrec_emp.sal>=1000andrec_emp.sal<2000thendbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');elsedbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');endif;endloop;endpro_cur;endpack_emp;

调用程序包中的过程以调用程序包中的游标

execpack_emp.pro_cur;

示例10：存储过程返回游标的子程序包(此程序包返回r_cur游标)

CREATEORREPLACEpackageSCOTT.pk_wtistypemytypeisrefcursor;procedurep_wt(mycsoutmytype);end;
CREATEORREPLACEpackageBODYSCOTT.pk_wtisprocedurep_wt(mycsoutmytype)isr_curmytype;beginopenr_curforselect*fromemp;mycs:=r_cur;endp_wt;endpk_wt;

查询有关过程、函数和程序包的信息：

USER_OBJECTS数据字典视图columnobject_nameformata18selectobject_name,object_typefromuser_objectswhereobject_typein('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');

以上所述是小编给大家介绍的OracleProcedure知识，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

全面解析Oracle Procedure 基本语法

热门推荐

随机推荐