1.å¦ä½ä½¿ç¨Oracleåå¨è¿ç¨çä¸ä¸ªç®åä¾å
å¦ä½ä½¿ç¨Oracleåå¨è¿ç¨çä¸ä¸ªç®åä¾å
楼主æ¨å¥½
---å建表
create table TESTTABLE
(
id1 VARCHAR2(),存储存储查询app 展示源码
name VARCHAR2()
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');
insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');
insert into TESTTABLE (ID1, NAME)
values ('3', 'wangwu');
insert into TESTTABLE (ID1, NAME)
values ('4', 'xiaoliu');
insert into TESTTABLE (ID1, NAME)
values ('5', 'laowu');
---å建åå¨è¿ç¨
create or replace procedure test_count
as
v_total number(1);
begin
select count(*) into v_total from TESTTABLE;
DBMS_OUTPUT.put_line('æ»äººæ°ï¼'||v_total);
end;
--åå¤
--线对scott解éï¼alter user scott account unlock;
--åºä¸ºåå¨è¿ç¨æ¯å¨scottç¨æ·ä¸ãè¿è¦ç»scottèµäºå¯ç
---alter user scott identified by tiger;
---å»å½ä»¤ä¸æ§è¡
EXECUTE test_count;
----å¨ql/splä¸çsqlä¸æ§è¡
begin
-- Call the procedure
test_count;
end;
create or replace procedure TEST_LIST
AS
---æ¯ç¨æ¸¸æ
CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;
begin
for Test_record IN test_cursor loop---éå游æ ï¼å¨æå°åºæ¥
DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);
END LOOP;
test_count;--åæ¶æ§è¡å¦å¤ä¸ä¸ªåå¨è¿ç¨ï¼TEST_LISTä¸å å«åå¨è¿ç¨test_countï¼
end;
-----æ§è¡åå¨è¿ç¨TEST_LIST
begin
TEST_LIST;
END;
---åå¨è¿ç¨çåæ°
---IN å®ä¹ä¸ä¸ªè¾å ¥åæ°åéï¼ç¨äºä¼ éåæ°ç»åå¨è¿ç¨
--OUT å®ä¹ä¸ä¸ªè¾åºåæ°åéï¼ç¨äºä»åå¨è¿ç¨è·åæ°æ®
---IN OUT å®ä¹ä¸ä¸ªè¾å ¥ãè¾åºåæ°åéï¼å ¼æ以ä¸ä¸¤è çåè½
--è¿ä¸ç§åæ°åªè½è¯´æç±»åï¼ä¸éè¦è¯´æå ·ä½é¿åº¦ æ¯å¦ varchar2()ï¼defaul å¯ä»¥ä¸åï¼ä½æ¯ä½ä¸ºä¸ä¸ªç¨åºåæ好è¿æ¯åä¸ã
---å建æåæ°çåå¨è¿ç¨
create or replace procedure test_param(p_id1 in VARCHAR2 default '0')
as v_name varchar2();
begin
select t.name into v_name from TESTTABLE t where t.id1=p_id1;
DBMS_OUTPUT.put_line('nameï¼'||v_name);
end;
----æ§è¡åå¨è¿ç¨
begin
test_param('1');
end;
default '0'
---å建æåæ°çåå¨è¿ç¨
create or replace procedure test_paramout(v_name OUT VARCHAR2 )
as
begin
select name into v_name from TESTTABLE where id1='1';
DBMS_OUTPUT.put_line('nameï¼'||v_name);
end;
----æ§è¡åå¨è¿ç¨
DECLARE
v_name VARCHAR2();
BEGIN
test_paramout(v_name);
DBMS_OUTPUT.PUT_LINE('nameï¼'||v_name);
END;
-------IN OUT
---å建åå¨è¿ç¨
create or replace procedure test_paramINOUT(p_phonenumber in out varchar2)
as
begin
p_phonenumber:='-'||p_phonenumber;
end;
----
DECLARE
p_phonenumber VARCHAR2();
BEGIN
p_phonenumber:='';
test_paramINOUT(p_phonenumber);
DBMS_OUTPUT.PUT_LINE('æ°ççµè¯å·ç ï¼'||p_phonenumber);
END;
-----sqlå½ä»¤ä¸ï¼æ¥è¯¢å½åç¨æ·çåå¨è¿ç¨æå½æ°çæºä»£ç ï¼
-----å¯ä»¥éè¿å¯¹USER_SOURCEæ°æ®åå ¸è§å¾çæ¥è¯¢å¾å°ãUSER_SOURCEçç»æå¦ä¸ï¼
SQL> DESCRIBE USER_SOURCE ;
Name Type Nullable Default Comments
---- -------------- -------- -------
-------------------------------------------------------------------------------------------------------------
NAME VARCHAR2() Y Name of the object
TYPE VARCHAR2() Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of
source
TEXT VARCHAR2() Y Source text
SQL>
---æ¥è¯¢åºåå¨è¿ç¨çå®ä¹è¯å¥
select text from user_source WHERE NAME='TEST_COUNT';
----æ¥è¯¢åå¨è¿ç¨test_paramINOUTçåæ°
SQL> DESCRIBE test_paramINOUT;
Parameter Type Mode Default?
------------- -------- ------ --------
P_PHONENUMBER VARCHAR2 IN OUT
SQL>
---æ¥çå½åçåå¨è¿ç¨çç¶ææ¯å¦æ£ç¡®ï¼
---VALID为æ£ç¡®ï¼INVALID表示åå¨è¿ç¨æ ææéè¦éæ°ç¼è¯
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='TEST_COUNT';
-----å¦æè¦æ£æ¥åå¨è¿ç¨æå½æ°çä¾èµæ§ï¼å¯ä»¥éè¿æ¥è¯¢æ°æ®åå ¸USER_DENPENDENCIESæ¥ç¡®å®ï¼è¯¥è¡¨ç»æå¦ä¸ï¼
SQL> DESCRIBE USER_DEPENDENCIES;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME VARCHAR2() Name of the object
TYPE VARCHAR2() Y Type of the object
REFERENCED_OWNER VARCHAR2() Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2() Y Name of referenced object
REFERENCED_TYPE VARCHAR2() Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2() Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2(4) Y
SQL>
---æ¥è¯¢åå¨è¿ç¨TEST_COUNTçä¾èµå ³ç³»
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='TEST_COUNT';