ejyoo's 개발 노트

#테이블과 인덱스 생성 CREATE TABLE usertable_many(userid VARCHAR2(20), password VARCHAR2(20), nickName VARCHAR2(20), email VARCHAR2(30), class VARCHAR2(30), ages VARCHAR2(30)); CREATE INDEX idx_userid ON usertable_many(userid); #PL/SQL DECLARE TYPE tbl_ins IS TABLE OF usertable_many%ROWTYPE INDEX BY BINARY_INTEGER; w_ins tbl_ins; BEGIN FOR i IN 1..14000 LOOP w_ins(i).userid :=i; w_ins(i).password :=i; w_..

CREATE TABLE "DB계정(예:JSP)"."QNA" ("QNO" NUMBER NOT NULL ENABLE, "QTYPE" VARCHAR2(30 BYTE), "TITLE" VARCHAR2(300 BYTE) NOT NULL ENABLE, "WRITER" VARCHAR2(50 BYTE) NOT NULL ENABLE, "CONTENT" VARCHAR2(3200 BYTE) NOT NULL ENABLE, "PICTURE" VARCHAR2(200 BYTE), "REGDATE" DATE, "VIEWCNT" NUMBER DEFAULT 0, "ISANSWER" VARCHAR2(2 BYTE) DEFAULT 'N', "ISDEL" VARCHAR2(2 BYTE) DEFAULT 'N', PRIMARY KEY ("QNO")..

create user JSP identified by JSP; grant connect, resource, dba to JSP;

https://offbyone.tistory.com/162

/ SET SERVEROUTPUT ON; / CREATE OR REPLACE PROCEDURE PROC_UP_USER_DDIT_ALL(P_ID IN VARCHAR2, P_BIR IN DATE, P_HP IN VARCHAR2, P_MAIL IN VARCHAR2, P_RECV_MAIL IN VARCHAR2, P_PHOTO IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(P_ID || ', ' || P_BIR || ', ' || P_HP || ', ' || P_MAIL || ', ' || P_RECV_MAIL || ', ' || P_PHOTO); UPDATE TB_USER_DDIT SET DDIT_BIR = P_BIR, DDIT_HP = P_HP, DDIT_MAIL = P_MAIL..

UPDATE ( SELECT A.DDIT_BIR dditBir ,A.DDIT_HP dditHp ,A.DDIT_MAIL dditMail ,A.RECV_MAIL recvMail ,B.USER_PHOTO userPhoto FROM TB_USER_DDIT A, TB_USER_ALL B WHERE A.USER_ID= B.USER_ID ) SET dditBir = '21/04/29';

-- 메뉴, 1,2,3,4 SELECT A.REST_NAME restName -- 식당이름 ,A.REST_FAV_CNT restFavCnt -- 식당평점 ,A.REST_ADD1 restAdd1 -- 식당주소1 ,A.REST_ADD2 restAdd2 -- 식당주소2 ,A.REST_REVIEW_CNT restReviewCnt -- 리뷰점수 ,A.REST_DISTANCE restDistance -- 거리 ,A.REST_TYPE restType -- 분류 ,A.REST_CODE redtCode ,B.MENU_NAME menuName --메뉴이름 ,B.MENU_PRICE menuPrice --메뉴 가격 FROM TB_REST_INFO A INNER JOIN TB_MENU B ON(A.REST_CODE = B.RE..

SELECT A.REST_NAME restName ,A.REST_FAV_CNT restFavCnt ,A.REST_CEO_NAME restCeoName ,A.REST_ADD1 restAdd1 ,A.REST_ADD2 restAdd2 ,A.REST_REVIEW_CNT restReviewCnt ,A.REST_DISTANCE restDistance ,A.REST_TYPE restType ,A.REST_CODE restCode ,B.MENU_NAME menuName ,B.MENU_PRICE menuPrice FROM TB_REST_INFO A INNER JOIN TB_MENU B ON(A.REST_CODE = B.REST_CODE) WHERE 1=1 A.REST_DISTANCE = #restDistance# A.R..

SELECT A.REST_NAME restName -- 식당이름 ,A.REST_FAV_CNT restFavCnt -- 식당평점 ,A.REST_ADD1 restAdd1 -- 식당주소1 ,A.REST_ADD2 restAdd2 -- 식당주소2 ,A.REST_REVIEW_CNT restReviewCnt -- 리뷰점수 ,A.REST_DISTANCE restDistance -- 거리 ,A.REST_TYPE restType -- 분류 ,A.REST_CODE redtCode ,B.MENU_NAME menuName --메뉴이름 ,B.MENU_PRICE menuPrice --메뉴 가격 FROM TB_REST_INFO A INNER JOIN TB_MENU B ON(A.REST_CODE = B.REST_CODE) WHERE ..

테이블이 같을 ㄷ때 insert 방법 ++ 엑셀에 데이터를 만든 다음 쿼리 작성할 것 INSERT INTO TB_MENU (REST_CODE, MENU_SEQ, MENU_NAME, MENU_PRICE, ATCH_FILE_ID) SELECT 202112311200, 1, '엉터리김밥', '5000', '1' FROM DUAL UNION ALL SELECT 202112311200, 2, '치즈라면', '6000', '1' FROM DUAL UNION ALL SELECT 202112311200, 3, '안심돈까스', '7000', '1' FROM DUAL; 테이블이 다를때 insert 방법 ++ INSERT ALL INTO TB_USER_ALL( USER_ID , USER_PW , USER_TYPE , USE..