/* oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGINEND 存储过程名字2.SELECT INTO STATEMENT 将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条 记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子: BEGIN SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ...3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF;4.while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP;5.变量赋值 V_TEST := 123;6.用for in 使用cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END;7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER;8.用pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试*/
/* 函数用于返回特定数据,可以返回一个或多个值。 在一个函数中必须包含一个或多个RETURN 语句 函数调用是PL/SQL表达式的一部分,而过程调用可以是一个独立的PL/SQL语句 语法结构: Sql代码 CREATE [OR REPLACE] FUNCTION function_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) RETURN return_datatype AS |IS /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END [function_name]; 示例: Sql代码 CREATE OR REPLACE FUNCTION F_FORMATSXDATA(VI_VALUE IN VARCHAR2, VI_FORMSTR IN VARCHAR2) RETURN VARCHAR2 IS RESULT VARCHAR2(200); V_NUMBERTEMP NUMBER(11, 2); BEGIN IF VI_FORMSTR = '001' OR VI_FORMSTR = '003' OR VI_FORMSTR = '005' THEN RESULT := VI_VALUE; ELSIF VI_FORMSTR = '002' THEN RESULT := TO_CHAR(VI_VALUE, 'FM999999999990.00'); ELSIF VI_FORMSTR = '004' THEN V_NUMBERTEMP := TO_NUMBER(VI_VALUE, 'FM999999999990.0099') * 100; RESULT := TO_CHAR(V_NUMBERTEMP, 'FM999999999990.00'); END IF; RETURN(RESULT); END F_FORMATSXDATA;*/
/*
oracle 存储过程的基本语法
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGINEND 存储过程名字2.SELECT INTO STATEMENT
将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子:
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;5.变量赋值
V_TEST := 123;6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试*/
/*
函数用于返回特定数据,可以返回一个或多个值。 在一个函数中必须包含一个或多个RETURN 语句 函数调用是PL/SQL表达式的一部分,而过程调用可以是一个独立的PL/SQL语句 语法结构: Sql代码 CREATE [OR REPLACE] FUNCTION function_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) RETURN return_datatype AS |IS /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END [function_name]; 示例: Sql代码 CREATE OR REPLACE FUNCTION F_FORMATSXDATA(VI_VALUE IN VARCHAR2, VI_FORMSTR IN VARCHAR2) RETURN VARCHAR2 IS RESULT VARCHAR2(200); V_NUMBERTEMP NUMBER(11, 2); BEGIN IF VI_FORMSTR = '001' OR VI_FORMSTR = '003' OR VI_FORMSTR = '005' THEN RESULT := VI_VALUE; ELSIF VI_FORMSTR = '002' THEN RESULT := TO_CHAR(VI_VALUE, 'FM999999999990.00'); ELSIF VI_FORMSTR = '004' THEN V_NUMBERTEMP := TO_NUMBER(VI_VALUE, 'FM999999999990.0099') * 100; RESULT := TO_CHAR(V_NUMBERTEMP, 'FM999999999990.00'); END IF; RETURN(RESULT); END F_FORMATSXDATA;*/
http://www.qqread.com/oracle/2009/07/b470404.html