oracle存储过程

注意：声明处不能赋初始值，在begin end里赋值。1、无参，最简单存储过程。存储过程语句（求总薪资）Oracle存储过程。

终有一刻

4395人浏览 · 2024-10-12 23:21:07

终有一刻 · 2024-10-12 23:21:07 发布

Oracle存储过程
1、无参，最简单存储过程

CREATE OR REPLACE PROCEDURE HELLO_WORLD IS  
BEGIN  
    DBMS_OUTPUT.PUT_LINE('Hello, World!');  
END HELLO_WORLD;

输出

Hello, World!

2、输出查询语句
创建源数据
在这里插入图片描述

存储过程语句（求总薪资）

CREATE OR REPLACE PROCEDURE sum_salary
is
v_sum salary.salary%TYPE:=0;
CURSOR CURSOR_p IS SELECT salary FROM salary;
BEGIN 
	FOR v_cursor IN CURSOR_p 
	LOOP
		v_sum:=v_sum+v_cursor.salary;
	END LOOP;
	 DBMS_OUTPUT.PUT_LINE('v_sum:'||v_sum);  
END;

调用存储过程

BEGIN 
sum_salary; 
END;

命令行调用

exec  zhang.sum_salary;

输出结果

v_sum:71000

SQL> exec  zhang.sum_salary; 
v_sum:71000

有参数调用
建表源数据
在这里插入图片描述
存储过程（有参数）

CREATE OR REPLACE PROCEDURE sum_salary
is
v_sum salary.salary%TYPE:=0;
CURSOR CURSOR_p IS SELECT salary FROM salary;
BEGIN 
	FOR v_cursor IN CURSOR_p LOOP
		v_sum:=v_sum+v_cursor.salary;
	END LOOP;
	 DBMS_OUTPUT.PUT_LINE('v_sum:'||v_sum);  
END;

调用存储过程1

DECLARE 
inp_continent PEOPLE.continent%TYPE;
resultpo PEOPLE.POPULATION%TYPE;
BEGIN
	inp_continent:='Europ';
	sum_salary_1(inp_continent,resultpo);
 	DBMS_OUTPUT.PUT_LINE('result:'||resultpo); 
END;

输出结果

result:14000

调用存储过程2

DECLARE 
inp_continent PEOPLE.continent%TYPE;
resultpo PEOPLE.POPULATION%TYPE;
BEGIN
	inp_continent:='EUROP';
	sum_salary_1(inp_continent,resultpo);
 	DBMS_OUTPUT.PUT_LINE('result:'||resultpo); 
END;

输出结果

result:40000

3、调用方式
CALL PROCEDURE_NAME: 是SQL标准的一部分，适用于在SQL命令行工具中调用存储过程,在调用存储过程时，可以传递输入参数和输出参数。
BEGIN …END :块是PL/SQL中的一种结构，提供了更大的灵活性和功能，适用于需要在调用存储过程的同时执行其他PL/SQL代码的情况,调用存储过程时，同样需要使用括号传递参数
EXECUTE PROCEDURE_NAME:是Oracle特有的，用于执行存储过程或函数，通常用于简单的存储过程调用,在使用EXECUTE语句时，可以传递输入参数，但通常不用于获取输出参数（尽管技术上可能）

存储过程（输入参数）

CREATE OR replace PROCEDURE create_table_de
IS
sql_statement varchar(300);
BEGIN
	sql_statement:='CREATE  TABLE user_info24(id NUMBER,name varchar(20),address varchar(20))';
	EXECUTE IMMEDIATE sql_statement;
DBMS_OUTPUT.PUT_LINE('TABLE CREATED SUCCESSFULLY!');
EXCEPTION 
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用

CALL create_table_de();
TABLE CREATED SUCCESSFULLY!

创建插入数据存储过程

CREATE OR REPLACE PROCEDURE insert_tb(
table_name IN VARCHAR2,
column_values IN VARCHAR2
)
IS
sql_statament varchar(200);
BEGIN
	sql_statament:='INSERT INTO'||table_name||'values('||column_definitions||')';
	execute immediate sql_statament;
	DBMS_OUTPUT.PUT_LINE('INSERT SUSCESSFULLY!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用

CALL insert_tb('user_info24','5,''zhang'',''guangzhou''');

注意：字段值使用的是两个单引号

4、select col into exp 使用select into 复制并输出

CREATE OR REPLACE PROCEDURE avg_score
IS 
AVG_SCORE T_SCORE.SCORE%TYPE;
BEGIN 
	select avg(score) into AVG_SCORE FROM T_SCORE;
	dbms_output.put_line('total avg:'||AVG_SCORE);
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用

CALL avg_score();
total avg:94
querry successfully!

5 使用记录型变量，获取某行的数据

CREATE OR REPLACE PROCEDURE ROW_NUM_INFO
IS 
SCORE T_SCORE%ROWTYPE;
BEGIN 
	select * INTO SCORE FROM T_SCORE WHERE STUDENT_ID=2;
	dbms_output.put_line('SUBJECT:'||SCORE.SUBJECT||',STU_ID:'||SCORE.STUDENT_ID||',SCORE:'||SCORE.SCORE);
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用

CALL ROW_NUM_INFO()
SUBJECT:语文   ,STU_ID:2        ,SCORE:85
querry successfully!

6 根据学生ID查询分数，查询赋值

CREATE OR REPLACE PROCEDURE id_score(id T_SCORE.STUDENT_ID%TYPE )
IS 
SCORE T_SCORE.SCORE%TYPE;
BEGIN 
	select score into SCORE FROM T_SCORE WHERE STUDENT_ID=id;
	dbms_output.put_line('score:'||SCORE);
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用及结果

CALL id_score(6);
score:96
querry successfully!

7、in out 可变参数

CREATE OR REPLACE PROCEDURE inpnum(inpNUMBER IN OUT number)
IS 
BEGIN 
	dbms_output.put_line('number:'||inpNUMBER);
	inpNUMBER:=20;
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DB

调用及结果

DECLARE 
num NUMBER:=12;
BEGIN
inpnum(num);
dbms_output.put_line('number:'||num);
END;

querry successfully!
number:12
querry successfully!
number:20

8 IF END 使用

CREATE OR REPLACE PROCEDURE iftest(inpNUMBER IN number)
IS
stu_id t_score.STUDENT_ID%TYPE;
BEGIN 
	IF (inpNUMBER=1) THEN 
	SELECT student_id INTO stu_id FROM T_SCORE WHERE student_id=inpNUMBER;
	elsif(inpNUMBER=2) THEN 
	SELECT student_id INTO stu_id FROM T_SCORE WHERE student_id=inpNUMBER;
	ELSE
	SELECT student_id INTO stu_id FROM T_SCORE WHERE student_id=3;
	END IF;
	DBMS_OUTPUT.PUT_LINE('student_id: '||stu_id);
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用及结果

CALL iftest(1)
student_id: 1        
querry successfully!
CALL iftest(2)
student_id: 2        
querry successfully!
CALL iftest(4)
student_id: 3        
querry successfully!
CALL iftest(6)
student_id: 3        
querry successfully!

9、循环 while loop end loop

CREATE OR REPLACE PROCEDURE whiletest
IS
id t_score.STUDENT_ID%TYPE;
v_score t_score.score%TYPE;
BEGIN
	id:=1;
	WHILE id<7 loop
	SELECT score INTO v_score FROM T_SCORE WHERE student_id=id;
	DBMS_OUTPUT.PUT_LINE('student_id: '||id||'student_score:'||v_score);
	id:=id+1;
END LOOP;
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用及输出

CALL whiletest();
student_id: 1        student_score:99
student_id: 2        student_score:85
student_id: 3        student_score:100
student_id: 4        student_score:96
student_id: 5        student_score:88
student_id: 6        student_score:96
querry successfully!

10 循环 LOOP

CREATE OR REPLACE PROCEDURE looptest
IS
id t_score.STUDENT_ID%TYPE;
v_score t_score.score%TYPE;
BEGIN
	id:=1;
	LOOP
	SELECT score INTO v_score FROM T_SCORE WHERE student_id=id;
	DBMS_OUTPUT.PUT_LINE('student_id: '||id||'student_score:'||v_score);
	id:=id+1;
	EXIT when(id>6);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用及结果

CALL looptest()
tudent_id: 1        student_score:99
student_id: 2        student_score:85
student_id: 3        student_score:100
student_id: 4        student_score:96
student_id: 5        student_score:88
student_id: 6        student_score:96
querry successfully!

11 循环for

CREATE OR REPLACE PROCEDURE fortest
IS
id t_score.STUDENT_ID%TYPE;
v_score t_score.score%TYPE;
BEGIN
	id:=1;
	FOR id IN 1..6 LOOP
	SELECT score INTO v_score FROM T_SCORE WHERE student_id=id;
	DBMS_OUTPUT.PUT_LINE('student_id: '||id||'student_score:'||v_score);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('querry successfully!');
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED:'||SQLERRM);
END;

调用及结果

CALL fortest();
student_id: 1student_score:99
student_id: 2student_score:85
student_id: 3student_score:100
student_id: 4student_score:96
student_id: 5student_score:88
student_id: 6student_score:96
querry successfully!