` 형식을 사용합니다.
다음 테이블에서 예제를 찾을 수 있습니다.
| Oracle 문 | MySQL 문 |
| --- | --- |
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_VARIABLE_01
(p_state IN NUMBER)
AS
l_dept_id NUMBER := 1;
BEGIN
FOR cur IN
(WITH dept_empl(id, name, surname,
lastname, state, dept_id)
AS
(
SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = p_state AND
dept_id = l_dept_id)
SELECT id,state
FROM dept_empl
ORDER BY id) LOOP
NULL;
END LOOP;
| CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_VARIABLE_01(IN par_P_STATE DOUBLE)
BEGIN
DECLARE var_l_dept_id DOUBLE DEFAULT 1;
DECLARE var$id VARCHAR (8000);
DECLARE var$state VARCHAR (8000);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT
ID, STATE
FROM (SELECT
ID, NAME, SURNAME, LASTNAME, STATE, DEPT_ID
FROM TEST_ORA_PG.DEPT_EMPLOYEES
WHERE STATE = par_p_state AND DEPT_ID = var_l_dept_id) AS dept_empl
ORDER BY ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$id, var$state;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
|
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_REGULAR_MULT_01
AS
BEGIN
FOR cur IN (
WITH dept_empl AS
(
SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = 1),
dept AS
(SELECT id deptid, parent_id,
name deptname
FROM test_ora_pg.department
)
SELECT dept_empl.*,dept.*
FROM dept_empl, dept
WHERE dept_empl.dept_id = dept.deptid
) LOOP
NULL;
END LOOP;
| CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
`(id, name, surname, lastname, state, dept_id)
AS
(SELECT id, name, surname, lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE state = 1);
CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
`(deptid, parent_id,deptname)
AS
(SELECT id deptid, parent_id, name deptname
FROM test_ora_pg.department);
CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
BEGIN
DECLARE var$ID DOUBLE;
DECLARE var$NAME VARCHAR (30);
DECLARE var$SURNAME VARCHAR (30);
DECLARE var$LASTNAME VARCHAR (30);
DECLARE var$STATE DOUBLE;
DECLARE var$DEPT_ID DOUBLE;
DECLARE var$deptid DOUBLE;
DECLARE var$PARENT_ID DOUBLE;
DECLARE var$deptname VARCHAR (200);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT
dept_empl.*, dept.*
FROM TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
` AS dept_empl,
TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
` AS dept
WHERE dept_empl.DEPT_ID = dept.DEPTID;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$ID, var$NAME, var$SURNAME,
var$LASTNAME, var$STATE, var$DEPT_ID, var$deptid,
var$PARENT_ID, var$deptname;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
call test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
|
| CREATE PROCEDURE
TEST_ORA_PG.P_WITH_SELECT_VAR_CROSS_02(p_state IN NUMBER)
AS
l_dept_id NUMBER := 10;
BEGIN
FOR cur IN (
WITH emp AS
(SELECT id, name, surname,
lastname, state, dept_id
FROM test_ora_pg.dept_employees
WHERE dept_id > 10
),
active_emp AS
(
SELECT id
FROM emp
WHERE emp.state = p_state
)
SELECT *
FROM active_emp
) LOOP
NULL;
END LOOP;
END;
| CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_VAR_CROSS_01$emp
`(id, name, surname, lastname, state, dept_id)
AS
(SELECT
id, name, surname, lastname,
state, dept_id
FROM TEST_ORA_PG.DEPT_EMPLOYEES
WHERE DEPT_ID > 10);
CREATE PROCEDURE
test_ora_pg.P_WITH_SELECT_VAR_CROSS_02(IN par_P_STATE DOUBLE)
BEGIN
DECLARE var_l_dept_id DOUBLE DEFAULT 10;
DECLARE var$ID DOUBLE;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT *
FROM (SELECT
ID
FROM
TEST_ORA_PG.
`P_WITH_SELECT_VAR_CROSS_01$emp` AS emp
WHERE emp.STATE = par_p_state)
AS active_emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN cur;
read_label:
LOOP
FETCH cur INTO var$ID;
IF done THEN
LEAVE read_label;
END IF;
BEGIN
END;
END LOOP;
CLOSE cur;
END;
|