Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the basic knowledge of oracle". In daily operation, I believe many people have doubts about the basic knowledge of oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the basic knowledge of oracle?" Next, please follow the editor to study!

1. Create a tablespace

-- create tablespace CREATE TABLESPACE fund DATAFILE'e:\ ORADATA\ fundd_file.dbf' SIZE 40m

two。 Create a user

-- create a user and password CREATE USER test_userIDENTIFIED BY test123456 DEFAULT TABLESPACE fund

3. Empower users

-- authorizing GRANT CONNECT,RESOURCE TO test_user

4. Create a tabl

-- create tables CREATE TABLE Fund (FundNo VARCHAR2 (20), CompanyId VARCHAR2 (20), FundName VARCHAR2 (20), Price NUMBER (10Power2), FundType NUMBER (1LECO), Invest NUMBER (1LECO), BuyLimit NUMBER (5LECO), IsChange NUMBER (1LJOO), YearRate NUMBER (6LINE5), ApplyDate DATE,State NUMBER (1LL0))

5. Add primary key and foreign key constraints to the table

-- deleting existing primary keys-- ALTER TABLE Fund DROP CONSTRAINT PK_Fund;-- adding primary key constraints ALTER TABLE Fund ADD CONSTRAINT PK_Fund_FundNo PRIMARY KEY (FundNo);-- adding foreign key constraints ALTER TABLE Fund ADD CONSTRAINT FK_Fund_CompanyId FOREIGN KEY (CompanyId) REFERENCES FundCompany (CompanyId)

6. Add comments to tables and fields

-- add comments to the table COMMENT ON TABLE Fund IS 'fund table';-add comments to the table field COMMENT ON COLUMN Fund.FundNo IS 'fund code ID, primary key'; COMMENT ON COLUMN Fund.CompanyId IS 'fund company ID, foreign key refers to the CompanyId';COMMENT ON COLUMN Fund.FundName IS' fund name'of FundCompany table; COMMENT ON COLUMN Fund.Price IS 'fund net worth'; COMMENT ON COLUMN Fund.FundType IS 'fund type, 1 for open-end, 2 for closed-end' COMMENT ON COLUMN Fund.Invest IS 'investment direction, 1 stock, 2 bonds, 3 currencies, 4 mixed'; COMMENT ON COLUMN Fund.BuyLimit IS 'purchase floor'; COMMENT ON COLUMN Fund.IsChange IS 'whether convertible, 0 means non-convertible, 1 means convertible'; COMMENT ON COLUMN Fund.YearRate IS 'annual interest rate, must be a number between 0-1'; COMMENT ON COLUMN Fund.ApplyDate IS 'application date' COMMENT ON COLUMN Fund.State IS 'fund status. 0: normal, 1: frozen'

7. Create function

CREATE OR REPLACE FUNCTION FUNC_NEXTID (I_SQ IN VARCHAR2,I_TITLE IN VARCHAR2,I_LEN IN NUMBER) RETURN VARCHAR2 ASV_SQ VARCHAR2 (100); V_KEY VARCHAR2 (100); BEGINSELECT I_SQ | | '.NEXTVAL' INTO V_SQ FROM DUAL;SELECT I_TITLE | | LPAD (V_SQ, I_LEN, 0) INTO V_KEY FROM DUAL;RETURN video key end

8. Create a sequence

-- create the sequence CREATE SEQUENCE SQ_COMPANYIDINCREMENT BY 1 for the primary key of the fund company table (FundCompany)-- add 1START WITH 1 every time-- NOMAXVALUE from 1-- no maximum NOCYCLE-- always accumulate non-cyclic CACHE 10

9. Create trigger

CREATE OR REPLACE TRIGGER TR_FundBEFORE INSERT ON FundFOR EACH ROWBEGIN:NEW.FUNDNO: = FUNC_NEXTID ('Vince,' SQ_FundNo.NEXTVAL', 6); END

10. Create a package (including functions and procedures in the package body)

Package:

/ *-create package description-package name: FundAccountManager_pack-- function description: the creation process or function is implemented respectively, fund account opening and fund account information query. -- original: Diaoyujun-- date: 2016/3/10--QQ:954739353---*/CREATE OR REPLACE PACKAGE FundAccountManager_pack IS-- current account FUNCTION FUNC_ADD_CURRENTACCOUNT (I_CURRENTPASSWORD VARCHAR2,I_DEPOSITSUM NUMBER,I_CARDTYPE NUMBER,I_CARDNO VARCHAR2) I_NAME VARCHAR2,I_ADDRESS VARCHAR2,I_PHONE VARCHAR2,I_SEX NUMBER,I_OPENACCDATE DATE,I_STATE NUMBER) RETURN NUMBER -- opening a financial account FUNCTION FUNC_ADD_FINANCINGACCOUNT (I_FINANCEPASSWORD VARCHAR2,I_MONEYTYPE NUMBER,I_ACCOUNTBALANCE NUMBER,I_ENABLEBALANCE NUMBER,I_CONGEALFUND NUMBER,I_STATE NUMBER,I_CURRENTACCOUNT VARCHAR2) RETURN NUMBER -- Fund account opening FUNCTION FUNC_ADD_FUNDACCOUNT (I_FINANCINGACCOUNT VARCHAR2,I_COMPANYID VARCHAR2,I_CARDTYPE NUMBER,I_CARDNO VARCHAR2,I_NAME VARCHAR2,I_SEX NUMBER,I_ADDRESS VARCHAR2,I_PHONE VARCHAR2,I_POSTNUM VARCHAR2,I_EMAIL VARCHAR2,I_CREATEDATE DATE,I_CONGEALSTATE NUMBER) RETURN NUMBER -- Fund account information query PROCEDURE PRO_QUERY_FUNDACCOUNT (O_RESULT OUT SYS_REFCURSOR,I_FINANCINGACCOUNT IN VARCHAR2); END FundAccountManager_pack

Package:

CREATE OR REPLACE PACKAGE BODY FundAccountManager_pack IS---- current account opening / *-create package description-package name: FundAccountManager_pack-- function description: creation process or function is realized respectively, fund account opening and fund account information query. Original: Diaoyujun-date: 2016/3/10--QQ:954739353---*/FUNCTION FUNC_ADD_CURRENTACCOUNT (I_CURRENTPASSWORD VARCHAR2,I_DEPOSITSUM NUMBER,I_CARDTYPE NUMBER,I_CARDNO VARCHAR2,I_NAME VARCHAR2,I_ADDRESS VARCHAR2) I_PHONE VARCHAR2,I_SEX NUMBER,I_OPENACCDATE DATE,I_STATE NUMBER) RETURN NUMBER ISERR_CURRENTACCOUNT EXCEPTION PRAGMA EXCEPTION_INIT (ERR_CURRENTACCOUNT,-1); / * the violation of the unique primary key constraint is-1*/BEGININSERT INTO CURRENTACCOUNT (CURRENTPASSWORD,DEPOSITSUM,CARDTYPE,CARDNO,NAME,ADDRESS,PHONE,SEX,OPENACCDATE,STATE) VALUES (IF SQL%FOUND THENRETURN end IF;EXCEPTIONWHEN ERR_CURRENTACCOUNT THENRETURN 0th end OTHERS THENRETURN-1 partner FUNC_ADD_CURRENTACCOUNT). -- FUNCTION FUNC_ADD_FINANCINGACCOUNT (I_FINANCEPASSWORD VARCHAR2,I_MONEYTYPE NUMBER,I_ACCOUNTBALANCE NUMBER,I_ENABLEBALANCE NUMBER,I_CONGEALFUND NUMBER,I_STATE NUMBER,I_CURRENTACCOUNT VARCHAR2) RETURN NUMBER ISERR_FINANCINGACCOUNT EXCEPTION;PRAGMA EXCEPTION_INIT (ERR_FINANCINGACCOUNT,-1); BEGININSERT INTO FINANCINGACCOUNT (FINANCEPASSWORD,MONEYTYPE,ACCOUNTBALANCE,ENABLEBALANCE,CONGEALFUND,STATE,CURRENTACCOUNT) VALUES (iFINANCEPASSWORD) IF SQL%FOUND THENRETURN 1 to end IF;EXCEPTIONWHEN ERR_FINANCINGACCOUNT THENRETURN 0 to when OTHERS THENRETURN-1 to end FUNC_ADD_FINANCINGACCOUNT -- Fund account opening FUNCTION FUNC_ADD_FUNDACCOUNT (I_FINANCINGACCOUNT VARCHAR2,I_COMPANYID VARCHAR2,I_CARDTYPE NUMBER,I_CARDNO VARCHAR2,I_NAME VARCHAR2,I_SEX NUMBER,I_ADDRESS VARCHAR2,I_PHONE VARCHAR2,I_POSTNUM VARCHAR2,I_EMAIL VARCHAR2,I_CREATEDATE DATE,I_CONGEALSTATE NUMBER) RETURN NUMBER ISERR_FUNDACCOUNT EXCEPTION PRAGMA EXCEPTION_INIT (ERR_FUNDACCOUNT,-1); V_COUNT NUMBER;BEGIN-- constraint check SELECT COUNT (FINANCINGACCOUNT) INTO V_COUNTFROM FUNDACCOUNTWHERE FINANCINGACCOUNT = I_FINANCINGACCOUNTAND COMPANYID = I_COMPANYID IF V_COUNT = 0 THENINSERT INTO FUNDACCOUNT (FINANCINGACCOUNT,COMPANYID,CARDTYPE,CARDNO,NAME,SEX,ADDRESS,PHONE,POSTNUM,EMAIL,CREATEDATE,CONGEALSTATE) VALUES (Illustrated FINANCINGACCOUNT) VALUES (Illustrated CARDTYPE, IF;EXCEPTIONWHEN ERR_FUNDACCOUNT THENRETURN ONONONONONONONONONONONONONONONONONONONONONEAMEN); IF SQL%FOUND THENRETURN 1 position IF;ELSERETURN-1 position IF;EXCEPTIONWHEN ERR_FUNDACCOUNT THENRETURN 0 when OTHERS THENRETURN-1 end FUNC_ADD_FUNDACCOUNT -- the query of the fund account PROCEDURE PRO_QUERY_FUNDACCOUNT (O_RESULT OUT SYS_REFCURSOR,I_FINANCINGACCOUNT IN VARCHAR2) ASBEGINOPEN O_RESULT FORSELECT * FROM FUNDACCOUNT TWHERE T.FINANCINGACCOUNT = end PRO_QUERY_FUNDACCOUNT;END FundAccountManager_pack;, the study of "what are the basics of oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.