oracle存儲過程 loop字符串遍歷

declare?

創(chuàng)新互聯(lián)專注于西吉企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,成都做商城網(wǎng)站。西吉網(wǎng)站建設(shè)公司,為西吉等地區(qū)提供建站服務(wù)。全流程按需搭建網(wǎng)站，專業(yè)設(shè)計，全程項目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

x?varchar2(20);

v_length?int;

y?int;

v_str?varchar2(1);

begin

x:='wqr3331412rr';

select?length(x)?into?v_length?from?dual;

y:=1;

while?y=v_length?loop

select?substr(x,y,1)?into?v_str?from?dual;

dbms_output.put_line(v_str);

y:=y+1;??

end?loop;

end;

直接運行吧，結(jié)果如圖

oracle中如何定義一個游標(biāo),打開一個游標(biāo),并遍歷

--創(chuàng)建一個測試用表

CREATE TABLE test_main (

id INT,

value VARCHAR(10),

PRIMARY KEY(id)

);

--插入測試數(shù)據(jù)

INSERT INTO test_main(id, value) VALUES (1, 'ONE');

INSERT INTO test_main(id, value) VALUES (2, 'TWO');

INSERT INTO test_main(id, value) VALUES (3, 'THREE');

--游標(biāo)舉例

DECLARE

-- 定義游標(biāo).

CURSOR c_test_main IS

SELECT id, value FROM test_main;

-- 保存游標(biāo)數(shù)據(jù)的變量

v_main_data c_test_main%ROWTYPE;

BEGIN

-- 打開游標(biāo).

OPEN c_test_main;

LOOP

-- 填充數(shù)據(jù)(主表).

FETCH c_test_main INTO v_main_data;

-- 假如沒有檢索到(主表)數(shù)據(jù)，結(jié)束循環(huán)處理

Exit when c_test_main%NOTFOUND;

dbms_output.put_line(TO_CHAR(v_main_data.id)

|| ':' || v_main_data.value );

END LOOP;

-- 關(guān)閉游標(biāo)

CLOSE c_test_main;

END;

/

希望對你有幫助。

oracle如何實現(xiàn)遍歷查詢？

declare

teacher_name varchar(20)------------跟teacher表中老師名字類型保持一致

cursor t_name is select teachername from teacher---------申明游標(biāo)t_name為從teacher表中查詢老師名字

begin

open t_name;------打開游標(biāo)t_name

loop-------開始循環(huán)（遍歷）

fetch t_name into teacher_name-------將老師名字值賦予變量teacher_name

if t_name%found-------------開始遍歷有值時插入以下數(shù)據(jù)

then

select name,count(*) into new_table

from table_teacher_student

where name=teacher_name group by name-----將一個老師名字依據(jù)條件插入新表數(shù)據(jù)

else

dmbs_output.put_line(‘完成所有工作’)；---------遍歷結(jié)束時輸出完成工作

exit；

end if；

end loop;

倉促寫下以上內(nèi)容，可能部分語法報錯，思路就是這樣，很基本的一個游標(biāo)使用。

oracle中如何根據(jù)表中一個字段遍歷數(shù)據(jù)

寫個for循環(huán)就可以遍歷一遍，例如meminfo 表中有member_id 你現(xiàn)在有的id需要在meminfo 中查詢出現(xiàn)次數(shù)

declare

i number(5）；

id number(15);

begin

for rec in(select member_id from meminfo) loop

if member_id=id

then i:=i+1;

end if;

end;

這樣就會遍歷一遍你的這個數(shù)據(jù)庫

如何在oracle建一個存儲過程來遍歷數(shù)組，新手求解

SQL code

DECLARE

-- Define a varray of twelve strings.

TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);

-- Define an associative array of strings.

TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)

INDEX BY BINARY_INTEGER;

-- Declare and construct a varray.

month MONTHS_VARRAY :=

months_varray('January','February','March'

,'April','May','June'

,'July','August','September'

,'October','November','December');

-- Declare an associative array variable.

calendar CALENDAR_TABLE;

BEGIN

-- Check if calendar has no elements.

IF calendar.COUNT = 0 THEN

-- Print a title

DBMS_OUTPUT.PUT_LINE('Assignment loop:');

DBMS_OUTPUT.PUT_LINE('----------------');

-- Loop through all the varray elements.

FOR i IN month.FIRST..month.LAST LOOP

-- Initialize a null associative array element.

calendar(i) := '';

-- Print an indexed element from the associative array.

DBMS_OUTPUT.PUT_LINE(

'Index ['||i||'] is ['||calendar(i)||']');

-- Assign the numeric index valued varray element

-- to an equal index valued associative array element.

calendar(i) := month(i);

END LOOP;

-- Print a title

DBMS_OUTPUT.PUT(CHR(10));

DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');

DBMS_OUTPUT.PUT_LINE('---------------------');

-- Loop through all the associative array elements.

FOR i IN calendar.FIRST..calendar.LAST LOOP

-- Print an indexed element from the associative array.

DBMS_OUTPUT.PUT_LINE(

'Index ['||i||'] is ['||calendar(i)||']');

END LOOP;

END IF;

END;

oracle存儲過程 遍歷字符串

不用循環(huán)不行么，一個sql就搞定啦

select??c?from

(with?test?as?(select?'21,32,43'?c?from?dual)

select?substr(t.ca,instr(t.ca,?',',?1,?c.lv)?+?1,instr(t.ca,?',',?1,?c.lv?+?1)?-?(instr(t.ca,?',',?1,?c.lv)?+?1))?AS?c

from?(select?','?||?c?||?','?AS?ca,length(c?||?',')?-?nvl(length(REPLACE(c,?',')),0)?AS?cnt?FROM?test)?t,

(select?LEVEL?lv?from?dual?CONNECT?BY?LEVEL?=?100)?c?where?c.lv?=?t.cnt?)

'21,32,43'? --這個你換成你要查的字符串，數(shù)字字母什么都可以，只要逗號分隔就好